human
Schema:
Manufacturers (Code, Name, Headquarter, Founder, Revenue)
Products (Code, Name, Price, Manufacturer)
Please answer with SQL.
assistant
Sure, I understand.
human
Find number of products which Sony does not make.
assistant
SELECT count(DISTINCT name) FROM products WHERE name NOT IN (SELECT T1.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer = T2.code WHERE T2.name = 'Sony')
human
Select all the data from the products and each product's manufacturer.
assistant
SELECT * FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer = T2.code
human
Select the names and the prices of all the products in the store.
assistant
SELECT name , price FROM products
human
Find the name of product that is produced by both companies Creative Labs and Sony.
assistant
SELECT T1.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer = T2.code WHERE T2.name = 'Creative Labs' INTERSECT SELECT T1.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer = T2.code WHERE T2.name = 'Sony'
human
Find the number of products for each manufacturer, showing the name of each company.
assistant
SELECT count(*) , T2.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer = T2.code GROUP BY T2.name
human
What are the names of all products?
assistant
SELECT Name FROM Products ;