Assignment 3
docx
keyboard_arrow_up
School
New Jersey Institute Of Technology *
*We aren’t endorsed by this school
Course
631
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
docx
Pages
4
Uploaded by CaptainPencilCobra39
Assignment 3
1. Find the SIDs of suppliers who supply a red part and a green part.
SELECT DISTINCT C1.SID
FROM CATALOG C1
JOIN CATALOG C2 ON C1.SID = C2.SID
JOIN PARTS P1 ON C1.PID = P1.PID
JOIN PARTS P2 ON C2.PID = P2.PID
WHERE P1.COLOR = 'red' AND P2.COLOR = 'green';
2.
Find the SIDs of suppliers who supply a red part or a green part. SELECT DISTINCT C.SID
FROM CATALOG C
JOIN PARTS P ON C.PID = P.PID
WHERE P.COLOR = 'red' OR P.COLOR = 'green';
3. Find the SNAMEs of suppliers who supply every red part and every green part. SELECT S.SNAME
FROM SUPPLIERS S
WHERE NOT EXISTS (
SELECT P.PID
FROM PARTS P
WHERE P.COLOR = 'red'
EXCEPT
SELECT C.PID
FROM CATALOG C
WHERE C.SID = S.SID
)
AND NOT EXISTS (
SELECT P.PID
FROM PARTS P
WHERE P.COLOR = 'green'
EXCEPT
SELECT C.PID
FROM CATALOG C
WHERE C.SID = S.SID
);
4.
Find the SNAMEs of suppliers who do not supply every red part. SELECT DISTINCT S.SNAME
FROM SUPPLIERS S
WHERE EXISTS (
SELECT P.PID
FROM PARTS P
WHERE P.COLOR = 'red'
EXCEPT
SELECT C.PID
FROM CATALOG C
WHERE C.SID = S.SID
);
5.
For every supplier that only supplies red parts, print the SID and the name of the supplier and the average cost of parts that she supplies. SELECT S.SID, S.SNAME, AVG(C.COST) AS AVERAGE_COST
FROM SUPPLIERS S
JOIN CATALOG C ON S.SID = C.SID
JOIN PARTS P ON C.PID = P.PID
WHERE P.COLOR = 'red'
GROUP BY S.SID, S.SNAME
HAVING COUNT(DISTINCT P.PID) = 1;
6. For each part, find the SNAMEs of the suppliers who do not charge the most for that part. The answer of this query should have two columns: PID and SNAME. SELECT DISTINCT c1.PID, s.SNAME
FROM CATALOG c1
JOIN SUPPLIERS s ON c1.SID = s.SID
WHERE c1.COST < (
SELECT MAX(c2.COST)
FROM CATALOG c2
WHERE c2.PID = c1.PID
)
ORDER BY c1.PID, s.SNAME;
7.
For every part supplied by a supplier who is at the city of Newark, print the PID and the SID and the name of the suppliers who sell it at the highest price. SELECT c.PID, c.SID, s.SNAME
FROM CATALOG c
JOIN SUPPLIERS s ON c.SID = s.SID
WHERE s.CITY = 'Newark' AND c.COST = (
SELECT MAX(c2.COST)
FROM CATALOG c2
WHERE c2.PID = c.PID
)
8.
For every part which has at least two suppliers, find its PID, its PNAME and the total number of suppliers who sell it. SELECT p.PID, p.PNAME, COUNT(c.SID) AS SupplierCount
FROM PARTS p
JOIN CATALOG c ON p.PID = c.PID
GROUP BY p.PID, p.PNAME
HAVING COUNT(c.SID) >= 2
9.
Find the PIDs of parts supplied by every supplier who is at the city of Newark or by every supplier who is at the city of Trenton. SELECT PID
FROM PARTS
WHERE NOT EXISTS (
SELECT *
FROM SUPPLIERS
WHERE CITY IN ('Newark', 'Trenton') AND NOT EXISTS (
SELECT *
FROM CATALOG
WHERE CATALOG.SID = SUPPLIERS.SID AND CATALOG.PID = PARTS.PID
)
)
10.
Find the PIDs of parts supplied by every supplier who is at the city of Newark and by every supplier who is at the city of Trenton. SELECT PID
FROM PARTS
WHERE NOT EXISTS (
SELECT *
FROM SUPPLIERS
WHERE CITY = 'Newark' AND NOT EXISTS (
SELECT *
FROM CATALOG
WHERE CATALOG.SID = SUPPLIERS.SID AND CATALOG.PID = PARTS.PID
)
) AND NOT EXISTS (
SELECT *
FROM SUPPLIERS
WHERE CITY = 'Trenton' AND NOT EXISTS (
SELECT *
FROM CATALOG
WHERE CATALOG.SID = SUPPLIERS.SID AND CATALOG.PID = PARTS.PID
)
)
11.
Find the SIDs of suppliers who supply a red part but do not supply a blue part. SELECT DISTINCT s.SID
FROM SUPPLIERS s
JOIN CATALOG c ON s.SID = c.SID
JOIN PARTS p ON c.PID = p.PID
WHERE p.COLOR = 'Red'
AND s.SID NOT IN (
SELECT s2.SID
FROM SUPPLIERS s2
JOIN CATALOG c2 ON s2.SID = c2.SID
JOIN PARTS p2 ON c2.PID = p2.PID
WHERE p2.COLOR = 'Blue'
)
12.
For every supplier who supplies at least 4 parts, find his SID, SNAME and the PID of the most expensive part(s) that he supplies. SELECT s.SID, s.SNAME, c.PID
FROM SUPPLIERS s
JOIN CATALOG c ON s.SID = c.SID
WHERE c.COST = (
SELECT MAX(c2.COST)
FROM CATALOG c2
WHERE c2.SID = s.SID
)
AND (
SELECT COUNT(DISTINCT c3.PID)
FROM CATALOG c3
WHERE c3.SID = s.SID
) >= 4
13
. For every distinct color of the parts, find the total number of suppliers who supply a part of this color. SELECT p.COLOR, COUNT(DISTINCT c.SID) AS SupplierCount
FROM PARTS p
JOIN CATALOG c ON p.PID = c.PID
GROUP BY p.COLOR
14.
Find the SIDs of suppliers who supply at least two parts of different color. SELECT s.SID
FROM SUPPLIERS s
JOIN CATALOG c ON s.SID = c.SID
JOIN PARTS p ON c.PID = p.PID
GROUP BY s.SID
HAVING COUNT(DISTINCT p.COLOR) >= 2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
15. For every part which has a supplier, find its PID, PNAME, its average cost, maximum cost and minimum cost.
SELECT p.PID, p.PNAME, AVG(c.COST) AS AvgCost, MAX(c.COST) AS MaxCost, MIN(c.COST) AS MinCost
FROM PARTS p
JOIN CATALOG c ON p.PID = c.PID
GROUP BY p.PID, p.PNAME