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
How many different markets are there?
assistant
SELECT COUNT( distinct market_id ) FROM phone_market;
human
How many different phones are there?
assistant
SELECT COUNT( distinct name ) FROM phone;
human
Which phone is sold in the most different markets?
assistant
SELECT T1.name FROM phone AS T1 JOIN phone_market AS T2 ON T1.phone_id = T2.phone_id GROUP by T1.phone_id ORDER by COUNT( * ) desc LIMIT 1;
human
How many phones are not available in any markets?
assistant
SELECT COUNT( Name ) FROM phone WHERE Phone_id NOT IN ( SELECT Phone_ID FROM phone_market );
human
What is the name of that phone?
assistant
SELECT Name FROM phone WHERE Phone_id NOT IN ( SELECT Phone_ID FROM phone_market ) ;