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 names of shops and the carriers of devices they have in stock.
assistant
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
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
List the open date of open year of the shop named 'Apple'.
assistant
SELECT Open_Date , Open_Year FROM shop WHERE Shop_Name = 'Apple'
human
What are the names and location of the shops in ascending alphabetical order of name.
assistant
SELECT Shop_Name , LOCATION FROM shop ORDER BY Shop_Name 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
Return the names of shops, ordered by year of opening ascending.
assistant
SELECT Shop_Name FROM shop ORDER BY Open_Year ASC ;