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
Find the component amounts and names of all furnitures that have more than 10 components.
assistant
SELECT Num_of_Component , name FROM furniture WHERE Num_of_Component > 10
human
List all manufacturer names and ids ordered by their opening year.
assistant
SELECT name , manufacturer_id FROM manufacturer ORDER BY open_year
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
Find the name of the company that produces both furnitures with less than 6 components and furnitures with more than 10 components.
assistant
SELECT t3.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID = t2.Furniture_ID JOIN manufacturer AS t3 ON t2.manufacturer_id = t3.manufacturer_id WHERE t1.num_of_component < 6 INTERSECT SELECT t3.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID = t2.Furniture_ID JOIN manufacturer AS t3 ON t2.manufacturer_id = t3.manufacturer_id WHERE t1.num_of_component > 10
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
Find the number of funiture types produced by each manufacturer as well as the company names.
assistant
SELECT COUNT( * ) , t1.name FROM manufacturer AS t1 JOIN furniture_manufacte AS t2 ON t1.manufacturer_id = t2.manufacturer_id GROUP BY t1.manufacturer_id ;