bossing.pdf

1. Find the names of the ingredients supplied by a particular supplier. SELECT name FROM ingredients WHERE supplierid = 'S001'; 2. Find the name of all ingredients supplied by two suppliers. SELECT name FROM ingredients WHERE supplierid = 'S001' OR supplierid = 'S002'; 3. Find the company name o...

1. Find the names of the ingredients supplied by a particular supplier. SELECT name FROM ingredients WHERE supplierid = 'S001'; 2. Find the name of all ingredients supplied by two suppliers. SELECT name FROM ingredients WHERE supplierid = 'S001' OR supplierid = 'S002'; 3. Find the company name of all suppliers who provided an ingredient with an inventory of fewer than 50. SELECT company_name FROM suppliers WHERE supplierid IN ( SELECT supplierid FROM ingredients WHERE inventory < 50 ); 4. Find the average unit price for all items provided by a supplier. sql SELECT AVG(unitprice) AS avg_unit_price FROM ingredients WHERE supplierid = 'S001'; 5. Find the names of all suppliers referred to by another supplier. SELECT company_name FROM suppliers WHERE referred_by = 'S001'; 6. Find all the ingredients with an inventory within 25% of the average inventory of ingredients. SELECT name FROM ingredients WHERE inventory BETWEEN ( (SELECT AVG(inventory) FROM ingredients) * 0.75 ) AND ( (SELECT AVG(inventory) FROM ingredients) * 1.25 ); 7. Find the companies who were referred by a supplier and provide an ingredient in the milk food group. SELECT company_name FROM suppliers WHERE referred_by = 'S004' AND supplierid IN ( SELECT supplierid FROM ingredients WHERE ingredientid IN (  SELECT ingredientid FROM ingredients WHERE name = 'Milk' ) ); 8. Find the name and price for all items using an ingredient supplied by a supplier. SELECT i.name, i.price FROM items i JOIN madewith mw ON i.itemid = mw.itemid JOIN ingredients ing ON mw.ingredientid = ing.ingredientid WHERE ing.supplierid = 'S001'; 9. Find the names and inventory value for all ingredients with an inventory value greater than the total inventory value of all ingredients provided by a supplier. SELECT name, (inventory * unitprice) AS inventory_value FROM ingredients WHERE (inventory * unitprice) > ( SELECT SUM(inventory * unitprice) FROM ingredients WHERE supplierid = 'S001' ); 10. Find all ingredients supplied by someone other than a particular supplier. SELECT name FROM ingredients WHERE supplierid != 'S001'; 11. Find the company name of small suppliers who do not provide any ingredients with large (>100) inventories. SELECT company_name FROM suppliers WHERE supplierid NOT IN ( SELECT supplierid FROM ingredients WHERE inventory > 100 ); 12. Find the name and price of all items using an ingredient supplied by a particular supplier. SELECT i.name, i.price FROM items i JOIN madewith mw ON i.itemid = mw.itemid JOIN ingredients ing ON mw.ingredientid = ing.ingredientid WHERE ing.supplierid = 'S001'; 13. Find all items that have a price greater than any salad item. SELECT name FROM items WHERE price > ( SELECT MAX(price) FROM items WHERE name LIKE '%salad%' ); 14. Find the name of all ingredients supplied by two different suppliers. SELECT name FROM ingredients WHERE supplierid IN ('S001', 'S002'); 15. Find all ingredients not supplied by two different suppliers. SELECT name FROM ingredients WHERE supplierid NOT IN ('S001', 'S002'); 16. Find the most expensive items. SELECT name FROM items WHERE price = ( SELECT MAX(price) FROM items ); 17. Find the items that contain 3 or more ingredients. SELECT i.name FROM items i JOIN madewith mw ON i.itemid = mw.itemid GROUP BY i.itemid HAVING COUNT(mw.ingredientid) >= 3; 18. Find the number of ingredients in a particular item. SELECT COUNT(mw.ingredientid) AS ingredient_count FROM items i JOIN madewith mw ON i.itemid = mw.itemid WHERE i.name = 'Tomato Soup'; 19. Find all suppliers who referred two or more suppliers. SELECT company_name FROM suppliers WHERE supplierid IN ( SELECT referred_by FROM suppliers GROUP BY referred_by HAVING COUNT(supplierid) >= 2 ); 20. Find the meals containing an ingredient from a specific food group. SELECT m.name FROM meals m JOIN partof p ON m.mealid = p.mealid JOIN items i ON p.itemid = i.itemid JOIN madewith mw ON i.itemid = mw.itemid JOIN ingredients ing ON mw.ingredientid = ing.ingredientid WHERE ing.foodgroup = 'Grains'; 21. Find all suppliers who did not recommend any other vendor. SELECT company_name FROM suppliers WHERE supplierid NOT IN ( SELECT referred_by FROM suppliers WHERE referred_by IS NOT NULL ); 22. List the name and inventory value of each ingredient in two food groups. SELECT name, (inventory * unitprice) AS inventory_value FROM ingredients WHERE foodgroup IN ('Grains', 'Dairy') 23. Find all suppliers who provided more ingredients than a specific supplier. SELECT company_name FROM suppliers WHERE supplierid IN ( SELECT supplierid FROM ingredients GROUP BY supplierid HAVING COUNT(ingredientid) > ( SELECT COUNT(ingredientid) FROM ingredients WHERE supplierid = 'S001' ) ); 24. Find the average inventory values for each supplier who recommends at least one different supplier. SELECT s.company_name, AVG(ing.inventory) AS avg_inventory FROM suppliers s JOIN ingredients ing ON s.supplierid = ing.supplierid WHERE s.supplierid IN ( SELECT supplierid FROM suppliers WHERE referred_by IS NOT NULL ) GROUP BY s.company_name;