· 6 years ago · May 06, 2019, 09:08 AM
1SET FOREIGN_KEY_CHECKS = 0;
2DROP TABLE IF EXISTS Posta;
3DROP TABLE IF EXISTS Predstava;
4DROP TABLE IF EXISTS Igralci;
5DROP TABLE IF EXISTS Vrsta_placila;
6DROP TABLE IF EXISTS Naslov;
7DROP TABLE IF EXISTS Abonent;
8DROP TABLE IF EXISTS Gledalisce;
9DROP TABLE IF EXISTS Abonma;
10DROP TABLE IF EXISTS Abonma_Predstava;
11DROP TABLE IF EXISTS Vloga;
12DROP TABLE IF EXISTS Abonma_abonent;
13DROP TABLE IF EXISTS Dvorana;
14DROP TABLE IF EXISTS Sedez;
15DROP TABLE IF EXISTS Izvajanje;
16DROP TABLE IF EXISTS Rezervacija;
17DROP TABLE IF EXISTS Sedez_Rezervacija;
18SET FOREIGN_KEY_CHECKS = 1;
19
20
21CREATE TABLE Posta (
22 id_posta INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
23 nazivPosta VARCHAR(90) NOT NULL,
24 postna_stevilka INT NOT NULL
25);
26
27CREATE TABLE Predstava (
28 id_predstava INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
29 naslovP VARCHAR(40) NOT NULL,
30 datum DATE NOT NULL,
31 cas_izvajanja TIME NOT NULL
32
33);
34
35CREATE TABLE Igralci (
36 id_igralci INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
37 ime VARCHAR(20) NOT NULL,
38 priimek VARCHAR(40) NOT NULL
39
40);
41
42CREATE TABLE Vrsta_placila (
43 id_vrstaPlacila INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
44 nazivPlacila VARCHAR(20) NOT NULL
45
46);
47
48CREATE TABLE Naslov (
49 id_naslov INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
50 ulica VARCHAR(50) NOT NULL,
51 hisna_stevilka INT NOT NULL,
52 id_posta_fk INT NOT NULL
53
54);
55
56CREATE TABLE Abonent (
57 id_abonent INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
58 ime VARCHAR(20) NOT NULL,
59 priimek VARCHAR(20) NOT NULL,
60 id_naslov_fk INT NOT NULL
61);
62
63CREATE TABLE Gledalisce (
64 id_gledalisce INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
65 nazivGl VARCHAR(50) NOT NULL,
66 id_naslov_fk INT NOT NULL
67);
68
69CREATE TABLE Abonma (
70 id_abonma INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
71 zvrst VARCHAR(20) NOT NULL,
72 datum_Zac DATE NOT NULL,
73 datumKon DATE NOT NULL,
74 cena DECIMAL NOT NULL,
75 id_gledalisce_fk INT NOT NULL
76);
77
78CREATE TABLE Abonma_predstava (
79 id_abonmaPredstava INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
80 id_abonma_fk INT,
81 id_predstava_fk INT
82);
83
84CREATE TABLE Vloga (
85 id_vloga INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
86 naziv_vloga VARCHAR(50) NOT NULL,
87 id_igralci_fk INT,
88 id_predstava_fk INT
89);
90
91
92CREATE TABLE Abonma_abonent (
93 id_abonmaAbonent INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
94 id_abonma_fk INT NOT NULL,
95 id_abonent_fk INT NOT NULL
96);
97
98CREATE TABLE Dvorana (
99 id_dvorana INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
100 stDvorane INT NOT NULL,
101 id_gledalisce_fk INT NOT NULL
102);
103
104CREATE TABLE Sedez (
105 id_sedez INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
106 sedez INT NOT NULL,
107 vrsta INT NOT NULL,
108 id_dvorana_fk INT NOT NULL
109);
110
111CREATE TABLE Izvajanje (
112 id_izvajanje INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
113 datum DATE NOT NULL,
114 cas_izvajanja TIME NOT NULL,
115 id_predstava_fk INT,
116 id_dvorana_fk INT NOT NULL
117);
118
119CREATE TABLE Rezervacija (
120 id_rezervacija INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
121 predstava VARCHAR(50),
122 datum DATE NOT NULL,
123 ura TIME NOT NULL,
124 znesek DECIMAL NOT NULL,
125 placilo BOOLEAN NOT NULL,
126 id_vrstaPlacila_fk INT,
127 id_izvajanje_fk INT
128);
129
130CREATE TABLE Sedez_Rezervacija (
131 id_sedezRezervacija INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
132 id_sedez_fk INT NOT NULL,
133 id_rezervacija_fk INT NOT NULL
134);
135
136
137
138
139
140ALTER TABLE Abonent ADD CONSTRAINT fk_abonent_naslov
141FOREIGN KEY (id_naslov_fk)
142REFERENCES Naslov(id_naslov)
143ON DELETE RESTRICT;
144
145ALTER TABLE Naslov ADD CONSTRAINT fk_naslov_posta
146FOREIGN KEY (id_posta_fk)
147REFERENCES Posta(id_posta)
148ON DELETE RESTRICT;
149
150ALTER TABLE Abonma ADD CONSTRAINT fk_abonma_gledalisce
151FOREIGN KEY (id_gledalisce_fk)
152REFERENCES Gledalisce(id_gledalisce)
153ON DELETE RESTRICT;
154
155ALTER TABLE Izvajanje ADD CONSTRAINT fk_izvajanje_predstava
156FOREIGN KEY (id_predstava_fk)
157REFERENCES Predstava(id_predstava)
158ON DELETE SET NULL;
159
160ALTER TABLE Vloga ADD CONSTRAINT fk_vloga_predstava
161FOREIGN KEY (id_predstava_fk)
162REFERENCES Predstava(id_predstava)
163ON DELETE SET NULL;
164
165ALTER TABLE Gledalisce ADD CONSTRAINT fk_gledalisce_naslov
166FOREIGN KEY (id_naslov_fk)
167REFERENCES Naslov(id_naslov)
168ON DELETE RESTRICT;
169
170ALTER TABLE Abonma_predstava ADD CONSTRAINT fk_abonmapredstava_abonma
171FOREIGN KEY (id_abonma_fk)
172REFERENCES Abonma(id_abonma)
173ON DELETE SET NULL;
174
175ALTER TABLE Abonma_predstava ADD CONSTRAINT fk_abonmapredstava_predstava
176FOREIGN KEY (id_predstava_fk)
177REFERENCES Predstava(id_predstava)
178ON DELETE SET NULL;
179
180ALTER TABLE Abonma_abonent ADD CONSTRAINT fk_abonmaabonent_abonma
181FOREIGN KEY (id_abonma_fk)
182REFERENCES Abonma(id_abonma)
183ON DELETE RESTRICT;
184
185ALTER TABLE Abonma_abonent ADD CONSTRAINT fk_abonmaabonent_abonent
186FOREIGN KEY (id_abonent_fk)
187REFERENCES Abonent(id_abonent)
188ON DELETE RESTRICT;
189
190ALTER TABLE Vloga ADD CONSTRAINT fk_vloga_igralci
191FOREIGN KEY (id_igralci_fk)
192REFERENCES Igralci(id_igralci)
193ON DELETE SET NULL;
194
195ALTER TABLE Dvorana ADD CONSTRAINT fk_dvorana_gledalisce
196FOREIGN KEY (id_gledalisce_fk)
197REFERENCES Gledalisce(id_gledalisce)
198ON DELETE RESTRICT;
199
200ALTER TABLE Sedez ADD CONSTRAINT fk_sedez_dvorana
201FOREIGN KEY (id_dvorana_fk)
202REFERENCES Dvorana(id_dvorana)
203ON DELETE RESTRICT;
204
205ALTER TABLE Izvajanje ADD CONSTRAINT fk_izvajanje_dvorana
206FOREIGN KEY (id_dvorana_fk)
207REFERENCES Dvorana(id_dvorana)
208ON DELETE RESTRICT;
209
210ALTER TABLE Rezervacija ADD CONSTRAINT fk_rezervacija_vrstaplacila
211FOREIGN KEY (id_vrstaPlacila_fk)
212REFERENCES Vrsta_placila(id_vrstaPlacila)
213ON DELETE SET NULL;
214
215ALTER TABLE Rezervacija ADD CONSTRAINT fk_rezervacija_izvajanje
216FOREIGN KEY (id_izvajanje_fk)
217REFERENCES Izvajanje(id_izvajanje)
218ON DELETE RESTRICT;
219
220ALTER TABLE Sedez_rezervacija ADD CONSTRAINT fk_sedezrezervacija_sedez
221FOREIGN KEY (id_sedez_fk)
222REFERENCES Sedez(id_sedez)
223ON DELETE RESTRICT;
224
225ALTER TABLE Sedez_rezervacija ADD CONSTRAINT fk_sedezrezervacija_rezervacija
226FOREIGN KEY (id_rezervacija_fk)
227REFERENCES Rezervacija(id_rezervacija)
228ON DELETE RESTRICT;
229
230
231
232INSERT INTO Posta (id_posta, nazivPosta, postna_stevilka) VALUES
233(default, "LJUBLJANA", 1000),
234(default, "MARIBOR", 2000),
235(default, "NOVA GORICA", 5000),
236(default, "LJUBLJANA", 1000),
237(default, "MARIBOR", 2000);
238SELECT * FROM Posta;
239
240INSERT INTO Predstava (id_predstava, naslovP, datum, cas_izvajanja) VALUES
241(default, "UGRABITEV IZ SERAJA", "2019-02-03", "19:10:00"),
242(default, "ÄŒAROBNA PIÅ ÄŒAL", "2019-02-01", "19:30:00"),
243(default, "NABUCCO", "2019-05-06", "11:00:00"),
244(default, "FAUST", "2019-03-05", "18:00:00"),
245(default, "KRILA GOLOBICE","2019-04-30", "17:00:00");
246SELECT * FROM Predstava;
247
248INSERT INTO Igralci (id_igralci, ime, priimek) VALUES
249(default, "Ela", "Hojnik"),
250(default, "Marjan", "Nežmah"),
251(default, "Anissa", "King"),
252(default, "Anna", "Kancler"),
253(default, "Nina", "RibiÄ");
254SELECT * FROM Igralci;
255
256INSERT INTO Vrsta_placila (id_vrstaPlacila, nazivPlacila) VALUES
257(default, "gotovina"),
258(default, "bancna kartica"),
259(default, "gotovina"),
260(default, "gotovina"),
261(default, "bancna kartica");
262SELECT * FROM Vrsta_placila;
263
264INSERT INTO Naslov (id_naslov, ulica, hisna_stevilka, id_posta_fk) VALUES
265(default, "Poštna", 4, 1),
266(default, "Koroška", 43, 1),
267(default, "IngoliÄeva", 3, 3),
268(default, "Smetanova", 67, 2),
269(default, "Prežihova", 9, 2),
270(default, "Vrazova", 12, 2),
271(default, "Kraljeva", 7, 1),
272(default, "Pohorska", 9, 2),
273(default, "GubÄeva", 2, 2),
274(default, "GregorÄiÄeva", 11, 2);
275SELECT * FROM Naslov;
276
277INSERT INTO Abonent (id_abonent, ime, priimek, id_naslov_fk) VALUES
278(default, "Miha", "Hodnik", 3),
279(default, "Janez", "Novak", 7),
280(default, "Miro", "Krajnc", 4),
281(default, "Ana", "Bajc", 8),
282(default, "Nina", "Pecovnik", 5),
283(default, "Mira", "Kos", 2),
284(default, "Jelka", "Brdelak", 1),
285(default, "Mojca", "Rep", 6),
286(default, "Andrej", "Lipovnik", 9),
287(default, "Robert", "Jeraj", 10);
288SELECT * FROM Abonent;
289
290INSERT INTO Gledalisce (id_gledalisce, nazivGl, id_naslov_fk) VALUES
291(default, "SNG MARIBOR", 4),
292(default, "SNG LJUBLJANA", 1),
293(default, "SNG NOVA GORICA", 7),
294(default, "SNG MARIBOR", 4),
295(default, "SNG MARIBOR", 4),
296(default, "SNG LJUBLJANA", 1),
297(default, "SNG NOVA GORICA", 7),
298(default, "SNG MARIBOR", 4),
299(default, "SNG NOVA GORICA", 7),
300(default, "SNG LJUBLJANA", 1);
301SELECT * FROM Gledalisce;
302
303INSERT INTO Abonma (id_abonma, zvrst, datum_Zac, datumKon, cena, id_gledalisce_fk) VALUES
304(default, "opera", "2019-04-01", "2019-04-06", 160, 1),
305(default, "drama", "2019-04-07", "2019-04-13", 130, 1),
306(default, "balet", "2019-04-14", "2019-04-20", 140, 3),
307(default, "drama", "2019-04-01", "2019-04-07", 120, 2),
308(default, "balet", "2019-05-04", "2019-05-08", 130, 3),
309(default, "drama", "2019-05-09", "2019-05-15", 140, 3),
310(default, "drama", "2019-05-16", "2019-05-22", 125, 1),
311(default, "balet", "2019-05-25", "2019-05-29", 160, 1),
312(default, "balet", "2019-06-01", "2019-06-04", 135, 2),
313(default, "balet", "2019-06-08", "2019-06-12", 130, 2);
314SELECT * FROM Abonma;
315
316INSERT INTO Abonma_predstava (id_abonmaPredstava, id_abonma_fk, id_predstava_fk) VALUES
317(default, 1, 1),
318(default, 3, 2),
319(default, 2, 3),
320(default, 2, 4),
321(default, 2, 5),
322(default, 3, 3),
323(default, 1, 1),
324(default, 1, 4),
325(default, 1, 1),
326(default, 3, 5);
327SELECT * FROM Abonma_predstava;
328
329INSERT INTO Vloga (id_vloga, naziv_vloga, id_igralci_fk, id_predstava_fk) VALUES
330(default, "Konstanca", 4, 1),
331(default, "Belmonte", 1, 2),
332(default, "Faust", 5, 4),
333(default, "Valentin", 4, 2),
334(default, "Pedrillo", 2, 1),
335(default, "Siebel", 3, 1),
336(default, "Hora", 5, 5),
337(default, "Bella", 2, 2),
338(default, "AmeriÄan", 4, 3),
339(default, "Francoz", 4, 4);
340SELECT * FROM Vloga;
341
342INSERT INTO Abonma_abonent (id_abonmaAbonent, id_abonma_fk, id_abonent_fk) VALUES
343(default, 1, 8),
344(default, 3, 2),
345(default, 2, 9),
346(default, 2, 4),
347(default, 2, 5),
348(default, 3, 7),
349(default, 1, 3),
350(default, 1, 4),
351(default, 1, 1),
352(default, 3, 6);
353SELECT * FROM Abonma_abonent;
354
355INSERT INTO Dvorana (id_dvorana, stDvorane, id_gledalisce_fk) VALUES
356(default, 4, 2),
357(default, 5, 3),
358(default, 2, 1),
359(default, 2, 3),
360(default, 1, 2),
361(default, 3, 3),
362(default, 6, 1),
363(default, 3, 2),
364(default, 2, 3),
365(default, 3, 1);
366SELECT * FROM Dvorana;
367
368INSERT INTO Sedez (id_sedez, sedez, vrsta, id_dvorana_fk) VALUES
369(default, 5, 1, 4),
370(default, 3, 2, 5),
371(default, 3, 3, 2),
372(default, 3, 4, 5),
373(default, 5, 5, 6),
374(default, 9, 6, 2),
375(default, 12, 7, 4),
376(default, 6, 8, 6),
377(default, 7, 11, 3),
378(default, 3, 10, 1);
379SELECT * FROM Sedez;
380
381INSERT INTO Izvajanje (id_izvajanje, datum, cas_izvajanja, id_predstava_fk, id_dvorana_fk) VALUES
382(default, "2019-02-03", "19:10:00", 3, 4),
383(default, "2019-02-01", "19:30:00", 4, 2),
384(default, "2019-05-06", "11:00:00", 2, 5),
385(default, "2019-03-05", "18:00:00", 5, 3),
386(default, "2019-04-30", "17:00:00", 3, 1),
387(default, "2019-04-03", "19:10:00", 1, 5),
388(default, "2019-03-01", "18:30:00", 4, 3),
389(default, "2019-06-06", "15:00:00", 5, 2),
390(default, "2019-05-05", "18:00:00", 1, 6),
391(default, "2019-04-28", "17:00:00", 2, 6);
392SELECT * FROM Izvajanje;
393
394INSERT INTO Rezervacija (id_rezervacija, predstava, datum, ura, znesek, placilo, id_vrstaPlacila_fk, id_izvajanje_fk) VALUES
395(default, 2, "2019-02-03", "19:10:00", 35, 1, 1, 3),
396(default, 4, "2019-02-01", "19:30:00", 25, 0, 2, 5),
397(default, 5, "2019-05-06", "11:00:00", 35, 1, 2, 8),
398(default, 3, "2019-03-05", "18:00:00", 45, 1, 2, 8),
399(default, 1, "2019-04-30", "17:00:00", 50, 1, 1, 2);
400SELECT * FROM Rezervacija;
401
402INSERT INTO Sedez_Rezervacija (id_sedezRezervacija, id_sedez_fk, id_rezervacija_fk) VALUES
403(default, 5, 1),
404(default, 3, 2),
405(default, 3, 3),
406(default, 3, 4),
407(default, 5, 5),
408(default, 9, 5),
409(default, 10, 3),
410(default, 6, 1),
411(default, 7, 3),
412(default, 3, 5);
413SELECT * FROM Sedez_Rezervacija;
414
415#izpisi vse predstave v februarju 2019
416SELECT *
417FROM Predstava
418WHERE month(datum)="02" AND year(datum)="2019";
419
420#kateri igralci nastopajo v predstavah
421SELECT ime, priimek FROM Igralci;
422
423#koliko stane najdrazji abonma
424SELECT MAX(cena)
425AS NajvisjaCena
426FROM Abonma;
427
428#koliko gledalcev je placalo s kartico in koliko z gotovino?
429SELECT COUNT(id_vrstaPlacila), nazivPlacila
430FROM Vrsta_placila
431GROUP BY nazivPlacila
432HAVING COUNT(id_vrstaPlacila);
433
434#spremeni zvrst abonmaja iz balet v drama
435UPDATE Abonma
436SET zvrst = "drama"
437WHERE abonma.id_abonma = 5;
438
439#izbriši predstavo Nabucco
440DELETE FROM Predstava
441WHERE Predstava.id_predstava= 3;
442SELECT * FROM Predstava;