Scribd
Upload
28 views2 pages

Fakultet

This document contains 7 SQL queries: 1. Selects the names of students who enrolled 2 years ago and have a budget status, ordered by student ID descending. 2. Selects study programs from a specific city that have a study duration of more than 8 semesters. 3. Selects student IDs and study programs for students enrolled more than 2 years ago. 4. Selects courses and number of students who passed each course with an average grade below 70. 5. Selects faculties, study levels, and number of budget students for programs from faculties founded after 1965. 6. Selects students with the minimum average grade. 7. Selects faculties, study programs

Uploaded by

mahlon.jony
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
28 views2 pages

Fakultet

This document contains 7 SQL queries: 1. Selects the names of students who enrolled 2 years ago and have a budget status, ordered by student ID descending. 2. Selects study programs from a specific city that have a study duration of more than 8 semesters. 3. Selects student IDs and study programs for students enrolled more than 2 years ago. 4. Selects courses and number of students who passed each course with an average grade below 70. 5. Selects faculties, study levels, and number of budget students for programs from faculties founded after 1965. 6. Selects students with the minimum average grade. 7. Selects faculties, study programs

Uploaded by

mahlon.jony
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

-- 1.

select ime || ' ' || prezime


from student
where extract(year from sysdate) - 2 = extract(year from datumupisa)
and status = 'budzet'
order by brojindeksa desc;

-- 2.
select *
from smer
where fakultetid in (select fakultetid
from fakultet
where grad = 'Novi Sad')
and fakultetid in (select fakultetid
from smer
where brojsemestara > 8);

-- 3.
select s.brojindeksa, sm.nazivsmera
from student s join smer sm on (s.smerid = sm.smerid)
join fakultet f on (s.fakultetid = f.fakultetid and sm.fakultetid = f.fakultetid)
where s.datumupisa + interval '2' year < '10-JUL-2019';

-- 4.
select p.predmetid, sum(case when po.ocena > 5 then 1 else 0 end) as polozilo
from predmet p join polaganje po on (p.predmetid = po.predmetid)
group by p.predmetid
having avg(po.brojpoena) < 70;

-- 5.
select sm.fakultetid, sm.nivostudija,
sum(case when s.status = 'budzet' then 1 else 0 end) as broj_budzet
from smer sm join student s on (sm.smerid = s.smerid)
join fakultet f on (sm.fakultetid = f.fakultetid and s.fakultetid = f.fakultetid)
where f.godinaosnivanja > 1965
group by sm.fakultetid, sm.nivostudija;

-- 6.
select ime, prezime, mestoprebivalista
from student
where brojindeksa in (select brojindeksa
from polaganje
group by brojindeksa
having avg(ocena) in (select min(avg(ocena))
from polaganje
group by brojindeksa));

-- 7.
select f.fakultetid, sm.smerid, sm.nazivsmera, sm.nivostudija,
max(s.datumupisa) as prvi_student
from smer sm join student s on (sm.smerid = s.smerid)
join fakultet f on (f.fakultetid = sm.fakultetid and f.fakultetid = s.fakultetid)
where sm.fakultetid in (select fakultetid
from fakultet
where fakultetid in (select fakultetid
from smer
group by fakultetid
having count(smerid) > 1))
and (sm.fakultetid, sm.smerid) in (select fakultetid, smerid
from student
group by fakultetid, smerid
having count(*) < (select max(count(*))
from student
where fakultetid =
sm.fakultetid
group by fakultetid,
smerid))
group by f.fakultetid, sm.smerid, sm.nazivsmera, sm.nivostudija
order by sm.smerid asc;

You might also like