human
Schema:
Manufacturers (Code, Name, Headquarter, Founder, Revenue)
Products (Code, Name, Price, Manufacturer)
Please answer with SQL.
assistant
Sure, I understand.
human
How many companies were created by Andy?
assistant
SELECT count(*) FROM manufacturers WHERE founder = 'Andy'
human
Where is the headquarter of the company founded by James?
assistant
SELECT headquarter FROM manufacturers WHERE founder = 'James'
human
Find the founder of the company whose name begins with the letter 'S'.
assistant
SELECT founder FROM manufacturers WHERE name LIKE 'S%'
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
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
Who is the founder of Sony?
assistant
SELECT founder FROM manufacturers WHERE name = "Sony" ;