File Structures
Query 1: For each store, show the price of its most expensive product
using GROUP BY
Query 2: For each store, show the price of its most expensive product
without using GROUP BY
Store (SID, Name)
Product (PID, Name, Price, SID)
SID Name
S1 Store1
S2 Store2
PID Name Price SID
P1 Pro1 120 S1
P2 Pro2 110 S2
P3 Pro3 40 S2
P4 Pro4 190 S1
SELECT S.SID, MAX(P.Price)
FROM Store AS S, Product AS P
WHERE S.SID = P.SID
GROUP BY S.SID;
Q1: with GROUP BY
SELECT S.SID, P.Price
FROM Store AS S, Product AS P
WHERE S.SID = P.SID AND
P.Price >= ALL (SELECT Y.Price
FROM Product Y
WHERE S.SID = Y.SID);
Q2: without GROUP BY