SQLize Online / PHPize Online  /  SQLtest Online

-- IDS projekt 2024 -- Cast 3 - SQL skript -- Autor: xsamoi00 & xmiron05 -- -------------------------------------------- -- ------------------ Drop -------------------- -- -------------------------------------------- -- Drop check constraints ALTER TABLE Zlodej DROP CONSTRAINT check_stav; ALTER TABLE Zlocin DROP CONSTRAINT check_mira_obtiznosti; ALTER TABLE Typ_Zlocinu DROP CONSTRAINT check_mira_obtiznosti_provedeni; ALTER TABLE Typ_Zlocinu DROP CONSTRAINT check_mira_obtiznosti_proskoleni; ALTER TABLE Rajon DROP CONSTRAINT check_pocet_lidi; ALTER TABLE Rajon DROP CONSTRAINT check_kapacita_zlodeju; ALTER TABLE Okres DROP CONSTRAINT check_pocet_rajonu; -- Drop klíčů ALTER TABLE Zlodej_Zlocin DROP CONSTRAINT FK_Zlodej_Zlocin_Zlodej; ALTER TABLE Zlodej_Zlocin DROP CONSTRAINT FK_Zlodej_Zlocin_Zlocin; ALTER TABLE Zlocin_Okres DROP CONSTRAINT FK_Zlocin_Okres_Zlocin; ALTER TABLE Zlocin_Okres DROP CONSTRAINT FK_Zlocin_Okres_Okres; ALTER TABLE Zlodej_Vybaveni DROP CONSTRAINT FK_Zlodej_Vybaveni_Zlodej; ALTER TABLE Zlodej_Vybaveni DROP CONSTRAINT FK_Zlodej_Vybaveni_Vybaveni; -- Drop vztahu ALTER TABLE Zlodej DROP CONSTRAINT FK_Zlodej_Povoleni; ALTER TABLE Zlodej DROP CONSTRAINT FK_Zlodej_TypVybaveni; ALTER TABLE Zlodej DROP CONSTRAINT FK_Zlodej_TypZlocinu; ALTER TABLE Zlodej DROP CONSTRAINT FK_Zlodej_Okres; ALTER TABLE Zlodej DROP CONSTRAINT FK_Zlodej_Rajon; ALTER TABLE Zlocin DROP CONSTRAINT FK_Zlocin_TypZlocinu; ALTER TABLE Vybaveni DROP CONSTRAINT FK_Vybaveni_TypVybaveni; ALTER TABLE Zlocin DROP CONSTRAINT FK_Zlocin_Povoleni; ALTER TABLE Povoleni DROP CONSTRAINT FK_Povoleni_TypZlocinu; ALTER TABLE Typ_Zlocinu DROP CONSTRAINT FK_TypZlocinu_TypVybaveni; ALTER TABLE Rajon DROP CONSTRAINT FK_Rajon_Okres; -- Drop tabulek DROP TABLE Zlodej; DROP TABLE Zlocin; DROP TABLE Typ_Zlocinu; DROP TABLE Vybaveni; DROP TABLE Typ_Vybaveni; DROP TABLE Okres; DROP TABLE Rajon; DROP TABLE Povoleni; DROP TABLE Zlodej_Zlocin; DROP TABLE Zlocin_Okres; DROP TABLE Zlodej_Vybaveni; -- -------------------------------------------- -- -----------Vytvoření tabulek---------------- -- -------------------------------------------- CREATE TABLE Zlodej ( id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, RealneJmeno VARCHAR(50) NOT NULL, Prezdivka VARCHAR(50) NOT NULL, Vek INT NOT NULL, Stav VARCHAR(50) NOT NULL, Vypsana_odmena INT NOT NULL, -- Provedení vztahu s tabulkami Kod_povoleni INT, Kod_typu_vybaveni INT, Kod_typu_zlocinu INT, Kod_rajonu INT, Kod_okresu INT ); CREATE TABLE Zlocin ( Kod_zlocinu NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Datum DATE NOT NULL, Mira_obtiznosti INT NOT NULL, Zasazene_koristi VARCHAR(100) NOT NULL, -- Provedení vztahu s tabulkami Kod_typu_zlocinu INT, Kod_povoleni INT ); CREATE TABLE Typ_Zlocinu ( Kod_typu_zlocinu NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Nazev_typu VARCHAR(100) NOT NULL, Popis VARCHAR(100) NOT NULL, Mira_obtiznosti_provedeni INT NOT NULL, Mira_obtiznosti_proskoleni INT NOT NULL, -- Provedení vztahu s tabulkami Kod_typu_vybaveni INT ); CREATE TABLE Vybaveni ( Kod_vybaveni NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Nazev_vybaveni VARCHAR(100) NOT NULL, -- Provedení vztahu s tabulkami Kod_typu_vybaveni INT ); CREATE TABLE Typ_Vybaveni ( Kod_typu_vybaveni NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Nazev_typu_vybaveni VARCHAR(100) NOT NULL ); CREATE TABLE Okres ( Kod_okresu NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Nazev_Okresu VARCHAR(100) NOT NULL, Pocet_rajonu INT NOT NULL ); CREATE TABLE Rajon ( Kod_rajonu NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, Nazev_rajonu VARCHAR(100) NOT NULL, Pozice VARCHAR(100) NOT NULL, Pocet_lidi INT NOT NULL, Celkove_dostupne_bohatstvi VARCHAR(100) NOT NULL, Kapacita_zlodeju INT NOT NULL, -- Provedení vztahu s tabulkami Kod_okresu INT -- Patrí do okresu, dolu je vytvořen vztah ); CREATE TABLE Povoleni ( Kod_povoleni NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Provedení vztahu s tabulkami Kod_typu_zlocinu INT ); -- -------------------------------------------- -- ------------Vytvoření vztahů---------------- -- -------------------------------------------- -- Vztah mezi tabulkami Zlodej a Povoleni (1...N Zlodej(ů) může mít 0...N Povolení) ALTER TABLE Zlodej ADD CONSTRAINT FK_Zlodej_Povoleni FOREIGN KEY (Kod_povoleni) REFERENCES Povoleni(Kod_povoleni) ON DELETE SET NULL; -- Vztah mezi tabulkami Zlodej a Typ_Vybaveni (0...N Zlodej(ů) může byt proškolen pro 0...N Vybevení) ALTER TABLE Zlodej ADD CONSTRAINT FK_Zlodej_TypVybaveni FOREIGN KEY (Kod_typu_vybaveni) REFERENCES Typ_Vybaveni(Kod_typu_vybaveni) ON DELETE SET NULL; -- Vztah mezi tabulkami Zlodej a Typ_Zlocinu (0...N Zlodej(ů) může být proškolen pro 0...N Zlocin(ů)) ALTER TABLE Zlodej ADD CONSTRAINT FK_Zlodej_TypZlocinu FOREIGN KEY (Kod_typu_zlocinu) REFERENCES Typ_Zlocinu(Kod_typu_zlocinu) ON DELETE SET NULL; -- Vztah mezi tabulkami Zlodej a Okres (0...N Zlodej(ů) může pohybovat v 0...N Okres(ů)) ALTER TABLE Zlodej ADD CONSTRAINT FK_Zlodej_Okres FOREIGN KEY (Kod_okresu) REFERENCES Okres(Kod_okresu) ON DELETE SET NULL; -- Vztah mezi tabulkami Zlodej a Rajon (0...N Zlodej(ů) eviduje 0...N Rajon(ů)) ALTER TABLE Zlodej ADD CONSTRAINT FK_Zlodej_Rajon FOREIGN KEY (Kod_rajonu) REFERENCES Rajon(Kod_rajonu) ON DELETE SET NULL; -- Vztah mezi tabulkami Okres a Rajon - Rajon je v okresu, maji vztah Specializace/Generalizace ALTER TABLE Rajon ADD CONSTRAINT FK_Rajon_Okres FOREIGN KEY (Kod_okresu) REFERENCES Okres(Kod_okresu) ON DELETE SET NULL; -- Vztah mezi tabulkami Zlocin a Typ_Zlocinu (0...N Zlocin(ů) může mít 1 Typ_Zlocinu) ALTER TABLE Zlocin ADD CONSTRAINT FK_Zlocin_TypZlocinu FOREIGN KEY (Kod_typu_zlocinu) REFERENCES Typ_Zlocinu(Kod_typu_zlocinu) ON DELETE SET NULL; -- Vztah mezi tabulkami Vybaveni a Typ_Vybaveni (0...N Vybaveni může mít 1 Typ_Vybaveni) ALTER TABLE Vybaveni ADD CONSTRAINT FK_Vybaveni_TypVybaveni FOREIGN KEY (Kod_typu_vybaveni) REFERENCES Typ_Vybaveni(Kod_typu_vybaveni) ON DELETE SET NULL; -- Vztah mezi tabulkami Zlocin a Povoleni (0...1 Zlocin může byt proveden na zakladě 1 Povoleni) ALTER TABLE Zlocin ADD CONSTRAINT FK_Zlocin_Povoleni FOREIGN KEY (Kod_povoleni) REFERENCES Povoleni(Kod_povoleni) ON DELETE SET NULL; -- Vztah mezi tabulkami Povoleni a Typ_Zlocinu (0...N Zlocin(ů) může být vydano na 1 Typ_Zlocinu) ALTER TABLE Povoleni ADD CONSTRAINT FK_Povoleni_TypZlocinu FOREIGN KEY (Kod_typu_zlocinu) REFERENCES Typ_Zlocinu(Kod_typu_zlocinu) ON DELETE SET NULL; -- Vztah mezi tabulkami Typ_Zlocinu a Typ_Vybaveni (0...N Typ_Zlocinu vyžaduje 0...N Typ_Vybaveni) ALTER TABLE Typ_Zlocinu ADD CONSTRAINT FK_TypZlocinu_TypVybaveni FOREIGN KEY (Kod_typu_vybaveni) REFERENCES Typ_Vybaveni(Kod_typu_vybaveni) ON DELETE SET NULL; -- Vztah mezi tabulkami Zlodej a Zlocin (1...N Zlodej(ů) může provest 0...N Zlocin(ů)) CREATE TABLE Zlodej_Zlocin ( Kod_zlodeje INT NOT NULL, Kod_zlocinu INT NOT NULL, CONSTRAINT PK_Zlodej_Zlocin PRIMARY KEY (Kod_zlodeje, Kod_zlocinu), CONSTRAINT FK_Zlodej_Zlocin_Zlodej FOREIGN KEY (Kod_zlodeje) REFERENCES Zlodej(id) ON DELETE SET NULL, CONSTRAINT FK_Zlodej_Zlocin_Zlocin FOREIGN KEY (Kod_zlocinu) REFERENCES Zlocin(Kod_zlocinu) ON DELETE SET NULL ); -- Vztah mezi tabulkami Zlocin a Okres (0...N Zlocin(ů) může byt provedeno v 1...N Okres(ů)) CREATE TABLE Zlocin_Okres ( Kod_zlocinu INT NOT NULL, Kod_okresu INT NOT NULL, constraint PK_Zlocin_Okres PRIMARY KEY (Kod_zlocinu, Kod_okresu), constraint FK_Zlocin_Okres_Zlocin FOREIGN KEY (Kod_zlocinu) REFERENCES Zlocin(Kod_zlocinu) ON DELETE SET NULL, constraint FK_Zlocin_Okres_Okres FOREIGN KEY (Kod_okresu) REFERENCES Okres(Kod_okresu) ON DELETE SET NULL ); -- vztah mezi tabulkami Zlodej a Vybaveni (0...1 Zlodej(ů) může vlastnit 0...N Vybavení a musí mít obdobi vlastnosti (datum_od, datum_do)) CREATE TABLE Zlodej_Vybaveni ( Kod_zlodeje INT NOT NULL, Kod_vybaveni INT NOT NULL, Datum_od DATE NOT NULL, Datum_do DATE NOT NULL, CONSTRAINT PK_Zlodej_Vybaveni PRIMARY KEY (Kod_zlodeje, Kod_vybaveni, Datum_od, Datum_do), CONSTRAINT FK_Zlodej_Vybaveni_Zlodej FOREIGN KEY (Kod_zlodeje) REFERENCES Zlodej(id) ON DELETE SET NULL, CONSTRAINT FK_Zlodej_Vybaveni_Vybaveni FOREIGN KEY (Kod_vybaveni) REFERENCES Vybaveni(Kod_vybaveni) ON DELETE SET NULL ); -- --------------------------------------------------------- -- ----------------Check constraints------------------------ -- --------------------------------------------------------- -- Check constraint pro tabulku Zlodej ALTER TABLE Zlodej ADD CONSTRAINT check_stav CHECK (Stav IN ('zivy', 'mrtvy')); -- Check constraint pro tabulku Zlocin ALTER TABLE Zlocin ADD CONSTRAINT check_mira_obtiznosti CHECK (Mira_obtiznosti BETWEEN 1 AND 10); -- Check constraint pro tabulku Typ_Zlocinu ALTER TABLE Typ_Zlocinu ADD CONSTRAINT check_mira_obtiznosti_provedeni CHECK (Mira_obtiznosti_provedeni BETWEEN 1 AND 10); -- Check constraint pro tabulku Typ_Zlocinu ALTER TABLE Typ_Zlocinu ADD CONSTRAINT check_mira_obtiznosti_proskoleni CHECK (Mira_obtiznosti_proskoleni BETWEEN 1 AND 10); -- Check constraint pro tabulku Rajon ALTER TABLE Rajon ADD CONSTRAINT check_pocet_lidi CHECK (Pocet_lidi > 0); -- Check constraint pro tabulku Rajon ALTER TABLE Rajon ADD CONSTRAINT check_kapacita_zlodeju CHECK (Kapacita_zlodeju > 0); -- Check constraint pro tabulku Okres ALTER TABLE Okres ADD CONSTRAINT check_pocet_rajonu CHECK (Pocet_rajonu > 0); -- --------------------------------------------------------- -- -----------------------Insert---------------------------- -- --------------------------------------------------------- -- insert do tabulky Zlodej INSERT INTO Zlodej (id, RealneJmeno, Prezdivka, Vek, Stav, Vypsana_odmena, Kod_povoleni, Kod_typu_vybaveni, Kod_typu_zlocinu, Kod_rajonu, Kod_okresu) VALUES (2, 'Anastasiia Samoilova', 'Rychlá', 28, 'zivy', 1000000, NULL, NULL, NULL, NULL, NULL); INSERT INTO Zlodej (id, RealneJmeno, Prezdivka, Vek, Stav, Vypsana_odmena, Kod_povoleni, Kod_typu_vybaveni, Kod_typu_zlocinu, Kod_rajonu, Kod_okresu) VALUES (1, 'Anastasiia Mironova', 'Oxxxymiron', 35, 'zivy', 35000, NULL, NULL, NULL, NULL, NULL); INSERT INTO Zlodej (id, RealneJmeno, Prezdivka, Vek, Stav, Vypsana_odmena, Kod_povoleni, Kod_typu_vybaveni, Kod_typu_zlocinu, Kod_rajonu, Kod_okresu) VALUES (4, 'Maksim Dubrovkin', 'Dub', 35, 'mrtvy', 20000, NULL, NULL, NULL, NULL, NULL); -- insert do tabulky Zlocin INSERT INTO Zlocin (Kod_zlocinu, Datum, Mira_obtiznosti, Zasazene_koristi, Kod_typu_zlocinu, Kod_povoleni) VALUES (2, TO_DATE('2024-02-18', 'YYYY-MM-DD'), 8, 'Drahé šperky', NULL, NULL); INSERT INTO Zlocin (Kod_zlocinu, Datum, Mira_obtiznosti, Zasazene_koristi, Kod_typu_zlocinu, Kod_povoleni) VALUES (3, TO_DATE('2024-01-25', 'YYYY-MM-DD'), 6, 'Hotovost', NULL, NULL); -- insert do tabulky Zlodej_zlocin INSERT INTO Zlodej_zlocin (Kod_zlocinu, Kod_zlodeje) VALUES (2, 1); INSERT INTO Zlodej_zlocin (Kod_zlocinu, Kod_zlodeje) VALUES (3, 1); INSERT INTO Zlodej_zlocin (Kod_zlocinu, Kod_zlodeje) VALUES (3, 2); INSERT INTO Zlodej_zlocin (Kod_zlocinu, Kod_zlodeje) VALUES (3, 4); -- insert do tabulky Typ_Zlocinu INSERT INTO Typ_Zlocinu (Nazev_typu, Popis, Mira_obtiznosti_provedeni, Mira_obtiznosti_proskoleni) VALUES ('Loupež', 'Okradení bohatých osob', 8, 6); INSERT INTO Typ_Zlocinu (Nazev_typu, Popis, Mira_obtiznosti_provedeni, Mira_obtiznosti_proskoleni) VALUES ('Krádež', 'Neoprávněné obohacení', 5, 4); -- insert do tabulky Typ_Vybaveni INSERT INTO Typ_Vybaveni (Nazev_typu_vybaveni) VALUES ('Chirurgické nástroje'); INSERT INTO Typ_Vybaveni (Nazev_typu_vybaveni) VALUES ('Technické vybavení'); -- insert do tabulky Vybaveni INSERT INTO Vybaveni (Nazev_vybaveni, Kod_typu_vybaveni) VALUES ('Skalpel', 1); INSERT INTO Vybaveni (Nazev_vybaveni, Kod_typu_vybaveni) VALUES ('Laserový řezák', 2); -- insert do tabulky Okres INSERT INTO Okres (Kod_okresu, Nazev_Okresu, Pocet_rajonu) VALUES (1, 'Praha', 2); Insert into Okres (Kod_okresu, Nazev_Okresu, Pocet_rajonu) VALUES (2, 'Brno', 1); -- insert do tabulky Rajon INSERT INTO Rajon (Kod_rajonu, Nazev_rajonu, Pozice, Pocet_lidi, Celkove_dostupne_bohatstvi, Kapacita_zlodeju, Kod_okresu) VALUES (228, 'Staré Město', 'Centrum', 10000, 'Vysoké', 50, 1); INSERT INTO Rajon (Kod_rajonu, Nazev_rajonu, Pozice, Pocet_lidi, Celkove_dostupne_bohatstvi, Kapacita_zlodeju, Kod_okresu) VALUES (999, 'Nové Město', 'Okraj', 15000, 'Střední', 80, 1); -- insert do tabulky Povoleni INSERT INTO Povoleni (Kod_typu_zlocinu) VALUES (1); INSERT INTO Povoleni (Kod_typu_zlocinu) VALUES (2); -- Typ_Zlocinu INSERT INTO Typ_Zlocinu (Nazev_typu, Popis, Mira_obtiznosti_provedeni, Mira_obtiznosti_proskoleni, Kod_typu_vybaveni) VALUES ('Bank Robbery', 'Stealing money from a bank', 8, 7, 2); -- Vybaveni INSERT INTO Vybaveni (Nazev_vybaveni, Kod_typu_vybaveni) VALUES ('Lock Pick', 2); -- Zlodej_Vybaveni INSERT INTO Zlodej_Vybaveni (Kod_zlodeje, Kod_vybaveni, Datum_od, Datum_do) VALUES (1, 1, TO_DATE('2024-01-01', 'YYYY-MM-DD'), TO_DATE('2024-12-31', 'YYYY-MM-DD')); INSERT INTO Zlodej_Vybaveni (Kod_zlodeje, Kod_vybaveni, Datum_od, Datum_do) VALUES (4, 2, TO_DATE('2024-01-01', 'YYYY-MM-DD'), TO_DATE('2024-12-31', 'YYYY-MM-DD')); -- Rajon INSERT INTO Rajon (Nazev_rajonu, Pozice, Pocet_lidi, Celkove_dostupne_bohatstvi, Kapacita_zlodeju, Kod_okresu) VALUES ('Downtown', 'City Center', 100000, 'High', 50, 2); INSERT INTO Zlodej (id, RealneJmeno, Prezdivka, Vek, Stav, Vypsana_odmena, Kod_povoleni, Kod_typu_vybaveni, Kod_typu_zlocinu, Kod_rajonu, Kod_okresu) VALUES (7, 'Ludmila Lubov', 'Hloupa', 47, 'zivy', 8000, NULL, 2, 1, 228, 1); INSERT INTO Zlodej_Vybaveni (Kod_zlodeje, Kod_vybaveni, Datum_od, Datum_do) VALUES ( (SELECT id FROM Zlodej WHERE Prezdivka = 'Rychlá'), -- Získání ID zloděje pomocí jeho přezdívky (SELECT Kod_vybaveni FROM Vybaveni WHERE Nazev_vybaveni = 'Skalpel'), -- Získání ID vybavení pomocí názvu TO_DATE('2024-01-01', 'YYYY-MM-DD'), -- Datum začátku vlastnictví vybavení TO_DATE('2024-12-31', 'YYYY-MM-DD') -- Datum konce vlastnictví vybavení ); -- -------------------------------------------- -- -------------- SELECT ----------------- -- -------------------------------------------- -- Hledáme Střední odměnu za zlodeju grouped by věk SELECT vek, AVG(Vypsana_odmena) FROM Zlodej GROUP BY vek; -- Hledáme Jmeno Zlodeje, které udělali zločin 2024-01-25 SELECT Zlodej.RealneJmeno, Zlocin.DATUM FROM Zlodej_zlocin JOIN Zlodej ON Zlodej.id = Zlodej_zlocin.Kod_zlodeje JOIN Zlocin ON Zlocin.kod_zlocinu = Zlodej_zlocin.Kod_zlocinu WHERE Zlocin.Datum = TO_DATE('2024-01-25', 'YYYY-MM-DD'); -- Dotaz 1: SELECT с JOIN -- Hledáme Jmeno Zlodeje a jeho Rajon SELECT Zlodej.RealneJmeno, Rajon.Nazev_rajonu FROM Zlodej JOIN Rajon ON Zlodej.Kod_rajonu = Rajon.Kod_rajonu; -- Dotaz 2: SELECT с JOIN -- Hledáme Jmeno Zlodeje, Typ Zlocinu a Rajon SELECT Zlodej.RealneJmeno, Typ_Zlocinu.Nazev_typu, Rajon.Nazev_rajonu FROM Zlodej JOIN Typ_Zlocinu ON Zlodej.Kod_typu_zlocinu = Typ_Zlocinu.Kod_typu_zlocinu JOIN Rajon ON Zlodej.Kod_rajonu = Rajon.Kod_rajonu; -- Dotaz 3: SELECT с GROUP BY -- Hledáme Jmeno Zlodeje a počet zločinů, které spáchal SELECT Zlodej.RealneJmeno, COUNT(Zlodej_Zlocin.Kod_zlocinu) AS Pocet_zlocinu FROM Zlodej LEFT JOIN Zlodej_Zlocin ON Zlodej.id = Zlodej_Zlocin.Kod_zlodeje GROUP BY Zlodej.RealneJmeno; -- Dotaz 5: SELECT s EXISTS -- Hledáme Jmeno Zlodeje, který má vybavení SELECT RealneJmeno FROM Zlodej WHERE EXISTS ( SELECT 1 FROM Zlodej_Vybaveni WHERE Zlodej.id = Zlodej_Vybaveni.Kod_zlodeje ); -- Dotaz 6: Hledáme Jmeno Zlodeje, který má vybavení a jeho vybavení SELECT Nazev_typu FROM Typ_Zlocinu WHERE Kod_typu_zlocinu IN ( SELECT DISTINCT Kod_typu_zlocinu FROM Povoleni ); -- 7. Dotaz na vypsání zlodějů, kteří mají vybavení typu 'Chirurgické nástroje' SELECT Zlodej.RealneJmeno, Zlodej.Prezdivka FROM Zlodej JOIN Zlodej_Vybaveni ON Zlodej.id = Zlodej_Vybaveni.Kod_zlodeje JOIN Vybaveni ON Zlodej_Vybaveni.Kod_vybaveni = Vybaveni.Kod_vybaveni JOIN Typ_Vybaveni ON Vybaveni.Kod_typu_vybaveni = Typ_Vybaveni.Kod_typu_vybaveni WHERE Typ_Vybaveni.Nazev_typu_vybaveni = 'Chirurgické nástroje'; -- Dotaz 8: SELECT s klauzulí GROUP BY a agregační funkcí -- Hledáme průměrný věk zlodějů podle stavu (živý/mrtvý) SELECT Stav, AVG(Vek) AS Prumerny_vek FROM Zlodej GROUP BY Stav; -- Dotaz 10: SELECT s predikátem EXISTS -- Hledáme Jmeno Zlodeje, který provedl alespoň jeden zločin SELECT RealneJmeno FROM Zlodej WHERE EXISTS ( SELECT 1 FROM Zlodej_Zlocin WHERE Zlodej.id = Zlodej_Zlocin.Kod_zlodeje ); -- --------------------------------------------------------- -- --------------------Triggers----------------------------- -- --------------------------------------------------------- -- Trigger pro vypočítání poocet zlodějů v rajonu CREATE OR REPLACE TRIGGER "pocet_zlodeju" AFTER INSERT OR DELETE OR UPDATE ON Zlodej FOR EACH ROW begin UPDATE Rajon SET Kapacita_zlodeju = ( SELECT COUNT(*) FROM Zlodej WHERE Zlodej.Kod_rajonu = Rajon.Kod_rajonu ) WHERE Rajon.Kod_rajonu = :new.Kod_rajonu; exception when no_data_found then null; end; -- Trigger pro vypočítání vypsané odměny: pokud ma 1 zlocin, tak vypsaná odměna je 10000, pokud ma 2 zlociny, tak 20000 atd. Pokud ma 0 zlocinu, tak vypsaná odměna je 3000 CREATE OR REPLACE TRIGGER "vypsana_odmena" AFTER INSERT OR DELETE OR UPDATE ON Zlodej_Zlocin FOR EACH ROW begin UPDATE Zlodej SET Vypsana_odmena = ( SELECT COUNT(*) FROM Zlodej_Zlocin WHERE Zlodej.id = Zlodej_Zlocin.Kod_zlodeje ) * 10000 + 3000 WHERE Zlodej.id = :new.Kod_zlodeje; exception when no_data_found then null; end;