Theory Of Database Systems

Sample Queries

SELECT tblvendors.vendorid, name,
companyname]

FROM tblvendors, tblingredients

WHERE tblingredients.vendorid =
tblvendors.vendorid






SELECT name

FROM tblingredients, tblvendors

WHERE tblingredients.vendorid=tblvendors.vendorid And
companyname='Veggies_R_Us';





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













SELECT state, SUM(price) AS [Sum]

FROM tblstores, tblorders

WHERE tblstores.storeid=tblorders.storeid

GROUP BY state;





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;




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';





SELECT companyname, MAX(price) AS prices

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;




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';





SELECT v2.companyname

FROM tblvendors AS v1, tblvendors AS v2

WHERE v1.vendorid = v2.referredby AND
v1.companyname = 'Veggies_R_Us';





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;








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






SELECT ingredientid
FROM tblingredients, tblvendors
WHERE name = companyname;


















SELECT *
FROM tblmeals, tblpartof;






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;






SELECT *

FROM tblmeals AS m1, tblmeals AS m2;








SELECT m.name AS meal, i.name AS item

FROM tblmeals AS m, tblpartof AS p, tblitems
AS i

WHERE i.itemid = p.itemid;










SELECT tblitems.name, ing.name

FROM tblitems, tblmadewith AS mw,
tblingredients AS ing

WHERE tblitems.itemid = mw.itemid AND mw.ingredientid = ing.ingredientid AND (3 * mw.quantity) > ing.inventory;





SELECT a.name

FROM tblitems AS a, tblitems AS q

WHERE a.price > q.price AND q.name = 'Garden Salad';





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;










SELECT i1.name, COUNT(*) AS rank

FROM tblingredients AS i1, tblingredients
AS i2

WHERE i1.name >= i2.name

GROUP BY i1.ingredientid, i1.name;











SELECT name

FROM tblingredients AS i INNER JOIN tblvendors AS v
ON i.vendorid=v.vendorid

WHERE v.companyname='Veggies_R_Us';