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
List the names of phones that are not on any market.
assistant
SELECT Name FROM phone WHERE Phone_id NOT IN (SELECT Phone_ID FROM phone_market)
human
List the names of phones in ascending order of price.
assistant
SELECT Name FROM phone ORDER BY Price ASC
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 different carriers of phones together with the number of phones with each carrier.
assistant
SELECT Carrier , COUNT(*) FROM phone GROUP BY Carrier
human
Show the most frequently used carrier of the phones.
assistant
SELECT Carrier FROM phone GROUP BY Carrier ORDER BY COUNT(*) DESC LIMIT 1
human
Show the names of phones with carrier either 'Sprint' or 'TMobile'.
assistant
SELECT Name FROM phone WHERE Carrier = "Sprint" OR Carrier = "TMobile" ;