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;