-
Notifications
You must be signed in to change notification settings - Fork 2
/
Lab4.tex
305 lines (272 loc) · 15.6 KB
/
Lab4.tex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
\documentclass[12pt,a4paper]{article}
\usepackage[utf8]{inputenc}
\usepackage[T2A]{fontenc}
\usepackage[russian, english]{babel}
\usepackage{graphicx}
\usepackage{listings}
\begin{document}
\section{Постановка задачи}
Цели лабораторной работы:
\begin{enumerate}
\item Язык SQL. Операторы Insert. Update. Delete. Условная логика IF, CASE.
\item Язык SQL. Подзапросы. Несвязанные подзапросы. Связанные подзапросы. Размерность подзапроса.
\end{enumerate}
\section{Типы подзапросов}
По месту нахождения:
\begin{enumerate}
\item В блоке SELECT
\item В блоке FROM
\item В блоке WHERE
\end{enumerate}
По типу связанности с внешним запросом:
\begin{enumerate}
\item Связанные
\item Несвязанные
\end{enumerate}
\section{По месту нахождения}
\subsection{Подзапросы в блоке SELECT}
Получить все фильмы и описания их жанров.
\begin{lstlisting}[language=SQL]
SELECT primaryTitle,
titleType,
(SELECT GROUP_CONCAT(genre) FROM title_genre inner_title_genre WHERE inner_title_genre.titleID = title.titleID) as genres_description
FROM title
INNER JOIN title_rating ON title.titleID = title_rating.titleID
ORDER BY numVotes DESC, averageRating DESC
LIMIT 10;
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+---------------------------------------------------+-----------+-------------------------+
| primaryTitle | titleType | genres_description |
+---------------------------------------------------+-----------+-------------------------+
| The Shawshank Redemption | movie | Drama |
| The Dark Knight | movie | Action,Crime,Drama |
| Inception | movie | Action,Adventure,Sci-Fi |
| Fight Club | movie | Drama |
| Pulp Fiction | movie | Crime,Drama |
| Forrest Gump | movie | Drama,Romance |
| Game of Thrones | tvSeries | Action,Adventure,Drama |
| The Matrix | movie | Action,Sci-Fi |
| The Lord of the Rings: The Fellowship of the Ring | movie | Adventure,Drama,Fantasy |
| The Lord of the Rings: The Return of the King | movie | Adventure,Drama,Fantasy |
+---------------------------------------------------+-----------+-------------------------+
10 rows in set (0.05 sec)
\end{lstlisting}
\subsection{Подзапросы в блоке FROM}
Получить все фильмы и описания их жанров.
\begin{lstlisting}[language=SQL]
SELECT title_genre_details.titleID, primaryTitle, genre_details, numVotes, averageRating
FROM (SELECT titleID, GROUP_CONCAT(genre) as genre_details FROM imdb_db_part.title_genre GROUP BY titleID) title_genre_details
INNER JOIN title ON title_genre_details.titleID = title.titleID
INNER JOIN title_rating ON title.titleID = title_rating.titleID
ORDER BY numVotes DESC, averageRating DESC
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+---------------------------------------------------+-------------------------+----------+---------------+
| titleID | primaryTitle | genre_details | numVotes | averageRating |
+-----------+---------------------------------------------------+-------------------------+----------+---------------+
| tt0111161 | The Shawshank Redemption | Drama | 2149031 | 9.3 |
| tt0468569 | The Dark Knight | Action,Crime,Drama | 2118625 | 9 |
| tt1375666 | Inception | Action,Adventure,Sci-Fi | 1883746 | 8.8 |
| tt0137523 | Fight Club | Drama | 1716980 | 8.8 |
| tt0110912 | Pulp Fiction | Crime,Drama | 1686388 | 8.9 |
| tt0109830 | Forrest Gump | Drama,Romance | 1653584 | 8.8 |
| tt0944947 | Game of Thrones | Action,Adventure,Drama | 1596735 | 9.4 |
| tt0133093 | The Matrix | Action,Sci-Fi | 1546559 | 8.7 |
| tt0120737 | The Lord of the Rings: The Fellowship of the Ring | Adventure,Drama,Fantasy | 1542044 | 8.8 |
| tt0167260 | The Lord of the Rings: The Return of the King | Adventure,Drama,Fantasy | 1526805 | 8.9 |
+-----------+---------------------------------------------------+-------------------------+----------+---------------+
10 rows in set (0.44 sec)
\end{lstlisting}
\subsection{Подзапросы в блоке WHERE}
Получить все фильмы у которых только 2 жанра.
\begin{lstlisting}[language=SQL]
SELECT title.titleID, primaryTitle, averageRating, numVotes FROM title
INNER JOIN title_rating ON title.titleID = title_rating.titleID
WHERE 2 = (SELECT COUNT(*) FROM title_genre WHERE title.titleID = title_genre.titleID)
ORDER BY numVotes DESC, averageRating DESC
LIMIT 10
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+-----------------------+---------------+----------+
| titleID | primaryTitle | averageRating | numVotes |
+-----------+-----------------------+---------------+----------+
| tt0110912 | Pulp Fiction | 8.9 | 1686388 |
| tt0109830 | Forrest Gump | 8.8 | 1653584 |
| tt0133093 | The Matrix | 8.7 | 1546559 |
| tt0068646 | The Godfather | 9.2 | 1474927 |
| tt1345836 | The Dark Knight Rises | 8.4 | 1415027 |
| tt1853728 | Django Unchained | 8.4 | 1243326 |
| tt0372784 | Batman Begins | 8.2 | 1219308 |
| tt0120815 | Saving Private Ryan | 8.6 | 1138073 |
| tt0209144 | Memento | 8.4 | 1048905 |
| tt1130884 | Shutter Island | 8.1 | 1031033 |
+-----------+-----------------------+---------------+----------+
10 rows in set (0.05 sec)
\end{lstlisting}
Получить все фильмы у которых жанр Comedy или Action.
\begin{lstlisting}[language=SQL]
SELECT title.titleID, primaryTitle, averageRating, numVotes FROM title
INNER JOIN title_rating ON title.titleID = title_rating.titleID
WHERE EXISTS (SELECT 1 FROM title_genre WHERE title_genre.genre IN ('Comedy', 'Action') AND title_genre.titleID = title.titleID)
ORDER BY numVotes DESC, averageRating DESC
LIMIT 10
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+------------------------------------------------+---------------+----------+
| titleID | primaryTitle | averageRating | numVotes |
+-----------+------------------------------------------------+---------------+----------+
| tt0468569 | The Dark Knight | 9 | 2118625 |
| tt1375666 | Inception | 8.8 | 1883746 |
| tt0944947 | Game of Thrones | 9.4 | 1596735 |
| tt0133093 | The Matrix | 8.7 | 1546559 |
| tt1345836 | The Dark Knight Rises | 8.4 | 1415027 |
| tt0172495 | Gladiator | 8.5 | 1240337 |
| tt0372784 | Batman Begins | 8.2 | 1219308 |
| tt0848228 | The Avengers | 8 | 1203436 |
| tt0076759 | Star Wars: Episode IV - A New Hope | 8.6 | 1142460 |
| tt0080684 | Star Wars: Episode V - The Empire Strikes Back | 8.7 | 1073886 |
+-----------+------------------------------------------------+---------------+----------+
10 rows in set (0.05 sec)
\end{lstlisting}
\section{По типу связи с внешним запросов}
\subsection{Связанные}
Получить фильмы для которых нет информации о съемочной группе.
\begin{lstlisting}[language=SQL]
SELECT title.titleID, primaryTitle, numVotes, averageRating FROM title
INNER JOIN title_rating ON title.titleID = title_rating.titleID
WHERE NOT EXISTS (
SELECT 1 FROM title_principals_profession
WHERE title_principals_profession.titleID = title.titleID
) AND titleType = 'movie'
ORDER BY numVotes DESC, averageRating DESC
LIMIT 5
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+-------------------------------------------+----------+---------------+
| titleID | primaryTitle | numVotes | averageRating |
+-----------+-------------------------------------------+----------+---------------+
| tt5013056 | Dunkirk | 483948 | 7.9 |
| tt5052448 | Get Out | 424443 | 7.7 |
| tt5463162 | Deadpool 2 | 419898 | 7.7 |
| tt4972582 | Split | 377084 | 7.3 |
| tt5027774 | Three Billboards Outside Ebbing, Missouri | 363790 | 8.2 |
+-----------+-------------------------------------------+----------+---------------+
5 rows in set (0.19 sec)
\end{lstlisting}
\subsection{Несвязанные}
Получить фильмы для которых нет информации о съемочной группе.
\begin{lstlisting}[language=SQL]
SELECT title.titleID, primaryTitle, numVotes, averageRating FROM title
INNER JOIN title_rating ON title.titleID = title_rating.titleID
WHERE title.titleID NOT IN (SELECT titleID FROM title_principals_profession) AND titleType = 'movie'
ORDER BY numVotes DESC, averageRating DESC
LIMIT 5
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+-------------------------------------------+----------+---------------+
| titleID | primaryTitle | numVotes | averageRating |
+-----------+-------------------------------------------+----------+---------------+
| tt5013056 | Dunkirk | 483948 | 7.9 |
| tt5052448 | Get Out | 424443 | 7.7 |
| tt5463162 | Deadpool 2 | 419898 | 7.7 |
| tt4972582 | Split | 377084 | 7.3 |
| tt5027774 | Three Billboards Outside Ebbing, Missouri | 363790 | 8.2 |
+-----------+-------------------------------------------+----------+---------------+
5 rows in set (0.18 sec)
\end{lstlisting}
\section{Примеры запросов}
Получить деятелей киноиндустрии отсортированных по количеству фильмов
в которых они принимали участия и которые находятся в топ 100000 по рейтингу.
\begin{lstlisting}[language=SQL]
SELECT nameID, primaryName, (
SELECT COUNT(*) FROM title_principals_profession
INNER JOIN (SELECT titleID FROM title_rating ORDER BY numVotes DESC, averageRating DESC LIMIT 10000) as title_rating_ordered
ON title_principals_profession.titleID = title_rating_ordered.titleID
WHERE title_principals_profession.nameID = name.nameID
) as count_of_films
FROM name
ORDER BY count_of_films DESC
LIMIT 10
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+---------------------+----------------+
| nameID | primaryName | count_of_films |
+-----------+---------------------+----------------+
| nm0186505 | Bryan Cranston | 75 |
| nm0000134 | Robert De Niro | 74 |
| nm0666739 | Aaron Paul | 71 |
| nm0319213 | Vince Gilligan | 68 |
| nm1125275 | David Benioff | 68 |
| nm0606487 | Dean Norris | 67 |
| nm0000025 | Jerry Goldsmith | 65 |
| nm0348152 | Anna Gunn | 63 |
| nm0006133 | James Newton Howard | 61 |
| nm0748784 | Scott Rudin | 61 |
+-----------+---------------------+----------------+
10 rows in set (11.71 sec)
\end{lstlisting}
Получить сериалы и отсортированные по количеству серий.
\begin{lstlisting}[language=SQL]
SELECT title.titleID, primaryTitle, (
SELECT COUNT(*) FROM title_episode
WHERE parentTitleID = title.titleID
) as episode_count
FROM title
INNER JOIN title_rating ON title.titleID = title_rating.titleID
WHERE titleType = 'tvSeries'
ORDER BY numVotes DESC, averageRating DESC
LIMIT 10
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+-----------------------+---------------+
| titleID | primaryTitle | episode_count |
+-----------+-----------------------+---------------+
| tt0944947 | Game of Thrones | 73 |
| tt0903747 | Breaking Bad | 62 |
| tt1520211 | The Walking Dead | 133 |
| tt1475582 | Sherlock | 15 |
| tt0108778 | Friends | 236 |
| tt4574334 | Stranger Things | 25 |
| tt0898266 | The Big Bang Theory | 280 |
| tt0773262 | Dexter | 96 |
| tt0460649 | How I Met Your Mother | 208 |
| tt0411008 | Lost | 118 |
+-----------+-----------------------+---------------+
10 rows in set (0.16 sec)
\end{lstlisting}
Аналог с использованием JOIN.
\begin{lstlisting}[language=SQL]
SELECT title.titleID, primaryTitle, COUNT(*) as episode_count FROM title
INNER JOIN title_episode ON title.titleID = title_episode.parentTitleID
INNER JOIN title_rating ON title.titleID = title_rating.titleID
GROUP BY titleID
ORDER BY numVotes DESC, averageRating DESC
LIMIT 10
\end{lstlisting}
\begin{lstlisting}[basicstyle = \tiny\ttfamily, columns = fixed]
+-----------+-----------------------+---------------+
| titleID | primaryTitle | episode_count |
+-----------+-----------------------+---------------+
| tt0944947 | Game of Thrones | 73 |
| tt0903747 | Breaking Bad | 62 |
| tt1520211 | The Walking Dead | 133 |
| tt1475582 | Sherlock | 15 |
| tt0108778 | Friends | 236 |
| tt4574334 | Stranger Things | 25 |
| tt0898266 | The Big Bang Theory | 280 |
| tt0773262 | Dexter | 96 |
| tt0460649 | How I Met Your Mother | 208 |
| tt0411008 | Lost | 118 |
+-----------+-----------------------+---------------+
10 rows in set (0.16 sec)
\end{lstlisting}
\section{Используемые источники}
\begin{enumerate}
\item https://dev.mysql.com/doc/refman/8.0/en/select.html
\item https://dev.mysql.com/doc/refman/8.0/en/select-into.html
\item https://dev.mysql.com/doc/refman/8.0/en/join.html
\item https://dev.mysql.com/doc/refman/8.0/en/union.html
\item https://dev.mysql.com/doc/refman/8.0/en/subqueries.html
\end{enumerate}
\end{document}