exam preparation 2

2. INSERT

INSERT INTO clients (full_name, phone_number)
SELECT
    CONCAT(first_name, ' ', last_name),
    '(088) 9999'||id*2
FROM
    drivers
WHERE
    id BETWEEN 10 AND 20


3.UPDATE

UPDATE cars
SET condition = 'C'
WHERE
    (mileage >= 800000 OR mileage IS NULL)
        AND
    year <= 2010
        AND
    make <> 'Mercedes-Benz';


4.DELETE

DELETE FROM clients
WHERE
    LENGTH(full_name) >= 3
        AND
    id NOT IN (SELECT client_id FROM courses);


6.CARS (many to many)

SELECT
    d.first_name,
    d.last_name,
    c.make,
    c.model,
    c.mileage
FROM
    drivers AS d
JOIN cars_drivers AS cd
ON cd.driver_id = d.id
JOIN cars AS c
ON c.id = cd.car_id

WHERE
    c.mileage IS NOT NULL
ORDER BY
    c.mileage DESC,
    d.first_name;


7.Number of Courses for Each Car (броят на курсовете се опрделя, като се COUNT id-тата на courses!!! Използваме LEFT JOIN за да се извлече информация и там където курсовете са 0, съответно average_bill е NULL!!! Използваме HAVING понеже exclude е след агрегациите!!!
SELECT
    cr.id AS car_id,
    cr.make AS make,
    cr.mileage AS mileage,
    COUNT(co.id) AS count_of_courses,
    ROUND(AVG(co.bill), 2) AS average_bill
FROM
    cars AS cr
LEFT JOIN
    courses AS co
ON
    cr.id = co.car_id
GROUP BY
    cr.id,
    cr.make AS make,
    cr.mileage AS mileage
HAVING
    COUNT(co.id) <> 2
ORDER BY
    count_of_courses DESC,
    cr.id ASC;


8.Regular Clients - свързват се само таблиците courses и clients, като броя на колите се взима чрез car_id. ПО УСЛОВИЕ КЛИЕНТИТЕ ТРЯБВА ДА СА ПЪТУВАЛ С ПОВЕЧЕ ОТ ЕДНА КОЛАТА, ЗАТОВА ИЗПОЛЗВАМЕ HAVING
SELECT
    cl.full_name,
    COUNT(car_id) AS count_of_cars,
    SUM(co.bill) AS total_sum
FROM
    clients AS cl
JOIN courses AS co
    ON co.client_id = cl.id
WHERE
    cl.full_name LIKE '_a%'
GROUP BY
    cl.full_name
HAVING
    COUNT(car_id) > 1
ORDER BY
    cl.full_name;


9.Full Information of Courses - ОСОБЕНО Е CASE END-a!!!

SELECT
    a.name,
    CASE
        WHEN EXTRACT(HOUR FROM co.start) BETWEEN 6 AND 20 THEN 'Day'
        ELSE 'Night'
    END AS day_time,
    co.bill,
    cl.full_name,
    c.make,
    c.model,
    cat.name
FROM
    addresses AS a
JOIN
    courses AS co
ON co.from_address_id = a.id
JOIN
    clients AS cl
ON cl.id = co.client_id
JOIN
    cars AS c
ON c.id = co.car_id
JOIN
    categories AS cat
ON cat.id = c.category_id
ORDER BY
    co.id;


10.Find all Courses by Client’s Phone Number

CREATE OR REPLACE FUNCTION fn_courses_by_client(phone_num VARCHAR(20))
RETURNS INT
AS
$$
BEGIN
    RETURN (SELECT
                COUNT(co.id)
            FROM
            clients AS cl
            JOIN courses AS co
            ON co.client_id = cl.id
            WHERE cl.phone_number = phone_num
    );

END;
$$
LANGUAGE plpgsql;

11. Full Info for Address

CREATE TABLE search_results (
    id SERIAL PRIMARY KEY,
    address_name VARCHAR(50),
    full_name VARCHAR(100),
    level_of_bill VARCHAR(20),
    make VARCHAR(30),
    condition CHAR(1),
    category_name VARCHAR(50)
);

CREATE OR REPLACE PROCEDURE sp_courses_by_address(address_name VARCHAR(100))
AS
$$
BEGIN
    TRUNCATE search_results;
    INSERT INTO search_results (address_name, full_name, level_of_bill, make, condition, category_name)
    SELECT
        a.name,
        cl.full_name,
        CASE
            WHEN co.bill <= 20 THEN 'Low'
            WHEN co.bill <= 30 THEN 'Medium'
            ELSE 'High'
        END AS level_of_bill,
        c.make,
        c.condition,
        cat.name
    FROM
        addresses AS a
    JOIN
        courses AS co
    ON co.from_address_id = a.id
    JOIN
        clients AS cl
    ON cl.id = co.client_id
    JOIN
        cars AS c
    ON c.id = co.car_id
    JOIN
        categories AS cat
    ON cat.id = c.category_id
    WHERE
        a.name = address_name
    ORDER BY
        c.make,
        cl.full_name;

END;
$$
LANGUAGE plpgsql;