# Podupiti

## ER dijagram:

## Upiti:

### 1. Prikazati broj i status svih nardužbi koje su naručene istog datuma kao i poslednja narudžba.

::: info
ℹ️ Uputstvo

Prvo pronađemo kojeg datuma je napravljena poslednja narudžba, a onda pronađemo sve narudžbe koje su naručene istog tog datuma.
:::

::: spoiler Rješenje
Poslednja narudžba će biti ona koja ima najveći (najkasniji) datum naručivanja.

SELECT MAX(datum_narucivanja)
FROM narudzbe;

Možemo ga pronaći i na ovaj način, ali prvi je kraći, pa ćemo koristiti njega u nastavku.

SELECT datum_narucivanja
FROM narudzbe
ORDER BY datum_narucivanja DESC
LIMIT 1;

Sada tražimo sve narudžbe koje su naručene na isti datum koji će nam vratiti ovaj upit.

SELECT broj_narudzbe, status
FROM narudzbe 
WHERE datum_narucivanja = (
                            SELECT max(datum_narucivanja)
                            FROM narudzbe
                          );

:::

### 2. Prikazati sva plaćanja veća nego prosječna.

::: info
ℹ️ Uputstvo

Prvo moramo pronaći koji je prosječan iznos svih plaćanja, a onda tražiti plaćanja čiji iznos je veći od toga.
:::

::: spoiler Rješenje

SELECT AVG(iznos)
FROM placanja;

Sada tražimo sva plaćanja čiji iznos je veći od ovoga što nam ovaj upit vrati.

SELECT *
FROM placanja
WHERE iznos > (
		SELECT AVG(iznos)
		FROM placanja
              );

:::

### 3. Prikazati sva plaćanja makar duplo veća nego prosječna.

::: info
ℹ️ Uputstvo

Malo izmijeniti prethodni upit.
:::

::: spoiler Rješenje

SELECT *
FROM placanja
WHERE iznos > 2*(
		SELECT AVG(iznos)
		FROM placanja
              );

:::

### 4. Prikazati sve proizvode koji nikad nisu prodati (koristeći podupit).

::: info
ℹ️ Uputstvo

Proivodi koji nisu prodati nikad nisu bili naručeni.
:::

::: spoiler Rješenje
Ne možemo direktno znati koji proizvodi nikad nisu bili naručeni, ali možemo znati koji jesu, pa onda uzimamo sve proizvodi koji su različiti od njih.

SELECT proizvod_id
FROM proizvodi_narudzbe pn;

Proizvodi koji jesu naručeni se mogu pronaći i na sledeći način:

SELECT p.id
FROM proizvodi p
JOIN proizvodi_narudzbe pn ON p.id = pn.proizvod_id;

Sada neki od ova dva upita koristimo da pronađemo koji proizvodi nemaju ID iz tog skupa rezultata.

SELECT *
FROM proizvodi p
WHERE p.id NOT IN (
                    SELECT proizvod_id 
                    FROM proizvodi_narudzbe pn
                  );

ili

SELECT *
FROM proizvodi p
WHERE p.id NOT IN (
                    SELECT p.id
                    FROM proizvodi p
                    JOIN proizvodi_narudzbe pn ON p.id = pn.proizvod_id
                  );

:::

### 5. Prikazati sve proizvode koji nisu prodati (koristeći korelisani podupit)

::: info
ℹ️ Uputstvo

Koristimo operator ***EXISTS*** da provjerimo da li za proizvod postoji neka narudžba na kojoj se on nalazi.
:::

::: spoiler Rješenje

SELECT *
FROM proizvodi p
WHERE NOT EXISTS (
                    SELECT * 
                    FROM proizvodi_narudzbe pn
                    WHERE pn.proizvod_id = p.id
                 );

::: warning
⚠️ Napomena

