Andmbaasid (1)

--Taavet Tamm(a72028)
--ylesanne 4.
--1. Leida klubi Laudnikud liikmete nimekiri ( eesnimi ja perenimi ) tähestiku järjekorras.
SELECT eesnimi, perenimi FROM isik, klubi WHERE klubi.nimi = 'Laudnikud' ORDER BY perenimi asc;
--2. Leida klubi Laudnikud liikmete arv.
SELECT COUNT (*) AS "Klubi Laudnikud liikmete arv" FROM isik WHERE klubi = '51';
--Leida V-tähega algavate klubide M-tähega algavate eesnimedega isikute perekonnanimed .
SELECT distinct perenimi FROM isik, klubi WHERE klubi.nimi like 'V%' and eesnimi like 'M%' ORDER BY perenimi asc;
--4. Leida kõige esimesena alanud partii algamise aeg.
SELECT min(Algushetk) FROM partii;
--5. Leida partiide mängijad (valge ja must), mis algasid 04. märtsil ajavahemikus 9:00 kuni 11:00.
SELECT partii.ID AS "Partii ID", perenimi || ', ' || eesnimi AS "Nimi", isik.ID AS "Isiku ID", must, valge FROM isik, partii WHERE isik.id in (partii.must,partii.valge) AND partii.algushetk between '2005-03-04 09:00:00.000' and '2005-03-04 11:00:00.000';
--6. Leida valgetega võitnute (valge_tulemus=2) nimed, kus partii kestis 9 kuni 11 minutit (vt funktsiooni datediff()).
SELECT Eesnimi, Perenimi FROM isik, partii WHERE isik.ID = partii.valge AND partii.Valge_tulemus='2' AND DATEDIFF(minute, partii.Algushetk, partii.Lopphetk) between 9 and 11;
--7. Leida rohkem kui 1 kord esinevad perekonnanimed (ja ei muud).
SELECT Perenimi FROM isik group by Perenimi having Count(*)>1 ORDER BY Count(*) desc ;
--8. Leida klubid, kus on alla 4 liikme
SELECT Nimi FROM isik, klubi WHERE klubi.id = isik.klubi GROUP BY Nimi HAVING Count(*) --9. Leida kõigi Arvode poolt valgetega mängitud partiide arv
SELECT Count(*) FROM isik, partii WHERE isik.ID = partii.valge and isik.eesnimi = ' Arvo ';
--10. Leida kõigi Arvode poolt valgetega mängitud partiide arv turniiride lõikes
SELECT Count(*) AS "Arvode poolt mängitud partiide arv", Nimi AS " Turniir " FROM isik, partii, turniir WHERE isik.ID = partii.valge and isik.eesnimi = 'Arvo' and turniir.id = partii.turniir Group by nimi;
--11. 11. Leida kõigi Mariade mustadega mängitud mängudest saadud punktide arv (tulemus = 2 on võit ja annab 1 punkti, tulemus = 1 on viik ja annab pool punkti).
SELECT SUM(IF partii.musta_tulemus=2 THEN 1 ELSE IF partii.musta_tulemus = 1 THEN 0.5 else 0 ENDIF)AS "Kogu Tulemus" FROM isik, partii WHERE isik.ID = partii.must AND isik.eesnimi = 'Maria';
--12. Leida partiide keskmine kestvus turniiride kaupa (tulemuseks on tabel 2 veeruga: #turniiri nimi, keskmine partii pikkus)
SELECT turniir.nimi, AVG(datediff(minute, partii.algushetk, partii.lopphetk)) AS "Keskmine partii pikkus" FROM turniir, partii WHERE partii.turniir = turniir.id GROUP BY turniir.nimi;
--ylesanne 5
--1.
CREATE VIEW v_turniiripartii(turniir_nimi, partii_id, partii_algus, partii_lopp) AS SELECT turniir_nimi, partii_id, partii_algus, partii_lopp FROM partii;
--2. Luua vaade v_klubipartiikogus(klubi_nimi, partiisid) partiisid = selliste partiide arv, kus kas valge või must mängija on klubi liige („klubipartiisid” on 2 korda partiide arv).
CREATE VIEW v_klubipartiikogus(klubi_nimi, partiisid) AS
SELECT nimi, count(*) AS partiisid FROM partii, isik JOIN klubi ON isik.klubi = klubi.id WHERE
isik.id=partii.must or isik.id=partii.valge group by klubi.nimi;
--3. Luua vaade v_punkt(partii, turniir, mangija, varv, punkt), kus oleksid kõigi mängijate kõigi partiide jooksul saadud punktid ( viitega partiile ja turniirile) koos värviga (valge (V), must (M))
CREATE VIEW v_punkt (partii, turniir, mangija, varv, punkt) AS
SELECT Partii.Id, Turniir, Valge, 'V', Valge_tulemus/2.0 FROM Partii
UNION SELECT Partii.Id, Turniir, Must, 'M', Musta_tulemus/2.0 FROM Partii;
--#4. Vaate v_punkt ja vaate v_mangija põhjal teha vaade v_edetabel(mangija, turniir, punkte), kus veerus mangija on mängija nimi (v_mangija.nimi) ja veerus turniir on turniiri
ID. Punkte arvutatakse iga turniiri jaoks (mängija punktid sellel turniiril).
CREATE VIEW v_mangija(id, nimi) AS SELECT Id, Perenimi || ', ' || Eesnimi FROM Isik;
CREATE VIEW v_edetabel(mangija, turniir, punkte) AS SELECT v_mangija.nimi, v_punkt.turniir, SUM(v_punkt.punkt)
FROM v_mangija, v_punkt WHERE v_mangija.id=v_punkt.mangija GROUP BY turniir, nimi;
--5.Leida (teha päring) turniiri “Kolme klubi kohtumine ”(turniiri ID = 41) edetabeli saamiseks (suurema punktiarvuga mängija eespool)
SELECT nimi, punkte FROM v_edetabel WHERE turniir='41' ORDER BY punkte;
--ylesanne 6
--1. Luua f-n klubiliikmete arvu leidmiseks klubi id põhjal f_klubi suurus(...)
CREATE FUNCTION f_klubisuurus(a_id INTEGER ) RETURNS Integer
BEGIN
DECLARE l_arv INTEGER;
SELECT COUNT(*) INTO l_arv FROM Isik WHERE Klubi = a_id;
RETURN l_arv;
END
--2.Luua f-n ees-ja perenime kokku liitmiseks eesti ametlikul viisil ("perenimi, eesnimi") f_nimi(...), parameetriks id.
CREATE FUNCTION f_nimi (a_eesnimi VARCHAR(50), a_perenimi VARCHAR(50)) RETURNS VARCHAR(100) DETERMINISTIC
BEGIN
RETURN a_perenimi || ', ' || a_eesnimi;
END
--3.Luua f-n ühe mängija partiide koguarv f_mangijakoormus(...)
CREATE FUNCTION f_mangijakoormus (a_id INTEGER) RETURNS INTEGER
BEGIN
DECLARE l_valge INTEGER;
DECLARE l_must INTEGER;
SELECT COUNT(*) INTO l_valge FROM Partii WHERE Valge = a_id;
SELECT COUNT(*) INTO l_must FROM Partii WHERE Must = a_id;
RETURN l_valge + l_must;
END
--4.Luua protseduur sp_uus_isik, mis lisab eesnime ja perenimega määratud isiku etteantud numbriga klubisse ning paneb neljandasse parameetrisse uue isiku ID väärtuse.
CREATE PROCEDURE sp_uus_isik (IN a_eesnimi VARCHAR(50), IN a_perenimi VARCHAR(50), IN a_klubi INTEGER, OUT a_id INTEGER)
BEGIN
INSERT INTO Isik (Eesnimi, Perenimi, Klubi) VALUES (a_eesnimi, a_perenimi, a_klubi);
SELECT @@ identity INTO l_id;
END
--5.Luua tabelit väljastav protseduur sp_infopump(). See peab andma välja unioni-ga kokku panduna järgmised asjad (kasutades varemdefineeritud võimalusi):
--1) klubi nimi ja tema mängijate arv (kasutada funktsiooni f_klubisuurus)
--2) turniiri nimi ja tema jooksul tehtud mängude arv (kasutada group by)
--3) mängija nimi ja tema poolt mängitud partiide arv (kasutada f_nimi ja f_mangijakoormus) ning tulemus sorteerida nii, et klubide info oleks kõige ees, siisturniiride oma ja siis alles isikud. Iga grupi sees sorteerida nime järgi.
CREATE PROCEDURE sp_infopump ()
RESULT (Osa INTEGER, Nimi VARCHAR(100), Arv INTEGER)
BEGIN
SELECT 1, Nimi, f_klubisuurus(Id) FROM Klubi UNION ALL
SELECT 2, Nimi, COUNT(*) FROM Turniir JOIN Partii ON Turniir.Id = Partii.Turniir GROUP BY Nimi UNION ALL
SELECT 3, f_nimi(Eesnimi, Perenimi), f_mangijakoormus(Id) FROM Isik
ORDER BY 1, Nimi;
END
--ylesanne 7.
--1. Luua tabel Asula (id integer, nimi varchar(100)) ID on primaarvõti, automaatselt tuleneva väärtusega Nimi on unikaalne. Mõlemad väljad on kohustuslikud.
CREATE TABLE Asula (Id INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY, Nimi VARCHAR(100) NOT NULL UNIQUE);
--2. Lisada uude tabelisse kõik aadressid tabelist Klubi ja toimumiskohad tabelist Turniir.
INSERT INTO Asula (Nimi) SELECT Aadress FROM Klubi UNION SELECT Toimumiskoht FROM Turniir;
--3. Lisada tabelisse Klubi veerg Asula (integer).
ALTER TABLE Klubi ADD Asula INTEGER;
--4. Väärtustada korraga kõigil Klubi kirjetel veerg Asula sobiliku ID’ga tabelist Asula:
update klubi set asula = (SELECT id FROM asula WHERE asula.nimi= klubi.aadress);
--5. Lisada tabelile Klubi välisvõti tabelisse Asula (fk_klubi_2_asula). Kontrollida andmeid (võrrelda tekstiveerge):
ALTER TABLE Klubi ADD CONSTRAINT fk_klubi_2_asula FOREIGN KEY (Asula) REFERENCES Asula (Id);
SELECT klubi.aadress, asula.nimi FROM klubi JOIN asula ON klubi.asula= asula.id;
--6. Lisada tabelisse Turniir veerg Asula (integer).
ALTER TABLE Turniir ADD Asula INTEGER;
--7. Väärtustada korraga kõigil Turniiri kirjetel veerg Asula sobiliku ID’ga tabelist Asula.
UPDATE Turniir SET Asula = (SELECT Id FROM Asula WHERE Asula.Nimi = Turniir.Toimumiskoht);
--8. Lisada tabelile Turniir välisvõti tabelisse Asula (fk_turniir_2_asula). Kontrollida andmeid (võrrelda tekstiveerge).
ALTER TABLE Turniir ADD CONSTRAINT fk_turniir_2_asula FOREIGN KEY (Asula) REFERENCES Asula (Id);
SELECT Turniir.Toimumiskoht, Asula.Nimi
FROM Turniir JOIN Asula ON Turniir.Asula = Asula.Id;
--9. Luua vaade v_asulaklubisid(asula_id, asula_nimi, klubisid), mis annaks asulate klubide arvud.
CREATE VIEW v_asulaklubisid(asula_id, asula_nimi, klubisid) AS SELECT Id, nimi, (SELECT COUNT(*) FROM Klubi WHERE Asula = Asula.Id) FROM Asula;
--10. Luua vaade v_asulasuurus(asula_id, asula_nimi, mangijaid), mis annaks asulatemängijate arvud. Kontrollküsimus: kas võib tekkida kirje, kus mangija id= 0?
--Võib, turniiriga asulas, kus puuduvad klubid.
CREATE VIEW v_asulasuurus (asula_id, asula_nimi, mangijaid) AS
SELECT Id, Nimi, (SELECT COUNT(*) FROM Isik JOIN Klubi ON Klubi.Id = Isik.Klubi
WHERE Klubi.Asula = Asula.Id) FROM Asula;
--11. Lisada lihtne protseduur klubi kustutamiseks sp_kustuta_klubi(klubinimi).
CREATE PROCEDURE sp_kustuta_klubi (IN klubinimi VARCHAR(100))
BEGIN
DELETE FROM Klubi WHERE Nimi = klubinimi;
END
--12. Luua trigger , mis klubi lisamise järel lisaks aadressi asula tabelisse, kui seda seal pole, ning kui lisab, siis väärtustaks klubi tabelis asula välja uue asula ID’ga (nimeks tg_lisa_klubi).
CREATE TRIGGER tg_lisa_klubi AFTER INSERT ON Klubi
REFERENCING NEW AS uus FOR EACH ROW
WHEN ((SELECT COUNT(*) FROM Asula WHERE Nimi = uus.aadress) = 0)
BEGIN
DECLARE l_id integer;
INSERT INTO Asula (Nimi) VALUES (uus.aadress);
SELECT @@identity INTO l_id;
UPDATE Klubi SET Asula = l_id WHERE Id = uus.id;
END
--13. Luua trigger, mis klubi kustutamisel kontrollib, kas klubi asula on kuskil kasutuses´(teiste klubide juures või turniiride juures), ja kui pole, siis kustutab ka asula maha (nimeks tg_kustuta_klubi).
CREATE TRIGGER tg_kustuta_klubi AFTER DELETE ON Klubi
REFERENCING OLD AS vana FOR EACH ROW
BEGIN
DECLARE l_kasula integer;
DECLARE l_tasula integer;
SELECT COUNT(*) INTO l_kasula FROM Klubi WHERE Asula = vana.Asula;
SELECT COUNT(*) INTO l_tasula FROM Turniir WHERE Asula = vana.Asula;
IF l_kasula + l_tasula = 0 THEN
DELETE FROM Asula WHERE Id = vana.Asula;
END IF;
END
--14. Lisada klubi “Kiire Aju” asukohaga Viljandi.
insert into klubi (nimi, aadress) VALUES ('Kiire Aju', 'Viljandi');
--15. Lisada klubi “ Kambja Kibe” asukohaga Kambja.
INSERT INTO klubi (nimi, aadress) VALUES ('Kambja kibe', 'Kambja');
--16. Teha päring asula tabelisse, et veenduda, mis asulad on olemas.
SELECT nimi FROM asula;
--17. Kustutada klubid maha:
CALL sp_kustuta_klubi('Kiire Aju');
CALL sp_kustuta_klubi('Kambja Kibe');
--18. Teha päring asula tabelisse, et veenduda, mis asulad on olemas.
SELECT nimi FROM asula;
--19. Lisada uus klubi “SQL klubi” asukohaga Tartu.
INSERT INTO Klubi (Nimi, Aadress) VALUES ('SQL klubi', 'Tartu');
--20. Lisada tabelisse Isik iseennast. Klubiks panna “SQL klubi”.
INSERT INTO Isik (Eesnimi, Perenimi, Isikukood , Klubi)
VALUES ('Taavet', 'Tamm', '38806174235',
(SELECT Id FROM Klubi WHERE Nimi = 'SQL klubi'));
--21. Proovida kustutada klubi sp_kustuta_klubi(‘SQL klubi’) - ei tohi õnnestuda (miks?).
--Sest protseduuri reegli järgi ei tohi klubi olla kasutuses.
sp_kustuta_klubi('SQL klubi');
--22. Luua klubi kustutamisele trigger(tg_kustuta_klubi_isikutega), mis kustutaks maha klubi isikud. NB! Kui isikul on partiisid, siis isikut ei õnnestu kustutada ja seega ei õnnenstu ka klubi kustutada. Nii peabki olema!
--Call sp_kustuta_klubi(“Laudnikud”) - ei tohi midagi halba teha (kui kõik seosed on varem õigesti loodud). Aga call sp_kustuta_klubi(“SQLklubi”) peab kustutama nii klubi, kui ka selle ühe liikme.
CREATE TRIGGER tg_kustuta_klubi_isikutega BEFORE DELETE ON Klubi
REFERENCING OLD AS vana FOR EACH ROW
BEGIN
DELETE FROM Isik WHERE Klubi = vana.Id;
END
--23. Parandada andmed tabelis Mina enda omadeks.
update mina set Eesnimi='Taavet', perenimi='Tamm', sugu='M', synnipaev='1988-06-17' WHERE algus='2008-02-21 10:24:22.838';
--24. Luua vaated ülesande 4 päringutele 1 kuni 12. Vaate nimeks panna V_. Näiteks V_1, V_2, … , V_12.
CREATE VIEW V_1 (Eesnimi, Perenimi) AS
SELECT eesnimi, perenimi FROM isik, klubi WHERE klubi.nimi = 'Laudnikud' ORDER BY perenimi asc;
SELECT* FROM V_1;
CREATE VIEW V_2 ("Klubi Laudnikud liikmete arv") AS
SELECT COUNT(*) AS "Klubi Laudnikud liikmete arv" FROM isik WHERE klubi = '51';
SELECT* FROM V_2;
CREATE VIEW V_3 ("V-tähega algavate klubide M-tähega algavate eesnimedega isikute perekonnanimed") AS
SELECT distinct perenimi FROM isik, klubi WHERE klubi.nimi like 'V%' and eesnimi like 'M%' ORDER BY perenimi asc;
SELECT* FROM V_3;
CREATE VIEW V_4 ("Esimesena alanud Partii") AS
SELECT min(Algushetk) FROM partii;
SELECT* FROM V_4;
CREATE VIEW V_5 ("Partii ID", "Isiku nimi", "Isiku ID", "must mängija", "valge mängija") AS
SELECT partii.ID AS "Partii ID", perenimi || ', ' || eesnimi AS "Nimi", isik.ID AS "Isiku ID", must, valge
FROM isik, partii WHERE isik.id in (partii.must,partii.valge) AND
partii.algushetk between '2005-03-04 09:00:00.000' and '2005-03-04 11:00:00.000';
SELECT* FROM V_5;
CREATE VIEW V_6 ("Eesnimi", "Perenimi") AS
SELECT Eesnimi, Perenimi FROM isik, partii WHERE isik.ID = partii.valge AND partii.Valge_tulemus='2' AND
DATEDIFF(minute, partii.Algushetk, partii.Lopphetk) between 9 and 11;
SELECT* FROM V_6;
CREATE VIEW V_7 ("Perenimi") AS
SELECT Perenimi FROM isik group by Perenimi having Count(*)>1 ORDER BY Count(*) desc;
SELECT* FROM V_7;
CREATE VIEW V_8 ("Klubi nimi kus on alla 4 liikme") AS
SELECT Nimi FROM isik, klubi WHERE klubi.id = isik.klubi GROUP BY Nimi HAVING Count(*) SELECT* FROM V_8;
CREATE VIEW V_9 ("Arvode poolt valgetega mängitud partiide arv") AS
SELECT Count(*) FROM isik, partii WHERE isik.ID = partii.valge and isik.eesnimi = 'Arvo';
SELECT* FROM V_9;
CREATE VIEW V_10 ("Arvode poolt valgetega mängitud partiide arv", "Turniir") AS
SELECT Count(*) AS "Arvode poolt mängitud partiide arv", Nimi AS "Turniir" FROM
isik, partii, turniir WHERE isik.ID = partii.valge and isik.eesnimi = 'Arvo'
and turniir.id = partii.turniir Group by nimi;
SELECT* FROM V_10;
CREATE VIEW V_11 ("Mariade mustadega mängitud mängudest saadud punktide arv") AS
SELECT SUM(IF partii.musta_tulemus=2 THEN 1 ELSE IF partii.musta_tulemus = 1 THEN 0.5 else 0 ENDIF ENDIF)AS "Kogu Tulemus"
FROM isik, partii WHERE isik.ID = partii.must AND isik.eesnimi = 'Maria';
SELECT* FROM V_11;
CREATE VIEW V_12 ("turniiri nimi", "Keskmine partii pikkus") AS
SELECT turniir.nimi, AVG(datediff(minute, partii.algushetk, partii.lopphetk)) AS "Keskmine partii pikkus"
FROM turniir, partii WHERE partii.turniir = turniir.id GROUP BY turniir.nimi;
SELECT* FROM V_12;
-- Koosta funktsioon, mis tagastaks kumb kahset klubist saavutas antud turniiril parema tulemuse
drop function f_klubiTulemus;
CREATE FUNCTION f_klubiTulemus (a_klubi1 INTEGER, a_klubi2 INTEGER, a_turniir INTEGER)
RETURNS INTEGER
BEGIN
DECLARE l_esimene_must INTEGER;
DECLARE l_esimene_valge INTEGER;
DECLARE l_esimene_summa INTEGER;
DECLARE l_teine_must INTEGER;
DECLARE l_teine_valge INTEGER;
DECLARE l_teine_summa INTEGER;
DECLARE l_vastus INTEGER;
select sum(partii.musta_tulemus/2.0) INTO l_esimene_must from partii JOIN isik ON partii.must= isik.id, klubi where partii.turniir=a_turniir and isik.klubi=a_klubi1;
select sum(partii.valge_tulemus/2.0) INTO l_esimene_valge from partii JOIN isik ON partii.valge= isik.id, klubi where partii.turniir=a_turniir and isik.klubi=a_klubi1;
select sum(partii.musta_tulemus/2.0) INTO l_teine_must from partii JOIN isik ON partii.must= isik.id, klubi where partii.turniir=a_turniir and isik.klubi=a_klubi2;
select sum(partii.valge_tulemus/2.0) INTO l_teine_valge from partii JOIN isik ON partii.valge= isik.id, klubi where partii.turniir=a_turniir and isik.klubi=a_klubi2;
SET l_esimene_summa= l_esimene_valge + l_esimene_must;
SET l_teine_summa= l_teine_must + l_teine_valge;
set l_vastus = if (l_esimene_summa > l_teine_summa) then a_klubi1 endif;
set l_vastus = if (l_esimene_summa RETURN l_vastus;
END
select f_klubiTulemus(59, 57, 41) as parema_klubi_id;
select count(*) from (select must, valge, musta_tulemus, valge_tulemus from partii where turniir='41')
where
select must, valge, musta_tulemus, valge_tulemus from partii where turniir='41';
select SUM(IF partii.musta_tulemus=2 THEN 1 ELSE IF partii.musta_tulemus = 1 THEN 0.5 else 0 ENDIF ENDIF) as Klubi_tulemus
from partii, isik, klubi, partii, turniir
where partii.turniir = '41' and turniir.id='41' and must=isik.id and isik.klubi='51' and klubi.id='51' group by klubi.id;
select sum(partii.musta_tulemus/2.0) as tulemus from partii JOIN isik ON partii.must= isik.id, klubi where partii.turniir='41' and isik.klubi='59';
select sum(partii.musta_tulemus/2.0) as tulemus, klubi.nimi as klubi from isik, klubi, partii where partii.turniir='41' and partii.must=isik.id and isik.klubi='51' group by klubi.nimi;
union select sum(partii.valge_tulemus/2.0) as tulemus from partii, isik, klubi where valge=isik.id and isik.klubi='51';