bazi-dml

1)
select distinct l.id
from Lice as l
    inner join Test as t on l.id=t.id
    inner join Vakcinacija_datum as v on l.id=v.id_lice
where t.rezultat='pozitiven' and t.datum < v.datum
order by l.id ASC;
2)
select distinct m.ime,m.prezime
from Muzicar as m
    inner join Muzicar_instrument as mi on m.id=mi.id_muzicar
    inner join Koncert_muzicar_bend as kmb on m.id=kmb.id_muzicar
    inner join Muzicar_bend as mb on m.id=mb.id_muzicar
    inner join Bend as b on mb.id_bend=b.id
    inner join Koncert as k on kmb.id_koncert=k.id
where mi.instrument='gitara' and mb.datum_napustanje<k.datum
order by m.ime
3)
select k.ime,k.prezime
from Korisnik as k
--посета на еден објект
    inner join Poseta as p1 on k.kor_ime=p1.kor_ime
    inner join Objekt as o1 on p1.id_mesto=o1.id_mesto
--посета на втор објект
    inner join Poseta as p2 on k.kor_ime=p2.kor_ime
    inner join Objekt as o2 on p2.id_mesto=o2.id_mesto
    inner join Sosedi as s on s.grad1=o1.id_grad and  s.grad2=o2.id_grad
where p1.datum=p2.datum


4)
select distinct k.ime,k.prezime
from Korisnik as k
--посета на еден објект
    inner join Poseta as p1 on k.kor_ime=p1.kor_ime
    inner join Objekt as o1 on p1.id_mesto=o1.id_mesto
--посета на втор објект
    inner join Poseta as p2 on k.kor_ime=p2.kor_ime
    inner join Objekt as o2 on p2.id_mesto=o2.id_mesto
    inner join Sosedi as s on s.grad1=o1.id_grad and  s.grad2=o2.id_grad
where s.rastojanie < 300


5)
select distinct ime,k.prezime
from  Premium_korisnik as pk
     inner join  Korisnik as k on pk.k_ime=k.k_ime
     inner join Preporaka as p on k.k_ime=p.k_ime_od
     inner join Video_zapis as v on p.naslov=v.naslov
where v.vremetraenje>120 and p.ocena>=4
order by k.datum_reg ASC;


6)
--прво cte е за "корисничките имиња и насловите на препорачаните видео записи за сите
--премиум корисници кои добиле препорака со оцена поголема од 3 за барем еден видео запис во 2021 годин"
with cte as(
    select *
    from Premium_korisnik as pk
        inner join Korisnik as k on pk.k_ime=k.k_ime
        inner join Preporaka as p on k.k_ime=p.k_ime_na
        inner join Video_zapis as v on p.naslov=v.naslov
    where p.ocena>3 and p.datum LIKE '2021%'
),cte2 as(
    select *
    from Premium_korisnik as pk
    inner join Korisnik as k on pk.k_ime=k.k_ime
    inner join Profil as p on k.k_ime=p.k_ime
    inner join Lista_zelbi as lz on p.k_ime=lz.k_ime
)
select distinct cte.k_ime,cte.naslov
from cte inner join cte2 on cte.k_ime=cte2.k_ime and cte.naslov=cte2.naslov

7)

8)
from Bend as b1
    inner join Bend as b2 on b1.godina_osnovanje=b2.godina_osnovanje
where b1.id!=b2.id and b1.ime>b2.ime

--id-to да им е различно за да не го спојам истот бенд со истот
-- вториот услов е како distinct само што името овде според примерот треба буквата од името
-- на првиот бенд да биде со поголем asc code од буквата на втриот
--пр B има поголем asc code од  A
9)
--во првото cte наоѓаме секој град колку знаменитости има
with cte as (
    select m.ime, count(*) as broj
    from Mesto as m
        inner join Grad as g on m.id=g.id_mesto
        inner join Objekt as o on g.id_mesto=o.id_grad
    group by m.ime
),-- во второто cte наоѓаме  градот со со најмногу објекти
cte2 as (
 select  max(broj) as max
    from cte
)


select cte.ime
from cte2 inner join cte on cte2.max=cte.broj