Korelisani podupiti su podupiti koji u sebi koriste tabele koje su definisane van njih, tj. u glavnom upitu.
U ovom slučaju glavni upit izvršavamo nad tabelom proizvodi, a koristeći ***EXISTS***, za svaki proizvod redom provjeravamo da li postoji ijedan unos u tabeli proizvodi_narudzbe gdje se pojavljajuje ID tog proizvoda iz glavnog upita.
:::

### 6. Prikazati ukupan broj narudžbi uz prosječan iznos tih narudžbi za svakog kupca.

::: info
ℹ️ Uputstvo

Podupiti se mogu nalaziti bilo gdje, ne moraju biti samo u ***WHERE*** klauzuli.
Ovdje koristimo korelisani podupit koji za svakog kupca iz spoljnog upita treba da pronađe i prikaže odgovarajuće podatke.
:::

::: spoiler Rješenje
U ovom primjeru podupiti se nalaze u ***SELECT*** klauzuli.

SELECT 
    k.ime, 
    k.prezime, 
    (
        SELECT count(*)
        FROM narudzbe n
        WHERE n.kupac_id = k.id
    ) as broj_narudzbi,
    (
        SELECT AVG(pn.kolicina * pn.cijena)
        FROM narudzbe n
        JOIN proizvodi_narudzbe pn ON n.id = pn.narudzba_id
        WHERE n.kupac_id = k.id
    ) as prosjecan_iznos
FROM kupci k;

::: warning
⚠️ Napomena

Kod korelisanih podupita ne smijete zaboraviti uslov u ***WHERE*** klauzuli u kojem vežete podatke iz podupita sa nekom tabelom iz glavnog upita.
:::

### 7. Naći broj proizvoda koji imaju cijenu manju ili jednaku iznosu svake pojedinačne narudžbe. Iznos se računa kao količina * cijena.

::: info
ℹ️ Uputstvo

Ovaj zadatak se može raditi na više načina, ali koristićemo operator ***ALL***, da pokažemo kako se upotrebljava. Prvo je potrebno izračunati iznos svake narudžbe, a onda koristeći ***ALL*** upoređivati nabavnu cijenu proizvoda sa iznosom svake narudžbe pojedinačno.
:::

::: spoiler Rješenje
Prvo nalazimo iznos svake narudžbe kao sumu iznosa svih proizvoda koji su za nju vezani.

SELECT sum(pn.kolicina*pn.cijena) AS iznos
FROM narudzbe n
JOIN proizvodi_narudzbe pn ON n.id = pn.narudzba_id
GROUP BY n.id;

Zatim uzimamo podatke iz tabele proizvodi i upoređujemo koristeći ***ALL*** da li je zadovoljen uslov za svaki red iz skupa rezultata.

SELECT *
FROM proizvodi p
WHERE p.nabavna_cijena <= ALL (
                                SELECT SUM(pn.kolicina*pn.cijena) AS iznos
                                FROM narudzbe n
                                JOIN proizvodi_narudzbe pn ON n.id = pn.narudzba_id
                                GROUP BY n.id
                              );

:::

### 8. Pronaći prosjek ukupnog iznosa plaćanja za kupce koji su imali makar 3 plaćanja.

::: info
ℹ️ Uputstvo

Već smo rekli da podupiti mogu biti u bilo kojem dijelu upita.
Svaki upit vraća skup rezultata u vidu tabele, pa nju možemo koristiti da nad njom radimo druge upite.
:::

::: spoiler Rješenje
Prvo moramo izbrojati koliko plaćanja ima svaki kupac.

SELECT k.naziv, COUNT(*) AS broj_placanja
FROM kupci k
JOIN placanja p ON p.kupac_id = k.id
GROUP BY k.id

Dodajemo uslov da uzimamo samo one sa makar 3 plaćanja.

...
HAVING broj_placanja >= 3;

Zatim računamo ukupan iznos plaćanja za ove kupce.

SELECT k.naziv, COUNT(*) AS broj_placanja, SUM(p.iznos) AS ukupan_iznos
FROM kupci k
JOIN placanja p ON p.kupac_id = k.id
GROUP BY k.id
HAVING broj_placanja >= 3;

