28eU8UK5

1DROP DATABASE IF EXISTS youtube_db;
2CREATE DATABASE youtube_db;
3USE youtube_db;
4
5CREATE TABLE User (
6	id INTEGER AUTO_INCREMENT NOT NULL,
7    name VARCHAR(255) NOT NULL,
8    username varchar(255) NOT NULL UNIQUE KEY,
9    views_num int NOT NULL DEFAULT 0,
10
11	PRIMARY KEY(id)
12);
13
14CREATE TABLE Channel (
15	id INTEGER AUTO_INCREMENT NOT NULL,
16    name VARCHAR(100) NOT NULL,
17    user_id int, 
18    PRIMARY KEY(id),
19    FOREIGN KEY(user_id) REFERENCES User(id)
20    ON DELETE CASCADE
21    ON UPDATE CASCADE
22);
23
24CREATE TABLE Video (
25	id INTEGER AUTO_INCREMENT NOT NULL,
26    name VARCHAR(50) NOT NULL,
27    channel_id int NOT NULL,
28    views INTEGER DEFAULT 0,
29    
30    PRIMARY KEY(id),
31    FOREIGN KEY(channel_id) REFERENCES Channel(id)
32    ON DELETE CASCADE
33    ON UPDATE CASCADE
34);
35
36CREATE TRIGGER VideosInsert
37AFTER INSERT
38ON Video
39FOR EACH ROW
40	UPDATE user 
41    SET views_num = ( select sum(views) from Video left join Channel on Video.channel_id = Channel.id) 
42    WHERE New.id = id;
43    
44
45CREATE TRIGGER VideosUpdate
46AFTER UPDATE
47ON Video
48FOR EACH ROW
49	UPDATE user 
50    SET views_num = ( select sum(views) from Video left join Channel on Video.channel_id = Channel.id) 
51    WHERE New.id = id;
52    
53
54CREATE TRIGGER VideosDelete
55AFTER Delete
56ON Video
57FOR EACH ROW
58	UPDATE user 
59    SET views_num = 0
60    WHERE OLD.id = id;
61    
62INSERT INTO User(name, username) VALUES ('Pesho Programista', 'pe60');
63INSERT INTO User(name, username) VALUES ('Gosho', 'go60');
64INSERT INTO User(name, username) VALUES ('Tosho', 'to60');
65
66INSERT INTO Channel(name, user_id) VALUES ('Channel1', 1);
67INSERT INTO Channel(name, user_id) VALUES ('Channel2', 2);
68INSERT INTO Channel(name, user_id) VALUES ('Channel3', 3);
69
70INSERT INTO Video(name, channel_id) VALUES ('Video1', 1);
71INSERT INTO Video(name, channel_id, views) VALUES ('Video2', 2, 2);
72INSERT INTO Video(name, channel_id) VALUES ('Video0', 3);
73
74Select v.name, v.views, u.name, ch.name from Video v
75LEFT JOIN Channel ch ON ch.id = v.channel_id
76LEFT JOIN  User u ON u.id = ch.user_id;
77
78Select ch.name from Channel ch
79Inner join Video v on v.channel_id = ch.id
80group by ch.name;
81
82Select u.name, u.username, u.views_num from User u 
83Inner join Channel ch on ch.user_id = u.id
84Inner join Video v on v.channel_id
85group by u.username;