10)
-- with gradovi_poseti as (
--     select p.id_mesto,  count(*) as broj_poseti
--     from poseta p
--         join mesto m on p.id_mesto = m.id
--         join grad g on m.id = g.id_mesto
--     group by   p.id_mesto
-- ),
-- najposeten_grad as (
--     select id_mesto
--     from  gradovi_poseti
--     where   broj_poseti = (select max(broj_poseti)
--                             from gradovi_poseti)
-- )
-- select m.ime
-- from mesto m
-- where  m.id in (
--         select   o.id_mesto
--         from
--             objekt o
--         join
--             najposeten_grad np on o.id_grad = np.id_mesto
--     );
--нз
11)
--korisnicite koi dale najmnogu prekoraki
with cte as(
    select k.k_ime, count(*) as broj
    from Korisnik as k
        inner join Preporaka as p on k.k_ime=p.k_ime_od
    group by k.k_ime
)
select cte.k_ime, count(*) as dobieni_preporaki
from cte inner join Preporaka as p on cte.k_ime=p.k_ime_na
where broj = (
    select max(broj)
    from cte
)
group by cte.k_ime

12)

--наоѓање на секој корисник бројот на препораки
with cte as (
        select k.k_ime, v.naslov, count(*) as broj
         from Korisnik as k
        inner join Preporaka as p on k.k_ime=p.k_ime_od
        inner join Video_zapis as v on p.naslov=v.naslov
        group by k.k_ime, v.naslov
),cte2 as (
    select cte.k_ime, max(broj) as max_broj
    from cte
    group by cte.k_ime
)

select cte.k_ime, cte.naslov, cte.broj
from cte inner join cte2 on cte.k_ime=cte2.k_ime and cte.broj=cte2.max_broj
--табелите ги спојувам според кориничкот име и бројот на преопораки со нивниот максимален број на препораки

13)
with cte_broj_odrzuvanja as (
    select  f.id as id_festival, count(*) as broj_odrzuvanja
    from festival f
    inner join   festival_odrzuvanje fo on f.id = fo.id
    group by f.id
),
cte_broj_bendovi as (
    select fb.id_festival,count(distinct fb.id_bend) as broj_bendovi
    from  festival_bend fb
    group by  fb.id_festival
)
select
    f.ime ,
    n.cena,
    n.kapacitet,
    cte_broj_odrzuvanja.broj_odrzuvanja,
    cte_broj_bendovi.broj_bendovi
from festival f
inner join  nastan n on f.id = n.id
inner join cte_broj_odrzuvanja on f.id = cte_broj_odrzuvanja.id_festival
inner join  cte_broj_bendovi on f.id = cte_broj_bendovi.id_festival
14)
--ИСТА Е КО 26 ТАТА


--Od sekoj video zapis zemem globalno prosecna ocena
with videoprosek as (
    select  v.naslov,  avg(p.ocena) as prosechna_ocenka
    from  video_zapis as v
    inner join  preporaka as p on v.naslov = p.naslov
    group by  v.naslov
)
--utkata mene u gornoto mi beshe shto ne znev kako da go zemame prosekot
-- a ustvar trebalo da spojam profilot so negovoata lista na zhelba kade se naogjaat naslovotie za koi prethodno najdovme prosecna ocena
select  p.ime,  avg(vp.prosechna_ocenka) as po_profil
from  profil as p
inner join  lista_zelbi as l on p.k_ime = l.k_ime and p.ime = l.ime
inner join videoprosek as vp on l.naslov = vp.naslov
group by p.ime;
15)
select v.ime,v.prezime
from Vraboten as v
    inner join  Shalterski_rabotnik as sk on v.ID=sk.ID
    inner join Transakcija_shalter as ts on sk.ID=ts.ID_v
    inner join Smetka as s on ts.broj=s.broj
where tip='isplata' and suma>1000 and valuta='EUR'
order by v.ime ASC
16)
select k.ime,k.prezime
from Klient as k
    inner join Smetka as s on k.MBR_k=s.MBR_k
    inner join Transakcija_bankomat as tb on s.broj=tb.broj and s.MBR_k=tb.MBR_k_s
where suma>400 and valuta='USD'
order by k.ime ASC
17)
select s.*
from Smetka s join Transakcija_bankomat tb on s.MBR_k=tb.MBR_k_s
              join Transakcija_shalter ts on s.MBR_k=ts.MBR_k_s
where s.valuta='MKD' and
      ts.tip='isplata' and
      ts.datum like '2021-%-%'
      and tb.datum like '2021-%-%'
order by mbr_k, broj
18)
-- --сите клиенти кои направиле барем една трансакција преку банкомат (за сметки кои работат со валута EUR),
with cte as (
select k.MBR_k
from Klient as k
    inner join Smetka as s on k.MBR_k=s.MBR_k
    inner join Transakcija_bankomat as tb on s.MBR_k=tb.MBR_k_s
where valuta='EUR'

EXCEPT
select k.MBR_k
from Klient as k
    inner join Transakcija_shalter as ts on k.MBR_k=ts.MBR_k
    inner join Smetka as s on ts.broj=s.broj
where valuta='EUR'
)
--Во горинте нема потреба од правење на ПОДРЕДУВАЊА

