BNP_Lab4.txt

-- First Query
SELECT V.ime, V.prezime FROM Vraboten V
JOIN Shalterski_rabotnik SR ON V.ID = SR.ID
JOIN Transakcija_shalter TS ON SR.ID = TS.ID_v
JOIN Smetka S ON TS.broj = S.broj
WHERE TS.suma > 1000 AND S.valuta = 'EUR' AND TS.tip = 'isplata'
ORDER BY V.ime;

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

-- Third Query
SELECT S.* FROM Smetka S
WHERE S.valuta = 'MKD' AND EXISTS (
    SELECT * FROM Transakcija_shalter TS
    WHERE TS.broj = S.broj AND TS.tip = 'isplata' AND TS.datum NOT BETWEEN '2021-01-01' AND '2021-12-31' AND TS.tip = 'isplata'
    ) AND EXISTS (
    SELECT * FROM Transakcija_shalter TB
    WHERE TB.broj = S.broj AND TB.datum NOT BETWEEN '2021-01-01' AND '2021-12-31'
    )
ORDER BY S.broj;

-- Fourth Query
SELECT K.* FROM Klient K
JOIN Smetka S ON K.MBR_k = S.MBR_k
WHERE S.valuta = 'EUR' AND EXISTS(
    SELECT * FROM Transakcija_bankomat TB
    WHERE TB.broj = S.broj
) AND NOT EXISTS(
    SELECT * FROM Transakcija_shalter TS
    WHERE TS.broj = S.broj
)
ORDER BY K.ime;

-- Fifth Query
WITH Transaction_count AS (
    SELECT TS.ID, TS.datum, COUNT(*) AS transactions FROM Transakcija_shalter TS
    GROUP BY TS.ID, TS.datum
),
Max_transaction AS (
    SELECT TC.ID, MAX(TC.transactions) AS max_transactions FROM Transaction_count TC
    GROUP BY TC.ID

)
SELECT TC.ID, TC.datum,TC.transactions FROM Transaction_count TC
JOIN Max_transaction MT ON TC.ID = MT.ID
ORDER BY TC.ID;

-- Fifth Query(Not sure)
SELECT TS.ID_v as vraboten, TS.datum, MAX(broj_trans) as broj_transakcii FROM (
    SELECT ID_v, datum, COUNT(*) AS broj_trans FROM Transakcija_shalter TS
    GROUP BY TS.ID_v,datum
) TS
GROUP BY TS.ID_v, datum
HAVING (broj_transakcii = MAX(broj_trans));


-- Sixth Query
WITH Average_TS AS (
    SELECT TS.broj, AVG(TS.suma) AS avg_ts_suma FROM Transakcija_shalter TS
    WHERE TS.datum BETWEEN '2021-01-01' AND '2021-12-31' AND TS.tip = 'isplata'
    GROUP BY TS.broj
),
Average_TB AS (
    SELECT TB.broj, AVG(TB.suma) as avg_tb_suma FROM Transakcija_bankomat TB
    WHERE TB.datum BETWEEN '2021-01-01' AND '2021-12-31'
    GROUP BY TB.broj
)
SELECT S.broj, COALESCE(ATS.avg_ts_suma, 0) AS avg_ts_suma, COALESCE(ATB.avg_tb_suma, 0) AS avg_tb_suma  FROM Smetka S
LEFT JOIN Average_TS ATS ON S.broj = ATS.broj
LEFT JOIN Average_TB ATB ON S.broj = ATB.broj
WHERE S.valuta = 'EUR' OR S.valuta = 'USD'
ORDER BY S.broj;

-- Sixth Query(Not sure)
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_bankomat tb ON s.broj=tb.broj AND tb.datum LIKE '2021%'
JOIN Transakcija_shalter ts ON s.broj=ts.broj AND ts.tip='isplata' AND ts.datum LIKE '2021%'
WHERE s.valuta='EUR' or s.valuta='USD'
GROUP BY s.broj
ORDER BY s.broj