[BNP] DML Masterpiece

--1)Да се напише DML израз со кој ќе се вратат матичните броеви на лицата (сортирани во растечки редослед) кои биле позитивни и потоа примиле барем една доза вакцина.

SELECT DISTINCT L.id
FROM Lice L JOIN Test T ON L.id=T.id JOIN Vakcinacija_datum VD ON L.id=VD.id_lice
WHERE T.rezultat='pozitiven' AND T.datum<VD.datum
ORDER BY L.id

--2)Да се напише DML израз со кој ќе се вратат имињата и презимињата на гитаристите (музичарите кои свират на инструментот гитара) кои настапиле на концерт заедно со бенд откако го напуштиле. Датумот на настап на музичарот заедно со бендот е датумот на самиот концерт. Резултатите треба да се подредени според името во растечки редослед.

SELECT ime,prezime
FROM Muzicar
WHERE Muzicar.id IN (SELECT MI.id_muzicar
FROM Muzicar_instrument MI JOIN Muzicar_bend MB ON MI.id_muzicar=MB.id_muzicar JOIN Koncert_muzicar_bend KMB ON KMB.id_bend=MB.id_bend AND KMB.id_muzicar=MB.id_muzicar JOIN Koncert K ON K.id=KMB.id_koncert
WHERE MI.instrument='gitara' AND datum_napustanje<datum)

--3)Да се напише DML израз со кој ќе се вратат името и презимето на корисниците кои во ист ден посетиле објекти кои се наоѓаат во соседни градови.

SELECT DISTINCT ime,prezime
FROM Korisnik K JOIN Poseta P ON K.kor_ime=P.kor_ime JOIN Objekt O ON P.id_mesto=O.id_mesto JOIN Poseta P2 ON P.kor_ime=P2.kor_ime AND P.id_mesto!=P2.id_mesto AND P.datum=P2.datum JOIN Objekt O2 ON O2.id_mesto=P2.id_mesto JOIN Sosedi S ON (O.id_grad=S.grad1 AND O2.id_grad=S.grad2) OR (O.id_grad=S.grad2 AND O2.id_grad=S.grad1)

--4)Да се напише DML израз со кој ќе се вратат името и презимето на корисниците кои посетиле објекти кои се наоѓаат во соседни градови чие растојание е помало од 300 km.

SELECT DISTINCT ime,prezime
FROM Korisnik K JOIN Poseta P ON K.kor_ime=P.kor_ime JOIN Poseta P2 ON K.kor_ime=P2.kor_ime AND P.id_mesto!=P2.id_mesto JOIN Objekt O ON P.id_mesto=O.id_mesto JOIN Objekt O2 ON O2.id_mesto=P2.id_mesto JOIN Sosedi S ON (S.grad1=O.id_grad AND S.grad2=O2.id_grad AND S.rastojanie<300) OR (S.grad2=O.id_grad AND S.grad1=O2.id_grad AND S.rastojanie<300)

--5)Да се напише DML израз со кој ќе се вратат имињата и презимињата на сите премиум корисници кои препорачале видео запис со времетраење подолго од 2 часа и за кој оставиле оцена поголема или еднаква на 4, подредени според датумот на регистрација во растечки редослед.

SELECT DISTINCT ime,prezime
FROM Premium_korisnik PK JOIN Preporaka P ON PK.k_ime=P.k_ime_od JOIN Video_zapis VZ ON P.naslov=VZ.naslov JOIN Korisnik K ON PK.k_ime=K.k_ime
WHERE ocena>=4 AND vremetraenje>120
ORDER BY datum_reg

--6)Да се напише DML израз со кој ќе се вратат корисничките имиња и насловите на препорачаните видео записи за сите премиум корисници кои добиле препорака со оцена поголема од 3 за барем еден видео запис во 2021 година кој е дел од листата на желби во барем еден од нивните профили, подредени според корисничкото име.

SELECT DISTINCT PRO.k_ime,LZ.naslov
FROM Premium_korisnik PK JOIN Preporaka P ON PK.k_ime=P.k_ime_na JOIN Profil PRO ON PK.k_ime=PRO.k_ime JOIN Lista_zelbi LZ ON PRO.k_ime=LZ.k_ime AND LZ.naslov=P.naslov
WHERE ocena>3 AND P.datum LIKE '2021%'

--7)

--8)Да се напише DML израз со кој ќе се вратат сите парови на бендови (пар од имињата на бендовите) кои се основани во иста година.

SELECT b1.ime AS B1,b2.ime AS B2
FROM Bend b1 JOIN Bend b2 ON b1.godina_osnovanje=b2.godina_osnovanje AND b1.id!=b2.id
WHERE b1.ime>b2.ime
--sveti zeleno kako Pelister


