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 most frequently used carrier of the phones.
assistant
SELECT Carrier FROM phone GROUP BY Carrier ORDER BY COUNT(*) DESC LIMIT 1
human
What is the carrier of the most expensive phone?
assistant
SELECT Carrier FROM phone ORDER BY Price 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'
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
List the names of phones in ascending order of price.
assistant
SELECT Name FROM phone ORDER BY Price ASC
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 ) ;