SQL / krvná skupina

Vytvorenie tabuliek v SQL

CREATE TABLE krvna_skupina
(id_ks NUMBER NOT NULL,
oznacenie CHAR(2) NOT NULL,
faktor CHAR(10),
PRIMARY KEY (id_ks)
);

CREATE TABLE darca
(id_darcu NUMBER NOT NULL,
id_ks NUMBER NOT NULL,
dat_nar DATE NOT NULL,
meno CHAR(10) NOT NULL,
priezvisko CHAR(15) NOT NULL,
ulica CHAR(12),
cislo NUMBER,
mesto CHAR(12) NOT NULL,
psc NUMBER NOT NULL,
telefon NUMBER
PRIMARY KEY (id_darcu),
FOREIGN KEY (id_ks) REFERENCES krvna_skupina(id_ks)
);

CREATE TABLE stredisko
(id_strediska NUMBER NOT NULL,
nazov CHAR(15) NOT NULL,
s_ulica CHAR(12) NOT NULL,
s_cislo NUMBER NOT NULL,
s_mesto CHAR(12) NOT NULL,
s_psc NUMBER NOT NULL,
PRIMARY KEY (id_strediska)
);

CREATE TABLE odber
(id_odberu NUMBER NOT NULL,
id_darcu NUMBER NOT NULL,
id_strediska NUMBER NOT NULL,
datum_odberu DATE,
objem_krvi NUMBER NOT NULL,
PRIMARY KEY (id_odberu, id_darcu),
FOREIGN KEY (id_strediska) REFERENCES stredisko(id_strediska)
);







7 Naplnenie tabuliek

