IMDB-SQLAnalysis/missing_data1.sql at main · koustavin/IMDB-SQLAnalysis · GitHub

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
ALTER TABLE title_ratings ADD FOREIGN KEY (tconst) REFERENCES title_basics(tconst) ON DELETE CASCADE;

SELECT * FROM imdb.title_basics tb
WHERE tb.tconst NOT IN
(SELECT tr.tconst FROM imdb.title_ratings tr);

SELECT tr.tconst
FROM imdb.title_ratings tr
LEFT JOIN imdb.title_basics tb
ON tr.tconst = tb.tconst
WHERE tb.tconst is NULL;

SELECT te.tconst
FROM imdb.title_episode te
LEFT JOIN imdb.title_basics tb
ON te.tconst = tb.tconst
WHERE tb.tconst is NULL;

SELECT * FROM imdb.title_basics tb
WHERE tb.originalTitle LIKE '%Batman%';

SELECT DISTINCT titleId, title FROM
imdb.title_akas
WHERE titleId IN
('tt11957490',
'tt15088914',
'tt16767666',
'tt25957158'); -- 3 titles

SELECT * FROM
imdb.title_basics
WHERE tconst IN
('tt11957490',
'tt15088914',
'tt16767666',
'tt25957158'); -- no records

SELECT * FROM
imdb.title_episode
WHERE tconst IN
('tt11957490',
'tt15088914',
'tt16767666',
'tt25957158'); -- 1 record

SELECT * FROM
imdb.title_episode
WHERE parentTconst IN
('tt11957490',
'tt15088914',
'tt16767666',
'tt25957158'); -- multiple episodes under parent tt11957490

SELECT * FROM
imdb.title_principals
WHERE tconst IN
('tt11957490',
'tt15088914',
'tt16767666',
'tt25957158'); -- 3 titles

SELECT * FROM
imdb.title_ratings
WHERE tconst IN
('tt11957490',
'tt15088914',
'tt16767666',
'tt25957158'); -- 4 titles