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
Find the name of companies whose revenue is between 100 and 150.
assistant
SELECT name FROM manufacturers WHERE revenue BETWEEN 100 AND 150
human
Find the total revenue of companies whose revenue is larger than the revenue of some companies based in Austin.
assistant
SELECT sum(revenue) FROM manufacturers WHERE revenue > (SELECT min(revenue) FROM manufacturers WHERE headquarter = 'Austin')
human
Find the name of companies whose revenue is smaller than the revenue of all companies based in Austin.
assistant
SELECT name FROM manufacturers WHERE revenue < (SELECT min(revenue) FROM manufacturers WHERE headquarter = 'Austin')
human
Find the name and revenue of the company that earns the highest revenue in each city.
assistant
SELECT name , max(revenue) , Headquarter FROM manufacturers GROUP BY Headquarter
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 ) ;