Mala Studentska sve, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

create table dosije (

indeks           integer      not null,
       ime              varchar(20)  not null,
       prezime          varchar(25)  not null,
       datum_upisa      date                 ,
       datum_rodjenja   date                 ,
       mesto_rodjenja   varchar(100)         ,
       primary key      (indeks)
);
create table predmet (
       id_predmeta     integer      not null,
       sifra           varchar(5)   not null,
       naziv           varchar(40)  not null,
       bodovi          smallint     not null,
       primary key(id_predmeta)
);
create table ispitni_rok (
       godina_roka     smallint     not null,
       oznaka_roka     varchar(5)   not null,
       naziv           varchar(15)  not null,
       primary key (godina_roka, oznaka_roka)
);
create table ispit (
       indeks          integer      not null                        ,
       id_predmeta     integer      not null                        ,
       godina_roka     smallint     not null                        ,
       oznaka_roka     varchar(5)      not null                        ,
       ocena           smallint     not null                        ,
       datum_ispita    date                                         ,
       bodovi          smallint                                     ,
       primary key (indeks, id_predmeta, godina_roka, oznaka_roka)  ,
       foreign key (godina_roka, oznaka_roka) references ispitni_rok,
       foreign key (indeks)                   references dosije     ,
       foreign key (id_predmeta)              references predmet
);

insert into dosije(indeks,ime,prezime,datum_upisa,datum_rodjenja,mesto_rodjenja) values
(20140021, 'Milos'   , 'Peric'      , '20140706', '19950120', 'Beograd' ),
(20140022, 'Marijana', 'Savkovic'   , '20140705', '19950311', 'Kraljevo'),
(20130023, 'Sanja'   , 'Terzic'     , '20130704', '19941109', 'Beograd' ),
(20130024, 'Nikola'  , 'Vukovic'    , '20130704', '19940917',  null     ),
(20140025, 'Marijana', 'Savkovic'   , '20140706', '19950204', 'Kraljevo'),
(20140026, 'Zorica'  , 'Miladinovic', '20140706', '19951008', 'Vranje'  ),
(20130027, 'Milena'  , 'Stankovic'  , null,  null, null     );

insert into predmet values
(1001, 'M111', 'Analiza 1', 6)                          ,
(1002, 'M112', 'Analiza 2', 6)                          ,
(1003, 'M113', 'Analiza 3', 6)                          ,
(1021, 'M131', 'Geometrija', 6)                         ,
(1101, 'M105', 'Diskretne strukture 1', 6)              ,
(1102, 'M106', 'Diskretne strukture 2', 6)              ,
(2001, 'P101', 'Programiranje 1', 8)                    ,
(2002, 'P102', 'Programiranje 2', 8)                    ,
(2003, 'P103', 'Objektno orijentisano programiranje', 6),
(2004, 'P104', 'Algoritmi i strukture podataka', 6)     ,
(3001, 'S1'  , 'Engleski jezik 1', 5)                   ,
(3002, 'S2'  , 'Engleski jezik 2', 5)                   ,
(4001, 'R101', 'Uvod u organizaciju racunara', 5)       ,
(4002, 'R102', 'Uvod u Veb i Internet tehnologije', 5)  ;

insert into ispitni_rok values
(2015, 'jan', 'Januar 2015')   ,
(2015, 'feb', 'Februar 2015')  ,
(2015, 'apr', 'April 2015')    ,
(2015, 'jun', 'Jun 2015')      ,
(2015, 'sep', 'Septembar 2015'),
(2015, 'okt', 'Oktobar 2015')  ;

insert into ispit(indeks, id_predmeta, godina_roka, oznaka_roka, ocena, datum_ispita, bodovi)  values 
(20140021, 1001, 2015, 'jan', 9, '20150120', 81),
(20140022, 1001, 2015, 'jan', 8, '20150120', 75),
(20130023, 1001, 2015, 'jan', 8, '20150120', 76),
(20130024, 1001, 2015, 'jan', 10,'20150120', 100),
(20140025, 1001, 2015, 'jan', 6, '20150120', 55),
(20140026, 1001, 2015, 'jan', 5, '20150120', 25),
(20130027, 1001, 2015, 'jan', 8, '20150120', 76),