--9)Да се напише DML израз со кој ќе се врати името на градот во кој се наоѓа објектот што бил посетен најголем број пати.

WITH Broj_poseti AS (SELECT P.id_mesto AS id_mesto, COUNT(*) AS broj_poseti
FROM Poseta P JOIN Objekt O ON P.id_mesto=O.id_mesto
GROUP BY P.id_mesto),
--in a way so ova sledno se osiguram deka nema poveke objekti so ista posetenost
Max_poseti AS (SELECT MAX(broj_poseti) AS max_poseti
FROM Broj_poseti),
Max_poseteni_objekti AS (SELECT BP.id_mesto AS id_mesto
FROM Broj_poseti BP,Max_poseti MP
WHERE BP.broj_poseti=MP.max_poseti)
SELECT DISTINCT M.ime
FROM Objekt O, Mesto M ON O.id_grad=M.id
WHERE id_grad IN (SELECT id_grad
FROM Max_poseteni_objekti MPO JOIN Objekt O ON MPO.id_mesto=O.id_mesto)

--10)Да се напише DML израз со кој ќе се вртат имињата на објектите кои се наоѓаат во градот што бил посетен најголем број пати. За посети на градови се сметаат посетите на места што претставуваат градови. Во ова не се вклучени посетите на објекти во тие градови.

--rizikuvam deka nema da ima poveke gradovi so ist broj na poseti, a i bi bilo glupavo vo resenieto da ima unija na objekti od dva istoposeteni gradovi imo
WITH Poseteni_gradovi AS (SELECT M.ime AS ime_grad, O.id_grad, COUNT(*) AS broj_poseti
FROM Poseta P JOIN Mesto M ON P.id_mesto=M.id JOIN Objekt O ON O.id_grad=M.id
GROUP BY M.ime),
Najposeten_grad AS (SELECT ime_grad, id_grad, MAX(broj_poseti) AS max_poseti
FROM Poseteni_gradovi)
SELECT M.ime
FROM Mesto M JOIN Objekt O ON M.id=O.id_mesto JOIN Najposeten_grad NG ON NG.id_grad=O.id_grad
ORDER BY M.ime DESC

--11)Да се напише DML израз со кој ќе се вратат корисничкото име и бројот на видео записи кои му биле препорачани на корисникот кој дал најголем број на препораки.
Напомена: при оценување на оваа задача нема да се признаваат решенија со користење на ORDER BY.

WITH Koj_kolku_preporacuval AS (SELECT P.k_ime_od AS k_ime,COUNT(P.ID) AS broj_preporaki
FROM Preporaka P JOIN Korisnik K ON P.k_ime_od=K.k_ime
GROUP BY P.k_ime_od),
Kolku_najmnogu_preporaki AS (SELECT MAX(broj_preporaki) AS max_preporaki
FROM Koj_kolku_preporacuval),
Koj_preporacal_najmnogu AS (SELECT k_ime
FROM Koj_kolku_preporacuval KKP JOIN Kolku_najmnogu_preporaki KNP
WHERE KKP.broj_preporaki=KNP.max_preporaki)
SELECT KPN.k_ime, COUNT(ID) AS dobieni_preporaki
FROM Preporaka P JOIN Koj_preporacal_najmnogu KPN ON P.k_ime_na=KPN.k_ime
GROUP BY KPN.k_ime

--12)Да се напише DML израз со кој за секој корисник ќе се врати видео записот кој го препорачал најголем број пати.

WITH Preporaki_po_covek AS (SELECT P.k_ime_od AS k_ime, P.naslov, COUNT (P.ID) AS broj_preporaki
FROM Preporaka P JOIN Korisnik K ON P.k_ime_od=K.k_ime
GROUP BY k_ime, P.naslov),
Max_preporaki_po_covek AS (SELECT k_ime, MAX(broj_preporaki) AS max_preporaki
FROM Preporaki_po_covek PPC
GROUP BY k_ime)
SELECT DISTINCT P.k_ime_od AS k_ime,P.naslov,MPPC.max_preporaki AS broj
FROM Preporaka P JOIN Max_preporaki_po_covek MPPC ON P.k_ime_od=MPPC.k_ime JOIN Preporaki_po_covek PPC ON P.k_ime_od=PPC.k_ime AND PPC.broj_preporaki=MPPC.max_preporaki AND P.naslov=PPC.naslov

--13)Да се напише DML израз со кој за секој фестивал ќе се врати името, цената на билетите, капацитетот на посетители, бројот на одржувања и вкупниот број на различни бендови кои настапиле.

