Практика 1 (Базы Данных)

-- 1 ЧАСТЬ_______________________________________________________________________________________
-- 1 пункт
SELECT NAME, quality1, quality2,
CASE
WHEN quality1 = 'evil' AND quality2 = 'cunning' THEN 'Slytherin'
WHEN quality1 = 'brave' AND quality2 != 'evil' THEN 'Gryffindor'
WHEN quality1 = 'studious' OR quality2 = 'intelligent' THEN 'Ravenclaw'
WHEN quality1 = 'hufflepuff' OR quality2 = 'hufflepuff' THEN 'Hufflepuff'
ELSE 'no suitable.'
END AS Houses
FROM students;

-- 2 пункт
CREATE TABLE hogwarts_students (id serial, NAME TEXT, quality1 TEXT, quality2 TEXT, houses TEXT, points INT);

INSERT INTO hogwarts_students SELECT *,
CASE
WHEN quality1 = 'evil' AND quality2 = 'cunning' THEN 'Slytherin'
WHEN quality1 = 'brave' AND quality2 != 'evil' THEN 'Gryffindor'
WHEN quality1 = 'studious' OR quality2 = 'intelligent' THEN 'Ravenclaw'
WHEN quality1 = 'hufflepuff' OR quality2 = 'hufflepuff' THEN 'Hufflepuff'
ELSE 'no suitable.'
END AS Houses, '0'
FROM students;

DELETE FROM hogwarts_students WHERE houses = 'no suitable.';

SELECT * FROM hogwarts_students;

-- 3 пункт
CREATE VIEW ravenclaw_students AS
SELECT *
FROM Hogwarts_students
WHERE houses = 'Ravenclaw';

SELECT * FROM ravenclaw_students;

-- 4 пункт
CREATE MATERIALIZED VIEW mymatview AS
SELECT houses, SUM(points) from hogwarts_students GROUP BY houses;

SELECT * FROM mymatview;

-- 5 пункт
UPDATE ravenclaw_students SET points = 15 WHERE id = 31;
UPDATE ravenclaw_students SET points = 10 WHERE id = 48;
UPDATE ravenclaw_students SET points = 5 WHERE id = 57;
UPDATE ravenclaw_students SET points = 12 WHERE id = 75;
/*
Не получится изменить оценки другого факультета, так как
в этом представлении только студенты Когтеврана
*/
SELECT * from ravenclaw_students;

-- 6 пункт
REFRESH MATERIALIZED VIEW mymatview;
SELECT * FROM mymatview;

-- 7 пункт
CREATE TABLE grade_by_subject (id serial, subject text, grade int[]);

INSERT INTO grade_by_subject SELECT id FROM hogwarts_students;

UPDATE grade_by_subject SET subject = 'Arithmancy' WHERE id <= 33;
UPDATE grade_by_subject SET subject = 'Muggle Studies' WHERE id > 33 AND id <= 66;
UPDATE grade_by_subject SET subject = 'Defence Against the Dark Arts' WHERE id > 66 AND id <= 99;

-- 7 (1) пункт
UPDATE grade_by_subject SET grade = '{4, 4, 3, 5}' WHERE id = 1;
UPDATE grade_by_subject SET grade = '{5, 2}' WHERE id = 31;
UPDATE grade_by_subject SET grade = '{3, 3, 2}' WHERE id = 8;

-- 7 (2) пункт
UPDATE grade_by_subject SET grade[4] = 2 WHERE id = 8;

-- 7 (3) пункт
SELECT * FROM grade_by_subject WHERE 2 = ANY (grade);

-- 7 (4) пункт
SELECT * FROM grade_by_subject;

WITH unnested AS (
SELECT id as student_id, unnest(grade) as score
FROM grade_by_subject
)
SELECT id, subject, avg(score)
FROM grade_by_subject
INNER JOIN unnested
ON student_id=id
GROUP BY 1, 2
ORDER BY 1;

-- 2 ЧАСТЬ_______________________________________________________________________________________
-- 1 задание:
WITH banned_id AS (
    SELECT *
    FROM user_data
    WHERE banned = 'yes'
)
SELECT id, client_id, driver_id, city_id, status, request_at FROM trip
JOIN banned_id ON banned_id.user_id = trip.client_id
WHERE banned_id.user_id=trip.client_id;

-- 2 задание:
WITH stats AS (
    SELECT request_at,
    COUNT(id) AS total
    FROM trip
    group by request_at
),
    cancel AS (
    SELECT request_at,
    COUNT(id) AS cancelled
    FROM trip
    WHERE status LIKE 'cancelled%'
    GROUP BY request_at
)
SELECT stats.request_at, total, cancelled, cancelled*100/total as rate FROM cancel
JOIN stats ON stats.request_at = cancel.request_at;