-- SQL skript pro vytvoreni databaze
-- xmarhe00, xvalek02
-- 02.04.2012

--smazani puvodnich tabulek
DROP TABLE Zakaznik CASCADE CONSTRAINTS;
DROP TABLE Vozidlo CASCADE CONSTRAINTS;
DROP TABLE Platba CASCADE CONSTRAINTS;
DROP TABLE Oprava CASCADE CONSTRAINTS;
DROP TABLE Sklad CASCADE CONSTRAINTS;
DROP TABLE Zamestnanec CASCADE CONSTRAINTS;

--tabulky vazeb
DROP TABLE vlastni CASCADE CONSTRAINTS;
DROP TABLE provadi CASCADE CONSTRAINTS;
DROP TABLE platba_za CASCADE CONSTRAINTS;
DROP TABLE se_provadi_oprava CASCADE CONSTRAINTS;
DROP TABLE dodava_dily CASCADE CONSTRAINTS;
DROP TABLE uskutecnuje_opravu CASCADE CONSTRAINTS;

--vytvoreni polozek tabulky
CREATE TABLE Zakaznik (
r_cislo 	VARCHAR2(11)	NOT NULL,
jmeno		VARCHAR2(40)	NOT NULL,
datum_nar	DATE,
adresa		VARCHAR2(50),
bydliste	VARCHAR2(20),
tel_cislo	NUMBER			UNIQUE
);

CREATE TABLE Vozidlo (
VIN			VARCHAR2(17)		NOT NULL,
znacka_typ	VARCHAR2(30)		NOT NULL,
rok_vyroby	NUMBER,
spz			VARCHAR2(10)		NOT NULL UNIQUE,
majitel		VARCHAR2(11)		NOT NULL,
barva		VARCHAR2(10)
);

CREATE TABLE Platba (
var_symbol	NUMBER,
castka		NUMBER,
datum		DATE,
zp_uhrady	VARCHAR2(8)		CHECK(zp_uhrady IN('hotove', 'karta', 'prevodem')) NOT NULL,
seznam_opr	VARCHAR2(255),
reklamace	VARCHAR2(255),
uhrazeno 	VARCHAR2(3)		DEFAULT 'ne' CHECK (uhrazeno IN ('ne', 'ano')) NOT NULL
);

CREATE TABLE Oprava (
c_opravy	NUMBER			NOT NULL,
nazev		VARCHAR2(100) 	NOT NULL,
zacatek		DATE			NOT NULL,
konec		DATE			NOT NULL,
cena		NUMBER			NOT NULL
);

CREATE TABLE Sklad (
kat_c_dilu		VARCHAR2(20),
nazev_zbozi		VARCHAR2(50) 	NOT NULL,
nazev_vyrobce	VARCHAR2(10),
nakup_cena		NUMBER			NOT NULL,
prodejni_cena	NUMBER
);

CREATE TABLE Zamestnanec (
fir_cislo	NUMBER,
r_cislo 	VARCHAR2(11),
jmeno		VARCHAR2(40) 	NOT NULL,
adresa		VARCHAR2(50),
bydliste	VARCHAR2(20),
tel_cislo	NUMBER 			UNIQUE,
prac_uvazek VARCHAR2(10)	NOT NULL,
mzda		NUMBER			NOT NULL
);


--vazby tabulek
CREATE TABLE vlastni (
r_cislo 	VARCHAR2(11)	NOT NULL,
VIN			VARCHAR2(17)	NOT NULL
);

CREATE TABLE provadi (
r_cislo 	VARCHAR2(11)	NOT NULL,
var_symbol	NUMBER
);

CREATE TABLE platba_za(
var_symbol	NUMBER			NOT NULL,
c_opravy	NUMBER			NOT NULL
);

CREATE TABLE se_provadi_oprava(
VIN			VARCHAR2(17)		NOT NULL,
c_opravy	NUMBER
);

CREATE TABLE dodava_dily(
c_opravy		NUMBER			NOT NULL,
kat_c_dilu		VARCHAR2(20)
);

CREATE TABLE uskutecnuje_opravu(
fir_cislo		NUMBER			NOT NULL,
r_cislo 		VARCHAR2(11)	NOT NULL,
c_opravy		NUMBER
);

--nastaveni primarnich klicu
ALTER TABLE Zakaznik ADD CONSTRAINT PK_Zakaznik PRIMARY KEY (r_cislo);
ALTER TABLE Vozidlo ADD CONSTRAINT PK_Vozidlo PRIMARY KEY (VIN);
ALTER TABLE Platba ADD CONSTRAINT PK_Platba PRIMARY KEY (var_symbol);
ALTER TABLE Oprava ADD CONSTRAINT PK_Oprava PRIMARY KEY (c_opravy);
ALTER TABLE Sklad ADD CONSTRAINT PK_Sklad PRIMARY KEY (kat_c_dilu);
ALTER TABLE Zamestnanec ADD CONSTRAINT PK_Zamestnanec PRIMARY KEY (fir_cislo, r_cislo);

