b

/* Dohvatite imena i prezimena svih kupaca iz Frankfurta i uz svakog ispišite broj računa (polje
BrojRacuna) koje je platio karticom, od višeg prema nižem. */

select k.IDKupac, k.Ime, k.Prezime, k.GradID, r.BrojRacuna, r.KreditnaKarticaID, r.DatumIzdavanja
    from Kupac as k
    inner join Racun as r
        on r.KupacID=k.IDKupac
    where k.GradID =
    (
        select g.IDGrad
        from Grad as g
        where g.Naziv='Frankfurt'
    )
    and r.KreditnaKarticaID is not null
    order by 1 desc, 5 desc


/* Dohvatite sve boje proizvoda. Uz svaku boju pomoću podupita dohvatite broj proizvoda u toj boji. /*
    select p.Boja,
    (
        select count(pr.IDProizvod)
        from Proizvod as pr
        where pr.Boja=p.Boja
    ) as Broj_proizvoda
    from Proizvod as p
    group by p.Boja
    having p.boja is not null

/* Dohvatite imena i prezimena 5 komercijalista koji su izdali najviše računa. */

select top 5 k.Ime, k.Prezime,
(
    select count(r.IDRacun)
    from racun as r
    where r.KomercijalistID=k.IDKomercijalist
) as Broj_izdanih_racuna
from Komercijalist as k
order by 3 desc

/*ZD1
Ispisite imena komercijalista koji nemaju svojih racuna*/

SELECT distinct k.Ime, k.Prezime
FROM Komercijalist AS k
WHERE k.IDKomercijalist NOT IN
(
    SELECT  r.KomercijalistID
    FROM Racun AS r
    where r.KomercijalistID=k.IDKomercijalist
)

/*ZD2
Ispisite za sve gradove ukupni ostvareni promet(gledano po polju UkupnaCijena u tablici Stavka)
po pojedinom gradu.Rezultat sortirajte padaju�e po ostvarenom prometu.*/

SELECT g.Naziv, (SELECT SUM(s.UkupnaCijena)
) AS OstvareniPromet
FROM Grad AS g

/*1. Ispisite brojku koliko ima proizvoda koji nisu nikad prodani.*/

select count(*)
from Proizvod as p
where p.IDProizvod not in (select s.ProizvodID from Stavka as s)

/*2. Ispisite za sve racune ID racuna, broj racuna i prosjecan iznos prodanih stavki na pojedinom racunu.*/

select r.IDRacun, r.BrojRacuna, (
    select AVG(s.UkupnaCijena)
    from Stavka as s
    where s.RacunID in (
        select r1.IDRacun
        from Racun as r1
        where r1.IDRacun=r.IDRacun)) as Prosjek
from Racun as r

/* ZD3
Ispisite nazive gradova kupaca(bez ponavljanja) kojima se prodavali komercijalisti koji nisu stalno zaposleni.
Zadatak treba rijesiti iskljucivo podupitima, bez spajanja. */

SELECT
g.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
            k.IDKomercijalist
            FROM Komercijalist AS k
            WHERE k.StalniZaposlenik = 0
        )
    )
)





/*3. Ispisite tipove kreditnih kartica (bez ponavljanja) koje su koristene za placanje plavih proizvoda.
   Zadatak treba rijesiti iskljucivo podupitima, bez spajanja.*/

select distinct kk.Tip
from KreditnaKartica as kk
where kk.IDKreditnaKartica in (
    select r.KreditnaKarticaID
    from Racun as r
    where r.IDRacun in (
        select s.RacunID
        from Stavka as s
        where s.ProizvodID in (
        select p.IDProizvod
        from Proizvod as p
        where p.Boja='plava')))

/*4. Ispisite 5 naziva gradova koji imaju najmanju kolicinu prodanih proizvoda koji imaju definiranu boju,
   gledane po polju Kolicina u tablici Stavka. U istom upitu prikazite i ostvaren promet pojedinog prikazanog
   grada (gledano po polju UkupnaCijena u tablici Stavka)*/

select top 5 g.Naziv, (
    select sum(s.Kolicina)
    from Stavka as s
    where s.ProizvodID in (
        select p.IDProizvod
        from Proizvod as p
        where p.Boja is not null and 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)))),
(select sum(s.UkupnaCijena)
from Stavka as s
where s.ProizvodID in (
        select p.IDProizvod
        from Proizvod as p
        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))))
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.IDRacun in (
            select s.RacunID
            from Stavka as s
            where s.ProizvodID in (
                select p.IDProizvod
                from Proizvod as p
                where p.Boja is not null))))
order by (select sum(s.Kolicina)
    from Stavka as s
    where s.ProizvodID in (
        select p.IDProizvod
        from Proizvod as p
        where p.Boja is not null and 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))))

