· 8 years ago · Jun 12, 2018, 03:52 PM
1# -------------
2# Q1
3drop table if exists q;
4create table q as # Do NOT delete this line. Add the query below.
5
6select movie.title
7from actor,role,genre,movie_has_genre,movie
8where actor.last_name='Allen' and actor.actor_id = role.actor_id and role.movie_id = movie.movie_id
9 and genre.genre_name='Comedy' and genre.genre_id=movie_has_genre.genre_id and movie_has_genre.movie_id=movie.movie_id;
10
11
12CALL ValidateQuery(1, 'q');
13drop table if exists q;
14# -------------
15
16
17# -------------
18# Q2
19drop table if exists q;
20create table q as # Do NOT delete this line. Add the query below.
21
22select dir.last_name,mov.title
23from director dir, movie_has_director mhd, movie mov, role rol, actor act
24where dir.director_id=mhd.director_id
25 and mov.movie_id = mhd.movie_id
26 and mov.movie_id= rol.movie_id
27 and act.actor_id=rol.actor_id
28 and act.last_name='ALLEN'
29 #edw tha broume ta id twn directors pou pou exoun skinothetisei panw apo ena eidos tainias
30 and dir.director_id in (
31 select mhd.director_id
32 from movie_has_director mhd, movie_has_genre mhg
33 where mhd.movie_id=mhg.movie_id
34 GROUP BY mhd.director_id
35 having count(*)>=2);
36
37
38CALL ValidateQuery(2, 'q');
39drop table if exists q;
40# -------------
41
42
43# -------------
44# Q3
45drop table if exists q;
46create table q as # Do NOT delete this line. Add the query below.
47
48select a1.last_name
49from movie m1,role r1,actor a1, movie_has_director mhd1,director d1 ,
50 movie m2 ,role r2,movie_has_director mhd2, director d2 , movie_has_genre mhg2, genre g2
51where a1.actor_id = r1.actor_id and m1.movie_id = r1.movie_id /* for the relationship actor-movie*/ and
52 m1.movie_id=mhd1.movie_id and d1.director_id=mhd1.director_id /* for the relationship director-movie*/and
53 a1.last_name = d1.last_name /*this is the first part of the question*/ and
54 a1.actor_id = r2.actor_id and m2.movie_id = r2.movie_id /* for the relationship actor-movie2*/ and
55 m2.movie_id=mhd2.movie_id and d2.director_id=mhd2.director_id /* for the relationship director2-movie2*/ and
56 a1.last_name <> d2.last_name and
57 mhg2.movie_id = m2.movie_id and mhg2.genre_id=g2.genre_id and
58 EXISTS (
59 select *
60 from movie m, movie_has_genre mhg,genre g , movie_has_director thismhd
61 where mhg.movie_id = m.movie_id and mhg.genre_id = g.genre_id /* for the relationship genre-movies */ and
62 g.genre_id=g2.genre_id /*they have the same genre*/and
63 NOT EXISTS( /*we want a1 not to have taken part in this movie8eloume o a1 na mhn paizei sthn tainia auth*/
64 select * /* therefore there should not exist a role relationship between them*/
65 from role
66 where role.actor_id=a1.actor_id and role.movie_id = m.movie_id
67 ) and
68 thismhd.movie_id = m.movie_id and thismhd.director_id = d1.director_id /* it was directed by d1*/
69 )
70group by a1.actor_id; /* If one meets the requirements many times, we want to show him only once*/
71
72CALL ValidateQuery(3, 'q');
73drop table if exists q;
74# -------------
75
76
77# -------------
78# Q4
79drop table if exists q;
80create table q as # Do NOT delete this line. Add the query below.
81
82SELECT 'yes' AS 'answer'
83FROM movie m#balame ena tuxaio from apla epeidi xreiazetain na uparxei ena
84#an uparxei estw kai ena drama tou 1995
85WHERE EXISTS ( SELECT *
86 FROM movie mov,movie_has_genre mhg, genre gen
87 WHERE mov.movie_id=mhg.movie_id
88 AND mhg.genre_id=gen.genre_id
89 AND mov.year='1995'
90 AND gen.genre_name='DRAMA')
91
92 UNION
93
94 SELECT 'no' AS 'answer'
95 FROM movie_has_genre mg #balame ena tuxaio from apla epeidi xreiazetain na uparxei ena
96 #an den uparxei kanena drama rou 1995
97 WHERE NOT EXISTS (
98 SELECT *
99 FROM movie mov,movie_has_genre mhg, genre gen
100 WHERE mov.movie_id=mhg.movie_id
101 AND mhg.genre_id=gen.genre_id
102 AND mov.year='1995'
103 AND gen.genre_name='DRAMA');
104
105
106CALL ValidateQuery(4, 'q');
107drop table if exists q;
108# -------------
109
110
111# -------------
112# Q5
113drop table if exists q;
114create table q as # Do NOT delete this line. Add the query below.
115
116select d1.last_name as director1, d2.last_name as director2
117from director d1, director d2, movie_has_director mhdir1 , movie_has_director mhdir2 , movie
118where d1.director_id > d2.director_id /* This way we avoid comparing a director with himself and selecting each pair twice*/
119 and
120 mhdir1.director_id = d1.director_id and mhdir1.movie_id = movie.movie_id and
121 mhdir2.director_id = d2.director_id and mhdir2.movie_id = movie.movie_id and
122 movie.year >= 2000 and movie.year <= 2006 and (
123 select count(distinct g1.genre_id ) /* we count the genres of d1 that are = with the genres of d2*/
124 from movie_has_director mhd1 , movie m1 , movie_has_genre mhg1 , genre g1,
125 movie_has_director mhd2, movie m2 , movie_has_genre mhg2, genre g2
126 where mhd1.director_id = d1.director_id and mhd1.movie_id=m1.movie_id and
127 mhg1.movie_id = m1.movie_id and mhg1.genre_id = g1.genre_id and
128 mhd2.director_id = d2.director_id and mhd2.movie_id = m2.movie_id and
129 mhg2.movie_id = m2.movie_id and mhg2.genre_id = g2.genre_id and
130 g1.genre_id = g2.genre_id)
131 >= 6;
132
133CALL ValidateQuery(5, 'q');
134drop table if exists q;
135# -------------
136
137
138# -------------
139# Q6
140drop table if exists q;
141create table q as # Do NOT delete this line. Add the query below.
142
143SELECT act.first_name, act.last_name, (SELECT count(DISTINCT director_id) AS 'col1'
144 FROM role rol,movie_has_director mhd
145 WHERE rol.movie_id=mhd.movie_id
146 AND rol.actor_id = act.actor_id
147 GROUP BY rol.actor_id) AS 'count'
148
149FROM actor act
150WHERE act.actor_id IN
151 (SELECT r.actor_id
152 FROM role r
153 GROUP BY actor_id
154 HAVING count(DISTINCT r.movie_id)=3); #distinct giati mporei na exei paiksei stin IDIA tainia duo fores me diaforetiko rolo
155
156
157CALL ValidateQuery(6, 'q');
158drop table if exists q;
159# -------------
160
161
162# -------------
163# Q7
164drop table if exists q;
165create table q as # Do NOT delete this line. Add the query below.
166
167select g.genre_id ,count(distinct d.director_id) as plh8os
168from genre g , movie_has_genre mhg, movie m , movie_has_director mhd, director d
169where g.genre_id in(
170 select genre_id /*select the genres that there exists movie that has only that genre*/
171 from movie_has_genre
172 group by movie_id
173 having count(*)=1) and
174 mhg.genre_id = g.genre_id and mhg.movie_id = m.movie_id /* relationship genre-movie */ and
175 mhd.movie_id = m.movie_id and mhd.director_id = d.director_id /* relationship directors-movies **/
176group by g.genre_id;
177
178CALL ValidateQuery(7, 'q');
179drop table if exists q;
180# -------------
181
182
183# -------------
184# Q8
185drop table if exists q;
186create table q as # Do NOT delete this line. Add the query below.
187
188SELECT actor_id
189FROM role rol, movie_has_genre mhg
190WHERE rol.movie_id=mhg.movie_id
191GROUP BY actor_id
192HAVING count(DISTINCT mhg.genre_id)= ( select count(*) #prepei ta eidi pou exei paiksei o actor na einai tosa
193 from genre); #osa kai to sunolo twn eidwn
194
195CALL ValidateQuery(8, 'q');
196drop table if exists q;
197# -------------
198
199
200# -------------
201# Q9
202drop table if exists q;
203create table q as # Do NOT delete this line. Add the query below.
204
205select g1.genre_id as eidos1, g2.genre_id as eidos2 , count(distinct d.director_id ) as plh8os
206from genre g1,genre g2,director d, movie_has_director mhd1, movie_has_director mhd2 , movie_has_genre mhg1 , movie_has_genre mhg2
207where g1.genre_id > g2.genre_id and /*This way we avoid comparing a director with himself and selecting each pair twice*/
208 d.director_id = mhd1.director_id and mhd1.movie_id = mhg1.movie_id and mhg1.genre_id = g1.genre_id and
209 d.director_id = mhd2.director_id and mhd2.movie_id = mhg2.movie_id and mhg2.genre_id = g2.genre_id
210group by g1.genre_id ,g2.genre_id;
211
212CALL ValidateQuery(9, 'q');
213drop table if exists q;
214# -------------
215
216
217# -------------
218# Q10
219drop table if exists q;
220create table q as # Do NOT delete this line. Add the query below.
221
222select mhg.genre_id
223 ,rol.actor_id
224 , count(*) as tainies
225from movie_has_genre mhg,
226 role rol
227where mhg.movie_id = rol.movie_id
228and rol.movie_id in ( select mhd2.movie_id
229 from movie_has_director mhd2
230 WHERE mhd2.director_id in ( select md.director_id #briskei tous directors pou exoun mono ena eidos tainiwn
231 from movie_has_genre mg,
232 movie_has_director md
233 where mg.movie_id = md.movie_id
234 group by md.director_id
235 having count(*) = 1 )
236 )
237#auto ginetai gia tin periptwsh pou mia tainia exei parapanw apo enan skinotheti
238#opote mporei o enas nai men na exei skhnothetisei ena eidos alla o allos
239#na exei skhnothetisi polla
240and rol.movie_id not in ( select mhd2.movie_id
241 from movie_has_director mhd2
242 WHERE mhd2.director_id in ( select md.director_id
243 from movie_has_genre mg,
244 movie_has_director md
245 where mg.movie_id = md.movie_id
246 group by md.director_id
247 having count(*) >1 )
248 )
249
250group by mhg.genre_id
251 , rol.actor_id
252
253 ;
254
255CALL ValidateQuery(10, 'q');
256drop table if exists q;
257# -------------