human
Schema:
Manufacturers (Code, Name, Headquarter, Founder, Revenue)
Products (Code, Name, Price, Manufacturer)
Please answer with SQL.
assistant
Sure, I understand.
human
Select the name of each manufacturer along with the name and price of its most expensive product.
assistant
SELECT T1.Name , max(T1.Price) , T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer = T2.code GROUP BY T2.name
human
Find the total revenue for each manufacturer.
assistant
SELECT sum(revenue) , name FROM manufacturers GROUP BY name
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 number of different products that are produced by companies at different headquarter cities.
assistant
SELECT count(DISTINCT T1.name) , T2.Headquarter FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer = T2.code GROUP BY T2.Headquarter
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
How many products are there for each manufacturer?
assistant
SELECT COUNT( * ) , T2.name FROM products AS T1 JOIN manufacturers AS T2 ON T1.Manufacturer = T2.code GROUP BY T2.name ;