human
Schema:
product (product_id, product, dimensions, dpi, pages_per_minute_color, max_page_size, interface)
store (Store_ID, Store_Name, Type, Area_size, Number_of_product_category, Ranking)
district (District_ID, District_name, Headquartered_City, City_Population, City_Area)
store_product (Store_ID, Product_ID)
store_district (Store_ID, District_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
Find all the product whose name contains the word 'Scanner'.
assistant
SELECT product FROM product WHERE product LIKE '%Scanner%'
human
Find products with max page size as 'A4' and pages per minute color smaller than 5.
assistant
SELECT product FROM product WHERE max_page_size = 'A4' AND pages_per_minute_color < 5
human
Find products with max page size as 'A4' or pages per minute color smaller than 5.
assistant
SELECT product FROM product WHERE max_page_size = 'A4' OR pages_per_minute_color < 5
human
Find the list of page size which have more than 3 product listed
assistant
SELECT max_page_size FROM product GROUP BY max_page_size HAVING count(*) > 3
human
Find the most prominent max page size among all the products.
assistant
SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY count(*) DESC LIMIT 1
human
Find the name of the products that are not using the most frequently-used max page size.
assistant
SELECT product FROM product WHERE product != ( SELECT max_page_size FROM product GROUP BY max_page_size ORDER BY COUNT( * ) DESC LIMIT 1 ) ;