Question

CS 203 Discrete Structure 2

Create a Microsoft Access Database consisting of the following two tables: Part_needs and Parts_Inventory

Can you provide the database and query on Microsoft access?

Part_needs
Supplier	Part_number	Project
23	1092	1
23	1101	3
23	9048	4
31	4975	3
31	3477	2
32	6984	4
32	9191	2
33	1001	1

Parts_Inventory
Part_number	Project	Quantity	Color_code
1001	1	14	8
1092	1	2	2
1101	3	1	1
3477	2	25	2
4975	3	6	2
6984	4	10	1
9048	4	12	2
9191	2	80	4

Answer the following questions:

1. What are the operations that correspond to the query expressed using this SQL statement?

SELECT Supplier

FROM Part_needs

WHERE 1000<= Part_number <= 5000

2. What is the result of the corresponding query, created using Access Database given Part_needs table as input?

3. What are the operations that correspond to the query expressed using this SQL statement?

SELECT Supplier, Project                                                                                                                                

FROM Part_needs, Parts_inventory

WHERE Quantity <= 10

4. What is the output of the corresponding query created ,using Microsoft Access Database t given Part_needs, and Parts_inventory

Answer 1

Please find below answer for above 2 queries and Don't forget to give a Like.

1) Operations used for this query is SELECT, FROM, WHERE

Result of query 1:

Supplier	Project
23	1
23	3
31	3
31	2
33	1

2)Operations used for this query is SELECT, FROM, WHERE

Result of query 2:

ambiguous column name: project

Since we are selecting project from 2 tables. And these 2 tables have column named project. So, we need to use objects.

Use below query to get the result.

select pn.suplier, pn.project from part_needs pn, parts_inventory pi

where quantity <=10;

we created two object pn and pi for two tables. We need to select from which table the project details should be displayed.

Thank you.