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
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
What are the memories and carriers of phones?
assistant
SELECT Memory_in_G , Carrier FROM phone
human
Show the carriers that have both phones with memory smaller than 32 and phones with memory bigger than 64.
assistant
SELECT Carrier FROM phone WHERE Memory_in_G < 32 INTERSECT SELECT Carrier FROM phone WHERE Memory_in_G > 64
human
List the distinct carriers of phones with memories bigger than 32.
assistant
SELECT DISTINCT Carrier FROM phone WHERE Memory_in_G > 32 ;