human
Schema:
Manufacturers (Code, Name, Headquarter, Founder, Revenue)
Products (Code, Name, Price, Manufacturer)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the number of manufactures that are based in Tokyo or Beijing.
assistant
SELECT count(*) FROM manufacturers WHERE headquarter = 'Tokyo' OR headquarter = 'Beijing'
human
Select the names of all the products in the store.
assistant
SELECT Name FROM Products
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
Who is the founder of Sony?
assistant
SELECT founder FROM manufacturers WHERE name = 'Sony'
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
What are the names of products produced by both 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" ;