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 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
List the software platform shared by the greatest number of devices.
assistant
SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC LIMIT 1
human
List the carriers of devices in ascending alphabetical order.
assistant
SELECT Carrier FROM device ORDER BY Carrier ASC
human
Please show the software platforms of devices in descending order of the count.
assistant
SELECT Software_Platform FROM device GROUP BY Software_Platform ORDER BY COUNT(*) DESC
human
Please show different software platforms and the corresponding number of devices using each.
assistant
SELECT Software_Platform , COUNT(*) FROM device GROUP BY Software_Platform
human
Count the number of devices.
assistant
SELECT COUNT( * ) FROM device ;