SELECT F.ime,N.cena,N.kapacitet,COUNT(DISTINCT FO.datum_od) AS broj_odrzuvanja,COUNT(DISTINCT FB.id_bend) AS broj_bendovi
FROM Nastan N JOIN Festival_odrzuvanje FO ON N.id=FO.id JOIN Festival F ON F.id=FO.id JOIN FestivaL_bend FB ON FB.id_festival=F.id
GROUP BY F.ime
ORDER BY kapacitet DESC

--14)Да се напише DML израз со кој за секој профил ќе се врати името на профилот и просечната оцена на видео записите во листата на желби асоцирана со тој профил. (Просечната оцена на секој видео запис се пресметува од сите оцени за тој видео запис).

WITH Avg_temp AS (SELECT P.ime,AVG(ocena) AS avg_ocena
FROM Profil P JOIN Lista_zelbi LZ ON P.ime=LZ.ime JOIN Preporaka P ON P.naslov=LZ.naslov
GROUP BY P.ime,LZ.naslov)
SELECT ime,AVG(avg_ocena) AS po_profil
FROM Avg_temp
GROUP BY ime

--15)Да се напише DML израз со кој за секој корисник ќе се врати видео записот кој го препорачал најголем број пати.

SELECT DISTINCT ime,prezime
FROM Vraboten V JOIN Shalterski_rabotnik SR ON V.ID=SR.ID JOIN Transakcija_shalter TS ON TS.ID_v=SR.ID JOIN Smetka S ON TS.MBR_k_s=S.MBR_k
WHERE suma>1000 AND valuta='EUR' AND tip='isplata'
ORDER BY ime

--16)Да се напише DML израз со кој ќе се вратат имињата и презимињата на на сите клиенти кои имаат направено трансакција за исплата на средства преку банкомат во износ поголем од 400 USD од сметка која работи со валута USD, подредени според името на клиентите.

SELECT ime,prezime
FROM Klient K JOIN Smetka S ON K.MBR_k=S.MBR_k JOIN Transakcija_bankomat TB ON TB.broj=S.broj
WHERE valuta='USD' AND suma>400
ORDER BY ime

--17)Да се напише DML израз со кој ќе се вратат сите сметки кои работат со валута MKD од кои е направена барем една трансакција за исплата преку шалтер и барем една трансакција за исплата преку банкомат во 2021 година, подредени според бројот на сметка.

SELECT S.MBR_k,S.broj,S.valuta,S.saldo
FROM Smetka S JOIN Transakcija_shalter TS ON S.broj=TS.broj
WHERE datum LIKE '2021%' and valuta='MKD' AND tip='isplata'
INTERSECT
SELECT S.MBR_k,S.broj,S.valuta,S.saldo
FROM Smetka S JOIN Transakcija_bankomat TB ON S.broj=TB.broj
WHERE datum LIKE '2021%' and valuta='MKD'

--18)Да се напише DML израз со кој ќе се вратат сите клиенти кои направиле барем една трансакција преку банкомат (за сметки кои работат со валута EUR), но не направиле ниту една трансакција преку шалтер од истата сметка, подредени според името на клиентот.

SELECT K.*
FROM Klient K JOIN Smetka S ON K.MBR_k=S.MBR_k JOIN Transakcija_bankomat TB ON TB.broj=S.broj
WHERE valuta='EUR' and S.broj NOT IN (SELECT S.broj
FROM Klient K JOIN Smetka S ON K.MBR_k=S.MBR_k JOIN Transakcija_shalter TS ON TS.broj=S.broj
WHERE valuta='EUR')
ORDER BY ime

--19)Да се напише DML израз со кој за секој шалтерски работник ќе се врати неговата шифра, датумот и бројот на трансакции за датумот на кој има направено најголем број на трансакции.

WITH Transakcii_po_datum AS (SELECT SR.ID,TS.datum, COUNT(TS.ID) AS broj_transakcii
FROM Vraboten SR JOIN Transakcija_shalter TS ON SR.ID=TS.ID_v
GROUP BY SR.ID,TS.datum),
Max_transakcii_po_datum AS (SELECT ID, MAX(broj_transakcii) AS max_transakcii
FROM Transakcii_po_datum
GROUP BY ID)
SELECT TPD.ID AS vraboten, datum, broj_transakcii
FROM Transakcii_po_datum TPD JOIN Max_transakcii_po_datum MTPD ON TPD.broj_transakcii=MTPD.max_transakcii AND TPD.ID=MTPD.ID

--20)Да се напише DML израз со кој за секоја сметка која работи со валута EUR или USD да се вратат просечната направена сума oд трансакции за исплата преку шалтер во 2021 година и просечната направена сума од трансакции за исплата преку банкомат во 2021 година, подредени според бројот на сметка.

