Z7-Rešenja

1. Zadaci za danas
   1. Primer 1
   2. Primer 2
   3. Primer 3
   4. Primer 4
   5. Primer 5
   6. Primer 6
   7. Primer 7
   8. Primer 8
   9. Primer 9
   10. Primer 10
   11. Primer 11
   12. Primer 12
   13. Primer 13
   14. Primer 14
   15. Primer 15
   16. Primer 16
   17. Primer 17
2. Zadaci za vežbu
   1. Primer 1
   2. Primer 2
   3. Primer 3
   4. Primer 4
   5. Primer 5
   6. Primer 6
   7. Primer 7
   8. Primer 8
   9. Primer 9
   10. Primer 10
   11. Primer 11
   12. Primer 12
   13. Primer 13
   14. Primer 14
   15. Primer 15

Zadaci za danas

Primer 1

-- file: 'primer7-1.sql'
--a
select indeks
from dosije
where mesto_rodjenja='Beograd' union
select indeks
from ispit
where ocena=10
order by indeks desc;

--b
select indeks
from dosije
where mesto_rodjenja='Beograd' intersect
select indeks
from ispit
where ocena=10
order by indeks desc;

--c
select indeks
from dosije
where mesto_rodjenja='Beograd' except
select indeks
from ispit
where ocena=10
order by indeks desc;

Primer 2

-- file: 'primer7-2.sql'
select d.indeks, count(*) br_ispita
from dosije d join ispit i on d.indeks=i.indeks group by d.indeks
union
select indeks, 0
from dosije d
where not exists ( select *
    from ispit i
    where d.indeks=i.indeks);

Primer 3

-- file: 'primer7-3.sql'
select indeks, id_predmeta, ocena, case
    when ocena=10 then 'deset' 
    when ocena=9 then 'devet' 
    when ocena=8 then 'osam' 
    when ocena=7 then 'sedam' 
    when ocena=6 then 'sest' 
    else 'nepolozen'
    end as rezultat_ispita
from ispit;

-- ili
select indeks, id_predmeta, ocena, case ocena
    when 10 then 'deset' 
    when 9 then 'devet' 
    when 8 then 'osam' 
    when 7 then 'sedam' 
    when 6 then 'sest' 
    else 'nepolozen'
    end as rezultat_ispita
from ispit;

Primer 4

-- file: 'primer7-4.sql'
select naziv, case
    when bodovi>7 then 'I kat' 
    when bodovi>4 then 'II kat' 
    else 'III kat'
    end as kategorija 
from predmet;

Primer 5

-- file: 'primer7-5.sql'
select kategorija, count(*) from (select naziv, case
    when bodovi>7 then 'I kat' 
    when bodovi>4 then 'II kat' 
    else 'III kat'
    end as kategorija 
  from predmet) as a
group by kategorija;

-- ili
with predmet_kategorija (predmet, kategorija) as 
  ( select naziv, case
      when bodovi>7 then 'I kat' 
      when bodovi>4 then 'II kat' 
      else 'III kat'
      end as kategorija from predmet)
select kategorija, 
count(*) 
from predmet_kategorija 
group by kategorija;

Primer 6

-- file: 'primer7-6.sql'
with student_bodovi as (
select indeks, sum(bodovi) pol_bodovi
from ispit i join predmet p on i.id_predmeta=p.id_predmeta
where ocena>5
group by indeks)
select count(*) br_studenata 
from student_bodovi
where pol_bodovi>10;

Primer 7

-- file: 'primer7-7.sql'
with student_ocene as (
select indeks, ocena br8, cast(null as smallint) br9 from ispit
where ocena=8
union all
select indeks, cast(null as smallint) br8, ocena br9 
from ispit
where ocena=9)
select indeks, count(br8) broj_8, count(br9) broj_9 
from student_ocene
group by indeks;

-- ili
with student_8 as (
select indeks, count(*) br8
from ispit
where ocena=8
group by indeks
),
student_9 as (
select indeks, count(*) br9
from ispit
where ocena=9
group by indeks
)
select coalesce(s1.indeks, s2.indeks), coalesce(br8,0) broj_8, coalesce(br9, 0) broj_9 
from student_8 s1 
full outer join student_9 s2 on s1.indeks=s2.indeks;

-- ili
select indeks, sum(case when ocena=8 then 1 else 0 end) broj_8, sum(case when ocena=9 then 1 else 0 end) broj_9
from ispit
group by indeks
having sum(case when ocena=8 then 1 else 0 end)>0 or sum(case when ocena=9 then 1 else 0 end)>0;

-- ili 
select indeks, sum(case when ocena=8 then 1 else 0 end) broj_8, sum(case when ocena=9 then 1 else 0 end) broj_9
from ispit
where ocena=8 or ocena=9
group by indeks;

Primer 8

-- file: 'primer7-8.sql'
insert into dosije (indeks, ime, prezime, god_rodjenja, mesto_rodjenja) 
values (20100028, 'Marko', 'Markovic', 1990, 'Kragujevac');

Primer 9