Sada treba da pronađemo prosjek izračunatih ukupnih iznosa, ali funkcija ***AVG()*** koja to radi se ne može pozvati odmah, jer ne možemo pozivati agregatnu funkciju od agregatne funkcije nad jednim ***GROUP BY***.
Ono što treba uraditi, jeste iskoristiti rezultate ovog upita kao novu tabelu nad kojom upitom izvlačimo prosjek nad kolonom *ukupan_iznos*.

SELECT AVG(izvedena_placanja.ukupan_iznos) AS prosjek
from (
        SELECT k.naziv, COUNT(*) AS broj_placanja, SUM(p.iznos) AS ukupan_iznos
        FROM kupci k
        JOIN placanja p ON p.kupac_id = k.id
        GROUP BY k.id
        HAVING broj_placanja >= 3
     ) AS izvedena_placanja;

:::

### 9. Postaviti opis svih vrsta proizvoda na NULL ukoliko ove godine nije bilo narudžbi za tu vrstu proizvoda.

::: info
ℹ️ Uputstvo

Prvo treba pronaći vrste proizvoda koje nisu bile naručivane ove godine, a onda im izbrisati opis.
Trenutnu godinu nalazimo koristeći ***CURDATE()*** funkciju koja vraća današnji datum.
:::

::: spoiler Rješenje

SELECT DISTINCT vp.id AS vrsta_proizvoda_id
FROM proizvodi p 
JOIN proizvodi_narudzbe pn ON p.id = pn.proizvod_id
JOIN narudzbe n ON n.id = pn.narudzba_id
JOIN vrste_proizvoda vp ON vp.id = p.vrsta_proizvoda_id
WHERE YEAR(n.datum_narucivanja) = YEAR(CURDATE());

Kada pronađemo ID-jeve vrsta proizvoda koje jesu bile naručivane, tražimo one vrste proizvoda koje nisu te.

SELECT *
FROM vrste_proizvoda
WHERE id NOT IN (
                    SELECT DISTINCT vp.id AS vrsta_proizvoda_id
                    FROM proizvodi p 
                    JOIN proizvodi_narudzbe pn ON p.id = pn.proizvod_id
                    JOIN narudzbe n ON n.id = pn.narudzba_id
                    JOIN vrste_proizvoda vp ON vp.id = p.vrsta_proizvoda_id
                    WHERE YEAR(n.datum_narucivanja) = YEAR(CURDATE())
                );

Ako smo sigurni da je upit dobar i da smo pronašli odgovarajuće vrste proizvoda, umjesto ***SELECT*** pišemo ***UPDATE*** upit da izmijenimo opise tih vrsti.

UPDATE vrste_proizvoda
SET opis = NULL
WHERE id NOT IN (
                    SELECT DISTINCT vp.id AS vrsta_proizvoda_id
                    FROM proizvodi p 
                    JOIN proizvodi_narudzbe pn ON p.id = pn.proizvod_id
                    JOIN narudzbe n ON n.id = pn.narudzba_id
                    JOIN vrste_proizvoda vp ON vp.id = p.vrsta_proizvoda_id
                    WHERE YEAR(n.datum_narucivanja) = YEAR(CURDATE())
                );

::: warning
⚠️ Napomena

Kada god se traži ***UPDATE*** ili ***DELETE*** podataka sa nekim uslovom, prvo se koristeći ***SELECT*** pronađu koji su to rezultati koje treba izmijeniti ili obrisati. Tek kad smo sto posto sigurni da smo pronašli to što je trebalo, onda taj ***SELECT*** izmijenimo u ***UPDATE*** ili ***DELETE***, da bismo izbjegli situaciju u kojoj izmijenimo ili obrišemo pogrešne podatke.
:::

### 10. Prikazati kupce sa ukupnim iznosima svih narudžbi većim nego prosječan iznos narudžbi drugih kupaca. Potrebno je prikazati ime kupca i iznos. Iznos se računa kao količina * cijena.

