-- ISHOD 6
SELECT * FROM Komercijalist

-- 2. zadatak
SELECT Ime,Prezime,SUBSTRING(ime,1,1)+'.'+SUBSTRING(prezime,1,1)+'.' AS 'Inicijali'
FROM Komercijalist
WHERE StalniZaposlenik=0

-- 3. zadatak
SELECT datediff(YEAR,MIN(DatumIzdavanja),getdate()) AS 'najraniji racun',
datediff(YEAR,MAX(DatumIzdavanja),getdate()) AS 'najstariji racun'
FROM Racun

-- 4. zadatak
SELECT DISTINCT datepart(YEAR,datumizdavanja) AS 'godina',
COUNT(idracun) AS 'broj racuna',
SUM(stavka.UkupnaCijena) AS 'ukupna suma'
FROM Racun
    INNER JOIN stavka ON stavka.RacunID=racun.IDRacun
WHERE datepart(MONTH,datumizdavanja)=12
GROUP BY DatumIzdavanja

-- 5. zadatak
SELECT k.Naziv AS 'naziv kategorije',COUNT(IDProizvod) AS 'broj neprodanih proizvoda'
FROM Proizvod
INNER JOIN Potkategorija ON proizvod.PotkategorijaID=Potkategorija.IDPotkategorija
INNER JOIN Kategorija AS k ON Potkategorija.KategorijaID=K.IDKategorija
LEFT OUTER JOIN stavka ON proizvod.IDProizvod=stavka.ProizvodID
WHERE stavka.IDStavka IS NULL
GROUP BY k.Naziv

SELECT * FROM Racun
SELECT * FROM Potkategorija

-- ISHOD 7
-- 6. zadatak
SELECT DISTINCT Ime,Prezime FROM kupac WHERE IDKupac NOT IN (
SELECT racun.KupacID
FROM Racun
)
SELECT * FROM Kupac

-- 7. zadatak
SELECT idracun,DatumIzdavanja,(
SELECT SUM(RacunID) FROM Stavka
WHERE RacunID=IDRacun)AS 'ukupna suma'
FROM Racun

-- 8. zadatak
SELECT naziv
FROM grad WHERE IDGrad IN (SELECT gradID FROM Kupac
WHERE IDKupac IN(SELECT kupacID FROM racun WHERE KreditnaKarticaID IN (SELECT KreditnaKarticaID FROM KreditnaKartica WHERE tip='Visa')
))

-- 9. zadatak
SELECT
    kat.Naziv, AVG(p.CijenaBezPDV) AS ProsjekKategorije,
    (
        SELECT
            avg(CijenaBezPDV)
        FROM Proizvod AS p1
    ) AS ProsjekSvihProizvoda
FROM Proizvod AS p
INNER JOIN Potkategorija AS pk ON pk.IDPotkategorija=p.PotkategorijaID
INNER JOIN Kategorija AS kat ON kat.IDKategorija=pk.IDPotkategorija
GROUP BY kat.Naziv
HAVING avg(p.CijenaBezPDV)<(
                    SELECT
                        avg(CijenaBezPDV)
                    FROM Proizvod AS p1
                        )


----------------------------------------------------------------------------------------------------
-------------------------------------------ISHOD UČENJA 6-------------------------------------------
----------------------------------------------------------------------------------------------------

--Zadatak 1.
SELECT
    k.Ime AS ImeKupca,
    k.Prezime AS PrezimeKupca,
    LEFT(k.Ime, LEN(k.Ime) / 2) + RIGHT(k.Prezime, LEN(k.Prezime) / 2)
FROM Kupac AS k

--Zadatak 2.
SELECT
    r.IDRacun AS IDracun,
    r.KreditnaKarticaID AS KKid,
    CONVERT(VARCHAR, r.IDRacun) + '-' + CONVERT(VARCHAR, r.KreditnaKarticaID) AS Kombinacija
FROM Racun AS r

--Zadatak 3.
SELECT TOP 3
    k.Tip AS TipKartice,
    SUM(s.UkupnaCijena) AS UkupniIznos