select k.MBR_k,k.ime,k.prezime,k.adresa,k.datum
from cte inner join Klient as k on cte.MBR_k=k.MBR_k
-- подредени според името на клиентот
order by k.ime
19)
-- ЗАБЕЛЕШКА МЕСТО ШАЛТЕРСКИ ВРАБОТЕН ОДИ СО ВРАБОТЕН ТАКА РАБОТИ
with cte as(
    select v.ID as vraboten, t.datum, count(*) as broj_transakcii
    from Vraboten as v
    inner join Transakcija_shalter as t on v.ID=t.ID_v
    group by v.ID, t.datum
),
cte2 as (
    select vraboten,max(broj_transakcii) as max
    from cte
    group by vraboten
)

select cte.vraboten,cte.datum, broj_transakcii
from cte2 inner join cte on cte2.vraboten=cte.vraboten and cte2.max=cte.broj_transakcii



20)
with cteShalter as (
    select s.MBR_k,s.broj, avg(suma) as prosechna_isplata_shalter
    from Smetka as s
        inner join Transakcija_shalter as t on s.broj=t.broj
    group by  s.MBR_k,s.broj
    having (valuta='EUR' or valuta='USD') and datum LIKE '2021%' and tip='isplata'
),
cteBankomat as(
    select s.MBR_k,s.broj, avg(suma) as prosechna_isplata_bankomat
    from Smetka as s
        inner join Transakcija_bankomat as b on s.broj=b.broj
    group by s.MBR_k, s.broj
    having (valuta='EUR' or valuta='USD') and datum LIKE '2021%'
)
select cb.MBR_k,cb.broj,prosechna_isplata_bankomat,prosechna_isplata_shalter
from cteBankomat as cb inner join  cteShalter as cs on cb.MBR_k=cs.MBR_k and cb.broj=cs.broj
21)
select distinct k.ime,k.prezime
from Premium_korisnik as pk
    inner join Korisnik as k on pk.k_ime=k.k_ime
    inner join Preporaka as p on k.k_ime=p.k_ime_od
    inner join Video_zapis as v on p.naslov=v.naslov
where vremetraenje>120 and ocena>=4
order by datum_reg ASC;
22)
-- го бараме прво корисникот кој дал најголем број на препораки
with KosinikPrep as (
    select k.k_ime,count(*) as brojPreporakiDal
    from Korisnik as k
        inner join Preporaka as p on k.k_ime=p.k_ime_od
    group by k.k_ime
),
--prep OD  dal , Na primil
KorisnikNajmnPrep as(
        select *
        from KosinikPrep
        where brojPreporakiDal=(select max(brojPreporakiDal)
                             from KosinikPrep)
)

select kn.k_ime, count(*) as dobieni_preporaki
from KorisnikNajmnPrep as kn
    inner join Preporaka as p on kn.k_ime=k_ime_na
group by kn.k_ime
23)
--Od sekoj video zapis zemem globalno prosecna ocena
with videoprosek as (
    select  v.naslov,  avg(p.ocena) as prosechna_ocenka
    from  video_zapis as v
    inner join  preporaka as p on v.naslov = p.naslov
    group by  v.naslov
)
-- treba da spojam profilot so negovoata lista na zhelba kade se naogjaat naslovotie za koi prethodno najdovme prosecna ocena
select  p.ime,  avg(vp.prosechna_ocenka) as po_profil
from  profil as p
inner join  lista_zelbi as l on p.k_ime = l.k_ime and p.ime = l.ime
inner join videoprosek as vp on l.naslov = vp.naslov
group by p.ime;

24)
select vzz.zanr, count(*) as broj_zanrovi
from Video_zapis_zanr as vzz
    inner join Video_zapis as v on vzz.naslov=v.naslov
    inner join Preporaka as p on p.naslov=v.naslov
where komentar LIKE '%interesting%'
group by vzz.zanr
order by broj_zanrovi DESC

25)
with cte as (
    select v.naslov, count(*) as brojPojavuvanja
    from Video_zapis as v
        inner join Lista_zelbi as lz on v.naslov = lz.naslov
        inner join Profil as p on p.ime = lz.ime and p.k_ime = lz.k_ime
    group by v.naslov
), cte2 as (
    select *
    from cte
    where cte.brojPojavuvanja >= 2
)

select v.naslov, v.vremetraenje, count(p.naslov) as broj_preporaki
from cte2 as c
    inner join Video_zapis as v on c.naslov = v.naslov
    left join Preporaka as p on v.naslov = p.naslov
group by v.naslov, v.vremetraenje
order by v.naslov;

26)