--nastaveni cizich klicu
ALTER TABLE Vozidlo ADD CONSTRAINT FK_Zakaznik_Vozidlo FOREIGN KEY (majitel) REFERENCES Zakaznik ON DELETE CASCADE;

--nastaveni primarnich klicu pre vazby
ALTER TABLE vlastni ADD CONSTRAINT PK_vlastni PRIMARY KEY (r_cislo, VIN);
ALTER TABLE provadi ADD CONSTRAINT PK_provadi PRIMARY KEY (r_cislo, var_symbol);
ALTER TABLE platba_za ADD CONSTRAINT PK_platba_za PRIMARY KEY (var_symbol, c_opravy);
ALTER TABLE se_provadi_oprava ADD CONSTRAINT PK_se_provadi_oprava PRIMARY KEY (VIN, c_opravy);
ALTER TABLE uskutecnuje_opravu ADD CONSTRAINT PK_uskutecnuje_opravu PRIMARY KEY (fir_cislo, r_cislo, c_opravy);
--nastaveni cizich klicu pre vazby
ALTER TABLE vlastni ADD CONSTRAINT FK_Zakaznik_vlastni FOREIGN KEY (r_cislo) REFERENCES Zakaznik ON DELETE CASCADE;
ALTER TABLE vlastni ADD CONSTRAINT FK_Vozidlo_vlastni FOREIGN KEY (VIN) REFERENCES Vozidlo ON DELETE CASCADE;
ALTER TABLE provadi ADD CONSTRAINT FK_Zakaznik_provadi FOREIGN KEY (r_cislo) REFERENCES Zakaznik ON DELETE CASCADE;
ALTER TABLE provadi ADD CONSTRAINT FK_Platba_provadi FOREIGN KEY (var_symbol) REFERENCES Platba ON DELETE CASCADE;
ALTER TABLE platba_za ADD CONSTRAINT FK_Platba_za_opravu FOREIGN KEY (var_symbol) REFERENCES Platba ON DELETE CASCADE;
ALTER TABLE platba_za ADD CONSTRAINT FK_Oprava_za_platbu FOREIGN KEY (c_opravy) REFERENCES Oprava ON DELETE CASCADE;
ALTER TABLE se_provadi_oprava ADD CONSTRAINT FK_Opr_se_prov_na_vozidle FOREIGN KEY (VIN) REFERENCES Vozidlo ON DELETE CASCADE;
ALTER TABLE se_provadi_oprava ADD CONSTRAINT FK_Na_voz_se_prov_opr FOREIGN KEY (c_opravy) REFERENCES Oprava ON DELETE CASCADE;
ALTER TABLE dodava_dily ADD CONSTRAINT FK_Oprava_prijima_dily FOREIGN KEY (c_opravy) REFERENCES Oprava ON DELETE CASCADE;
ALTER TABLE dodava_dily ADD CONSTRAINT FK_Sklad_dodava FOREIGN KEY (kat_c_dilu) REFERENCES Sklad ON DELETE CASCADE;
ALTER TABLE uskutecnuje_opravu ADD CONSTRAINT FK_Oprava_opravuje FOREIGN KEY (c_opravy) REFERENCES Oprava ON DELETE CASCADE;
ALTER TABLE uskutecnuje_opravu ADD CONSTRAINT FK_Zamestnanec_opravuje FOREIGN KEY (fir_cislo, r_cislo) REFERENCES Zamestnanec ON DELETE CASCADE;

--vkladame informace do tabulek
INSERT INTO Zakaznik VALUES('440726/0672', 'Matus Marhefka',TO_DATE('14.11.1990', 'dd.mm.yyyy'), 'Sabinovska 109', 'Košice', 111111111);
INSERT INTO Zakaznik VALUES('900801/5115', 'Tomas Valek', TO_DATE('23.05.1990', 'dd.mm.yyyy'), 'Liptovska', 'Opava', 222222222);
INSERT INTO Zakaznik VALUES('900522/5305', 'Martin Stanovsky', TO_DATE('23.05.1989', 'dd.mm.yyyy'), 'Rooseveltova', 'Krnov', 555555555);
INSERT INTO Zakaznik VALUES('910630/1232', 'Michal Risa', TO_DATE('23.06.1990', 'dd.mm.yyyy'), 'Druzobna 20', 'Bratislava', 333333333);

--Matus
INSERT INTO Vozidlo VALUES('1234567891234567b', 'Dodge Viper', 2001, '1T96953', '440726/0672', 'modra');
INSERT INTO Vozidlo VALUES('1234567891234567c', 'Skoda Octavia', 2001, '1T96959', '440726/0672', 'cerna');
INSERT INTO Vozidlo VALUES('123456789123456Oc', 'Skoda Octavia', 1997, '1T96957', '440726/0672', 'bila');
--Tom
INSERT INTO Vozidlo VALUES('12345678912345678', 'WV Golf', 2005, '1T96950', '900801/5115', 'cervena');
--Michal
INSERT INTO Vozidlo VALUES('1234567894234567a', 'Skoda Octavia', 2005, '1T96951', '910630/1232', 'cervena');

