· 9 years ago · Apr 04, 2017, 06:16 PM
1DROP VIEW prepravaNad30kg;
2DROP VIEW produktNad100Eur;
3DROP VIEW pouzivateliaGmailuKtoryVNomNemajuMeno;
4DROP VIEW zakazniciNakupiliVDanomObchode;
5DROP VIEW obchodZlavaNadpolovicna;
6DROP VIEW pocetTypovZliavPreSortiment;
7DROP VIEW zoznamProduktovSPoctomJedna;
8DROP VIEW produktyDanehoObchoduAIchCeny;
9DROP VIEW statistikyDodaniaTovaru;
10DROP VIEW zoznamZakaznikovKtorySiKupiliTovar;
11DROP TABLE pracuje_pre;
12DROP TABLE produkt_obchod;
13DROP TABLE Obchod;
14DROP TABLE Produkt;
15DROP TABLE Prepravna_spolocnost;
16DROP TABLE Zakaznik;
17DROP TABLE Zlavomat;
18DROP SEQUENCE seq_zlavomat_pk;
19
20CREATE SEQUENCE seq_zlavomat_pk START 1;
21
22CREATE TABLE Zlavomat
23(
24 vyska_zlavy INT NOT NULL,
25 typ_zlavy VARCHAR(150),
26 kontakt VARCHAR(150) NOT NULL,
27 nazov VARCHAR(105) NOT NULL,
28 id INTEGER PRIMARY KEY DEFAULT nextval('seq_zlavomat_pk')
29);
30
31CREATE TABLE Zakaznik
32(
33 meno VARCHAR(150) NOT NULL,
34 email VARCHAR(150) NOT NULL,
35 adresa VARCHAR(150) NOT NULL,
36 mesto VARCHAR(150) NOT NULL,
37 id SERIAL PRIMARY KEY ,
38 priezvisko VARCHAR(150) NOT NULL,
39 telefonne_cislo INTEGER NOT NULL,
40 cislo_uctu INTEGER NOT NULL,
41 id_zlavomatu INTEGER NOT NULL,
42 FOREIGN KEY (id_zlavomatu) REFERENCES Zlavomat(id)
43);
44
45CREATE TABLE Prepravna_spolocnost
46(
47 typ_prepravy VARCHAR(150) NOT NULL,
48 cas_dodania DATE NOT NULL,
49 nazov VARCHAR(150) NOT NULL,
50 id SERIAL PRIMARY KEY,
51 id_zlavomatu INTEGER NOT NULL,
52 FOREIGN KEY (id_zlavomatu) REFERENCES Zlavomat(id)
53);
54
55CREATE TABLE Produkt
56(
57 cena INTEGER NOT NULL,
58 nazov VARCHAR(150) NOT NULL,
59 pocet_kusov INTEGER NOT NULL,
60 sposob_dopravy VARCHAR(150) DEFAULT 'do 50 kg',
61 id SERIAL PRIMARY KEY,
62 id_zlavomatu INTEGER NOT NULL,
63 id_zakaznika INTEGER NOT NULL,
64 FOREIGN KEY (id_zlavomatu) REFERENCES Zlavomat(id),
65 FOREIGN KEY (id_zakaznika) REFERENCES Zakaznik(id)
66);
67
68CREATE TABLE Obchod
69(
70 nazov VARCHAR(150) NOT NULL,
71 sortiment VARCHAR(150) NOT NULL,
72 bankove_spojenie INTEGER NOT NULL,
73 id SERIAL PRIMARY KEY
74);
75
76CREATE TABLE pracuje_pre
77(
78 id_obchodu INTEGER NOT NULL,
79 id_prepravnej_spolocnosti INTEGER NOT NULL,
80 PRIMARY KEY (id_obchodu, id_prepravnej_spolocnosti),
81 FOREIGN KEY (id_prepravnej_spolocnosti) REFERENCES Prepravna_spolocnost(id),
82 FOREIGN KEY (id_obchodu) REFERENCES Obchod(id)
83);
84
85CREATE TABLE produkt_obchod
86(
87 id_obchodu INTEGER NOT NULL,
88 id_produktu INTEGER NOT NULL,
89 PRIMARY KEY (id_obchodu,id_produktu),
90 FOREIGN KEY (id_obchodu) REFERENCES Obchod(id),
91 FOREIGN KEY (id_produktu) REFERENCES Produkt(id)
92);
93
94INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (50,'jarna','helpdesk@heureka.sk','heureka');
95INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (10,'vernostna','ozlave@ozlave.sk','ozlave');
96INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (70,'black friday','zlavomat@zlavomat.sk','zlavomat');
97INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (50,'vypredaj','ezlva@ezlava.sk','ezlava');
98INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (50,'kuponova','kupon@kuponovnik.sk','kuponovnik');
99INSERT INTO Zlavomat (vyska_zlavy, typ_zlavy, kontakt, nazov) VALUES (90,'likvidacna','info@zlavadna.sk','zlavadna');
100
101INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('marian','thew7u11@gmail.com','javorova 8','gelnica','ferenc',0908111111,7557686,1);
102INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('erik','erik@gmail.com','drienov 20','drienov','jakuba',0915222222,8754567,1);
103INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('vlado','vlado@post.sk','hlavna 4','kezmarok','kovalsky',0944333333,8754567,2);
104INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('juraj','juraj271@gmail.com','zeleznicna 3','margecany','klein',0904444444,2544567,2);
105INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('radovan','ofmann@azet.sk','kvp 10','kosice','kocik',0944555555,985467,3);
106INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('kristian','rovastus@gmail.com','jarna 2','budimir','golias',0911666666,985467,4);
107INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('matus','koporec@gmail.com','mnisek 8','mnisek nad hnilcom','koporec',0911777777,985467,4);
108INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('martinka','filipova@gmail.com','snp 3','spisska nova ves','filipova',0904888888,45547,5);
109INSERT INTO Zakaznik (meno, email, adresa,mesto, priezvisko, telefonne_cislo, cislo_uctu,id_zlavomatu) VALUES ('jana','stastna@gmail.com','ceskoslovenskej armady 31','trebisov','stastna',0910999999,87547,6);
110
111INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 30kg',current_date+1,'UPC',1);
112INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 100kg',current_date+1,'TOPTRANS',2);
113INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 30kg',current_date+1,'posta',3);
114INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 70kg',current_date+2,'geis',4);
115INSERT INTO Prepravna_spolocnost (typ_prepravy, cas_dodania, nazov, id_zlavomatu) VALUES ('do 50kg',current_date+5,'dpd',5);
116
117INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (760,'telefon',1,2,1);
118INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (200,'ssd',1,1,2);
119INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (800,'notebook',2,3,3);
120INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (300,'tablet',1,4,4);
121
122INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (50,'sluchadla',3,3,5);
123INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (43,'hra',2,1,6);
124INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (195,'graficka karta',1,2,7);
125INSERT INTO Produkt (cena, nazov, pocet_kusov, id_zlavomatu, id_zakaznika) VALUES (15,'puzdro na telefon',1,3,8);
126
127INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('alza.sk','elektronika',578754);
128INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('hej.sk','elektronika',87787);
129INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('mall.sk','elektronika',448487);
130INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('muziker.sk','hudobone nastroje',324451);
131INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('hrac.sk','hry a softver',977878);
132INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('tichepc.sk','pc komponenty',811454);
133INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('vsetkonamobil.sk','prislusenstvo pre telefony',115447);
134INSERT INTO Obchod (nazov, sortiment, bankove_spojenie) VALUES ('nahodsa.sk','oblecenie',54844);
135
136INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (1,1);
137INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (1,2);
138INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (2,3);
139INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (3,4);
140INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (4,5);
141
142INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (5,6);
143INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (6,7);
144INSERT INTO produkt_obchod (id_obchodu, id_produktu) VALUES (7,8);
145
146INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (1,1);
147INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (2,3);
148INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (3,4);
149INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (4,5);
150INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (5,5);
151INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (6,3);
152INSERT INTO pracuje_pre (id_obchodu, id_prepravnej_spolocnosti) VALUES (7,1);
153
154/*Ziskanie prepravnej spolocnosti, ktora prepravuje nad 30kg*/
155CREATE VIEW prepravaNad30kg AS
156 SELECT nazov, typ_prepravy
157 FROM Prepravna_spolocnost
158 WHERE NOT typ_prepravy='do 30kg'
159 ORDER BY cas_dodania
160;
161
162/*Ziskanie produktu s cenou nad 100 Eur*/
163CREATE VIEW produktNad100Eur AS
164 SELECT nazov, cena
165 FROM Produkt
166 WHERE cena > 100
167 ORDER BY cena
168;
169
170/* Ziskanie mena a priezviska zakaznikov, ktory nakupili v danom obchode*/
171CREATE VIEW zakazniciNakupiliVDanomObchode AS
172SELECT meno,priezvisko
173FROM Zakaznik
174 JOIN Produkt ON Zakaznik.id = Produkt.id
175 JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
176WHERE id_obchodu=1;
177
178/*Ziskanie obchodu, ktory ponuka vysku zlavy vacsiu ako 50%*/
179CREATE VIEW obchodZlavaNadpolovicna AS
180SELECT Obchod.nazov,sortiment
181FROM Obchod
182JOIN produkt_obchod ON Obchod.id = produkt_obchod.id_obchodu
183JOIN produkt ON produkt_obchod.id_produktu = Produkt.id
184JOIN zakaznik ON Produkt.id_zakaznika = Zakaznik.id
185JOIN Zlavomat ON Produkt.id_zlavomatu = Zlavomat.id
186WHERE vyska_zlavy > 50;
187
188/*Ziskanie poctu typov zliav spojenych s danym sortimentom*/
189CREATE VIEW pocetTypovZliavPreSortiment AS
190SELECT count(DISTINCT typ_zlavy) AS pocet
191FROM Zlavomat
192JOIN Zakaznik ON Zlavomat.id = Zakaznik.id_zlavomatu
193JOIN Produkt ON Zakaznik.id = Produkt.id_zakaznika
194JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
195JOIN Obchod ON produkt_obchod.id_obchodu = Obchod.id
196WHERE sortiment='elektronika';
197
198/* Ziskanie obchodu, sortimentu a produktu, kde pocet kusov produktu je 1*/
199CREATE VIEW zoznamProduktovSPoctomJedna AS
200SELECT obchod.nazov AS obchod, obchod.sortiment,produkt.nazov AS produkt
201FROM obchod
202JOIN produkt_obchod ON Obchod.id = produkt_obchod.id_obchodu
203JOIN produkt ON produkt_obchod.id_produktu = Produkt.id
204WHERE pocet_kusov=1
205ORDER BY obchod.nazov;
206
207/*Ziskanie produktov daneho obchodu alza.sk a ich ceny v roznych menach*/
208CREATE VIEW produktyDanehoObchoduAIchCeny AS
209SELECT produkt.nazov AS produkt,
210 ROUND(cena,2)||'€' AS EUR,
211 ROUND((cena*0.76),2)||'$' AS USD,
212 ROUND((cena * 19.20),2)||'CK' AS CZK
213FROM Produkt
214JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
215JOIN Obchod ON produkt_obchod.id_obchodu = Obchod.id
216WHERE Obchod.nazov='alza.sk'
217ORDER BY ROUND(cena,2) DESC;
218;
219
220/*Ziskanie pouzivatelov gmailu, ktorym sa nenachadza ich meno ani ich priezvisko v ich emailovej adrese*/
221CREATE VIEW pouzivateliaGmailuKtoryVNomNemajuMeno AS
222 SELECT meno,priezvisko,email
223 FROM Zakaznik
224 WHERE email LIKE '%gmail.com%'
225 AND Zakaznik.email NOT LIKE CONCAT(Zakaznik.meno,'%gmail.com%')
226 AND Zakaznik.email NOT LIKE CONCAT(Zakaznik.priezvisko,'%gmail.com%')
227 ORDER BY priezvisko,meno;
228
229/*Ziskanie max, min a priemernej dlzky dodania tovaru dopravnou spolocnostou pre dany produkt*/
230CREATE VIEW statistikyDodaniaTovaru AS
231SELECT
232 produkt.nazov AS produkt,
233 max((EXTRACT(DAY FROM cas_dodania)*24)-(extract(DAY FROM current_date))*24) AS longest,
234 min((EXTRACT(DAY FROM cas_dodania)-extract(DAY FROM current_date))*24) AS shortest,
235 avg((extract(DAY FROM cas_dodania)-extract(DAY FROM current_date))*24) AS average
236FROM prepravna_spolocnost
237JOIN pracuje_pre ON Prepravna_spolocnost.id = pracuje_pre.id_prepravnej_spolocnosti
238JOIN Obchod ON pracuje_pre.id_obchodu = Obchod.id
239JOIN produkt_obchod ON Obchod.id = produkt_obchod.id_obchodu
240JOIN Produkt ON produkt_obchod.id_produktu = Produkt.id
241GROUP BY produkt.nazov
242ORDER BY max((EXTRACT(DAY FROM cas_dodania)*24)-(extract(DAY FROM current_date))*24) DESC;
243;
244
245/*Zoznam zakaznikov, ktory si kupil dany tovar z daneho obchodu*/
246CREATE VIEW zoznamZakaznikovKtorySiKupiliTovar AS
247SELECT DISTINCT meno,priezvisko, produkt.nazov AS produkt,produkt.pocet_kusov, obchod.nazov AS obchod FROM Zakaznik
248LEFT OUTER JOIN Produkt ON Zakaznik.id = Produkt.id_zakaznika
249 LEFT OUTER JOIN produkt_obchod ON Produkt.id = produkt_obchod.id_produktu
250 LEFT OUTER JOIN obchod ON produkt_obchod.id_obchodu = Obchod.id
251ORDER BY pocet_kusov DESC
252;