INSERT INTO darca VALUES
('1','Jana',� 39;Suchá','01.10.1981' ,'Lorencova', '5','Bratislava' ,'53264','904123568&# 039;,'1');
INSERT INTO darca VALUES
('2','Peter',&# 039;Macko','05.02.1978' ;,'Ružova','6', 'Bratislava','85219� 39;,'903125845','3� 39;);
INSERT INTO darca VALUES
('3','Alica',&# 039;Malá','04.03.1979' ,'Bulíková','10' ,'Bratislava','42563& #039;,'905147856','4& #039;);
INSERT INTO darca VALUES
('4','Linda',&# 039;Bacová','07.07.1982 9;,'Hlavná','4', 'Košice','35698' ,'905653245','2' );
INSERT INTO darca VALUES
('5','Diana',&# 039;Mikulová','08.02.1977&# 039;,'Biela','8' ,'Poprad','54789' ;,'905189540','5' ;);
INSERT INTO darca VALUES
('6','Matúš',&# 039;Wolf','06.09.1980' ,'Maurerova','13' ;,'Bratislava','54896 ','903568947','6 ');
INSERT INTO darca VALUES
('7','Erik',� 39;Tkác','08.08.1978', 'Cintorínska','2' ;,'Košice','54789 9;,'905876322','7 9;);
INSERT INTO darca VALUES
('8','Petra',&# 039;Jánošová','03.05.1982&# 039;,'Bulíková','6� 39;,'Bratislava','4325 6','904569321',' 4');
INSERT INTO darca VALUES
('9','Jana',� 39;Novotná','03.02.1979 9;,'Hlavná','NULL 9;,'Nitra','45893 9;,'902547896','1 9;);
INSERT INTO darca VALUES
('10','Peter',& #039;Jánoš','07.07.1981 9;,'NULL','6',& #039;Bratislava','43256 9;,'903546877','1 9;);

INSERT INTO krvna_skupina VALUES
('1','A',' pozitiv');
INSERT INTO krvna_skupina VALUES
('2','B',' pozitiv');
INSERT INTO krvna_skupina VALUES
('3','AB',' ;negativ');
INSERT INTO krvna_skupina VALUES
('4','0',' NULL');
INSERT INTO krvna_skupina VALUES
('5','A',' negativ');
INSERT INTO krvna_skupina VALUES
('6','B',' neagtiv');
INSERT INTO krvna_skupina VALUES
('7','AB',' ;pozitiv');

INSERT INTO stredisko VALUES
('1','SDA', 9;Hlavná','5','B ratislava','54689');
INSERT INTO stredisko VALUES
('2','JSK', 9;Biela','8','Br atislava','45451');
INSERT INTO stredisko VALUES
('3','KSL', 9;Hlavná','12',' Bratislava','45466');
INSERT INTO stredisko VALUES
('4','RUR', 9;Lorencova','22',� 39;Košice','78923');
INSERT INTO stredisko VALUES
('5','LKJ', 9;Šustekova','4', 9;Košice','63254');
INSERT INTO stredisko VALUES
('6','MNI', 9;Ružova','10',' Nitra','12547);

INSERT INTO odber VALUES
('1','2',' 2','01.03.2003',' ;1.5');
INSERT INTO odber VALUES
('2','2',' 2','05.03.2004',' ;3');
INSERT INTO odber VALUES
('3','1',' 3','28.02.2004',' ;2.5');
INSERT INTO odber VALUES
('4','7',' 5','15.03.2004',' ;3.2');
INSERT INTO odber VALUES
('5','10',' ;1','15.03.2004', 9;3');

INSERT INTO odber VALUES
('6','5',' 3','19.02.2003',' ;2.5');
INSERT INTO odber VALUES
('7','3',' 4','15.03.2004',' ;1.8');
INSERT INTO odber VALUES
('8','2',' 4','19.03.2003',' ;3');
INSERT INTO odber VALUES
('9','1',' 5','14.05.2003',' ;3');
INSERT INTO odber VALUES
('10','8',' ;3','13.03.2004', 9;1.5');
INSERT INTO odber VALUES
('11','9',' ;6','12.02.2004', 9;1.8');
INSERT INTO odber VALUES
('12','4',' ;1','02.05.2003', 9;1.4');
INSERT INTO odber VALUES
('13','6',' ;6','01.08.2004', 9;2.3');




8 Zadania dotazov

1. Vypíš krvnú skupinu a faktor, ktorej id je 5.
2. Vypíš priezvisko darcov, ktorí sú z Bratislavy a majú krvnú skupinu A.
3. Vypíš meno, priezvisko a telefónne císlo tých, ktorí nezadali úplnú adresu.
4. Vypíš pocet darcov, ktorí sa narodili pred 1.1.1980
5. Vypíš ku všetkým darcom ich krvnú skupinu a faktor.
6. Vypíš celkové množstvo odobratej krvi.
7. Vypíš kolko krát boli jednotliví darcovia darovat krv.
8. Vypíš mená a priezviská darcov, ktorí darovali krv v marci 2004.
9. Vypíš celkový pocet odberov za rok 2004.
10. Vypíš názov strediska a pocet odberov na nom vykonaných (pre všetky strediská).
11. Ktorú skupinu má najviac darcov?
12. Kolko krvi daroval každý darca celkovo?
13. Vypíš iniciály krvnej skupiny, meno a priezvisko darcov, ktorí na jeden raz dali viac ako 2 dcl krvi.
14. Kolko je všetkých darcov spolu?
15. Aké bolo priemerné množstvo odbratej krvi?
16. Vypíš mená a priezviská darcov, ktorí darovali krv v Bratislave.
17. Vypíš pocty odberov na jednotlivých strediskách, v ktorých bolo odobratých viac ako 3 dcl krvi.
18. Akú krvnú skupinu má darca Wolf?













9 Riešenia dotazov v relacnej algebre

1. V1 s id_ks=5 krvna_skupina
V2 p oznacenie, faktor V1

2. V1 krvna_skupina × id_ks=id_ks darca
V2 s mesto=’Bratislava’ AND oznacenie=‘A‘ V1
V3 p meno, priezvisko V2

3. V1 s ulica=‘NULL‘ OR cislo=‘NULL‘ darca
V2 p meno, priezvisko V1

4. V1 s dat_nar<1.1.1980 darca
V2 (pocet_darcov_nad_23) g COUNT(*) V1

5. V1 krvna_skupina × * darca
V2 p meno, priezvisko, oznacenie, faktor V1

6. V1 (celkove_mnozstvo) g SUM(objem_krvi) odber

7. V1 (id_darcu,_pocet odberov) (id_darcu) g COUNT(*) odber
V2 V1 × id_darcu=id_darcu darca
V3 p meno, priezvisko, pocet_odberov V2

8. V1 s datum_odberu>’28.2.2004’ AND datum_odberu<‘1.4.2004‘ odber
V2 V1 × id_darcu=id_darcu darca
V3 p meno, priezvisko, datum_odberu V2

9. V1 s datum_odberu>’31.12.2003’ AND datum_odberu<‘1.1.2005‘ odber
V2 (pocet_odberov_za_2004) g COUNT(*) V1

10. V1 stredisko × id_strediska=id_strediska odber
V2 (nazov, pocet odberov) id_strediska g COUNT(*) V1

11. V1 (id_ks, pocet_darcov) id_ks g COUNT(*) darca
V2 (max) g MAX(pocet_darcov) V1
V3 V1 × id_ks=max V2
V4 V3 × id_ks=id_ks krvna_skupina
V5 p oznacenie, faktor V4

12. V1 (id_darcu, objem_krvi) id_darcu g SUM(objem_krvi) odber
V2 V1 × id_darcu=id_darcu darca
V3 p meno, priezvisko, objem_krvi V2

13. V1 s objem_krvi>2 odber
V2 V1 × id_darcu=id_darcu darca
V3 V2 × id_ks=id_ks krvna_skupina
V4 p meno, priezvisko, oznacenie, faktor V3

14. V1 (pocet_darcov) g COUNT(*) darca

15. V1 (priem_odber) g AVG(objem_krvi) odber

16. V1 stredisko × id_strediska=id_strediska odber
V2 V1 × id_darcu=id_darcu darca
V3 s s_mesto=‘Bratislava‘ V2
V4 p meno, priezvisko V3

17. V1 stredisko × id_strediska=id_strediska odber
V2 s objem krvi>3 V1
V3 (nazov, pocet_odberov) id_strediska g COUNT(*) V2

18. V1 krvna_skupina × id_ks=id_ks darca
V2 s priezvisko=‘Wolf‘ V1
V3 p oznacenie, faktor V2





10 Riešenia dotazov v SQL

1. select oznacenie, faktor
from krvna_skupina
where id_ks=5

2. select d.priezvisko
from darca d, krv_skupina k
where d.id_ks=k.id_ks
and mesto='Bratislava'
and oznacenie='A'

3. select meno, priezvisko, telefon
from darca
where ulica is NULL
or cislo is NULL

4. select count(*) as pocet_darcov
from darca
where dat_nar<'01-01-1980'

5. select d.meno, d.priezvisko, k.oznacenie, k.faktor
from darca d, krvna_skupina k
where d.id_ks=k.id_ks

6. select sum(objem_krvi) as celk_mnoz
from odber

7. select d.meno, d.priezvisko, count(*) as pocet_odberov
from darca d, odber o
where d.id_darcu=o.id_darcu
group by o.id_darcu, d.meno, d.priezvisko

8. select d.meno, d.priezvisko, o.datum_odberu
from darca d, odber o
where d.id_darcu=o.id_darcu
and o.datum_odberu<'01-04-2004 9;
and o.datum_odberu>'28-02-2004 9;

9. select count(*) as pocet_odberov_za_2004
from odber o
where o.datum_odberu<'01-01-2005 9;
and o.datum_odberu>'31-12-2003 9;

10. select s.nazov, count(*) as odbery
from odber o, stredisko s
where o.id_strediska=s.id_strediska
group by s.nazov, o.id_strediska

11. select k.oznacenie, k.faktor, count(*) as pocet_darcov
from krvna_skupina k, darca d
where k.id_ks=d.id_ks
group by k.oznacenie, k.faktor
having count(*)=(select max(count(*))
from krvna_skupina k, darca d
where k.id_ks=d.id_ks
group by k.oznacenie, k.faktor)

12. select o.id_darcu, d.meno, d.priezvisko, sum(objem_krvi)
from darca d, odber o
where d.id_darcu=o.id_darcu
group by o.id_darcu, d.meno, d.priezvisko

13. select d.meno, d.priezvisko, k.oznacenie, k.faktor
from krvna_skupina k, darca d, odber o
where k.id_ks=d.id_ks
and d.id_darcu=o.id_darcu
and objem_krvi>2

14. select count(*) as celk_poc_darcov
from darca

15. select avg(o.objem_krvi) as priem_odber
from odber o

16. select d.meno, d. priezvisko
from darca d, stredisko s, odber o
where d.id_darcu=o.id_darcu
and s.id_strediska=o.id_strediska
and s.s_mesto='Bratislava'

17. select s.nazov, s.s_mesto, count(*) as odber_viac_ako_3
from stredisko s, odber o
where s.id_strediska=o.id_strediska
and o.objem_krvi>3
group by s.nazov, s.s_mesto, o.id_strediska

18. select d.meno, d.priezvisko, k.oznacenie, k.faktor
from darca d, krvna_skupina k
where d.id_ks=k.id_ks
and d.priezvisko='Wolf'