Database: device

CREATE TABLE "device" (
"Device_ID" int,
"Device" text,
"Carrier" text,
"Package_Version" text,
"Applications" text,
"Software_Platform" text,
PRIMARY KEY ("Device_ID")
);
CREATE TABLE "shop" (
"Shop_ID" int,
"Shop_Name" text,
"Location" text,
"Open_Date" text,
"Open_Year" int,
PRIMARY KEY ("Shop_ID")
);
CREATE TABLE "stock" (
"Shop_ID" int,
"Device_ID" int,
"Quantity" int,
PRIMARY KEY ("Shop_ID","Device_ID"),
FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`),
FOREIGN KEY (`Device_ID`) REFERENCES `device`(`Device_ID`)
);

Question: How many devices are there?
SQL: SELECT count(*) FROM device

Question: Count the number of devices.
SQL: SELECT count(*) FROM device

Question: List the carriers of devices in ascending alphabetical order.
SQL: SELECT Carrier FROM device ORDER BY Carrier ASC

Question: What are the different carriers for devices, listed in alphabetical order?
SQL: SELECT Carrier FROM device ORDER BY Carrier ASC

Question: What are the carriers of devices whose software platforms are not "Android"?
SQL: SELECT Carrier FROM device WHERE Software_Platform != 'Android'

Question: Return the device carriers that do not have Android as their software platform.
SQL: SELECT Carrier FROM device WHERE Software_Platform != 'Android'

Question: What are the names of shops in ascending order of open year?
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year ASC

Question: Return the names of shops, ordered by year of opening ascending.
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year ASC

Question: What is the average quantity of stocks?
SQL: SELECT avg(Quantity) FROM stock

Question: Give the average quantity of stocks.
SQL: SELECT avg(Quantity) FROM stock

Question: What are the names and location of the shops in ascending alphabetical order of name.
SQL: SELECT Shop_Name ,  LOCATION FROM shop ORDER BY Shop_Name ASC

Question: Return the names and locations of shops, ordered by name in alphabetical order.
SQL: SELECT Shop_Name ,  LOCATION FROM shop ORDER BY Shop_Name ASC

Question: How many different software platforms are there for devices?
SQL: SELECT count(DISTINCT Software_Platform) FROM device

Question: Count the number of different software platforms.
SQL: SELECT count(DISTINCT Software_Platform) FROM device

Question: List the open date of open year of the shop named "Apple".
SQL: SELECT Open_Date ,  Open_Year FROM shop WHERE Shop_Name  =  "Apple"

Question: What are the open dates and years for the shop named Apple?
SQL: SELECT Open_Date ,  Open_Year FROM shop WHERE Shop_Name  =  "Apple"

Question: List the name of the shop with the latest open year.
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year DESC LIMIT 1

Question: What is the shop name corresponding to the shop that opened in the most recent year?
SQL: SELECT Shop_Name FROM shop ORDER BY Open_Year DESC LIMIT 1

Question: Show names of shops and the carriers of devices they have in stock.
SQL: SELECT T3.Shop_Name ,  T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID JOIN shop AS T3 ON T1.Shop_ID  =  T3.Shop_ID

Question: What are the names of device shops, and what are the carriers that they carry devices in stock for?
SQL: SELECT T3.Shop_Name ,  T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID JOIN shop AS T3 ON T1.Shop_ID  =  T3.Shop_ID

Question: Show names of shops that have more than one kind of device in stock.
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID HAVING COUNT(*)  >  1

Question: What are the names of shops that have more than a single kind of device in stock?
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID HAVING COUNT(*)  >  1

Question: Show the name of the shop that has the most kind of devices in stock.
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the name of the shop that has the most different kinds of devices in stock?
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the name of the shop that have the largest quantity of devices in stock.
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY SUM(T1.quantity) DESC LIMIT 1

Question: What is the name of the shop that has the greatest quantity of devices in stock?
SQL: SELECT T2.Shop_Name FROM stock AS T1 JOIN shop AS T2 ON T1.Shop_ID  =  T2.Shop_ID GROUP BY T1.Shop_ID ORDER BY SUM(T1.quantity) DESC LIMIT 1

Question: Please show different software platforms and the corresponding number of devices using each.
SQL: SELECT Software_Platform ,  COUNT(*) FROM device GROUP BY Software_Platform

Question: What are the different software platforms for devices, and how many devices have each?
SQL: SELECT Software_Platform ,  COUNT(*) FROM device GROUP BY Software_Platform

Question: Please show the software platforms of devices in descending order of the count.
SQL: SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC

Question: What are the different software platforms for devices, ordered by frequency descending?
SQL: SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC

Question: List the software platform shared by the greatest number of devices.
SQL: SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the software platform that is most common amongst all devices?
SQL: SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC LIMIT 1

Question: List the names of shops that have no devices in stock.
SQL: SELECT Shop_Name FROM shop WHERE Shop_ID NOT IN (SELECT Shop_ID FROM stock)

Question: What are the names of shops that do not have any devices in stock?
SQL: SELECT Shop_Name FROM shop WHERE Shop_ID NOT IN (SELECT Shop_ID FROM stock)

Question: Show the locations shared by shops with open year later than 2012 and shops with open year before 2008.
SQL: SELECT LOCATION FROM shop WHERE Open_Year  >  2012 INTERSECT SELECT LOCATION FROM shop WHERE Open_Year  <  2008

Question: Which locations contains both shops that opened after the year 2012 and shops that opened before 2008?
SQL: SELECT LOCATION FROM shop WHERE Open_Year  >  2012 INTERSECT SELECT LOCATION FROM shop WHERE Open_Year  <  2008

Question: List the carriers of devices that have no devices in stock.
SQL: SELECT Carrier FROM device WHERE Device_ID NOT IN (SELECT Device_ID FROM stock)

Question: What are the carriers of devices that are not in stock anywhere?
SQL: SELECT Carrier FROM device WHERE Device_ID NOT IN (SELECT Device_ID FROM stock)

Question: Show the carriers of devices in stock at more than one shop.
SQL: SELECT T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID GROUP BY T1.Device_ID HAVING COUNT(*)  >  1

Question: What are the carriers of devices that are in stock in more than a single shop?
SQL: SELECT T2.Carrier FROM stock AS T1 JOIN device AS T2 ON T1.Device_ID  =  T2.Device_ID GROUP BY T1.Device_ID HAVING COUNT(*)  >  1