-
Notifications
You must be signed in to change notification settings - Fork 0
/
tablice.sql
88 lines (58 loc) · 3.15 KB
/
tablice.sql
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
CREATE table clanVideoteka(
clan_id int primary key auto_increment not null,
ime varchar(30) not null,
prezime varchar(50) not null,
broj_mob varchar(20) not null,
lozinka varchar(70) not null
alter table clanVideoteka add lozinka varchar(70) not null;
CREATE table filmVideoteka(
film_id int primary key auto_increment not null,
naslov varchar(100) not null,
redatelj varchar(100) not null,
trajanje varchar(15) not null,
god_izdanja varchar(15) not null
create table posudbaVideoteka(
id_posudba int auto_increment primary key not null,
clan_id int not null,
film_id int not null,
datum_posudbe date not null,
datum_vracanja date not null,
foreign key (film_id) references filmVideoteka(film_id) on delete restrict on update cascade,
foreign key (clan_id) references clanVideoteka(clan_id) on delete cascade on update cascade
insert into filmVideoteka (naslov,redatelj,god_izdanja,trajanje) values
('Gladiator','Ridley Scott','2000','170'),
('Joker','Todd Philips','2019','122'),
('A Clockwork Orange','Stanley Kubrick','1971','137'),
('Dr. Strangelove','Stanley Kubrick','1963','95'),
('Mad Max: Fury Road','George Miller','2015','120'),
('Watchmen','Zack Snyder','2009','215'),
('Blade Runner (The Final Cut)','Ridley Scott','1982','119'),
('The Lord of the Rings: The Fellowship of the Ring','Peter Jackson','2001','178'),
('The Lord of the Rings: The Two Towers','Peter Jackson','2002','180'),
('The Lord of the Rings: Return of the King','Peter Jackson','2003','201'),
('Memento','Cristopher Nolan','2000','113'),
('300','Zack Snyder','2006','117'),
('Inglorious Bastards','Quentin Tarantino','2009','153')
;
########################################################################################
(1,'The Dark Knight','Cristopher Nolan','152','2008')
('2001 a Space Odyssey','Stanley Kubrick','1968','149')
('Gladiator','Ridley Scott','2000','170'),
('Joker','Todd Philips','2019','122'),
('A Clockwork Orange','Stanley Kubrick','1971','137'),
('Dr. Strangelove','Stanley Kubrick','1963','95'),
('Mad Max: Fury Road','George Miller','2015','120'),
('Watchmen','Zack Snyder','2009','215'),
('Blade Runner (The Final Cut)','Ridley Scott','1982','119'),
('The Lord of the Rings: The Fellowship of the Ring','Peter Jackson','2001','178'),
('The Lord of the Rings: The Two Towers','Peter Jackson','2002','180'),
('The Lord of the Rings: Return of the King','Peter Jackson','2003','201'),
('Memento','Cristopher Nolan','2000','113'),
('300','Zack Snyder','2006','117'),
('Inglorious Bastards','Quentin Tarantino','2009','153')
SELECT clan_id FROM clanVideoteka WHERE broj_mob=(select broj_mob from clanVideoteka where prezime='Pangos');
select datum_vracanja, clanVideoteka.ime, clanVideoteka.broj_mob, filmVideoteka.film_id, filmVideoteka.god_izdanja from posudbaVideoteka
left outer join clanVideoteka on posudbaVideoteka.clan_id = clanVideoteka.clan_id left outer join filmVideoteka on filmVideoteka.film_id = posudbaVideoteka.film_id ;
SELECT * FROM filmVideoteka WHERE redatelj='Terry Gilliam' OR god_izdanja='1999';
SELECT id_posudba FROM posudbaVideoteka WHERE film_id=2;
update filmVideoteka set naslov='naslov', redatelj='test', god_izdanja='123', trajanje='434' where film_id=44;