Create Schema demo;

CREATE TABLE demo.emp_info (
    emp_id CHAR(4),
    emp_name VARCHAR(20),
    skills VARCHAR(40),
    exp INT
);

INSERT INTO demo.emp_info
VALUES
("A123", "Rohit Jain", "SQL|C|R|Python|Tableau", 7),
("A124", "Aaina Singh", "SQLC|R|Tableau", 4),
("A125", "Mark John", "C|Python|Java", 10),
("A126", "Sam Keith", "SQL|C", 2),
("A127", "Kenny Ford", "SQL|C|R|Python|Power BI", 5);

CREATE TABLE demo.emp_sal_desig (
    emp_id CHAR(4),
    desig VARCHAR(20),
    salary FLOAT
);

INSERT INTO demo.emp_sal_desig
VALUES
("A123", "L3", 4500),
("A126", "L1", 2500),
("A121", "L2", 3500),
("A122", "L5", 9500);

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';