Untitled-1
txt
keyboard_arrow_up
School
Washington State University *
*We aren’t endorsed by this school
Course
490
Subject
Industrial Engineering
Date
Feb 20, 2024
Type
txt
Pages
3
Uploaded by Titanninja
Let's tackle each query one by one:
1. Find all distinct parts supplied by Pullman stores. Return "pid"s of those parts.
SELECT DISTINCT Catalog.pid
FROM Catalog
JOIN Suppliers ON Catalog.sid = Suppliers.sid
WHERE Suppliers.city = 'Pullman';
```
2. Find the suppliers in Pullman who supply a yellow part for less than $11. Return
"sid"s and names for those suppliers.
SELECT sid, sname
FROM Suppliers
WHERE city = 'Pullman'
AND sid IN (
SELECT sid
FROM Catalog
JOIN Parts ON Catalog.pid = Parts.pid
WHERE color = 'yellow' AND price < 11
);
3. Find all parts which are provided by some supplier (i.e., they appear in the catalog) but they were never ordered by a customer. Return the "pid"s and names of those parts.
SELECT Parts.pid, pname
FROM Parts
LEFT JOIN Catalog ON Parts.pid = Catalog.pid
WHERE Catalog.pid IS NOT NULL
AND Catalog.pid NOT IN (
SELECT pid
FROM Orders
);
4. Find all customers who ordered one of the products that Andy ordered. Return names and cities of those customers and the "pid"s of the products they ordered.
SELECT DISTINCT Customers.cname, Customers.city, Orders.pid
FROM Orders
JOIN Customers ON Orders.cid = Customers.cid
WHERE Orders.pid IN (
SELECT pid
FROM Orders
JOIN Customers ON Orders.cid = Customers.cid
WHERE cname = 'Andy'
);
5. Find the suppliers who have received orders from customers who live in the city where that supplier is located. Return "sid"s, names, and cities of those suppliers.
SELECT DISTINCT Suppliers.sid, Suppliers.sname, Suppliers.city
FROM Suppliers
JOIN Orders ON Suppliers.sid = Orders.sid
JOIN Customers ON Orders.cid = Customers.cid
WHERE Suppliers.city = Customers.city;
6. Find customers who ordered more than 2 items from a single supplier (i.e., sum of the quantities of all parts customer purchased from the supplier is at least 3).
Return names of the customers, "sid"s of the suppliers, and number of items they ordered.
SELECT Customers.cname, Orders.sid, SUM(Orders.qty) AS total_items_ordered
FROM Orders
JOIN Customers ON Orders.cid = Customers.cid
GROUP BY Customers.cname, Orders.sid
HAVING SUM(Orders.qty) > 2;
7. Find the customer "cid"s who paid more than $20 on 'green' parts.
SELECT DISTINCT Orders.cid
FROM Orders
JOIN Catalog ON Orders.pid = Catalog.pid AND Orders.sid = Catalog.sid
JOIN Parts ON Orders.pid = Parts.pid
WHERE Parts.color = 'green' AND Orders.qty * Catalog.price > 20;
8. Find the parts which are supplied by at least 2 different suppliers. Return the "pid"s, names, and colors of those parts.
SELECT Parts.pid, Parts.pname, Parts.color
FROM Parts
JOIN Catalog ON Parts.pid = Catalog.pid
GROUP BY Parts.pid, Parts.pname, Parts.color
HAVING COUNT(DISTINCT Catalog.sid) >= 2;
9. For each part in the catalog find the supplier that offers the lowest price; give the part name, supplier "sid" and the price supplier sells the part for.
SELECT Parts.pname, Catalog.sid, MIN(Catalog.price) AS lowest_price
FROM Catalog
JOIN Parts ON Catalog.pid = Parts.pid
GROUP BY Parts.pname, Catalog.sid;
10. Find the number of suppliers in each city.
SELECT city, COUNT(sid) AS num_suppliers
FROM Suppliers
GROUP BY city;
These SQL queries should help you get the desired results for each question.
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