--Michal plati 2x
INSERT INTO Platba VALUES(1, 1000, TO_DATE('16.3.2012', 'dd.mm.yyyy'), 'prevodem', 'vyfuk', null, 'ano');
INSERT INTO Platba VALUES(2, 5000, TO_DATE('23.3.2012', 'dd.mm.yyyy'), 'hotove', 'karoserie, tlumice', null, 'ne');
INSERT INTO Platba VALUES(3, 1999, TO_DATE('26.3.2012', 'dd.mm.yyyy'), 'hotove', 'diagnostika RJ', null, 'ano');

INSERT INTO Oprava VALUES(1, 'vyfuk', TO_DATE('19.3.2012', 'dd.mm.yyyy'), TO_DATE('16.3.2012', 'dd.mm.yyyy'), 1000);
INSERT INTO Oprava VALUES(2, 'karoserie', TO_DATE('19.3.2012', 'dd.mm.yyyy'), TO_DATE('20.3.2012', 'dd.mm.yyyy'), 2500);
INSERT INTO Oprava VALUES(3, 'diagnostika RJ', TO_DATE('23.3.2012', 'dd.mm.yyyy'), TO_DATE('23.3.2012', 'dd.mm.yyyy'), 1999);

INSERT INTO Sklad VALUES('1F65', 'spona', 'Aci', 98.70, 100);
INSERT INTO Zamestnanec VALUES(1, '870711/9067', 'Franta Novak', 'Palackeho 9', 'Brno', 777980987, 'plny', 22000);

--vkladame informace do vazeb
--Matus
INSERT INTO vlastni VALUES('440726/0672', '1234567891234567b');
INSERT INTO vlastni VALUES('440726/0672', '1234567891234567c');
--Tom
INSERT INTO vlastni VALUES('900801/5115', '1234567894234567a');
--Michal
INSERT INTO vlastni VALUES('910630/1232', '1234567894234567a');
INSERT INTO provadi VALUES('910630/1232', 1);
INSERT INTO provadi VALUES('910630/1232', 3);
INSERT INTO platba_za VALUES(1, 1);
INSERT INTO platba_za VALUES(1, 2);
INSERT INTO platba_za VALUES(2, 3);
INSERT INTO se_provadi_oprava VALUES('1234567894234567a', 1);
INSERT INTO se_provadi_oprava VALUES('1234567894234567a', 2);
INSERT INTO se_provadi_oprava VALUES('1234567894234567a', 3);

--zamestnanec provadi opravu
INSERT INTO uskutecnuje_opravu VALUES(1,'870711/9067',1);
INSERT INTO uskutecnuje_opravu VALUES(1,'870711/9067',2);
INSERT INTO uskutecnuje_opravu VALUES(1,'870711/9067',3);

--sklad
INSERT INTO dodava_dily VALUES(1, '1F65');

COMMIT;


--02.04.2012 SELECTY


--DVA DOTAZY VYUZIVAJICI SPOJENI DVOU TABULEK:
--Kteri zakaznici(rodne cislo, jmeno) vlastni vozidlo Skoda Octavia.
SELECT Z.r_cislo, Z.jmeno, V.znacka_typ FROM Zakaznik Z, Vozidlo V WHERE Z.r_cislo=V.majitel AND V.znacka_typ='Skoda Octavia';

--Vypis vsech plateb, ktere provedl zakaznik.
SELECT DISTINCT P.* FROM provadi P, Zakaznik Z WHERE P.r_cislo=Z.r_cislo AND Z.r_cislo='910630/1232';


--JEDEN DOTAZ VYUZIVAJICI SPOJENI TRI TABULEK:
--Jaky zamestnanec(firemni cislo, jmeno) provadi opravu na vozidle s VINem:1234567894234567a.
SELECT Z.fir_cislo, Z.jmeno, S.VIN FROM Zamestnanec Z, uskutecnuje_opravu U, se_provadi_oprava S WHERE Z.fir_cislo=U.fir_cislo AND U.c_opravy=S.c_opravy AND S.VIN='1234567894234567a';


--DVA DOTAZY S KLAUZULI GROUP BY A AGREGACNI FUNKCI:
--Celkove sumy(kc,-) podle zpusobu uhrady.
SELECT zp_uhrady, sum(castka) FROM Platba GROUP BY zp_uhrady;

--Vypis poctu aut podle znacky a typu. Abychom vedeli, jaky typ vozidla nejcasteji opravujeme.
SELECT znacka_typ, COUNT(znacka_typ) FROM Vozidlo GROUP BY znacka_typ;


--JEDEN DOTAZ OBSAHUJICI PREDIKAT EXISTS
--Vypis dosud nezaplacenych plateb(variabilnich symbolu).
SELECT var_symbol FROM Platba
WHERE EXISTS(
	SELECT jmeno
	FROM Zakaznik
	WHERE Platba.uhrazeno = 'ne'
);


--JEDEN DOTAZ OBSAHUJICI PREDIKAT IN
--Vypis zakazniku, kteri bydli v Presove nebo v Opave
SELECT jmeno, bydliste FROM ZAKAZNIK WHERE bydliste IN ('Presov','Opave');

