Reference no: EM133302137
Data and Information Management Relational Data Retrieval: SQL
Practice Questions
S (SUPPLIER) P (PART): Stock Info
|
S#
|
SNAME
|
CITY(location)
|
|
P#
|
PNAME
|
UNIT
|
COST
|
|
S1
|
Smith
|
London
|
P1
|
Screw
|
10
|
$100
|
|
S2
|
Jones
|
Rome
|
P2
|
Hinge
|
20
|
$150
|
|
S3
|
Lee
|
Tokyo
|
P3
|
Pin
|
30
|
$200
|
|
S4
|
Poston
|
Rome
|
P4
|
Nail
|
40
|
$250
|
|
S5
|
Zhang
|
London
|
|
|
|
|
|
|
SP
|
|
|
|
POP-INFO
|
|
|
|
|
SNUM
|
PNUM
|
|
Location
|
Population
|
|
|
|
S1
|
P1
|
London
|
2M
|
|
|
S1
|
P3
|
Rome
|
1.5M
|
|
|
S2
|
P1
|
Tokyo
|
1M
|
|
|
S2
|
P2
|
Paris
|
2.5M
|
|
|
S1
|
P2
|
|
|
|
S3
|
P1
|
|
|
S1
|
P4
|
|
|
a) "SP" is used to keep track of which supplier provides which part(s).
b) "Pop-info" is used to keep track of population of city.
- Try to come up with your own SQL expression for the following questions and then compare yours with the answers on the last pages.
Question I: Working with only one relation
- List the names and locations (CITY) of all suppliers.
- List the supplier-number (S#) and names of suppliers (SNAME) located in "Rome"
- List the current available number of units (UNIT) and price (COST) of "P2" are available?
- Which parts (PNUM) does supplier "S1" provide?
- Which suppliers (SNUM) does provide "P4"?
5.1. a) Which part is most expensive? List the highest (MAX) unit price of all parts we currently have.
b) List the part number (P#) and part name (PNAME) of the most expensive one.
5.2. a) What is the average (AVG) unit cost of all parts we currently have?
b) List the part number (P#) and part name (PNAME) of the parts that cost less than the average unit cost?
Question II: Joining two tables
6. List the suppliers (SNUM) and parts (PNUM) information along with exact part names.
7. List the suppliers (SNUM) and parts (PNUM) information along with suppliers' names and locations (cities).
----- For joining condition and additional search criteria -------
8. Find the exact names of part(s) supplied by "S1."
9. Find the locations (cities) of suppliers who supply "P1."
10. Find the locations (cities) of suppliers who supply "P2" and the populations of the locations (cities).
Question III. Working with Set Operators (UNION, INTERSECTION, EXCEPT) and EXITS
11. Which suppliers (SNUM) do provide P1 or P2?
list the supplier-numbers (SNUM) of the suppliers.
list the supplier-numbers (SNUM) and names (SNAME) of the suppliers.
12.1. Which suppliers (SNUM) do provide both P1 and P2?
a) list the supplier-numbers (SNUM) of the suppliers.
b) list the supplier-numbers (SNUM) and names (SNAME) of the suppliers.
12.2. Which suppliers (SNUM) do provide P1, P2, and P3? Display the supplier number (SNUM) and names (SNAME) of such suppliers.
12.3. Which suppliers (SNUM) are located in "Rome," and do provide both P1 and P2?
a) list the supplier-numbers (SNUM) of the suppliers.
b) list the supplier-numbers (SNUM) and names (SNAME) of the suppliers.
12.4. Which suppliers (SNUM) do provide ALL parts we currently have? Display the supplier number (SNUM) and supplier name (SNAME)? (Use A SQL statement for a DIVISION operator in relational algebra)
SELECT S#, SNAME FROM S
WHERE NOT EXISTS (
(SELECT P.P# FROM P)
EXCEPT
(SELECT SP.PNUM FROM SP WHERE SP.SNUM = S.S#) );
13. Are there any suppliers who do not provide any part currently? If so, list the supplier-numbers (SNUM) and names (SNAME) of the suppliers.
14. Are there any suppliers who provides any part that costs more than $120 ?
a) If so, list the supplier-numbers (SNUM) of the suppliers.
b) If so, list the supplier-numbers (SNUM) and names (SNAME) of the suppliers
Question IV. Working with "Group By" and "Having"
15. For each supplier (SNUM), display the number of parts they provide.
16. Are there any suppliers (SNUM) who provides more than two parts?
a. If so, list the supplier-numbers (SNUM) of the suppliers.
b. If so, list the supplier-numbers (SNUM) and names (SNAME) of the suppliers