Table of Contents
Primer 1 Primer 2 Primer 3 Primer 4 Primer 5 Primer 6 Primer 7 Primer 8 Primer 9 Primer 10
Primer 1
1
2
3
4
5
select ime , count ( * ) n
from dosije
where pol = 'm'
group by ime
order by n ;
Primer 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select i . indeks , rtrim ( d . ime ) || ' ' || rtrim ( d . prezime ), sum ( p . bodovi ) polozeno
from dosije d
join upisan_kurs uk
on d . indeks = uk . indeks
join kurs k
on k . id_predmeta = uk . id_predmeta and k . godina = uk . godina
and k . semestar = uk . semestar
join predmet p
on k . id_predmeta = p . id_predmeta
join ispit i
on uk . indeks = i . indeks
and uk . id_predmeta = i . id_predmeta and uk . godina = i . godina
and uk . semestar = i . semestar
where i . status_prijave = 'o' and i . ocena > 5
group by i . indeks , rtrim ( d . ime ) || ' ' || rtrim ( d . prezime )
order by 3 ;
Primer 3
1
2
3
4
select indeks , max ( coalesce ( datum_usmenog , datum_pismenog )) poslednji
from ispit
where ocena > 5 and status_prijave = 'o'
group by indeks ;
Primer 4
1
2
3
4
5
6
7
8
9
select s . naziv smer , p1 . naziv obavezan_predmet , p2 . naziv uslovni_predmet
from nivo_kvalifikacije nk
join smer s on s . id_nivoa = nk . id
join obavezan_predmet op on op . id_smera = s . id_smera
join predmet p1 on p1 . id_predmeta = op . id_predmeta
left outer join uslovni_predmet up on up . id_predmeta = op . id_predmeta
left outer join predmet p2 on p2 . id_predmeta = up . id_uslovnog
where nk . stepen = 'VI'
order by smer , obavezan_predmet ;
Primer 5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
with indeksbodova as ( select indeks , sum ( p . bodovi ) polbodovi
from ispit i
join predmet p on i . id_predmeta = p . id_predmeta
where ocena > 5 and status_prijave = 'o'
group by indeks ), maxsmer as ( select id_smera , max ( polbodovi ) maxb
from dosije d
join indeksbodova on d . indeks = indeksbodova . indeks
group by id_smera )
select s . naziv , d . indeks , ime , prezime , polbodovi
from dosije d
join indeksbodova on d . indeks = indeksbodova . indeks
join maxsmer on maxsmer . id_smera = d . id_smera
join smer s on maxsmer . id_smera = s . id_smera
where polbodovi = maxb ;
Primer 6
1
2
3
4
5
6
7
8
9
select d . indeks , d . ime , d . prezime , p . naziv
from dosije d
join smer s on s . id_smera = d . id_smera
join nivo_kvalifikacije nk on nk . id = s . id_nivoa
join obavezan_predmet op on d . id_smera = op . id_smera
join predmet p on p . id_predmeta = op . id_predmeta
where s . naziv = 'Informatika' and nk . stepen = 'VI' and d . indeks between 20070000 and 20079999 and not exists ( select *
from ispit i
where i . indeks = d . indeks and i . id_predmeta = op . id_predmeta and ocena > 5 and status_prijave = 'o' );
Primer 7
1
2
3
4
5
6
7
8
9
10
11
12
13
with nepolozeni as ( select d . indeks , d . ime , d . prezime , p . id_predmeta , p . naziv
from dosije d
join obavezan_predmet op on d . id_smera = op . id_smera
join predmet p on p . id_predmeta = op . id_predmeta
where d . id_smera = 201 and d . indeks / 10000 = 2007 and not exists ( select *
from ispit i
where i . indeks = d . indeks and i . id_predmeta = op . id_predmeta and ocena > 5 and status_prijave = 'o' ))
select nepolozeni . ime , nepolozeni . prezime , nepolozeni . naziv predmet , p . naziv
from nepolozeni
join uslovni_predmet up on nepolozeni . id_predmeta = up . id_predmeta
join predmet p on p . id_predmeta = up . id_uslovnog
where up . id_uslovnog not in ( select id_predmeta from ispit i
where indeks = nepolozeni . indeks and ocena > 5 and status_prijave = 'o' )
Primer 8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
with a as ( select id_nivoa , id_predmeta , count ( * ) n
from smer s
join obavezan_predmet op on s . id_smera = op . id_smera
group by id_nivoa , id_predmeta ),
b as ( select id_nivoa , count ( * ) n
from smer
group by id_nivoa )
select nk . naziv , p . naziv
from a join b on a . id_nivoa = b . id_nivoa
join nivo_kvalifikacije nk on nk . id = a . id_nivoa
join predmet p on a . id_predmeta = p . id_predmeta where a . n = b . n ;
--ili
select distinct kv . naziv , p . naziv
from obavezan_predmet op join smer s1
on op . id_smera = s1 . id join nivo_kvalifikacije kv
on kv . id = s1 . id_nivoa join predmet p
on op . id_predmeta = p . id_predmeta
where not exists ( select *
from smer s
where s . id_nivoa = s1 . id_nivoa
and not exists ( select *
from obavezan_predmet p1
where op . id_predmeta = p1 . id_predmeta and p1 . id_smera = s . id_smera ));
Primer 9
1
2
3
4
5
6
7
8
with a as ( select id_predmeta id , sum ( case when ocena > 5 then 1 else 0 end ) brP , count ( distinct indeks ) uk
from ispit
where status_prijave = 'o'
group by id_predmeta )
select p . naziv , a . brP * 100 . 0 / a . uk prolaznost
from predmet p
join a on p . id_predmeta = a . id
order by prolaznost desc ;
Primer 10
1
2
3
4
5
select s . naziv , d1 . dat_rodjenja , d1 . dat_rodjenja , d1 . indeks , d1 . ime , d1 . prezime , d2 . indeks , d2 . ime , d2 . prezime
from dosije d1
join smer s on d1 . id_smera = s . id_smera
join dosije d2 on d1 . id_smera = d2 . id_smera and d1 . dat_rodjenja = d2 . dat_rodjenja
where d1 . indeks < d2 . indeks ;