(20140021, 2001, 2015, 'jan', 10,'20150120', 98),
(20140022, 2001, 2015, 'jan', 9, '20150120', 86),
(20130023, 2001, 2015, 'jan', 8, '20150120', 72),
(20130024, 2001, 2015, 'jan', 7, '20150120', 63),
(20140025, 2001, 2015, 'jan', 5, '20150120', 0),

(20140021, 3001, 2015, 'jan', 7, '20150127', 67),
(20130023, 3001, 2015, 'jan', 5, '20150127', 30),
(20130024, 3001, 2015, 'jan', 6, '20150128', 59),
(20140026, 3001, 2015, 'jan', 6, '20150128', 52),

(20140026, 1001, 2015, 'feb', 7, '20150210', 68),

(20140025, 2001, 2015, 'feb', 6, '20150210', 55),
(20140026, 2001, 2015, 'feb', 7, '20150210', 65),

(20140021, 1021, 2015, 'apr', 7, '20150403', 63),
(20130023, 1021, 2015, 'apr', 10,'20150403', 95),
(20140021, 1002, 2015, 'okt', 5,'20150403', 35);

insert into ispit(indeks, id_predmeta, godina_roka, oznaka_roka, ocena, datum_ispita)  values 
(20140022, 1021, 2015, 'apr',  5,  '20150403');

insert into ispit(indeks, id_predmeta, godina_roka, oznaka_roka, ocena)  values 
(20130024, 1021, 2015, 'apr', 6)              ,
(20140026, 1021, 2015, 'jan', 7)              ,
(20140026, 1021, 2015, 'feb', 7)              ,
(20140026, 2001, 2015, 'jan', 7)             ,
(20140026, 1021, 2015, 'apr', 8)              ,
(20130027, 1021, 2015, 'jan', 7)              ,
(20130027, 1021, 2015, 'feb', 7)              ,
(20130027, 1021, 2015, 'apr', 8)              ;

/*with pomocna as (
    select indeks,sum(p.bodovi) polozeno
    from ispit i join predmet p on i.id_predmeta=p.id_predmeta
    where ocena>5
    group by indeks
    )
select count(*) broj_studenata
from pomocna
where polozeno>20

with irpomocna as (
    select godina_roka,oznaka_roka,count(distinct id_predmeta) broj
    from ispit
    where ocena>5
    group by godina_roka,oznaka_roka
    )
select count(*)
from irpomocna
where broj>1

select p.id_predmeta, count(distinct indeks)
from predmet p left join ispit i on i.id_predmeta=p.id_predmeta
group by p.id_predmeta
select id_predmeta,count(distinct indeks) broj_studenata
from ispit
group by id_predmeta
union
select id_predmeta, 0 broj_studenata
from predmet
where id_predmeta not in (select id_predmeta
                         from ispit)
with polagani as(
    select id_predmeta, count(distinct indeks) broj_studenata
    from ispit
    group by id_predmeta
    )                         
select p1.id_predmeta, case
                        when p1.id_predmeta in (select id_predmeta
                                            from polagani)
                        then (select broj_studenata
                                       from polagani p
                                        where p.id_predmeta=p1.id_predmeta)
                        else 0
                            end broj_studenata           
from predmet p1

with pomocna as(
select d.indeks,ime, prezime, count(distinct id_predmeta) broj_predmeta
from dosije d left join ispit i on d.indeks=i.indeks
where ocena=5
group by d.indeks,ime,prezime
union 
select d.indeks,ime,prezime,0 broj_predmeta
from dosije d
where 0 = (select count(distinct id_predmeta)
           from ispit i
           where d.indeks=i.indeks and ocena=5
          ) or d.indeks not in (select indeks from predmet)
)
select ime,prezime,broj_predmeta
from pomocna*/
/*
with pom1 as(
    select indeks, godina_roka,oznaka_roka
    from ispit
    where ocena>5
    )
select *
from pom1
    */

View schema