-- file: 'primer7-9.sql'
insert into predmet
values (2005, 'P112', 'Uvod u arhitekturu racunara', 6), (2006, 'P116', 'Razvoj softvera', 6);

Primer 10

-- file: 'primer7-10.sql'
insert into ispit (indeks, id_predmeta, godina_roka, oznaka_roka, ocena) 
select indeks, id_predmeta, 2011, 'jun', 9
from dosije, predmet
where god_rodjenja=1991 and naziv='Programiranje 2';

Primer 11

-- file: 'primer7-11.sql'
delete from dosije
where god_rodjenja=1990;

Primer 12

-- file: 'primer7-12.sql'
delete from ispit
where id_predmeta in ( select id_predmeta
    from predmet
    where naziv='Programiranje 2' or bodovi=15);

Primer 13

-- file: 'primer7-13.sql'
update predmet
set bodovi=bodovi*1.2 
where sifra like 'P%';

Primer 14

-- file: 'primer7-14.sql'
update ispit
set (godina_roka, oznaka_roka, datum_ispita)=(2011, 'jun', null) 
where id_predmeta in (select id_predmeta
    from predmet
    where naziv='Analiza 1') and godina_roka=2011 and oznaka_roka='jan';

Primer 15

-- file: 'primer7-15.sql'
update predmet
set bodovi=(select max(bodovi)
    from predmet) 
where exists ( select *
    from ispit i 
    join dosije d on i.indeks=d.indeks
    where mesto_rodjenja='Beograd' and i.id_predmeta=predmet.id_predmeta);

Primer 16

-- file: 'primer7-16.sql'
select distinct coalesce(ir.naziv, ‘nema odgovarajuceg‘), coalesce(p.naziv, ‘nema odgovarajuceg‘)
from ispitni_rok ir 
left outer join ispit i on ir.godina_roka=i.godina_roka and ir.oznaka_roka=i.oznaka_roka 
full outer join predmet p on p.id_predmeta=i.id_predmeta;

Primer 17

-- file: 'primer7-17.sql'
with pom as (select indeks, count(distinct id_predmeta) br_polozenih, avg(ocena+0.0) prosek
    from ispit i 
    join predmet p on p.id_predmeta=i.id_predmeta 
    where ocena>5
    group by indeks
    having sum(bodovi) between 15 and 25)
select d.indeks, ime, prezime, 
count(distinct id_predmeta) br_polaganih, br_polozenih, prosek
from dosije d 
join pom on d.indeks = pom.indeks 
join ispit i on i.indeks = d.indeks 
group by d.indeks, ime, prezime, br_polozenih,prosek
order by d.indeks

Zadaci za vežbu

Primer 1

Izdvojiti ukupan broj studenata i maksimalni broj indeksa studenta iz tabele dosije.

-- file: 'primer7-v1.sql'
select count(*) ukupno_studenata, max(indeks) maksimalni_indeks 
from dosije

Primer 2

Odrediti ukupan broj studenata kojima je poznata godina rođenja i broj različitih vrednosti za godinu rođenja.

-- file: 'primer7-v2.sql'
select count(god_rodjenja) brSaPoznatomGod, count(distinct god_rodjenja) brRazlicitihGod 
from dosije

Primer 3

Prikazati ukupan broj položenih ispita studenta sa brojem indeksa 22/2010.

-- file: 'primer7-v3.sql'
select count(*) ukupno
from ispit
where indeks = 20100022 and ocena>5

Primer 4

Izdvojiti ukupan broj studenata koji su bar jedan ispit položili sa ocenom 8.

-- file: 'primer7-v4.sql'
select count(distinct indeks) broj_studenata 
from ispit
where ocena = 8

Primer 5

Za svakog studenta izdvojiti broj indeksa i ukupan broj skupljenih bodova.

-- file: 'primer7-v5.sql'
select indeks, sum(bodovi) ukupno
from ispit i 
join predmet p on i.id_predmeta = p.id_predmeta 
where ocena>5
group by indeks

Primer 6

Za svakog studenta koji je skupio barem 20 bodova prikazati ukupan broj skupljenih bodova.

-- file: 'primer7-v6.sql'
select indeks, sum(bodovi) ukupno 
from ispit i 
join predmet p on i.id_predmeta = p.id_predmeta 
where ocena > 5
group by indeks
having sum(bodovi) >= 20

Primer 7

Za svakog studenta izračunati trenutni prosek ocena.

-- file: 'primer7-v7.sql'
select indeks, avg(ocena+0.0) prosek 
from ispit
where ocena>5
group by indeks

Primer 8

Za svaki od ispitnih rokova i za svaki polagan predmet odrediti broj položenih ispita.

-- file: 'primer7-v8.sql'
select oznaka_roka, godina_roka, id_predmeta, count(*) ukupno 
from ispit
where ocena>5
group by oznaka_roka, godina_roka, id_predmeta

Primer 9

Izdvojiti id predmeta koji ili nose više od 6 bodova ili ih je polagao student čiji broj indeksa je 20100024.

