ug2r2vYa

1//1
2
3CREATE TABLE IF NOT EXISTS users (
4  id INT(11) PRIMARY KEY AUTO_INCREMENT,
5  username VARCHAR(30) UNIQUE NOT NULL,
6  password VARCHAR(30) NOT NULL ,
7  email VARCHAR(50) NOT NULL
8);
9
10CREATE TABLE IF NOT EXISTS categories (
11  id INT(11) PRIMARY KEY AUTO_INCREMENT,
12  category VARCHAR(30) NOT NULL
13);
14
15CREATE TABLE IF NOT EXISTS articles (
16  id INT(11) PRIMARY KEY AUTO_INCREMENT,
17  title VARCHAR(50) NOT NULL ,
18  content TEXT NOT NULL,
19  category_id INT(11),
20  CONSTRAINT fk_acat FOREIGN KEY (category_id) REFERENCES categories(id)
21);
22
23CREATE TABLE IF NOT EXISTS users_articles (
24  user_id INT(11) ,
25  article_id INT(11),
26  CONSTRAINT fk_uau FOREIGN KEY (user_id) REFERENCES users(id),
27  CONSTRAINT fk_uaa FOREIGN KEY (article_id) REFERENCES articles(id)
28);
29
30CREATE TABLE IF NOT EXISTS comments (
31  id INT(11) PRIMARY KEY AUTO_INCREMENT,
32  comment VARCHAR(255) NOT NULL ,
33  article_id INT(11) NOT NULL ,
34  user_id INT(11) NOT NULL ,
35  CONSTRAINT fk_ca FOREIGN KEY (article_id) REFERENCES articles(id),
36  CONSTRAINT fk_cu FOREIGN KEY (user_id) REFERENCES users(id)
37);
38
39CREATE TABLE IF NOT EXISTS likes (
40  id INT(11) PRIMARY KEY AUTO_INCREMENT,
41  article_id INT(11),
42  comment_id INT(11),
43  user_id INT(11) NOT NULL ,
44  CONSTRAINT fk_la FOREIGN KEY (article_id) REFERENCES articles(id),
45  CONSTRAINT fk_lc FOREIGN KEY (comment_id) REFERENCES comments(id),
46  CONSTRAINT fk_lu FOREIGN KEY (user_id) REFERENCES users(id)
47);
48
49//2
50
51insert into likes (article_id, comment_id, user_id)
52select if(u.id % 2 = 0, char_length(u.username), null),
53       if(u.id % 2 <> 0, char_length(u.email), null),
54       u.id
55from users as u
56where u.id between 16 and 20;
57
58//3
59
60update comments
61set comment = (case
62                   when id % 2 = 0 then 'Very good article.'
63                   when id % 3 = 0 then 'This is interesting.'
64                   when id % 5 = 0 then 'I definitely will read the article again.'
65                   when id % 7 = 0 then 'The universe is such an amazing thing.'
66                   else
67                       comment
68    end)
69where id between 1 and 15;
70
71
72//4
73
74DELETE
75FROM articles
76WHERE category_id IS NULL;
77
78
79//5
80
81SELECT title, CONCAT(SUBSTRING(content, 1, 20), '...') AS summary
82FROM articles
83WHERE id IN (27, 23, 16)
84ORDER BY id;
85
86//6
87
88SELECT article_id, title
89FROM articles AS e
90         JOIN users_articles ua on e.id = ua.article_id
91WHERE ua.article_id = user_id
92GROUP BY article_id ORDER BY article_id;
93
94//7
95
96SELECT category,
97       (SELECT COUNT(m.id)
98        FROM articles AS m
99        WHERE e.id = m.category_id) AS articles,
100       (SELECT Count(s.id)
101        FROM likes As s
102                 JOIN articles a on s.article_id = a.id
103        WHERE a.category_id = e.id
104       )                            AS likes
105FROM categories AS e
106GROUP BY id
107ORDER BY likes DESC, articles DESC, e.id;
108
109//8
110
111SELECT title,
112       (SELECT COUNT(m.id)
113        FROM comments AS m
114        WHERE e.id = m.article_id) AS comments
115FROM articles AS e
116WHERE e.category_id = 5
117ORDER BY comments DESC
118LIMIT 1;
119
120//9
121
122SELECT CONCAT(SUBSTRING(comment, 1, 20), '...') AS count_likes
123FROM comments AS e
124         LEFT JOIN likes l on e.id = l.comment_id
125WHERE (SELECT COUNT(id)
126       FROM likes
127       WHERE comment_id = e.id) = 0
128GROUP BY e.id
129ORDER BY e.id DESC;
130
131//10
132
133create function udf_users_articles_count(username VARCHAR(30))
134    RETURNS INTEGER
135BEGIN
136    declare result INT;
137    set result = (SELECT Count(e.id)
138                  FROM articles AS e
139                           JOIN users_articles ua on e.id = ua.article_id
140                           JOIN users u on ua.user_id = u.id
141                  WHERE u.username = username);
142    return result;
143end;
144
145//11
146
147create procedure udp_like_article(username VARCHAR(30), title VARCHAR(30))
148begin
149    declare name_id varchar(30);
150    declare title_id varchar(30);
151    start transaction;
152    if ((select count(u.id) from users as u where u.username = username) = 0) then
153        SIGNAL SQLSTATE '45000'
154            SET MESSAGE_TEXT = 'Non-existent user.';
155        rollback;
156    elseif ((select count(a.id) from articles as a where a.title = title) = 0) then
157        SIGNAL SQLSTATE '45000'
158            SET MESSAGE_TEXT = 'Non-existent article.';
159        rollback;
160    else
161        set name_id := (select u.id
162                        from users as u
163                        where u.username = username);
164        set title_id := (select a.id
165                         from articles as a
166                         where a.title = title);
167
168        insert into likes (article_id, comment_id, user_id)
169            value (title_id, null, name_id);
170        commit;
171    end if;
172end