Andmebaasid (1)

CREATE TABLE mina ( eesnimi varchar(30) not null, perenimi varchar(100) not null, sugu varchar(5) not null, synnipaev date not null, algus datetime not null default current timestamp, viimati datetime default timestamp, CONSTRAINT pk_mina PRIMARY KEY (eesnimi) ); INSERT INTO mina (eesnimi, perenimi, sugu, synnipaev) VALUES (' Juku ', 'Mets', 'Mees', '1980-02-04'); grant connect to Sandra identified by 'tere'; grant group to dba; grant membership in group dba to Sandra; grant all on mina to Sandra; SELECT * FROM mina; UPDATE mina SET eesnimi = 'J�ri' WHERE eesnimi = 'Juku'; CREATE GLOBAL TEMPORARY TABLE paha (nimi VARCHAR(40) NOT NULL, CONSTRAINT pk_paha PRIMARY KEY (nimi)) ON COMMIT PRESERVE ROWS; INSERT INTO paha (nimi) VALUES ('Mari'); SELECT * FROM paha; create table isik( Id integer not null default autoincrement primary key, Eesnimi varchar (50) not null, Perenimi varchar(50) not null, Isikukood varchar(11), Klubi integer, Unique (eesnimi, perenimi)) create table Klubi( Id integer not null default autoincrement primary key, Nimi varchar(100) not null unique) create table Turniir ( Id integer not null default autoincrement primary key, Nimetus varchar(100) not null unique, Toimumiskoht varchar(100), Alguskuupaev date not null, Loppkuupaev date) create table Partii( Id integer not null default autoincrement primary key, Turniir integer not null, Algushetk datetime not null default current timestamp, Lopphetk datetime, Valge integer not null, Must integer not null, Valge_tulemus smallint check (valge_tulemus in (0,1,2)), Musta_tulemus smallint check (musta_tulemus in (0,1,2)), Kokkuvote varchar(5000)) INPUT INTO Klubi FROM 'C:\ Users \ Rauno \ Documents \AAAndmebaasidekindelkaust\ope\klubi.txt' FORMAT ASCII DELIMITED BY '\x09'; INPUT INTO isik FROM 'C:\Users\Rauno\Documents\AAAndmebaasidekindelkaust\ope\isik.txt' FORMAT ASCII DELIMITED BY '\x09' (id, eesnimi, perenimi, klubi); input into partii from 'C:\Users\Rauno\Documents\AAAndmebaasidekindelkaust\ope\partii.txt' format ascii delimited by '\x09' (Turniir ,algushetk ,lopphetk ,valge,must,valge_tulemus,musta_tulemus); input into turniir FROM 'C:\Users\Rauno\Documents\AAAndmebaasidekindelkaust\ope\turniir.txt' format ascii delimited by '\x09'; ALTER TABLE turniir RENAME nimetus TO nimi; ALTER TABLE klubi ADD aadress varchar(50) NOT NULL DEFAULT 'Tartu'; ALTER TABLE isik ADD CONSTRAINT un_nimi UNIQUE (eesnimi, perenimi); ALTER TABLE isik DROP CONSTRAINT un_nimi; ALTER TABLE isik DROP UNIQUE (eesnimi, perenimi); ALTER TABLE klubi MODIFY aadress varchar(60); ALTER TABLE klubi ADD aadress70 varchar(70) NOT NULL DEFAULT 'Tartu'; UPDATE klubi SET aadress70 = aadress; ALTER TABLE klubi DROP aadress; ALTER TABLE klubi RENAME aadress70 TO aadress; Isik -> Klubi (klubi) id; nimi Partii -> Isik (valge) id; eesnimi, perenimi Partii -> Isik (must) id; eesnimi, perenimi Partii -> Turniir (turniir) id; nimi Alamtabel -> �lemtabel ALTER TABLE isik ADD CONSTRAINT fk_isik_2_klubi FOREIGN KEY (klubi) REFERENCES klubi (id) ON DELETE RESTRICT ON UPDATE CASCADE; alter table partii add constraint fk_partii_2_valge_isik foreign key (valge) references isik(id) on delete restrict on update cascade; alter table partii add constraint fk_partii_2_turniir foreign key (turniir) references turniir(id) on delete cascade on update cascade; alter table partii drop constraint fk_partii_2_Turniir alter table isik add constraint fk_isik_2_Klubi foreign key (klubi) references klubi(id) on delete restrict on update cascade; CREATE VIEW v_klubi54 AS SELECT * FROM isik WHERE klubi = 54; CREATE VIEW v_klubi54pisi (eesnimi, perenimi) AS SELECT eesnimi, perenimi FROM isik WHERE klubi = 54; CREATE VIEW v_mangija (klubi_nimi, klubi_id, isik_nimi, isik_id) AS SELECT klubi.nimi, klubi.id, isik.perenimi || ', ' || isik.eesnimi, isik.id FROM isik JOIN klubi ON isik.klubi = klubi.id; CREATE VIEW v_partii (id, turniir, algus, valge_nimi, valge_klubi, valge_punkt, must_nimi, must_klubi, must_punkt ) AS SELECT p.id, p.turniir, p.algushetk, v.isik_nimi, v.klubi_nimi, p.valge_tulemus / 2.0, m.isik_nimi, m.klubi_nimi, p.musta_tulemus / 2.0 FROM partii as p, v_mangija as v, v_mangija as m WHERE p.valge = v.isik_id AND p.must = m.isik_id; CREATE VIEW v_turniiripartii(turniir_nimi, partii_id, partii_algus, partii_lopp) AS SELECT Turniir.nimi, Partii.id, Partii.Algushetk, Partii.Lopphetk FROM Turniir JOIN Partii ON Turniir.Id=Partii.Turniir; create view v_klubipartiikogus(klubi_nimi, partiisid) as SELECT klubi.nimi, (SELECT COUNT (*) FROM v_partii WHERE valge_klubi=klubi.nimi OR must_klubi=klubi.nimi) FROM klubi; SELECT klubi.nimi, (SELECT COUNT(*) FROM v_partii WHERE valge_klubi=klubi.nimi OR must_klubi=klubi.nimi) FROM klubi; CREATE VIEW v_punkt(partii, turniir, mangija, varv, punkt) AS SELECT Partii.id, Partii.Turniir, Partii.Valge, 'V', Partii.Valge_tulemus/2.0 FROM Partii UNION SELECT Partii.id, Partii.Turniir, Partii.Must, 'M', Partii.Musta_tulemus/2.0 FROM Partii ORDER BY 1 ASC ; CREATE VIEW v_edetabel(mangija, turniir, punkte) AS SELECT v_mangija.isik_nimi, v_punkt.turniir, sum(v_punkt.punkt) FROM v_mangija, v_punkt WHERE v_mangija.isik_id=v_punkt.mangija GROUP BY v_punkt.turniir, v_mangija.isik_nimi ORDER BY 1 ASC ; SELECT v_edetabel.mangija, v_edetabel.punkte FROM v_edetabel WHERE v_edetabel.turniir=41 ORDER BY 2 DESC ; CREATE FUNCTION f_liida ( a_arv1 integer, a_arv2 integer) RETURNS INTEGER DETERMINISTIC BEGIN DECLARE summa INTEGER; SET summa = a_arv1 + a_arv2; RETURN summa; END; CREATE FUNCTION f_eesnimi( a_id integer) RETURNS varchar(50) NOT DETERMINISTIC BEGIN DECLARE d_enimi varchar(50); SELECT eesnimi INTO d_enimi FROM isik WHERE id = a_id; RETURN d_enimi; END; CREATE PROCEDURE sp_uus_klubi(IN a_nimi VARCHAR(100), IN a_aadress VARCHAR(100), OUT a_id INTEGER) BEGIN DECLARE i_id INTEGER; INSERT INTO klubi (nimi, aadress) VALUES (a_nimi, a_aadress); SELECT @@identity INTO i_id; MESSAGE 'Uus klubi: ' || i_id; SET a_id = i_id; END; CREATE VARIABLE uusid INTEGER; CALL sp_uus_klubi('Valga Valge', 'Valga', uusid); SELECT uusid; CREATE PROCEDURE sp_klubimangija ( IN a_klubi_id INTEGER) RESULT ( eesnimi VARCHAR(50), perenimi VARCHAR(50), kuupaev DATE) BEGIN SELECT eesnimi, perenimi, CURRENT DATE FROM isik WHERE klubi = a_klubi_id ORDER BY eesnimi ; END CALL sp_klubimangija(51); CREATE INDEX ix_algus ON partii (algushetk DESC); CREATE INDEX ix_nimi ON isik (perenimi ASC, eesnimi ASC); 1. Luua f-n klubiliikmete arvu leidmiseks klubi id p�hjal f_klubisuurus(...) create function f_klubisuurus(a_id integer) returns integer not deterministic begin declare b_id integer; select count(*) into b_id from isik where klubi = a_id; return b_id; end; select f_klubisuurus(51); 2. Luua f-n ees- ja perenime kokku liitmiseks eesti ametlikul viisil ("perenimi, eesnimi") f_nimi(...) create function f_nimi(e_nimi varchar(20), p_nimi varchar(20)) returns varchar(40) not deterministic BEGIN declare pe_nimi varchar(40); SET pe_nimi = '"'||p_nimi||', '|| e_nimi||'"'; return pe_nimi; end; select f_nimi('eesti', 'maalane'); 3. Luua f-n �he m�ngija partiide koguarv f_mangijakoormus(...) create function f_mangijakoormus(a_id integer) returns integer not deterministic BEGIN declare b_id integer; select count(*) into b_id from Partii where valge = a_id or must = a_id; return b_id; end; select f_mangijakoormus(73); 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 e_nimi varchar(20), in p_nimi varchar(20), in k_number integer, out u_id integer) begin declare i_id integer; insert into isik(Eesnimi,Perenimi,Klubi) values(e_nimi, p_nimi,k_number); select @@identity into i_id; message 'Uus inimene: '|| i_id; set u_id = i_id; end; create variable u_id integer; call sp_uus_isik(' Rein ', 'Ots',51,u_id); select u_id; 5. Luua tabelit v�ljastav protseduur sp_infopump() See peab andma v�lja unioniga 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, siis turniiride oma ja siis alles isikud. Iga grupi sees sorteerida nime j�rgi. alter procedure sp_infopump() result( Nimi varchar(50), Andmed integer) begin (select nimi as Nimi,f_klubisuurus(id), 1 as J�rjestus from klubi) union (select turniir.nimi as Nimi, count(partii.id), 2 as J�rjestus from partii KEY join Turniir group by turniir.nimi) UNION (select f_nimi(isik.eesnimi, isik.perenimi)as Nimetus, f_mangijakoormus(isik.Id), 3 as J�rjestus from isik) order by J�rjestus, Nimi asc end; select nimi,f_klubisuurus(id) from klubi call sp_infopump() 6. Luua tabelit v�ljastav protseduur sp_top10, millel on �ks parameeter - turniiri id, ja mis kasutab vaadet v_edetabel ja annab tulemuseks k�mme parimat etteantud turniiril. alter procedure sp_top10(in a_id integer) result( nimi varchar(50), tulemus double) begin select top 10 v_edetabel.mangija, v_edetabel.punkte from v_edetabel where v_edetabel.turniir = a_id order by v_edetabel.punkte desc; end; call sp_top10(41); 7. Luua indeks turniiride algusaegade peale create index ix_turniir_algus on turniir(Alguskuupaev desc) 8. Luua indeksid partiidele kahanevalt valge ja musta tulemuse peale. create index ix_partii_tulemus on partii (Valge_tulemus desc, musta_tulemus desc) CREATE TRIGGER tg_turniiriaeg1 BEFORE INSERT, UPDATE ON turniir REFERENCING NEW as uus FOR EACH ROW WHEN (uus.loppkuupaev unikaalne M�lemad v� ljad on kohustuslikud create table Asula(id integer not null default autoincrement primary key , nimi varchar(100) not null unique) insert into Asula(nimi) select aadress from klubi where aadress not in ( select nimi from Asula) union select toimumiskoht from turniir where toimumiskoht not in (select nimi from Asula) alter table Klubi add Asula integer; update klubi set asula = (select id from asula where asula.nimi = klubi.aadress) alter table Klubi add constraint fk_klubi_2_asula foreign KEY (Asula) references Asula(id) on update cascade; select klubi.aadress, asula.nimi from klubi join asula on klubi.asula = asula.id; alter table Turniir add Asula Integer; update turniir set asula = (select id from asula where asula.nimi = turniir.toimumiskoht) alter table Turniir add constraint fk_Turniir_2_asula foreign KEY (Asula) references Asula(id) on update cascade; select Turniir.toimumiskoht, asula.nimi from turniir join asula on turniir.asula = asula.id create view v_asulaklubisid(asula_id, asula_nimi, klubisid)AS select Asula.id, Asula.nimi, count(Klubi.asula) from Asula,Klubi where Asula.id = Klubi.Asula group by Asula.id, Asula.nimi ; create view v_asulasuurus(asula_id, asula_nimi, mangijad)AS select asula.id, Asula.nimi, count(isik.klubi) from Asula,Klubi,isik where Asula.id = Klubi.Asula and isik.klubi = klubi.Id group by Asula.id, Asula.nimi; create procedure sp_kustuta_klubi(in a_nimi varchar(100)) begin delete from Klubi where a_nimi = klubi.nimi end; create trigger tg_lisa_klubi after insert, update on Klubi referencing new as uus for each row BEGIN insert into Asula(nimi) select aadress from klubi where aadress not in ( select nimi from Asula); update Klubi set asula = (select id from asula where asula.nimi = klubi.aadress) end; CREATE TRIGGER tg_kustuta_klubi AFTER DELETE, UPDATE ON Klubi BEGIN DELETE FROM Asula WHERE Asula.nimi NOT IN (SELECT Aadress FROM Klubi) AND Asula.nimi NOT IN (SELECT Toimumiskoht FROM Turniir); END; INSERT INTO Klubi (nimi, aadress) VALUES('Kiire Aju', 'Viljandi'); insert into Klubi (nimi, aadress) values ('Kambja Kibe', 'Kambja'); select * from asula call sp_kustuta_klubi('Kiire Aju'); call sp_kustuta_klubi('Kambja Kibe'); select * from asula insert into Klubi(nimi,aadress) values('SQL klubi', 'Tartu') insert into isik(eesnimi, perenimi, klubi) values('Rauno', 'Varul', '63') call sp_kustuta_klubi('SQL klubi') 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 update mina set synnipaev ='1993-05-25' where synnipaev='1980-02-04'; create view v_1(eesnimi,perenimi) as SELECT eesnimi, perenimi from isik, klubi where isik.klubi = klubi.id AND klubi.nimi = 'Laudnikud' order by 1 asc, 2 asc; create view v_2(Laudnikute_liikmete_arv) as SELECT count(*) from isik, klubi where isik.klubi = klubi.id AND nimi = 'Laudnikud'; create view v_3( perenimed ) as Select perenimi from isik,klubi where isik.klubi = klubi.id and left (DBA.Klubi.Nimi, 1)='V' AND left(DBA.Isik.eesnimi, 1) = 'M'; create view v_4(Algushetk) as select min(Algushetk) from partii; alter view v_5(valge,must)as select valge.eesnimi +' ' + valge.perenimi as Valge, must.eesnimi+ ' ' + must.perenimi as Must from partii join isik valge on valge.id = partii.valge join isik must on must.id = partii.must where algushetk between '2005-03-04 9:00:00:000' and '2005-03-04 11:00:00:000'; create view v_6(eesnimi,perenimi) as select eesnimi, perenimi from isik, partii where isik.id = partii.valge and valge_tulemus = 2 and datediff(mi,algushetk,lopphetk) between 9 and 11; create view v_7(perenimi) as select perenimi from isik group by perenimi having count(*)>1; create view v_8(nimi) as select nimi from isik, klubi join isik on isik.klubi = klubi.id group by nimi having count(*) 0 THEN ' SELECT ' || paring || '
' || sql_html_tabeliks('SELECT '||paring) ELSE '' END IF END; CREATE OR REPLACE FUNCTION tabeli_info(tabeli_nimi LONG VARCHAR) RETURNS LONG VARCHAR BEGIN RETURN ' Select table: isik isik Partii Turniie Asula
' || IF LENGTH (tabeli_nimi) > 0 THEN 'Tabeli ' || tabeli_nimi || ' andmed (mitte rohkem kui 100 rida)' || sql_html_tabeliks('select top 100 * from '||tabeli_nimi) ELSE '' END IF; END; CREATE SERVICE tabelid AUTHORIZATION OFF USER dba TYPE 'raw' AS SELECT lehekulje_keha(tabeli_info(:tabeli_nimi)); CREATE SERVICE avaleht AUTHORIZATION OFF USER dba TYPE 'raw' AS SELECT lehekulje_keha('See HTML veebilehek�lg on loodud, et n�idata SQL Anywhere andmebaasi veebiteenuste ja funktsiooni sql_html_tabeliks t��tamist.'); CREATE SERVICE isql AUTHORIZATION OFF USER dba TYPE 'raw' AS SELECT lehekulje_keha(web_isql(:paring));