Przykładowe zapytania SQL

Spis treści

1. Złączenie zewnętrzne
2. Operacje mnogościowe
3. UPDATE ze skorelowanym podzapytaniem
4. Aktualizacja po stronie jeden perspektywy, która jest złączeniem
5. Różne sztuczki z aktualizacją perspektyw
6. Drzewo genealogiczne
7. Przodkowie
8. Wykorzystanie pseudozmiennej LEVEL

Złączenie zewnętrzne

• 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;

Operacje mnogościowe

• 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;

UPDATE ze skorelowanym podzapytaniem

• 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
                                );

Aktualizacja po stronie jeden perspektywy, która jest złączeniem

• 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;
/ 

Różne sztuczki z aktualizacją perspektyw

• 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;

Drzewo genealogiczne

• 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;

Przodkowie

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;

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;