human
Schema:
phone (Name, Phone_ID, Memory_in_G, Carrier, Price)
market (Market_ID, District, Num_of_employees, Num_of_shops, Ranking)
phone_market (Market_ID, Phone_ID, Num_of_stock)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of phones and their stocks.
assistant
SELECT T2.Name , T1.Num_of_stock FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID = T2.Phone_ID;
human
Of those, which have 64 GB memory?
assistant
SELECT T2.Name , T1.Num_of_stock FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID = T2.Phone_ID WHERE T2.Memory_in_G = 64;
human
How about the having total stocks at least 2000 instead?
assistant
SELECT T2.Name , T1.Num_of_stock FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID = T2.Phone_ID GROUP BY T2.Name HAVING SUM( T1.Num_of_stock ) >= 2000;
human
Don't show their number of stocks.
assistant
SELECT T2.Name FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID = T2.Phone_ID GROUP BY T2.Name HAVING SUM( T1.Num_of_stock ) >= 2000 ;