Database: phone_market

CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
);
CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
);
CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
);

Question: How many phones are there?
SQL: SELECT count(*) FROM phone

Question: List the names of phones in ascending order of price.
SQL: SELECT Name FROM phone ORDER BY Price ASC

Question: What are the memories and carriers of phones?
SQL: SELECT Memory_in_G ,  Carrier FROM phone

Question: List the distinct carriers of phones with memories bigger than 32.
SQL: SELECT DISTINCT Carrier FROM phone WHERE Memory_in_G  >  32

Question: Show the names of phones with carrier either "Sprint" or "TMobile".
SQL: SELECT Name FROM phone WHERE Carrier  =  "Sprint" OR Carrier  =  "TMobile"

Question: What is the carrier of the most expensive phone?
SQL: SELECT Carrier FROM phone ORDER BY Price DESC LIMIT 1

Question: Show different carriers of phones together with the number of phones with each carrier.
SQL: SELECT Carrier ,  COUNT(*) FROM phone GROUP BY Carrier

Question: Show the most frequently used carrier of the phones.
SQL: SELECT Carrier FROM phone GROUP BY Carrier ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the carriers that have both phones with memory smaller than 32 and phones with memory bigger than 64.
SQL: SELECT Carrier FROM phone WHERE Memory_in_G  <  32 INTERSECT SELECT Carrier FROM phone WHERE Memory_in_G  >  64

Question: Show the names of phones and the districts of markets they are on.
SQL: 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

Question: Show the names of phones and the districts of markets they are on, in ascending order of the ranking of the market.
SQL: 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

Question: Show the names of phones that are on market with number of shops greater than 50.
SQL: 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

Question: For each phone, show its names and total number of stocks.
SQL: 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

Question: Show the names of phones that have total number of stocks bigger than 2000, in descending order of the total number of stocks.
SQL: 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

Question: List the names of phones that are not on any market.
SQL: SELECT Name FROM phone WHERE Phone_id NOT IN (SELECT Phone_ID FROM phone_market)