Cq8y4E7Y

1DROP DATABASE IF EXISTS tumblr;
2CREATE DATABASE tumblr CHARSET 'utf8';
3USE tumblr;
4
5create table Users(
6	id integer not null auto_increment primary key,
7    name varchar(50) not null
8   
9);
10CREATE TABLE Blog(
11	Id integer not null auto_increment primary key,
12    name varchar(50) not null,
13    number_of_likes int not null
14);
15
16create table BlogPost(
17	id integer not null auto_increment primary key,
18    name varchar(50) not null,
19    blog_id integer not null ,
20    FOREIGN KEY (blog_id) REFERENCES Blog(Id)
21);
22
23
24
25create table Likes(
26	id integer not null auto_increment primary key,
27    user_id integer not null ,
28    FOREIGN KEY (user_id) REFERENCES Users(id),
29    blog_post integer not null,
30    foreign key (blog_post) references BlogPost(id)
31);
32
33INSERT INTO Users(Id, name) VALUES( 101, 'Зюмбюл Петров');
34INSERT INTO Users(name) VALUES('Яшо');
35INSERT INTO Users(name) VALUES('Simko');
36
37insert into Blog(Id, name, number_of_likes) values (1, 'Simkos blog', 0);
38insert into Blog(name, number_of_likes) values ('Qsho blog', 0);
39insert into Blog(name, number_of_likes) values ('Zyumis blog', 0);
40
41
42insert into BlogPost(Id, name, blog_id) values (1, 'Today I learned', 1);
43insert into BlogPost(name, blog_id) values ('Today I didnt learn', 1);
44insert into BlogPost(name, blog_id) values ('The dangers of gyros', 2);
45insert into BlogPost(name, blog_id) values ('Archer is great', 3);
46insert into BlogPost(name, blog_id) values ('Dragonborn', 3);
47
48insert into Likes(id, user_id, blog_post) values (1, 101, 1);
49insert into Likes(user_id, blog_post) values (101, 2);
50insert into Likes(user_id, blog_post) values (101, 3);
51insert into Likes(user_id, blog_post) values (102, 1);
52
53DELIMITER $ 
54
55#CREATE TRIGGER Likes_AI AFTER INSERT ON Likes FOR EACH ROW
56#BEGIN
57#	UPDATE Blog SET number_of_likes = OLD() + 1
58#    WHERE Id = OLD.blog_post;
59#END$
60
61#CREATE TRIGGER Likes_AD AFTER INSERT ON Likes FOR EACH ROW
62#BEGIN
63#	UPDATE Blog SET number_of_likes = OLD() - 1
64#    WHERE Id = OLD.blog_post;
65#END$
66
67Delimiter ;
68
69Select Blog.name as blog_name, BlogPost.name as post_name from BlogPost
70left join Blog on BlogPost.blog_id = Blog.id;
71
72Select Users.name as user_name , count(users.name) as number_of_likes from Likes
73left join Users on Likes.user_id = Users.id
74group by Users.name;
75
76Select Blog.name as blog_name, BlogPost.name as post_name, Users.name from Likes
77left join Users on Likes.user_id = Users.id
78left join BlogPost on Likes.blog_post = BlogPost.id
79left join Blog on BlogPost.blog_id = Blog.id;
80
81Select Users.name as name , Blog.name as blog_name from Likes
82left join Users on Likes.user_id = Users.id
83left join BlogPost on Likes.blog_post = BlogPost.id
84left join Blog on BlogPost.blog_id = Blog.id
85group by  Blog.name, Users.name;