# Upiti nad više tabela
## ER dijagram:
## Upiti:
### 1. Naći proizvod kojeg na lageru ima manje od 600, i čiji opis vrste proizvoda sadrži tekst *official logos and insignias*.
::: info
ℹ️ Uputstvo
Postoji više načina da se rade upiti nad više tabela.
U rješenju ćemo pokazati svaki od njih, ali kasnije ćemo koristiti isključivo ***JOIN***.
:::
::: spoiler Rješenje
1. Za korišćenje više tabela u jednom upitu, moguće je navesti svaku od njih u **FROM** klauzuli, pri čemu u **WHERE** klauzuli moramo navesti vezu između stranog ključa jedne tabele sa primarnim ključem druge tabele.
``` SQL
SELECT *
FROM proizvodi, vrste_proizvoda
WHERE proizvodi.vrsta_proizvoda_id = vrste_proizvoda.id
AND vrste_proizvoda.opis LIKE "%official logos and insignias%";
```
2. Kada navodimo tabele, svakoj od njih možemo dati neki alias (obično prvo slovo) po kojem kasnije pozivamo tu tabelu, da ne bismo svaki put pisali puni naziv.
``` SQL
SELECT *
FROM proizvodi p, vrste_proizvoda vp
WHERE p.vrsta_proizvoda_id = vp.id
AND vp.opis LIKE "%official logos and insignias%";
```
3. Rješenje koje ćemo koristiti u ostalim primjerima je upotrebom ***JOIN***-a. Krećemo od jedne tabele kojoj pridružujemo (***JOIN***) drugu tabelu, pri čemu ponovo moramo navesti na koji način (***ON***) su te dvije tabele povezane.
``` SQL
SELECT *
FROM proizvodi p
JOIN vrste_proizvoda vp ON p.vrsta_proizvoda_id = vp.id
WHERE vp.opis LIKE "%official logos and insignias%";
```
::: warning
⚠️ Napomena
Koji god način da se koristi, u svakom slučaju se podacima iz jedne tabele pridružuju odgovarajući podaci iz druge tabele i time se proširuje početni skup rezultata. Nakon toga se proširena tabela rezultata filtrira u zavisnosti od uslova.
:::
### 2. Naći broj radnika koji imaju kao nadređenog radnika sa emailom *abow@classicmodelcars.com*.
::: info
ℹ️ Uputstvo
Pošto je ovdje riječ o rekurziji, jednoj tabeli pridružujemo ponovo istu tu tabelu. Da bismo ih razlikovali, daćemo im odgovarajuće aliase.
:::
::: spoiler Rješenje
``` SQL
SELECT *
FROM zaposleni zap
JOIN zaposleni nadr ON zap.nadredjeni_id = nadr.id
WHERE nadr.email LIKE "abow@classicmodelcars.com";
```
::: info
ℹ️ U ovom primjeru smo podacima iz tabele zaposleni, koji ima samo ID svog nadređenog, pridružili sve podatke o tom nadređenom (koji je isto zaposleni). Iz tih proširenih rezultata smo filtrirali samo one zaposlene kod kojih nadređeni ima traženi email.
::: warning
⚠️ Napomena
Kada koristimo ***JOIN***, podrazumijeva se ***INNER JOIN***, a osim ***INNER JOIN***-a imamo i ***LEFT JOIN***, ***RIGHT JOIN*** i ***CROSS JOIN***.
* ***(INNER) JOIN*** prikazuje samo one zaposlene koji imaju nadređenog, tj. kojima polje nadredjeni_id nije prazno, tj. one zaposlene koji su nadređeni makar nekome.
* ***LEFT JOIN*** takođe prikazuje sve zaposlene koji imaju nadređenog, ali će prikazati i one koji nemaju nadređenog. Zove se ***LEFT*** jer uz podatke koji imaju vezu između dvije tabele, prikazuje i podatke iz "lijeve" tabele, a to je ona tabela koja je prva pozvana.
* ***RIGHT JOIN*** takođe prikazuje sve zaposlene koji imaju nadređenog, ali će prikazati i one zaposlene koji nisu nadređeni nikome. Zove se ***RIGHT*** jer uz podatke koji imaju vezu između dvije tabele, prikazuje i podatke iz "desne" tabele, a to je ona tabela koja je druga pozvana.
* ***CROSS JOIN*** prikazuje sve zaposlene koji imaju nadređenog, ali i one zaposlene koji nemaju nadređenog, kao i one koji nisu nadređeni nikome.
:::
### 3. Naći najmanji i najveći limit kredita kupaca sa kojima posluje radnik čiji email ima 31 karakter, a koji radi u poslovnici čiji broj telefona počinje sa +1 i čija ulica ne sadrži riječ Street.
::: info
ℹ️ Uputstvo
U ovom slučaju moramo da koristimo 3 tabele - kupci, zaposleni i poslovnice.
:::
::: spoiler Rješenje
``` SQL
SELECT min(k.limit_racuna) as najmanji, max(k.limit_racuna) as najveci
FROM kupci k
JOIN zaposleni z ON z.id = k.odgovorni_zaposleni_id
JOIN poslovnice P on p.id = z.poslovnica_id
WHERE LENGTH(z.email) = 31
AND p.telefon LIKE "+1%"
AND p.adresa NOT LIKE "%street%";
```
:::
### 4. Koliko porudžbina je u kojem statusu?
::: info
ℹ️ Uputstvo
Za brojanje rezultata smo već koristili funkciju ***COUNT()***, ali da bismo brojali *po određenom kriterijumu*, koristimo ***GROUP BY***, da grupišemo rezultate.
:::
::: spoiler Rješenje
``` SQL
SELECT status, count(*) as broj_narudzbi
FROM narudzbe
GROUP BY status;
```
::: warning
⚠️ Napomena
Sve rezultate koje vrati upit
``` SQL
SELECT *
FROM narudzbe;
```
grupišu se pomoću GROUP BY klauzule, a sve agregatne funkcije (sum, count, avg) koje pozivamo u SELECT klauzuli se izvršavaju nad grupisanim podacima, za svaku od tih grupa pojedinačno.
:::
### 5. Koji su sve mogući statusi porudžbi?
::: info
ℹ️ Uputstvo
Možemo koristiti ***GROUP BY***, ali bolje rješenje je ***DISTINCT***, koje vraća samo jedinstvene rezultate za određenu kolonu.
:::
::: spoiler Rješenje
``` SQL
SELECT status
FROM narudzbe
GROUP BY status;
```
ili
``` SQL
SELECT DISTINCT status
FROM narudzbe;
```
:::
### 6. Prikaži ukupna plaćanja po datumu, sa nazivima kolona “Datum” i “Ukupna placanja”.
::: info
ℹ️ Uputstvo
Potrebno je grupisati rezultate po datumu, a onda sabrati sve plaćene iznose za te grupisane rezultate.
:::
::: spoiler Rješenje
``` SQL
SELECT datum as Datum, SUM(iznos) as "Ukupna placanja"
FROM placanja
GROUP BY datum;
```
:::
### 7. Prikaži proizvode naručene u januaru.
::: info
ℹ️ Uputstvo
Potrebno je povezati proizvode i narudzbe preko međutabele proizvodi_narudzbe.
:::
::: spoiler Rješenje
``` SQL
SELECT DISTINCT p.naziv
FROM proizvodi p
JOIN proizvodi_narudzbe pn ON pn.proizvod_id = p.id
JOIN narudzbe n ON n.id = pn.narudzba_id
WHERE MONTH(n.datum_narucivanja) = 1;
```
:::
### 8. Koji proizvod se nalazi na najvise porudžbi?
::: info
ℹ️ Uputstvo
Potrebno je prvo izračunati koliko narudžbi je imao koji proizvod, a onda uzeti samo jedan koji ih ima najviše.
:::
::: spoiler Rješenje
Krećemo od spajanja proizvoda sa narudžbama, jer su to dvije tabele koje će nam trebati u zadatku (proizvodi_narudzbe je treća tabela koju moramo koristiti da bismo povezali ove dvije, iako nam ona ne treba za prikaz).
``` SQL
SELECT *
FROM proizvodi p
JOIN proizvodi_narudzbe pn ON pn.proizvod_id = p.id
JOIN narudzbe n ON n.id = pn.narudzba_id;
```
Nakon toga moramo izračunati koliko narudžbi je bilo za koji proizvod, koristeći ***GROUP BY***.
``` SQL
SELECT p.*, count(n.id) as broj_narudzbi
FROM proizvodi p
JOIN proizvodi_narudzbe pn ON pn.proizvod_id = p.id
JOIN narudzbe n ON n.id = pn.narudzba_id
GROUP BY p.id DESC;
```
Zatim sortiramo rezultate u opadajućem poretku po tom broju narudžbi, i uzimamo samo jedan, najbolji proizvod.
``` SQL
SELECT p.*, count(n.id) as broj_narudzbi
FROM proizvodi p
JOIN proizvodi_narudzbe pn ON pn.proizvod_id = p.id
JOIN narudzbe n ON n.id = pn.narudzba_id
GROUP BY p.id DESC
ORDER BY broj_narudzbi
LIMIT 1;
```
::: warning
⚠️ Napomena
Kada se traži jedno, najbolje ili najgore rješenje, prva pomisao će vam možda biti da koristite ***MIN()*** i ***MAX()*** funkcije.
Te funkcije će svakako vratiti najmanju, tj. najveću vrijednost određene kolone za date rezultate, **ali ukoliko je potrebno osim te same vrijednosti prikazati bilo koji drugi podatak, tu će doći do greške.**
Greška je u tome što ove funkcije izvuku najmanju, tj. najveću vrijednost jedne kolone iz svih rezultata, ali **ostale kolone koje se prikazuju, uzimaće vrijednost prvog reda iz rezultata**, a ne odgovarajućeg reda kako bi trebalo.
:::
### 9. Koji proizvodi imaju manje od 25 porudžbi? Prikazati kod i ime proizvoda.
::: info
ℹ️ Uputstvo
Potrebno je prvo izračunati koliko narudžbi je imao koji proizvod, a onda prikazati samo one koji ih imaju manje od 25.
:::
::: spoiler Rješenje
Krećemo od spajanja proizvoda sa narudžbama, a onda računamo koliko narudžbi je bilo za koji proizvod, koristeći ***GROUP BY***, kao u prethodnom primjeru.
``` SQL
SELECT p.*, count(n.id) as broj_narudzbi
FROM proizvodi p
JOIN proizvodi_narudzbe pn ON pn.proizvod_id = p.id
JOIN narudzbe n ON n.id = pn.narudzba_id
GROUP BY p.id DESC;
```
Dobijene rezultate filtriramo koristeći ***HAVING***.
``` SQL
SELECT p.*, count(n.id) as broj_narudzbi
FROM proizvodi p
JOIN proizvodi_narudzbe pn ON pn.proizvod_id = p.id
JOIN narudzbe n ON n.id = pn.narudzba_id
GROUP BY p.id DESC
HAVING broj_narudzbi < 25;
```
::: warning
⚠️ Napomena
Kako je u zadatku rečeno da se traže proizvodi sa manje od 25 narudžbi, to predstavlja uslov, za koji bi možda pomislili da treba da ide u ***WHERE*** klauzuli.
Razlog zbog kojeg se to ne može tako uraditi jeste što mi broj narudžbi za proizvode dobijamo tek nakon što odradimo ***GROUP BY***, a možete primijetiti da se ***WHERE*** klauzula uvijek piše prije njega, što znači da mi u ***WHERE*** klauzuli još uvijek nemamo taj podatak. Da bismo filtrirali podatke koje dobijamo nakon ***GROUP BY*** (to su uvijek rezultati agregatnih funkcija), koristimo ***HAVING*** koje se piše nakon njega.
:::
### 10. Prikaži ukupan iznos svih porudžbi grupisanih po kupcu. Potrebno je prikazati ime kupca i iznos. Iznos se računa kao količina * cijena.
::: info
ℹ️ Uputstvo
Potrebno je prvo spojiti narudžbe sa odgovarajućim kupcima, a onda sabrati sve iznose narudžbi po kupcu.
Iznos narudžbe se računa kao zbir iznosa (količina * cijena) za svaki proizvod koji se nalazi na toj narudžbi.
:::
::: spoiler Rješenje
``` SQL
SELECT *
FROM narudzbe n
JOIN kupci k ON k.id = n.kupac_id;
```
Iznos se računa iz tabele proizvodi_narudzbe, pa moramo pridružiti i nju.
``` SQL
SELECT k.*, n.*, pn.kolicina * pn.cijena as iznos
FROM narudzbe n
JOIN kupci k ON k.id = n.kupac_id
JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id;
```
Sada grupišemo rezultate po kupcu i sabiramo iznose narudžbi koristeći ***SUM()***.
``` SQL
SELECT k.ime, k.prezime, SUM(pn.kolicina * pn.cijena) as ukupan_iznos
FROM narudzbe n
JOIN kupci k ON k.id = n.kupac_id
JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
GROUP BY k.id;
```
:::
### 11. Prikazati broj i iznose porudžbi koje su otkazane kao ‘Iznos’. Iznos se računa kao količina * cijena.
::: info
ℹ️ Uputstvo
Potrebno je povezati tabele narudzbe i proizvodi_narudzbe, a onda grupisati rezultate po narudžbama i sabrati iznose.
:::
::: spoiler Rješenje
``` SQL
SELECT n.*, pn.cijena * pn.kolicina as iznos
FROM narudzbe n
JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
GROUP BY n.id;
```
:::
### 12. Prikazati sumu iznosa svih porudžbi grupisanih po statusu. Iznos se računa kao količina * cijena.
::: info
ℹ️ Uputstvo
Kao u prethodnom primjeru, samo ne grupišemo po pojedinačnoj narudžbi, nego po statusu narudžbe.
:::
::: spoiler Rješenje
``` SQL
SELECT n.status, pn.cijena * pn.kolicina as iznos
FROM narudzbe n
JOIN proizvodi_narudzbe pn ON pn.narudzba_id = n.id
GROUP BY n.status;
```
:::
Sign in with Wallet
Connect another wallet