::: info
ℹ️ Uputstvo

Neophodno je prvo izračunati ukupan iznos svih narudžbi za svakog kupca, zatim pronaći prosjek tih narudžbi, a onda pronaći kupce koji imaju iznos veći od tog prosjeka.
:::

::: spoiler Rješenje
Prvo računamo ukupan iznos narudžbi po kupcu.

 SELECT k.id, SUM(pn.kolicina*pn.cijena) AS ukupan_iznos
 FROM kupci k
 JOIN narudzbe n ON n.kupac_id = k.id
 JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
 GROUP BY k.id;

Zatim nalazimo prosjek tih iznosa kao u 8. primjeru.

SELECT AVG(izvedene_narudzbe.ukupan_iznos)
FROM (
        SELECT k.id, SUM(pn.kolicina*pn.cijena) AS ukupan_iznos
         FROM kupci k
         JOIN narudzbe n ON n.kupac_id = k.id
         JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
         GROUP BY k.id
     ) AS izvedene_narudzbe;

Sada ponovo za svakog kupca računamo ukupan iznos i upoređujemo ga sa izračunatim prosjekom.

 SELECT k.ime, sum(pn.kolicina*pn.cijena) AS ukupan_iznos
 FROM kupci k
 JOIN narudzbe n ON n.kupac_id = k.id
 JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
 GROUP BY k.id
 HAVING ukupan_iznos >= (
                            SELECT AVG(izvedene_narudzbe.ukupan_iznos)
                            FROM (
                                    SELECT k.id, SUM(pn.kolicina*pn.cijena) AS ukupan_iznos
                                     FROM kupci k
                                     JOIN narudzbe n ON n.kupac_id = k.id
                                     JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
                                     GROUP BY k.id
                                 ) AS izvedene_narudzbe
                        );

::: warning
⚠️ Napomena

Voditi računa da ukupan iznos po kupcu možemo izračunati samo kada grupišemo podatke i pozovemo agregatnu funkciju ***SUM()***. Agregatne funkcije se pišu ili u ***SELECT*** ili u ***HAVING***, a nikako u ***WHERE*** klauzuli.
Pošto taj iznos dobijamo nakon grupisanja, upoređivanje sa rezultatima podupita moramo raditi u ***HAVING*** dijelu.
:::

### 11. Prikazati sve kupce koji imaju više nego prosječan limit računa od ostalih. Uzeti u obzir samo kupce koji su pravili porudžbine koje imaju više od 17 proizvoda.

::: info
ℹ️ Uputstvo

Prvo pronalazimo koje narudžbe imaju preko 17 proizvoda.
Nakon toga tražimo prosjek limita računa za kupce koji su vlasnici tih narudžbi.
Na kraju tražimo kupce koji imaju limit računa veći od tog izračunatog prosjeka.
:::

::: spoiler Rješenje
Uzimamo ID kupaca za narudžbe koje imaju preko 17 proizvoda.

SELECT n.kupac_id                 
FROM narudzbe n
JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
GROUP BY n.id
HAVING COUNT(pn.proizvod_id) > 17;

Tražimo prosjek limita računa za kupce sa tim ID-jevima.

SELECT AVG(k.limit_racuna) as prosjecan_limit
FROM kupci k
WHERE k.id IN (
                SELECT n.kupac_id                 
                FROM narudzbe n
                JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
                GROUP BY n.id
                HAVING COUNT(pn.proizvod_id) > 17
              );

Na kraju tražimo kupce sa limitom računa većim od tog prosjeka.

SELECT *
FROM kupci k
WHERE k.limit_racuna > (
                        SELECT AVG(k.limit_racuna) as prosjecan_limit
                        FROM kupci k
                        WHERE k.id IN (
                                        SELECT n.kupac_id     
                                        FROM narudzbe n
                                        JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
                                        GROUP BY n.id
                                        HAVING COUNT(pn.proizvod_id) > 17
                                      )
                        );

:::