Question

Database - SQL - Operations

CS 203 Discrete Structure 2

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

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

(a) SELECT Supplier

FROM Part_needs

WHERE 1000<= Part_number <= 5000

In this query, it selects supplier column and throw as output to the above query.

That is, it gets supplier column as output from part_need Table whose Part_number is Less than or Equal to 5000.

Output:

Supplier

23

31

33

(b) SELECT Supplier, Project                                                                                                                                

FROM Part_needs, Parts_inventory

WHERE Quantity <= 10

In this query, it selects Supplier and project columns and throw as output to the above query.

That is, it gets Supplier and Project columns in Part_needs and Parts_inventory tables whose Quantity is Less than or Equal to 10.

Output:

Supplier Project

23 1

23 3

31 3