Przykładowe zapytania SQL
Spis treści
- Złączenie zewnętrzne.
- Operacje mnogościowe.
- UPDATE ze skorelowanym podzapytaniem.
- Aktualizacja po stronie jeden perspektywy, która jest złączeniem.
- Różne sztuczki z aktualizacją perspektyw.
- Drzewo genealogiczne.
- Przodkowie.
- Wykorzystanie pseudozmiennej
LEVEL
.
- Baza danych: Las.
- Znaleźć: ilość mięsa, jakie zjada każdy z gatunków
(uwzględnić także tych co żadnego miesa nie jedzą).
- Skrypt: outerjoin.sql
select drapieznik.nazwa, nvl(sum(jak_duzo*ofiara.wielkosc),0) "KG MIESA"
from gatunki drapieznik, pozeranie, gatunki ofiara
where drapieznik.nr = kto (+)
and kogo = ofiara.nr (+)
group by drapieznik.nazwa
order by 2 desc;
- Baza danych: zdefiniowana lokalnie.
- Zrozumieć:semnatykę operacji UNION, MINUS i INTERSECT na zbiorach
i wielozbiorach.
- Skrypt: zbiory.sql
create table A (
a1 integer
);
create table B (
b1 integer
);
insert into A values (1);
insert into A values (1);
insert into A values (2);
insert into A values (3);
insert into A values (3);
insert into A values (5);
insert into B values (1);
insert into B values (1);
insert into B values (1);
insert into B values (2);
insert into B values (2);
insert into B values (3);
insert into B values (4);
select * from A union select * from B;
select * from A minus select * from B;
select * from A intersect select * from B;
select * from A union all select * from B;
select * from A minus all select * from B;
select * from A intersect all select * from B;
- Baza danych: Las.
- Ustalić w bazie: rozrodczość i wielkość każdego gatunku na
średnią z rozrodczości i wielkości wszystkich gatunków o mniejszych numerach.
- Skrypt: updskorel.sql
update gatunki gat
set (rozrodczosc, wielkosc) = (select avg(rozrodczosc), avg(wielkosc)
from gatunki
where nr <= gat.nr
);
- Baza danych: Las.
- Wykorzystać: Wyzwalacze INSTEAD OF.
- Skrypt: joinupd.sql
create view pasniki_info as
select pasniki.nr, karma, pasniki.wielkosc, nr_lasu,
lasy.nazwa nazwa_lasu, lasy.wielkosc wielkosc_lasu, rodzaj rodzaj_lasu
from lasy, pasniki
where lasy.nr = nr_lasu;
insert into pasniki_info (nr, karma, wielkosc, nr_lasu)
values (343254, 'siano', 23, 101);
create or replace trigger wielkoscLasuWPasniki_Info
instead of update on pasniki_info
for each row
begin
if (:old.wielkosc_lasu <> :new.wielkosc_lasu) then
update lasy
set wielkosc = :new.wielkosc_lasu
where nr = :new.nr_lasu;
end if;
end;
/
- Baza danych:zdefiniowana lokalnie.
- Skrypt: viewupd.sql.
create table Pamietnik (
nr number(3) primary key,
podpis varchar(30) default user not null,
tresc varchar(28) not null,
data_wpisu date default sysdate not null
);
grant select on Pamietnik to public;
create or replace view PamietnikDoWpisow as
select nr, tresc
from pamietnik;
grant insert, update, delete, select on PamietnikDoWpisow to public;
create or replace view PamietnikCoPokazujeDodatnie as
select nr, tresc
from pamietnik
where nr > 0;
grant insert, update, delete, select on PamietnikCoPokazujeDodatnie to public;
create or replace view PamietnikDoWpisowDodatnich as
select nr, tresc
from pamietnik
where nr > 0
with check option constraint tylkoDodatnie;
grant insert, update, delete, select on PamietnikDoWpisowDodatnich to public;
create or replace view TylkoMojeWpisy as
select nr, tresc
from pamietnik
where podpis = user;
grant insert, update, delete, select on TylkoMojeWpisy to public;
- Baza danych: Ewidencja ludności.
- Znaleźć:drzewo genealogiczne zaczynające się od osób, które
w bazie nie mają ani ojca ani matki.
- Skrypt: downtree.sql
select level, id, imie, nazwisko, id_ojca, id_matki
from osoba
start with id_ojca is null
and id_matki is null
connect by prior id = id_ojca
or prior id = id_matki;
select level, id, imie, nazwisko, id_ojca, id_matki
from osoba
start with imie = 'Krzysztof'
and nazwisko = 'Stencel'
connect by prior id_ojca = id
or prior id_matki = id;
- Baza danych: Las.
- Znaleźć:zwierzę znajdujące się na wierzchołku najdłuższego
łańcucha pokarmowego.
- Skrypt: hierarchy.sql
select kto
from pozeranie
where level = (select max(level)
from pozeranie
connect by prior kogo = kto
)
start with kogo in (select kogo
from pozeranie
where level = (select max(level)
from pozeranie
connect by prior kogo = kto
)
connect by prior kogo = kto
)
connect by prior kto = kogo;