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 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
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, 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 total revenue created by the companies whose headquarter is located at Austin.
assistant
SELECT sum(revenue) FROM manufacturers WHERE headquarter = 'Austin'
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
What are the names of companies with revenue less than the lowest revenue of any manufacturer in Austin?
assistant
SELECT name FROM manufacturers WHERE revenue < ( SELECT MIN( revenue ) FROM manufacturers WHERE headquarter = "Austin" ) ;