· 6 years ago · Mar 13, 2019, 06:24 PM
1--Creare tabele baza de date filmografie
2
3
4drop table if exists filme;
5create table filme(
6 CodFilm BIGINT
7 constraint pk_filme primary key
8 constraint nn_filme not null
9 constraint un_filme unique,
10 NumeFilm varchar(255),
11 AnLansare int,
12 CodProducator int,
13 CodRegizor int,
14 Tara varchar(50),
15 CostUSD bigint);
16
17drop table if exists cineasti;
18create table cineasti(
19 CodCineast bigint
20 constraint pk_cineasti primary key
21 constraint nn_cineasti not null
22 constraint un_cineasti unique,
23 NumeCineast varchar(50),
24 DataNasterii date,
25 Tara varchar(50));
26
27drop table if exists incasari;
28create table incasari(
29 CodFilm bigint,
30 constraint pk_incasari PRIMARY KEY (CodFilm, Tara, An),
31 constraint un_incasari UNIQUE (CodFilm, Tara, An),
32 Tara varchar(50),
33 An int,
34 Incasari_USD bigint);
35
36
37drop table if exists tipuri_premii;
38create table tipuri_premii(
39 CodPremiu bigint
40 constraint pk_tipuri_premii primary key,
41 NumePremiu varchar(50),
42 Localitate varchar(50),
43 Tara varchar(50));
44
45drop table if exists premii_obtinute;
46create table premii_obtinute(
47 IdPremiere bigint
48 constraint pk_premii_obtinute primary key
49 constraint nn_premii_obtinute not null
50 constraint un_premii_obtinute unique,
51 CodPremiu int,
52 AnDecernare int,
53 CodFilm bigint,
54 CodCineast bigint);
55
56
57drop table if exists distributie;
58create table distributie(
59 CodFilm bigint,
60 Rol varchar(50),
61 CodActor int,
62 OnorariuUSD bigint,
63 constraint pk_distributie primary key(CodFilm, Rol),
64 constraint un_distributie unique (CodFilm, Rol));
65
66
67-----------------------------------------------
68
69alter table premii_obtinute add constraint fk_premiiobtinute_tipuripremii FOREIGN KEY (CodPremiu) REFERENCES tipuri_premii (CodPremiu)
70 ON DELETE RESTRICT ON UPDATE CASCADE;
71
72CREATE INDEX IFK_premii_obtinute_CodPremiu ON premii_obtinute (CodPremiu);
73
74alter table premii_obtinute add constraint fk_premiiobtinute_cineasti FOREIGN KEY (CodCineast) REFERENCES cineasti (CodCineast)
75 ON DELETE RESTRICT ON UPDATE CASCADE;
76
77CREATE INDEX IFK_premii_obtinute_CodCineast ON premii_obtinute (CodCineast);
78
79alter table premii_obtinute add constraint fk_premiiobtinute_filme FOREIGN KEY (CodFilm) REFERENCES filme (CodFilm)
80 ON DELETE RESTRICT ON UPDATE CASCADE;
81
82CREATE INDEX IFK_premii_obtinute_CodFilm ON premii_obtinute (CodFilm);
83
84alter table distributie add constraint fk_distributie_cineasti FOREIGN KEY (CodActor) REFERENCES cineasti (CodCineast)
85 ON DELETE RESTRICT ON UPDATE CASCADE;
86
87CREATE INDEX IFK_distributie_CodActor ON distributie (CodActor);
88
89alter table distributie add constraint fk_distributie_filme FOREIGN KEY (CodFilm) REFERENCES filme (CodFilm)
90 ON DELETE RESTRICT ON UPDATE CASCADE;
91
92CREATE INDEX IFK_distributie_CodFilm ON distributie (CodFilm);
93
94alter table incasari add constraint fk_incasari_filme FOREIGN KEY (CodFilm) REFERENCES filme (CodFilm)
95 ON DELETE RESTRICT ON UPDATE CASCADE;
96
97CREATE INDEX IFK_incasari_CodFilm ON incasari (CodFilm);
98-------------------------
99Populare BD Filmografie
100-------------------------
101--insert tipuri premii
102insert into tipuri_premii values (123, 'Oscar-regie', 'Hollywood', 'USA');
103insert into tipuri_premii values (124, 'Oscar-best film', 'Hollywood', 'USA');
104insert into tipuri_premii values (125, 'Oscar-best musical film', 'Hollywood', 'USA');
105insert into tipuri_premii values (126, 'Palme d’Or', 'Cannes', 'Franta');
106insert into tipuri_premii values (127, 'Ursul de Aur', 'Festivalul de la Berlin', 'Germania');
107insert into tipuri_premii values (128, 'Trofeul Transilvania', 'TIFF', 'Romania');
108insert into tipuri_premii values (129, 'Oscar-best decoration', 'Hollywood', 'USA');
109insert into tipuri_premii values (130, 'Oscar-best sound', 'Hollywood', 'USA');
110
111--insert cineasti
112insert into cineasti values (18, 'Tom Hanks', '1957-06-12', 'USA');
113insert into cineasti values (19, 'Gary Sinise', '1955-03-17', 'USA');
114insert into cineasti values (20, 'Geoffrey Blake', '1962-08-20', 'USA');
115insert into cineasti values (21, 'Keanu Reeves', '1964-09-02', 'USA');
116insert into cineasti values (22, 'Carrie-Anne Moss', '1967-08-21', 'USA');
117insert into cineasti values (23, 'Marcus Chong', '1967-07-08', 'USA');
118insert into cineasti values (24, 'Angelina Jolie', '1975-06-04', 'USA');
119insert into cineasti values (25, 'Elle Fanning', '1998-04-08', 'USA');
120insert into cineasti values (26, 'Sharlto Copley', '1973-11-27', 'Africa de Sud');
121insert into cineasti values (27, 'Javier Bardem', '1969-03-01', 'Spain');
122insert into cineasti values (28, 'Josh Brolin', '1968-02-12', 'USA');
123insert into cineasti values (29, 'Barry Corbin', '1940-10-16', 'USA');
124insert into cineasti values (30, 'Maia Morgenstern', '1962-05-01', 'Romania');
125insert into cineasti values (31, 'Victor Rebenciug', '1933-02-10', 'Romania');
126insert into cineasti values (32, 'Ionel Mihailescu', '1959-05-21', 'Romania');
127insert into cineasti values (33, 'Chiwetel Ejiofor', '1977-07-10', 'Marea Britanie');
128insert into cineasti values (34, 'Daniel Olbrychski', '1945-02-27', 'Polonia');
129insert into cineasti values (35, 'Sylvester Stallone', '1946-07-06', 'USA');
130insert into cineasti values (36, 'Jason Statham', '1967-07-26', 'Marea Britanie');
131insert into cineasti values (37, 'Dolph Lundgren', '1957-11-03', 'Suedia');
132insert into cineasti values (38, 'Sung Kang', '1972-04-08', 'USA');
133insert into cineasti values (39, 'Adewale Akinnuoye-Agbaje', '1967-08-22', 'Marea Britanie');
134insert into cineasti values (40, 'Mary Elizabeth Winstead', '1984-11-28', 'USA');
135insert into cineasti values (41, 'Ulrich Thomsen', '1963-12-06', 'Danemarca');
136insert into cineasti values (42, 'Kristofer Hivju', '1978-12-07', 'Norvegia');
137insert into cineasti values (43, 'Arnold Schwarzenegger', '1947-07-30', 'Austria');
138insert into cineasti values (44, 'Jet Li', '1963-04-23', 'China');
139insert into cineasti values (45, 'Mimi Leder', '1952-01-26', 'USA');
140insert into cineasti values (46, 'George Clooney', '1961-05-06', 'USA');
141insert into cineasti values (47, 'Marcel Iures', '1951-08-02', 'Romania');
142insert into cineasti values (48, 'Rene Medvešek', '1963-06-21', 'Croatia');
143insert into cineasti values (49, 'Mel Gibson', '1956-01-03', 'USA');
144insert into cineasti values (50, 'Jim Caviezel', '1968-09-26', 'USA');
145insert into cineasti values (51, 'Monica Belucci', '1964-10-30', 'Italia');
146insert into cineasti values (52, 'Maia Morgenstern', '1962-05-01', 'Romania');
147insert into cineasti values (53, 'Jean-Baptiste Maunier', '1990-12-20', 'Franta');
148insert into cineasti values (54, 'Gérard Jugnot', '1951-05-04', 'Franta');
149insert into cineasti values (55, 'Jacques Perrin', '1941-07-13', 'Franta');
150insert into cineasti values (56, 'Mathieu Amalric', '1965-10-25', 'Franta');
151insert into cineasti values (57, 'Emmanuelle Seigner', '1966-06-22', 'Franta');
152insert into cineasti values (58, 'Anne Consigny', '1963-05-23', 'Franta');
153insert into cineasti values (59, 'Steven Spielber', '1946-12-18', 'USA');
154insert into cineasti values (60, 'Jeff Goldblum', '1952-10-22', 'USA');
155insert into cineasti values (61, 'Laura Dern', '1967-02-10', 'USA');
156insert into cineasti values (62, 'Sam Neill', '1947-09-14', 'Marea Britanie');
157insert into cineasti values (63, 'Taika Waititi', '1975-08-16', 'Noua Zeelanda');
158insert into cineasti values (64, 'Chris Hemsworth', '1983-08-11', 'Australia');
159insert into cineasti values (65, 'Tessa Thompson', '1983-10-03', 'USA');
160insert into cineasti values (66, 'Josh Boone', '1979-04-05', 'USA');
161insert into cineasti values (67, 'Shailene Woodley', '1991-11-15', 'USA');
162insert into cineasti values (68, 'Ansel Helgort', '1994-03-14', 'USA');
163insert into cineasti values (69, 'Sergiu Nicolaescu', '1930-04-13', 'Romania');
164insert into cineasti values (70, 'Bud Spencer', '1929-10-31', 'Italia');
165
166
167--insert firme
168insert into filme values (1001, 'Forrest Gump', 1994, 12, 17, 'USA', 8000000);
169insert into filme values (1002, 'The Matrix', 1999, 13, 18, 'USA', 7000000);
170insert into filme values (1003, 'Maleficent', 2014, 14, 19, 'USA', 3000000);
171insert into filme values (1004, 'No Country for Old Men', 2007, 15, 20, 'USA', 6500000);
172insert into filme values (1005, 'Balanta', 1992, 16, 210, 'Romania', 300000);
173insert into filme values (1006, 'Salt', 2010, 17, 22, 'USA', 50000000);
174insert into filme values (1007, 'The Expendables', 2010, 18, 35, 'USA', 5400000);
175insert into filme values (1008, 'Bullet to the Head', 2012, 19, 24, 'USA', 10000000);
176insert into filme values (1009, 'The Thing', 2011, 20, 25, 'USA', 7580000);
177insert into filme values (1010, 'The Expendables 2', 2012, 21, 26, 'USA', 7000000);
178insert into filme values (1011, 'The Peacemaker', 1997, 22, 45, 'USA', 700000);
179insert into filme values (1012, 'The Passion of the Christ', 2004, 23, 49, 'USA', 45000000);
180insert into filme values (1013, 'Les Choristes', 2004, 24, 513, 'Franta', 450000);
181insert into filme values (1014, 'Le scaphandre et le papillon', 2007, 25, 514, 'Franta', 6500000);
182insert into filme values (1015, 'Jurassic Park', 1993, 26, 59, 'USA', 90000000);
183insert into filme values (1016, 'Thor: Ragnarok', 2017, 27, 63, 'USA', 7000000);
184insert into filme values (1017, 'The Fault in Our Stars', 2014, 28, 66, 'USA', 9000000);
185insert into filme values (1018, 'The post', 2017, 29, 59, 'USA', 70000000);
186insert into filme values (1019, 'The Adventures of Tintin: The Secret of the Unicorn', 2011, 30, 59, 'USA', 100000000);
187insert into filme values (1020, 'Cu mainile curate', 1972, 31, 69, 'Romania', 7000000);
188insert into filme values (1021, 'Ultimul cartus', 1973, 32, 69, 'Romania', 80000000);
189insert into filme values (1022, 'Duelul', 1981, 33, 69, 'Romania', 90000000);
190insert into filme values (1023, 'Rocky Balboa', 2006, 34, 35, 'USA', 60000000);
191insert into filme values (1024, 'Rambo', 2008, 35,35, 'USA', 50000000);
192insert into filme values (1025, 'The Mechanik', 2005, 36, 37, 'USA', 3000000);
193insert into filme values (1026, 'Command Performance', 2009, 37, 37, 'USA', 2500000);
194insert into filme values (1027, 'Missionary Man', 2007, 38, 37, 'USA', 3750000);
195insert into filme values (1028, 'Braveheart', 1995, 39, 49, 'USA', 10000000);
196insert into filme values (1029, 'Hacksaw Ridge', 2016, 40, 49, 'USA', 1500000);
197insert into filme values (1030, 'Banana Joe', 1982, 41, 70, 'Italia', 1500000);
198insert into filme values (1031, 'Watch out, We are mad!', 1974, 42, 70, 'Italia', 4000000);
199insert into filme values (1032, 'Odds and Evens', 1978, 43, 70, 'Italia', 3500000);
200
201--insert premii obtinute
202insert into premii_obtinute values (11111, 123, 1995, 1001, 18);
203insert into premii_obtinute values (11112, 124, 1995, 1001, 18);
204insert into premii_obtinute values (11113, 129, 1996, 1001, 18);
205insert into premii_obtinute values (11114, 129, 2001, 1002, 21);
206insert into premii_obtinute values (11115, 126, 2000, 1002, 21);
207insert into premii_obtinute values (11116, 130, 2000, 1002, 21);
208insert into premii_obtinute values (11117, 127, 2015, 1003, 24);
209insert into premii_obtinute values (11118, 124, 2015, 1003, 24);
210insert into premii_obtinute values (11119, 123, 2008, 1004, 27);
211insert into premii_obtinute values (11120, 125, 2008, 1004, 27);
212insert into premii_obtinute values (11121, 125, 1992, 1005, 30);
213insert into premii_obtinute values (11122, 126, 1993, 1005, 30);
214insert into premii_obtinute values (11123, 123, 2010, 1006, 33);
215insert into premii_obtinute values (11124, 124, 2011, 1006, 33);
216insert into premii_obtinute values (11125, 128, 2012, 1006, 33);
217insert into premii_obtinute values (11126, 130, 2013, 1006, 33);
218insert into premii_obtinute values (11127, 123, 2011, 1007, 37);
219insert into premii_obtinute values (11128, 124, 2012, 1007, 37);
220insert into premii_obtinute values (11129, 127, 2013, 1007, 37);
221insert into premii_obtinute values (11130, 123, 2013, 1008, 37);
222insert into premii_obtinute values (11131, 124, 2013, 1008, 37);
223insert into premii_obtinute values (11132, 129, 2013, 1008, 37);
224insert into premii_obtinute values (11133, 124, 2012, 1009, 40);
225insert into premii_obtinute values (11134, 129, 2013, 1009, 40);
226insert into premii_obtinute values (11135, 130, 2013, 1009, 40);
227insert into premii_obtinute values (11136, 123, 2013, 1010, 35);
228insert into premii_obtinute values (11137, 124, 2013, 1010, 35);
229insert into premii_obtinute values (11138, 123, 1997, 1011, 45);
230insert into premii_obtinute values (11139, 130, 1998, 1011, 45);
231insert into premii_obtinute values (11140, 124, 2004, 1012, 49);
232insert into premii_obtinute values (11141, 129, 2005, 1012, 49);
233insert into premii_obtinute values (11142, 126, 2004, 1013, 50);
234
235--insert incasari
236insert into incasari values (1001, 'USA', 1994, 20000000);
237insert into incasari values (1001, 'USA', 1995, 50000000);
238insert into incasari values (1001, 'USA', 1996, 45000000);
239insert into incasari values (1002, 'USA', 1995, 450000000);
240insert into incasari values (1002, 'USA', 1996, 400000000);
241insert into incasari values (1003, 'USA', 2014, 200000000);
242insert into incasari values (1003, 'USA', 2015, 80000000);
243insert into incasari values (1003, 'USA', 2016, 7000000);
244insert into incasari values (1004, 'USA', 2007, 80000000);
245insert into incasari values (1005, 'Romania', 1992, 2000000);
246insert into incasari values (1005, 'Romania', 1993, 500000);
247insert into incasari values (1005, 'Romania', 1994, 400000);
248insert into incasari values (1006, 'USA', 2010, 60000000);
249insert into incasari values (1006, 'USA', 2011, 65000000);
250insert into incasari values (1006, 'USA', 2012, 50000000);
251insert into incasari values (1007, 'USA', 2010, 90000000);
252insert into incasari values (1007, 'USA', 2011, 89520000);
253insert into incasari values (1007, 'USA', 2013, 70000000);
254insert into incasari values (1008, 'USA', 2012, 75000000);
255insert into incasari values (1008, 'USA', 2013, 55000000);
256insert into incasari values (1008, 'USA', 2014, 65000000);
257insert into incasari values (1009, 'USA', 2011, 40000000);
258insert into incasari values (1009, 'USA', 2012, 32500000);
259insert into incasari values (1010, 'USA', 2012, 37000000);
260insert into incasari values (1010, 'USA', 2013, 35000000);
261insert into incasari values (1011, 'USA', 1997, 19000000);
262insert into incasari values (1011, 'USA', 1998, 17500000);
263insert into incasari values (1012, 'USA', 2004, 23400000);
264insert into incasari values (1012, 'USA', 2005, 20000000);
265insert into incasari values (1013, 'Franta', 2005, 4200000);
266insert into incasari values (1014, 'Franta', 2016, 5400000);
267insert into incasari values (1029, 'USA', 2016, 650000000);
268insert into incasari values (1015, 'Franta', 2016, 50000000);
269insert into incasari values (1010, 'USA', 2016, 5350000);
270insert into incasari values (1030, 'Italia', 2016, 15750000);
271insert into incasari values (1031, 'Italia', 2016, 1700000);
272insert into incasari values (1032, 'Italia', 2016, 17040000);
273
274--insert distributie
275insert into distributie values (1001, 'Principal', 18, 5000000);
276insert into distributie values (1001, 'Secundar', 19, 2300000);
277insert into distributie values (1001, 'Episodic', 20, 1500000);
278insert into distributie values (1002, 'Principal', 21, 7000000);
279insert into distributie values (1002, 'Secundar', 22, 5500000);
280insert into distributie values (1002, 'Episodic', 23, 1000000);
281insert into distributie values (1003, 'Principal', 24, 10000000);
282insert into distributie values (1003, 'Secundar', 25, 5000000);
283insert into distributie values (1003, 'Episodic', 26, 1000000);
284insert into distributie values (1004, 'Principal', 27, 9000000);
285insert into distributie values (1004, 'Secundar', 28, 4500000);
286insert into distributie values (1004, 'Episodic', 29, 1250000);
287insert into distributie values (1005, 'Principal', 30, 7800000);
288insert into distributie values (1005, 'Secundar', 31, 3500000);
289insert into distributie values (1005, 'Episodic', 32, 790000);
290insert into distributie values (1006, 'Principal', 24, 10000000);
291insert into distributie values (1006, 'Secundar', 33, 5000000);
292insert into distributie values (1006, 'Episodic', 34, 1150000);
293insert into distributie values (1007, 'Principal', 35, 15000000);
294insert into distributie values (1007, 'Secundar', 36, 8950000);
295insert into distributie values (1007, 'Episodic', 37, 4000000);
296insert into distributie values (1008, 'Principal', 35, 13500000);
297insert into distributie values (1008, 'Secundar', 38, 7000000);
298insert into distributie values (1008, 'Episodic', 39, 2000000);
299insert into distributie values (1009, 'Principal', 40, 9000000);
300insert into distributie values (1009, 'Secundar', 41, 4500000);
301insert into distributie values (1009, 'Episodic', 42, 670000);
302insert into distributie values (1010, 'Principal', 35, 10000000);
303insert into distributie values (1010, 'Secundar', 43, 5000000);
304insert into distributie values (1010, 'Episodic', 44, 1000000);
305insert into distributie values (1011, 'Principal', 46, 20000000);
306insert into distributie values (1011, 'Secundar', 47, 10000000);
307insert into distributie values (1011, 'Episodic', 48, 1000000);
308insert into distributie values (1012, 'Principal', 50, 7500000);
309insert into distributie values (1012, 'Secundar', 51, 5000000);
310insert into distributie values (1012, 'Episodic', 52, 1000000);
311insert into distributie values (1013, 'Principal', 53, 6500000);
312insert into distributie values (1013, 'Secundar', 54, 4000000);
313insert into distributie values (1013, 'Episodic', 55, 1500000);
314insert into distributie values (1014, 'Principal', 56, 6500000);
315insert into distributie values (1014, 'Secundar', 57, 3000000);
316insert into distributie values (1014, 'Episodic', 58, 500000);
317insert into distributie values (1015, 'Principal', 60, 8000000);
318insert into distributie values (1015, 'Secundar', 61, 5000000);
319insert into distributie values (1015, 'Episodic', 62, 1000000);
320insert into distributie values (1016, 'Principal', 64, 7000000);
321insert into distributie values (1016, 'Secundar', 65, 3500000);
322insert into distributie values (1016, 'Episodic', 60, 1500000);
323insert into distributie values (1017, 'Principal', 67, 8000000);
324insert into distributie values (1017, 'Secundar', 68, 3000000);
325insert into distributie values (1017, 'Episodic', 61, 750000);
326
327--1)
328select numepremiu "Premii obtinute"
329 from tipuri_premii tp
330 inner join premii_obtinute po on tp.codpremiu=po.codpremiu
331 inner join filme f on po.codfilm=f.codfilm
332 where numefilm='Forrest Gump';
333--2)
334select andecernare AS "An decernare" , Count(*) AS "numar premii"
335 from tipuri_premii tp inner join premii_obtinute po on tp.codpremiu=po.codpremiu
336 where andecernare=2013 and tp.codpremiu IN (123, 124, 125, 129, 130)
337 group by andecernare
338
339--3)
340select tara, SUM(incasari_usd) as incasari
341 from incasari
342 where an=2016
343 group by tara
344 Order by incasari desc
345 Limit 1
346
347--4)
348select numefilm as "Nume film"
349 from filme f
350 inner join cineasti ci on f.codregizor=ci.codcineast
351 where ci.tara='USA'
352INTERSECT
353select numefilm as "Nume film"
354 from filme f
355 inner join distributie d on d.codfilm=f.codfilm
356 inner join cineasti c on c.codcineast=d.codactor
357 where c.tara='Romania'