`
use mydb_hw;

-- Task 1. SQL query that displays the `order_details` table and the `customer_id` field from the `orders` table, respectively, for each record field from the `order_details` table
SELECT 
    od.id, 
    od.order_id, 
    od.product_id, 
    od.quantity,
    (SELECT o.customer_id FROM orders o
    WHERE id = od.order_id) AS customer_id
FROM order_details od;
    
    -- 1. Option II
    SELECT 
    od.*,
    (SELECT o.customer_id
        FROM orders o
        WHERE id = od.order_id) AS customer_id
FROM order_details od;

-- Task 2. SQL query that displays the `order_details` table. The results are filtered so that the corresponding record from the `orders` table fulfils the condition `shipper_id=3`. A nested query in the `WHERE` clause used
SELECT
    od.id,
    od.order_id,
    od.product_id,
    od.quantity,
    o.shipper_id
FROM
    order_details od
JOIN
    orders o ON od.order_id = o.id
    WHERE od.order_id IN (
    SELECT o.id
        FROM orders o
        WHERE o.shipper_id = 3);
        
-- Task 3. SQL query nested in the `FROM' statement, which selects rows with the `quantity>10' condition from the `order_details` table.
SELECT
    od.order_id,
    AVG(od.quantity) AS avg_qty
FROM (SELECT
            id,
            order_id,
            product_id,
            quantity
        FROM
            order_details
        WHERE
            quantity > 10
    ) od
    GROUP BY od.order_id;
    
-- Task 4: Solution for task 3 using the `WITH` statement to create the temporary table `temp`
WITH temp AS (
SELECT
            id,
            order_id,
            product_id,
            quantity
        FROM
            order_details 
WHERE quantity > 10)
SELECT
    temp.order_id,
    AVG(temp.quantity) AS avg_qty
FROM temp
GROUP BY temp.order_id;

-- Task 5: This function has two parameters that divide the first parameter by the second. Both the parameters and the return value are of `FLOAT` type. The `DROP FUNCTION IF EXISTS` construct applied to the `quantity` attribute of the `order_details` table
DROP FUNCTION IF EXISTS DevideParams;

DELIMITER //

CREATE FUNCTION DevideParams(param1 FLOAT, param2 FLOAT)
RETURNS FLOAT
DETERMINISTIC 
NO SQL
BEGIN
    DECLARE result FLOAT;
    SET result = param1 / param2;
    RETURN result;
END //

UPDATE mydb_hw.order_details
SET quantity = DevideParams(quantity, 1);

DELIMITER ;
 `