1. Find the product id and unit list price of products belonging to the 'Yoghurt' category. SELECT product_id, unit_list_price FROM products WHERE product_category = 'yoghurt'; ----------- 2. Find all the information (i.e., all columns) of the managers with null values in their first name. SELECT * FROM managers WHERE first_name IS NULL; ----------- 3. Find the sales ID and sales date where quantity sold on that date is greater than or equal to 2100. SELECT sales_id, sales_date, quantity_sold FROM sales WHERE quantity_sold >= 2100; ----------- 4. Find the manager ID and the sum of quantity sold for all products (except those with ID 7001001) for each of the managers in the sales table and sort the output by manager ID in descending order. SELECT sales_manager_id, SUM(quantity_sold) AS quant_sold FROM sales WHERE product_id <> 7001001 GROUP BY sales_manager_id ORDER BY sales_manager_id DESC; ----------- 5. Find the product ID, product name, and unit production cost of the products with maximum unit production cost below $1.10 and sort the output by production cost in ascending order (HINT: Use HAVING). SELECT product_id, product_name, MAX(unit_production_cost) AS unit_production_cost FROM products GROUP BY 1 , 2 HAVING MAX(unit_production_cost) < 1.10 ORDER BY unit_production_cost DESC; SELECT product_id, product_name, unit_production_cost FROM products WHERE unit_production_cost < 1.10 ORDER BY unit_production_cost DESC; ----------- 6. Find the product ID and sales date with the highest quantity sold from sales transacted after 30 Oct 2021 (exclusive) except for products with IDs 7001001 and 7001002. SELECT product_id, sales_date, quantity_sold FROM sales WHERE sales_date > '2021-10-30' AND product_id NOT IN (7001001 , 7001002) ORDER BY quantity_sold desc LIMIT 1; SELECT product_id, sales_date, quantity_sold FROM sales WHERE sales_date > '2021-10-30';
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter