human
Schema:
manufacturer (Manufacturer_ID, Open_Year, Name, Num_of_Factories, Num_of_Shops)
furniture (Furniture_ID, Name, Num_of_Component, Market_Rate)
furniture_manufacte (Manufacturer_ID, Furniture_ID, Price_in_Dollar)
Please answer with SQL.
assistant
Sure, I understand.
human
Give me the names and prices of furnitures which some companies are manufacturing.
assistant
SELECT t1.name , t2.price_in_dollar FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID = t2.Furniture_ID
human
Find the average number of factories for the manufacturers that have more than 20 shops.
assistant
SELECT avg(Num_of_Factories) FROM manufacturer WHERE num_of_shops > 20
human
List all manufacturer names and ids ordered by their opening year.
assistant
SELECT name , manufacturer_id FROM manufacturer ORDER BY open_year
human
what is the average number of factories and maximum number of shops for manufacturers that opened before 1990.
assistant
SELECT max(num_of_shops) , avg(Num_of_Factories) FROM manufacturer WHERE open_year < 1990
human
Which manufacturer has the most number of shops? List its name and year of opening.
assistant
SELECT open_year , name FROM manufacturer ORDER BY num_of_shops DESC LIMIT 1
human
Give me the name and year of opening of the manufacturers that have either less than 10 factories or more than 10 shops.
assistant
SELECT name , open_year FROM manufacturer WHERE num_of_shops > 10 OR Num_of_Factories < 10 ;