SELECT S.MBR_k,S.broj,AVG(TB.suma) AS prosechna_isplata_bankomat,AVG(TS.suma) AS prosechna_isplata_shalter
FROM Smetka S JOIN Transakcija_shalter TS ON S.broj=TS.broj JOIN Transakcija_bankomat TB ON S.broj=TB.broj
WHERE TS.datum LIKE '2021%' AND valuta IN ('USD','EUR') AND tip='isplata' AND TB.datum LIKE '2021%'
GROUP BY S.MBR_k,S.broj
ORDER BY S.broj

--21)Да се вратат имињата и презимињата на сите премиум корисници кои препорачале видео запис со времетраење подолго од 2 часа и за кој оставиле оцена поголема или еднаква на 4, подредени според датумот на регистрација во растечки редослед (времетраењето се чува во минути)

SELECT DISTINCT ime,prezime
FROM Premium_korisnik PK JOIN Preporaka P ON P.k_ime_od=PK.k_ime JOIN Video_zapis VZ ON VZ.naslov=P.naslov JOIN Korisnik K ON PK.k_ime=K.k_ime
WHERE ocena>=4 AND vremetraenje>120
ORDER BY datum_reg

--22)Да се вратат корисничкото име и бројот на видео записи кои му биле препорачани на корисникот кој дал најголем број на препораки.

WITH Koj_preporacal AS (SELECT P.k_ime_od AS k_ime,COUNT(P.ID) AS broj_preporaki
FROM Preporaka P JOIN Korisnik K ON K.k_ime=P.k_ime_od
GROUP BY P.k_ime_od),
Max_preporaki AS (SELECT MAX(broj_preporaki) AS max_preporaki
FROM Koj_preporacal)
SELECT P.k_ime_na AS k_ime,COUNT(P.ID) AS dobieni_preporaki
FROM Preporaka P
WHERE P.k_ime_na=(SELECT k_ime
FROM Koj_preporacal KP JOIN Max_preporaki MP ON KP.broj_preporaki=MP.max_preporaki)

--23)За секој профил да се врати името на профилот и просечната оцена на видео записите во листата на желби асоцирана со тој профил. (Просечната оцена на секој видео запис се пресметува од сите оцени за тој видео запис).

WITH Avg_po_naslov AS (SELECT P.ime,AVG(PR.ocena) AS prosecna_ocena
FROM Profil P JOIN Lista_zelbi LZ ON P.ime=LZ.ime JOIN Preporaka PR ON LZ.naslov=PR.naslov
GROUP BY P.ime,PR.naslov
ORDER BY P.ime)
SELECT ime,AVG(prosecna_ocena) AS po_profil
FROM Avg_po_naslov
GROUP BY ime

--24)Да се вратат жанровите заедно со бројот на препораки со коментар што го содржи зборот „interesting“, подредени според бројот на препораки во опаѓачки ред.

SELECT VZZ.zanr, COUNT(P.komentar) AS broj_zanrovi
FROM Video_zapis_zanr VZZ JOIN Preporaka P ON VZZ.naslov=P.naslov
WHERE komentar LIKE '%interesting%'
ORDER BY zanr DESC

--25)Да се врати список со насловите на видеата, времетраењето и бројот на препораки, за видеа кои се во листата на желби на најмалку два различни профили.

WITH Naslovi_2p AS (SELECT LZ.naslov AS naslov
FROM Profil P JOIN Lista_zelbi LZ ON LZ.ime=P.ime
GROUP BY LZ.naslov
HAVING COUNT(P.ime)>1)
SELECT N2P.naslov,vremetraenje,COUNT(P.ID) AS broj_preporaki
FROM Naslovi_2p N2P LEFT OUTER JOIN Preporaka P ON N2P.naslov=P.naslov JOIN Video_zapis VZ ON VZ.naslov=N2P.naslov
GROUP BY N2P.naslov
ORDER BY N2P.naslov

--26)Да се вратат имињата на сите корисници кои имаат дадено препораки за видеа кои никој од нивните профили не ги има во листата на желби.

--se resavat samo so prvite dva reda ali ne mi svetese zeleno zaradi orderot posto tie prvo ja vklucuvaat verovatno Preporaka, a jas ispadna da ja vklucam posledno, i ako sakav da mi svetni zeleno morav da napravam nov join na Preporaka so mojot rezultat za da go dobijam toj redosled auf
WITH Rezultat AS (SELECT DISTINCT P.k_ime AS k_ime_od
FROM Profil P,Video_zapis VZ LEFT OUTER JOIN Lista_zelbi LZ ON P.k_ime=LZ.k_ime AND VZ.naslov=LZ.naslov JOIN Preporaka PR  ON P.k_ime=PR.k_ime_od AND VZ.naslov=PR.naslov AND LZ.naslov IS NULL)
SELECT DISTINCT PR.k_ime_od
FROM Preporaka PR JOIN Rezultat R ON PR.k_ime_od=R.k_ime_od