human
Schema:
Manufacturers (Code, Name, Headquarter, Founder, Revenue)
Products (Code, Name, Price, Manufacturer)
Please answer with SQL.
assistant
Sure, I understand.
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 the name of companies whose revenue is greater than the average revenue of all companies.
assistant
SELECT name FROM manufacturers WHERE revenue > (SELECT avg(revenue) FROM manufacturers)
human
Select the average price of each manufacturer's products, showing the manufacturer's name.
assistant
SELECT avg(T1.Price) , T2.name FROM products AS T1 JOIN Manufacturers AS T2 ON T1.manufacturer = T2.code GROUP BY T2.name
human
Find the average prices of all products from each manufacture, and list each company's name.
assistant
SELECT avg(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
Return the average, maximum, and total revenues across all manufacturers.
assistant
SELECT avg ( revenue ) , MAX( revenue ) , SUM( revenue ) FROM manufacturers ;