FROM Racun AS r
INNER JOIN KreditnaKartica AS k ON k.IDKreditnaKartica = r.KreditnaKarticaID
INNER JOIN Stavka AS s ON s.RacunID = r.IDRacun
WHERE (DAY(r.DatumIzdavanja) BETWEEN 1 AND 7) OR (MONTH(r.DatumIzdavanja) BETWEEN 7 AND 9)
GROUP BY k.Tip
ORDER BY UkupniIznos DESC

--Zadatak 4.
SELECT
    COUNT(k.IDKupac) AS Suma
FROM Potkategorija AS P
FULL OUTER JOIN Proizvod AS pr ON pr.PotkategorijaID = p.IDPotkategorija
FULL OUTER JOIN Stavka AS s ON s.ProizvodID = pr.IDProizvod
FULL OUTER JOIN Racun AS r ON r.IDRacun = s.RacunID
FULL OUTER JOIN Kupac AS k ON k.IDKupac = r.KupacID
WHERE p.Naziv != 'Čarape'

----------------------------------------------------------------------------------------------------
-------------------------------------------ISHOD UČENJA 7-------------------------------------------
----------------------------------------------------------------------------------------------------

--Zadatak 1.
SELECT
    k.Ime AS ImeKomercijalista,
    k.Prezime AS PrezimeKomercijalista
FROM Komercijalist AS k
WHERE
    (
        SELECT
            COUNT(*)
        FROM Racun AS r
        WHERE r.KomercijalistID = k.IDKomercijalist
    ) = 0

--Zadatak 2.
SELECT
    g.Naziv,
    (
        SELECT
            SUM(s.UkupnaCijena)
        FROM Stavka AS s
        WHERE s.RacunID IN
        (
            SELECT
                r.IDRacun
            FROM Racun AS r
            WHERE r.KupacID IN
            (
                SELECT
                    k.IDKupac
                FROM Kupac AS k
                WHERE k.GradID = g.IDGrad
            )
        )
    ) AS Ukupno
FROM Grad AS g
ORDER BY Ukupno DESC

--Zadatak 3.
SELECT
    g.Naziv AS Naziv
FROM Grad AS g
WHERE g.IDGrad IN
    (
        SELECT
            k.GradID
        FROM Kupac AS k
        WHERE k.IDKupac IN
        (
            SELECT
                r.KupacID
            FROM Racun AS r
            WHERE r.KomercijalistID IN
            (
                SELECT
                    ko.IDKomercijalist
                FROM Komercijalist AS ko
                WHERE ko.StalniZaposlenik = 0
            )
        )
    )

--Zadatak 4.
SELECT TOP 5
    p.Naziv AS Naziv,
    (
        SELECT
            SUM(s.Kolicina)
        FROM Stavka AS s
        WHERE s.ProizvodID IN
        (
            SELECT
                pr.IDProizvod
            FROM Proizvod AS pr
            WHERE (pr.PotkategorijaID = p.IDPotkategorija) AND (pr.Boja = 'Crna' OR pr.Boja IS NULL)
        )
    ) AS UkupnaKolicina
FROM Potkategorija AS p
ORDER BY UkupnaKolicina DESC

----------------------------------------------------------------------------------------------------
------------------------------------------------KRAJ------------------------------------------------
----------------------------------------------------------------------------------------------------



-- SVE OSTALO
SELECT
    YEAR(r.DatumIzdavanja) AS 'GodinaIzdavanja',
    COUNT(r.IDRacun) AS 'KolikoRacuna',
    SUM(s.UkupnaCijena) AS 'Promet'
FROM Racun AS r
INNER JOIN Stavka AS s ON s.RacunID = r.IDRacun
WHERE MONTH(r.DatumIzdavanja) = 12
GROUP BY YEAR (r.DatumIzdavanja)

SELECT * FROM Stavka

-- 9. zadatak KRIVO
SELECT Naziv
FROM Kategorija WHERE IDKategorija IN(
SELECT kategorijaID FROM Potkategorija
WHERE IDPotkategorija IN(
SELECT PotkategorijaID FROM Proizvod
GROUP BY PotkategorijaID,CijenaBezPDV
HAVING
CijenaBezPDV<(SUM(CijenaBezPDV)/COUNT(IDProizvod))
))
-- select sum(CijenaBezPDV)/count(IDProizvod) from proizvod