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
For each phone, show its names and total number of stocks.
assistant
SELECT T2.Name , sum(T1.Num_of_stock) FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID = T2.Phone_ID GROUP BY T2.Name
human
Show the names of phones that are on market with number of shops greater than 50.
assistant
SELECT T3.Name FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID = T3.Phone_ID WHERE T2.Num_of_shops > 50
human
Show the names of phones that have total number of stocks bigger than 2000, in descending order of the total 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 ORDER BY sum(T1.Num_of_stock) DESC
human
List the names of phones in ascending order of price.
assistant
SELECT Name FROM phone ORDER BY Price ASC
human
Show the names of phones and the districts of markets they are on.
assistant
SELECT T3.Name , T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID = T3.Phone_ID
human
Show the names of phones and the districts of markets they are on, in ascending order of the ranking of the market.
assistant
SELECT T3.Name , T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID = T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID = T3.Phone_ID ORDER BY T2.Ranking ;