How to use JOIN in SQL table TABLES:

tblingredients


tblitems


tblmadewith


tblmenuitems


tblorders


tblpartof


tblslogan


tblstores


tblvendors


tblmeals


SAMPLE QUERIES:

1.For each ingrdient, find its name and the name and ID of the vendor that supplies it


SQL statement:
SELECT tblvendors.vendorid, name, companyname FROM tblingredients, tblvendors WHERE tblingredients.vendorid=tblvendors.vendorid;

2.Find the names of the ingredients supplied to us by Veggies_R_Us


SQL statement:
SELECT name FROM tblingredients, tblvendors WHERE tblingredients.vendorid=tblvendors.vendorid And companyname='Veggies_R_Us';

3.Find the store ID and price of all orders made in our California stores


SQL statement:
SELECT tblstores.storeid, price FROM tblorders, tblstores WHERE tblorders.storeid = tblstores.storeid AND state = 'CA';

4. find the total sales by state


SQL statement:
SELECT state, SUM(price) FROM tblstores, tblorders WHERE tblstores.storeid = tblorders.storeid GROUP BY state;

5. Find the name of all ingredients supplied by Veggies_R_Us or Spring Water Supply


SQL statement:
SELECT v.vendorid AS ["VENDOR ID"], name FROM tblingredients AS i, tblvendors AS v WHERE i.vendorid=v.vendorid And v.companyname In ('Veggies_R_Us','Spring Water Supply') ORDER BY v.vendorid;

6. Find the name and price of all items using an ingredients supplied by Veggies_R_Us


SQL statement:
SELECT DISTINCT mi.name, price FROM tblingredients AS i, tblvendors AS v, tblitems AS mi, tblmadewith AS mw WHERE i.vendorid=v.vendorid AND i.ingredientid=mw.ingredientid AND mw.itemid=mi.itemid AND companyname='Veggies_R_Us';

7. Find the cost of the most expensive item that uses an ingredient supplied by each vendor


SQL statement:
SELECT companyname, MAX(price) AS presyo FROM tblingredients AS i, tblvendors AS v, tblitems AS mi, tblmadewith AS mw WHERE i.vendorid=v.vendorid And i.ingredientid=mw.ingredientid And mw.itemid=mi.itemid GROUP BY v.vendorid, companyname;

8. Find all of the vendor who supply an ingredient used to make a fruit plate


SQL statement:
SELECT DISTINCT (companyname) FROM tblingredients AS i, tblvendors AS v, tblitems AS mi, tblmadewith AS mw WHERE i.vendorid=v.vendorid AND i.ingredientid=mw.ingredientid AND mw.itemid=mi.itemid AND mi.name='Fruit Plate';

9. Find all of the vendor refffered by Veggies_R_Us:


SQL statement:
SELECT v2.companyname FROM tblvendors AS v1, tblvendors AS v2 WHERE v1.vendorid = v2.referredby AND v1.companyname = 'Veggies_R_Us';

10. LIst all the items for each meal


SQL statement:
SELECT i.name, m.name FROM tblitems AS i, tblmeals AS m, tblpartof AS p WHERE i.itemid = p.itemid AND p.mealid = m.mealid;

11.find all ingredients with an inventory equal to the quantity required to make some item


SQL statement:
SELECT i.name FROM tblingredients AS i, tblmadewith AS mw WHERE i.ingredientid=mw.ingredientid AND inventory=quantity;

12. Find all ingredients with the same name as a.vendor


SQL statement:
SELECT ingredientid FROM tblingredients, tblvendors WHERE name=companyname;

13. Cartesian product


SQL statement:
SELECT m.name AS meal, i.name AS item
FROM tblmeals AS m, tblpartof AS p, tblitems AS i
WHERE m.mealid=p.mealid AND i.itemid=p.itemid;

14. Find the IDs of the items in each meal


SQL statement:
SELECT m.name AS meal, i.name AS item
FROM tblmeals AS m, tblpartof AS p, tblitems AS i
WHERE m.mealid=p.mealid AND i.itemid=p.itemid;

15. Self-Cartesian product


SQL statement:
SELECT *
FROM tblmeals AS m1, tblmeals AS m2;

16. Missing join predicate


SQL statement:
SELECT m.name AS meal, i.name AS item
FROM tblmeals AS m, tblpartof AS p, tblitems AS i
WHERE i.itemid=p.itemid;

17. Find all of the items and ingredients where we do not have enough of the ingredient to make three items


SQL statement:
SELECT tblitems.name, ing.name FROM tblitems, tblmadewith AS mw, tblingredients AS ing WHERE tblitems.itemid=mw.itemid And mw.ingredientid=ing.ingredientid And 2*mw.quantity>ing.inventory;

18.Find the name of all items that cost more than the garden salad


SQL statement:
SELECT a.name FROM tblitems AS a, tblitems AS q WHERE a.price>q.price AND q.name='Garden Salad';

19. Find all of the ingredients that are in items and cost more than the garden salad


SQL statement:
SELECT DISTINCT (i.name) FROM tblitems AS a, tblitems AS q, tblmadewith AS m, tblingredients AS i WHERE a.price>q.price AND q.name='Garden Salad' AND i.ingredientid=m.ingredientid AND m.itemid=a.itemid;

20. Alphabetic ranking of ingredients


SQL statement:
SELECT i1.name, COUNT(*) AS rank
FROM tblingredients AS i1, tblingredients AS i2
WHERE i1.name >= i2.name
GROUP BY i1.ingredientid, i1.name;

21. Find the names of the ingredients supplied to us by Veggies_R_Us


SQL statement:
SELECT name FROM tblingredients AS i INNER JOIN tblvendors AS v ON i.vendorid=v.vendorid WHERE v.companyname='Veggies_R_Us';

22. Find the name of all items that cost more than the garden salad


SQL statement:
SELECT i1.name FROM tblitems AS i1 INNER JOIN tblitems AS i2 ON i1.price>i2.price WHERE i2.name='Garden Salad';

26. Find each ingredients, find its name and the name and ID of the vendor that supplies them, include vendors who supply no ingredients and ingredients supplied by no vendors


SQL statement:
SELECT companyname, i.vendorid, i.name FROM tblvendors AS v INNER JOIN tblingredients AS i ON v.vendorid = i.vendorid;

jhikey & aai