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 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 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
List the names of shops that have no devices in stock.
assistant
SELECT Shop_Name FROM shop WHERE Shop_ID NOT IN (SELECT Shop_ID FROM stock)
human
Show the carriers of devices in stock at more than one shop.
assistant
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
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
What are the carriers of devices that are not in stock anywhere?
assistant
SELECT Carrier FROM device WHERE Device_ID NOT IN ( SELECT Device_ID FROM stock ) ;