human
Schema:
Manufacturers (Code, Name, Headquarter, Founder, Revenue)
Products (Code, Name, Price, Manufacturer)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name, headquarter and founder of the manufacturer that has the highest revenue.
assistant
SELECT name , headquarter , founder FROM manufacturers ORDER BY revenue DESC LIMIT 1
human
Select the names of manufacturer whose products have an average price higher than or equal to $150.
assistant
SELECT avg(T1.Price) , T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer = T2.code GROUP BY T2.name HAVING avg(T1.price) >= 150
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 all manufacturers' names and their headquarters, sorted by the ones with highest revenue first.
assistant
SELECT name , headquarter FROM manufacturers ORDER BY revenue DESC
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 companies that do not make DVD drive.
assistant
SELECT name FROM manufacturers EXCEPT SELECT T2.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer = T2.code WHERE T1.name = "DVD drive" ;