-- file: 'primer7-v9.sql'
(select id_predmeta
from predmet where bodovi>6 union
select id_predmeta
from ispit where indeks = 20100024) except
(select id_predmeta
from predmet where bodovi>6 intersect
select id_predmeta
from ispit where indeks = 20100024)

-- Napomena: Ovo je ako se traži da bude ispunjen tačno jedan od uslova a ne i oba istovremeno. Za drugo tumačenje zadatka (bar jedan od uslova ispunjen) rešenje je:
select id_predmeta
from predmet where bodovi>6
union
select id_predmeta
from ispit where indeks = 20100024

Primer 10

Izdvojiti id predmeta koji su polagani i u januaru 2011 i u februaru 2011.

-- file: 'primer7-v10.sql'
select id_predmeta
from ispit
where oznaka_roka=‘jan‘ and godina_roka=2011 intersect
select id_predmeta
from ispit
where oznaka_roka=‘feb‘ and godina_roka=2011;

-- Ili:
select distinct id_predmeta
from ispit
where oznaka_roka=‘jan‘ and godina_roka=2011 and id_predmeta in (select id_predmeta
from ispit
where oznaka_roka=‘feb‘ and godina_roka=2011) 

-- Ili:
select distinct i.id_predmeta
from ispit i
where i.oznaka_roka=‘jan‘ and i.godina_roka=2011 and exists (select * from ispit
where id_predmeta = i.id_predmeta and oznaka_roka=‘feb‘ and godina_roka=2011)

Primer 11

Izdvojiti sve identifikatore za predmete koje položio student sa brojem indeksa 20100021, a nije položio student sa indeksom 20100025, sortirane u opadajućem poretku.

-- file: 'primer7-v11.sql'
select i.id_predmeta
from ispit i
where i.indeks = 20100021 and i.ocena>5 except
select i.id_predmeta
from ispit i
where i.indeks = 20100025 and i.ocena>5 order by id_predmeta

-- Ili:
select i.id_predmeta
from ispit i
where i.indeks = 20100021 and i.ocena>5 and not exists (select *
from ispit
where id_predmeta = i.id_predmeta
and indeks = 20100025 and ocena>5)

-- Ili:
select i.id_predmeta
from ispit i
where i.indeks = 20100021 and i.ocena>5 and i.id_predmeta not in (select id_predmeta from ispit
where indeks = 20100025 and ocena>5)

Primer 12

Izdvojiti brojeve indeksa studenata koji su položili barem 3 ispita i id predmeta koje su položila barem tri studenta. Sve to uradi u jednom upitu i rezultat urediti u opadajućem poretku po broju položenih ispita, odnosno studenata.

-- file: 'primer7-v12.sql'
select indeks id, count(*) ukupno from ispit
where ocena > 5
group by indeks
having count(*)>=3
union
select id_predmeta id, count(*) ukupno from ispit
where ocena > 5
group by id_predmeta
having count(*)>=3
order by 2 desc

Primer 13

Izdvojiti sve informacije za predmete i uz svaki od njih ispisati lak ako predmet nosi manje od 6 bodova, srednje tezak ako nosi 6 ili 7 bodova i tezak ako nosi bar 8 bodova.

-- file: 'primer7-v13.sql'
select p.*, case
when bodovi<6 then ‘lak’
when bodovi between 6 and 7 then ‘srednje tezak’ else ‘tezak’
end as tezina
from predmet p

Primer 14

Izdvojiti sva polaganja predmeta Programiranje 1 i uz svako od njih u zavisnosti od ocena izdvojiti pao ako je ocena 5, provukao se ako je ocena 6, inače polozio.

-- file: 'primer7-v14.sql'
select i.*, case ocena when 5 then 'pao'
when 6 then 'provukao se' else 'polozio'
end as napomena
from ispit i join predmet p
on i.id_predmeta = p.id_predmeta and p.naziv = ’Programiranje 1’

Primer 15

Za svakog studenta koji je položio više od 30 kredita (bodova) i čije prezime ne sadrži slovo o izdvojiti indeks, mesto rođenja, broj predmeta koje je polagao ali pao u koloni “Broj neuspesnih”, broj predmeta koje je položio u koloni “Broj uspesnih” i prosečnu ocenu u koloni “Prosek”. Rezultat urediti prema proseku opadajuce.

-- file: 'primer7-v15.sql'
with Pomocna as(select d.indeks, d.mesto_rodjenja, sum(krediti) ukupnoBodova
from dosije d join ispit i on d.indeks=i.indeks join predmet p on i.id_predmeta=p.id_predmeta where d.prezime not like '%o%' and i.ocena > 5
group by d.indeks, d.mesto_rodjenja
having sum(krediti) > 30)

select P.indeks, P.mesto_rodjenja, sum(case when i.ocena<6 then 1 else 0 end) "Broj neuspelih", sum(case when i.ocena>=6 then 1 else 0 end) "Broj uspesnih",
dec(avg(i.ocena*1.0),4,2) Prosek
from Pomocna P join ispit i on P.indeks=i.indeks group by P.indeks, P.mesto_rodjenja
order by Prosek desc;