A4hDHKYB

1DROP TABLE IF EXISTS filme, cineasti, distributie, tipuri_premii, incasari, premii_obtinute;
2
3DROP DOMAIN IF EXISTS dom_an;
4DROP DOMAIN IF EXISTS dom_coduri;
5DROP DOMAIN IF EXISTS dom_financiar;
6
7/*CREATE THE DOMAINS*/
8
9CREATE DOMAIN dom_an INTEGER 
10	CHECK(VALUE IS NOT NULL AND VALUE BETWEEN 1895 AND 2050);
11
12CREATE DOMAIN dom_coduri INTEGER
13	CHECK(VALUE IS NOT NULL AND VALUE BETWEEN 0 AND 10000);
14
15CREATE DOMAIN dom_financiar BIGINT
16	CHECK(VALUE IS NOT NULL AND VALUE > 0);
17	
18/*CREATE THE TABLES*/
19
20CREATE TABLE filme (
21    CodFilm SERIAL PRIMARY KEY,
22    NumeFilm VARCHAR(100) NOT NULL,
23    AnLansare dom_an,
24    CodProducator INTEGER NOT NULL,
25    CodRegizor INTEGER NOT NULL,
26    Tara VARCHAR(20) NOT NULL,
27    CostUSD dom_financiar
28);
29
30CREATE TABLE cineasti (
31    CodCineast SERIAL PRIMARY KEY,
32    NumeCineast VARCHAR(100) NOT NULL,
33    DataNasterii DATE NOT NULL,
34    Tara VARCHAR(20) NOT NULL
35);
36
37CREATE TABLE distributie (
38    CodFilm INTEGER NOT NULL REFERENCES filme(CodFilm) ON DELETE CASCADE ON UPDATE CASCADE,
39    Rol VARCHAR(20) NOT NULL,
40    CodActor INTEGER NOT NULL,
41    OnorariuUSD dom_financiar,
42    CONSTRAINT pk_codrol PRIMARY KEY (CodFilm,Rol)
43);
44
45CREATE TABLE tipuri_premii (
46    CodPremiu SERIAL PRIMARY KEY,
47    NumePremiu VARCHAR(100) NOT NULL,
48    Localitate VARCHAR(30) NOT NULL,
49    Tara VARCHAR(20) NOT NULL
50);
51
52CREATE TABLE incasari (
53    CodFilm INTEGER NOT NULL REFERENCES filme(CodFilm) ON DELETE CASCADE ON UPDATE CASCADE,
54    Tara VARCHAR(20) NOT NULL,
55    An dom_an,
56    Incasari_USD dom_financiar,
57    CONSTRAINT pk_codtaraan PRIMARY KEY (CodFilm, Tara, An)
58);
59    
60CREATE TABLE premii_obtinute (
61    IdPremiere SERIAL PRIMARY KEY,
62    CodPremiu INTEGER NOT NULL REFERENCES tipuri_premii(CodPremiu) ON DELETE CASCADE ON UPDATE CASCADE,
63    AnDecernare dom_an,
64    CodFilm INTEGER NOT NULL REFERENCES filme(CodFilm) ON DELETE CASCADE ON UPDATE CASCADE,
65    CodCineast INTEGER NOT NULL REFERENCES cineasti(CodCineast) ON DELETE CASCADE ON UPDATE CASCADE
66);
67
68
69/*POPULATE THE TABLES*/
70
71INSERT INTO filme VALUES(1001,'Forrest Gump',1994,12,17,'USA',8000000);
72INSERT INTO filme VALUES(1002,'Planeta Petrila',2016,13,23,'Romania',10000);
73INSERT INTO filme VALUES(1003,'Varza, Cartofi si alti demoni',2016,14,27,'Romania',500000);
74INSERT INTO filme VALUES(1004,'Două lozuri',2016,15,24,'Romania',1000000);
75INSERT INTO filme VALUES(1005,'Matrix',1999,19,26,'USA',150000);
76INSERT INTO filme VALUES(1006,'Casino',1995,20,32,'USA',40000000);
77INSERT INTO filme VALUES(1007,'Inception',2010,21,33,'USA',160000000);
78INSERT INTO filme VALUES(1008,'The Dark Knight',2008,22,34,'USA',180000000);
79INSERT INTO filme VALUES(1009,'Avatar',2009,23,35,'USA',237000000);
80INSERT INTO filme VALUES(1010,'Jurassic World',2015,24,36,'USA',150000000);
81INSERT INTO filme VALUES(1011,'Jaws',1975,24,25,'USA',12000000);
82
83INSERT INTO cineasti VALUES(18,'Tom Hanks','1957-06-12','USA');
84INSERT INTO cineasti VALUES(25,'Steven Spielberg', '1964-05-11','USA');
85INSERT INTO cineasti VALUES(22,'Michael Fox', '1975-03-12','USA');
86INSERT INTO cineasti VALUES(23,'Andrei Dascalescu', '1960-04-05','Romania');
87INSERT INTO cineasti VALUES(24,'Paul Negoescu', '1958-02-12','Romania');
88INSERT INTO cineasti VALUES(27,'Serban Georgescu', '1973-05-09','Romania');
89INSERT INTO cineasti VALUES(26,'Robert Deniro','1943-08-17','USA');
90INSERT INTO cineasti VALUES(28,'Leonardo DiCaprio', '1974-11-18','USA');
91INSERT INTO cineasti VALUES(29,'Christian Bale', '1974-12-30','USA');
92INSERT INTO cineasti VALUES(30,'Samuel Worthington', '1976-08-02','USA');
93INSERT INTO cineasti VALUES(31,'Christopher Pratt', '1979-06-21','USA');
94
95INSERT INTO incasari VALUES(1001,'USA',1994, 20000000);
96INSERT INTO incasari VALUES(1002,'Romania',2016, 50000000);
97INSERT INTO incasari VALUES(1003,'Romania',2016,152400);
98INSERT INTO incasari VALUES(1004,'Romania',2016,3000000);
99INSERT INTO incasari VALUES(1005,'USA',1999,25000000);
100INSERT INTO incasari VALUES(1006,'USA',1995, 116000000);
101INSERT INTO incasari VALUES(1007,'USA',2010, 818300000);
102INSERT INTO incasari VALUES(1008,'USA',2008,1003000000);
103INSERT INTO incasari VALUES(1009,'USA',2009,2788000000);
104INSERT INTO incasari VALUES(1010,'USA',2015,1670000000);
105
106INSERT INTO distributie VALUES(1001,'Principal',18,5000000);
107INSERT INTO distributie VALUES(1002,'Principal',19,10000);
108INSERT INTO distributie VALUES(1003,'Secundar',20,5000);
109INSERT INTO distributie VALUES(1004,'Principal',21,15000);
110INSERT INTO distributie VALUES(1005,'Principal',22,10000000);
111INSERT INTO distributie VALUES(1006,'Principal',23,20000000);
112INSERT INTO distributie VALUES(1007,'Principal',24,3000000);
113INSERT INTO distributie VALUES(1008,'Secundar',25,100000);
114INSERT INTO distributie VALUES(1009,'Principal',26,1500000);
115INSERT INTO distributie VALUES(1010,'Principal',27,2000000);
116INSERT INTO distributie VALUES(1011,'Principal',29,2000000);
117
118INSERT INTO tipuri_premii VALUES(123,'Oscar-regie','Hollywood','USA');
119INSERT INTO tipuri_premii VALUES(124,'Oscar-best film','Hollywood','USA');
120INSERT INTO tipuri_premii VALUES(125,'Oscar-best make-up','Hollywood','USA');
121INSERT INTO tipuri_premii VALUES(126,'GOPO-best actor','Bucuresti', 'Romania');
122INSERT INTO tipuri_premii VALUES(127,'OSCAR-best scenario','New York','USA');
123INSERT INTO tipuri_premii VALUES(128,'Oscar-regie','Hollywood','USA');
124INSERT INTO tipuri_premii VALUES(129,'Oscar-best film','Hollywood','USA');
125INSERT INTO tipuri_premii VALUES(130,'Oscar-best make-up','Bucuresti','Romania');
126INSERT INTO tipuri_premii VALUES(131,'GOPO-best actor','Bucuresti','Romania');
127INSERT INTO tipuri_premii VALUES(132,'OSCAR-best scenario','New York','USA');
128INSERT INTO tipuri_premii VALUES(133,'OSCAR-best actor','Hollywood','USA');
129
130INSERT INTO premii_obtinute VALUES(11111,123,1995,1001,25);
131INSERT INTO premii_obtinute VALUES(11112,124,1995,1001,NULL);
132INSERT INTO premii_obtinute VALUES(11114,126,2017,1002,23); 
133INSERT INTO premii_obtinute VALUES(11113,125,2017,1003,27);
134INSERT INTO premii_obtinute VALUES(11115,127,2017,1004,24);
135INSERT INTO premii_obtinute VALUES(11116,123,2010,1009,NULL);
136INSERT INTO premii_obtinute VALUES(11117,127,1995,1006,NULL);
137INSERT INTO premii_obtinute VALUES(11118,124,2008,1008,29); 
138INSERT INTO premii_obtinute VALUES(11119,128,1995,1006,26);
139
140/* Cerinta 1)
141
142SELECT filme.*, cineasti.codcineast, cineasti.tara 
143FROM filme 
144INNER JOIN cineasti 
145ON filme.codregizor = cineasti.codcineast
146WHERE filme.anlansare = 2016 AND cineasti.tara = 'Romania';
147*/
148
149/* Cerinta 3)
150
151SELECT *
152FROM filme
153INNER JOIN incasari
154ON filme.codfilm = incasari.codfilm
155WHERE incasari.an = 2016 
156AND incasari.incasari_usd = (
157	SELECT MAX(incasari.incasari_usd)
158	FROM incasari
159	WHERE incasari.an = 2016 
160);
161*/
162
163/* Cerinta 4) presupunem ca Michael Fox are CodActor = 29 intr-o tabela "actori" pe care nu o avem definita (conform PDF-ului primit) cu referinta in tabela distributie la coloana CodActor
164
165SELECT filme.*
166FROM filme
167INNER JOIN cineasti
168ON filme.codregizor = cineasti.codcineast
169INNER JOIN distributie
170ON filme.codfilm = distributie.codfilm
171WHERE cineasti.numecineast = 'Steven Spielberg' AND distributie.codactor = 29;
172*/