human
Schema:
device (Device_ID, Device, Carrier, Package_Version, Applications, Software_Platform)
shop (Shop_ID, Shop_Name, Location, Open_Date, Open_Year)
stock (Shop_ID, Device_ID, Quantity)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the name of the shop that has the most kind of devices in stock.
assistant
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
human
Show names of shops that have more than one kind of device in stock.
assistant
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
human
Show the locations shared by shops with open year later than 2012 and shops with open year before 2008.
assistant
SELECT LOCATION FROM shop WHERE Open_Year > 2012 INTERSECT SELECT LOCATION FROM shop WHERE Open_Year < 2008
human
What are the names of shops in ascending order of open year?
assistant
SELECT Shop_Name FROM shop ORDER BY Open_Year ASC
human
List the name of the shop with the latest open year.
assistant
SELECT Shop_Name FROM shop ORDER BY Open_Year DESC LIMIT 1
human
What are the open dates and years for the shop named Apple?
assistant
SELECT Open_Date , Open_Year FROM shop WHERE Shop_Name = "Apple" ;