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;