Z5-Rešenja

Table of Contents

  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
  18. Primer 18

Primer 1

select *
from predmet
where bodovi >= all ( select bodovi
    from predmet);

Primer 2

select *
from dosije
where not god_rodjenja >= all ( select god_rodjenja
    from dosije);

select *
from dosije
where god_rodjenja < any ( select god_rodjenja
    from dosije);

Primer 3

select distinct current_time from dosije; values current time;
select current_time from sysibm.sysdummy1;

Primer 4

values user;

Primer 5

values dayname(date('18.11.2010')); 
values dayname('18.11.2010');

Primer 6

values (dayofyear(current_date), week(current_date), dayofweek(current_date), dayname(current_date), monthname(current_date));

Primer 7

values second(current_time);

Primer 8

values date('2008-11-11') - date('2005-08-06');

values (mod(date('2008-11-11') - date('2005-08-06'),100), (mod(date('2008-11-11') - date('2005- 08-06'),10000))/100, integer(date('2008-11-11') - date('2005-08-06'))/10000);

values (day(date('2008-11-11') - date('2005-08-06')), month(date('2008-11-11') - date('2005-08- 06')), year(date('2008-11-11') - date('2005-08-06')));

Primer 9

values current date + 12 years + 5 months + 25 days;

Primer 10

select *
from ispit
where datum_ispita>date('2011-01-28');

select *
from ispit
where datum_ispita>'2011-01-28';

Primer 11

select *
from ispit
where current_date-datum_ispita < 800;

Primer 12

select indeks, ime || ' ' || prezime "ime i prezime", substr(ime,1,1) || substr(prezime,1,1) inicijali, replace(mesto_rodjenja, 'Beograd', 'Bg') "mesto rodjenja"
from dosije;

select indeks, ime || ' ' || prezime "ime i prezime", concat(substr(ime,1,1),substr(prezime,1,1)) as inicijali, replace(mesto_rodjenja, 'Beograd', 'Bg') "mesto rodjenja"
from dosije;

Primer 13

select indeks, ime, prezime, coalesce(mesto_rodjenja, 'Nepoznato') as "mesto rodjenja" 
from dosije;

Primer 14

values char(current_time, ISO), char(current_time, USA), char(current_time, LOCAL);

Primer 15

-- a)
select sifra, naziv, decimal(bodovi*1.200, 6, 2) as uvecanje 
from predmet;

-- b)
select sifra, naziv, ceil(bodovi*1.2) as uvecanja 
from predmet
where ceil(bodovi*1.2)>8;

Primer 16

select indeks, naziv, ocena, year(current_date-datum_ispita) godina, month(current_date- datum_ispita) meseci, day(current_date-datum_ispita) dana
from ispit i 
join predmet p on i.id_predmeta=p.id_predmeta
where year(current_date-datum_ispita)<=5 ;

Primer 17

select ime, prezime from dosije d1
where exists ( select *
    from dosije d2
    where d1.god_rodjenja+1=d2.god_rodjenja and d1.mesto_rodjenja=d2.mesto_rodjenja);

select ime, prezime
from dosije d1
where (god_rodjenja, mesto_rodjenja) IN (select god_rodjenja-1, mesto_rodjenja
    from dosije d2);

select d1.ime, d1.prezime
from dosije d1 
join dosije d2 on d1.god_rodjenja=d2.god_rodjenja-1 and d1.mesto_rodjenja=d2.mesto_rodjenja;

Primer 18

select naziv, id_predmeta from predmet p
where not exists (select *
    from dosije d
    where mesto_rodjenja='Beograd' and not exists (select * d.indeks=i.indeks and ocena>5));
        from ispit i
        where p.id_predmeta=i.id_predmeta and