/* Vratite količinu zarađenog novca za svaku boju proizvoda, ali samo za one boje koje su zaradile više od
20.000.000. */

    select p.Boja,
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        inner join Proizvod as pr
            on pr.IDProizvod=s.ProizvodID
        where pr.Boja=p.Boja
    ) as Zaradjeno
    from proizvod as p
    group by p.Boja
    having
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        inner join Proizvod as pr
            on pr.IDProizvod=s.ProizvodID
        where pr.Boja=p.Boja
    ) > 20000000

/* Vratiti sve proizvode koji imaju dodijeljenu potkategoriju i koji su prodani u količini većoj od 5.000. Uz
svaki proizvod vratiti prodanu količinu i naziv kategorije. */

    select p.Naziv, p.IDProizvod
    ,(
        select sum(s.Kolicina)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    ) as Prodana_kolicina,
    (
        select kt.Naziv
        from Potkategorija as pk
        inner join Kategorija as kt
            on kt.IDKategorija=pk.KategorijaID
        where p.PotkategorijaID=pk.IDPotkategorija
    ) as Kategorija
    from proizvod as p
    where p.PotkategorijaID is not null
    and
    (
        select sum(s.Kolicina)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    )>5000

/* Ispišite ukupno zarađene iznose i broj prodanih primjeraka za svaki od ikad prodanih proizvoda. */
    select p.Naziv,
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    ) as Ukupno_zaradjeno,
    (
        select sum(st.kolicina)
        from stavka as st
        where st.ProizvodID=p.IDProizvod
    ) as Broj_prodanih_primjeraka
    from proizvod as p
    where
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    ) is not null
    order by 2 desc

/* Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2.000 primjeraka. */

    select p.Naziv,
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    ) as Ukupno_zaradjeno,
    (
        select sum(st.kolicina)
        from stavka as st
        where st.ProizvodID=p.IDProizvod
    ) as Broj_prodanih_primjeraka
    from proizvod as p
    where
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    ) is not null
    and
    (
        select sum(st.kolicina)
        from stavka as st
        where st.ProizvodID=p.IDProizvod
    )>2000
    order by 3 desc


/* Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2.000 primjeraka ili je
zaradio više od 2.000.000 dolara */
    select p.Naziv,
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    ) as Ukupno_zaradjeno,
    (
        select sum(st.kolicina)
        from stavka as st
        where st.ProizvodID=p.IDProizvod
    ) as Broj_prodanih_primjeraka
    from proizvod as p
    where
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    ) is not null
    and
    (
    (
        select sum(st.kolicina)
        from stavka as st
        where st.ProizvodID=p.IDProizvod
    )>2000
    or
    (
        select sum(s.UkupnaCijena)
        from stavka as s
        where s.ProizvodID=p.IDProizvod
    )>2000000
    )
    order by 3 desc

-- PRIKAZATI KOMERCIJALISTE S IZNADPROSJEÈNIM BROJEM IZDANIH RAÈUNA

select kom.ime, kom.Prezime,
(
    select count(*)
    from racun as r
    where r.KomercijalistID=kom.IDKomercijalist
) as KreiranihRacuna,
(
    select COUNT(*)/COUNT(distinct rn.komercijalistid)
    from racun as rn
    where rn.Komercijalistid is not null
) as ProsjecnoKreiranoRacuna
from komercijalist as kom
where
(
    select count(*)
    from racun as r
    where r.KomercijalistID=kom.IDKomercijalist
)
    >
(
    select COUNT(*)/COUNT(distinct rn.komercijalistid)
    from racun as rn
    where rn.Komercijalistid is not null
)
order by 3 desc

-- 11. Vratite proizvode koji su prodavani u kolièini manjoj od 5.

-- spajanjima
select distinct p.naziv
from proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
where s.Kolicina<5

-- podupitom
select p.Naziv
from Proizvod as p
where exists
(
    select *
    from stavka as s
    where s.ProizvodID=p.IDProizvod
    and s.Kolicina<5
)

-- 7. Dohvatite imena i prezimena 5 najboljih komercijalista, te uz svakog dohvatite i iznos prodane robe.

-- spajanjima
select top 5 kom.ime, kom.prezime, count(distinct r.IDRacun) as BrojIzdanihRaèuna, SUM(s.ukupnacijena) as UkupanIznos
from racun as r
inner join Komercijalist as kom
on kom.IDKomercijalist=r.KomercijalistID
inner join stavka as s
on s.RacunID=r.IDRacun
group by kom.ime, kom.Prezime
order by BrojIzdanihRaèuna desc

-- podupitima
select top 5 k.Ime, k.Prezime,
(
    select COUNT(r.BrojRacuna)
    from Racun as r
    where r.KomercijalistID = k.IDKomercijalist
) as Broj_Izdanih_Racuna,
(
    select SUM(s.UkupnaCijena)
    from Stavka as s
    where s.RacunID in
        (
            select r.IDRacun
            from Racun as r
            where r.KomercijalistID = k.IDKomercijalist
        )
) as Zarada
from Komercijalist as k
order by
(
    select COUNT(r.BrojRacuna)
    from Racun as r
    where r.KomercijalistID = k.IDKomercijalist
) desc
order by BrojIzdanihRaèuna desc