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 id and number of shops for the company that produces the most expensive furniture.
assistant
SELECT t1.manufacturer_id , t1.num_of_shops FROM manufacturer AS t1 JOIN furniture_manufacte AS t2 ON t1.manufacturer_id = t2.manufacturer_id ORDER BY t2.Price_in_Dollar DESC LIMIT 1
human
Find the names of furnitures whose prices are lower than the highest price.
assistant
SELECT t1.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID = t2.Furniture_ID WHERE t2.Price_in_Dollar < (SELECT max(Price_in_Dollar) FROM furniture_manufacte)
human
Find the name and component amount of the least popular furniture.
assistant
SELECT name , Num_of_Component FROM furniture ORDER BY market_rate LIMIT 1
human
How many furniture components are there in total?
assistant
SELECT sum(num_of_component) FROM furniture
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 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 ;