# **SQL VAJE**
## **Vaja - soccer**
### **1. Poiščite ime prizorišča in mesto, kjer so igrali finalno tekmo EURO 2016.**
```sql=
Select venue_name, city FROM soccer_venue
INNER JOIN soccer_city ON soccer_venue.city_id = soccer_city.city_id
WHERE venue_id = (select venue_id FROM match_mast where play_stage='F');
```
---
### **2. Poiščite število zadetkov, ki jih je dosegla vsaka ekipa po rednem delu tekem.**
```sql=
SELECT soccer_country.country_name, sum(goal_score) as goals_scored
FROM match_details
INNER JOIN soccer_country ON match_details.team_id = soccer_country.country_id
JOIN goal_details
ON match_details.match_no = goal_details.match_no
WHERE goal_details.goal_type = 'N'
GROUP BY soccer_country.country_id
ORDER BY 2 DESC;
```
---
### **3. Izpišite lestvico strelcev po rednem delu tekem.**
```sql=
SELECT player_name, COUNT(*) AS 'število zadetkov' FROM player_mast
JOIN goal_details
ON player_mast.player_id = goal_details.player_id
GROUP BY goal_details.player_id
ORDER BY 2 DESC;
```
---
### **4. Poiščite najboljšega strelca EURA 2016**
```sql= & !
SELECT player_name, COUNT(*) AS 'RANDOM'
FROM player_mast
JOIN goal_details
ON player_mast.player_id = goal_details.player_id
GROUP BY goal_details.player_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM goal_details GROUP BY goal_details.player_id ORDER BY 1 DESC LIMIT 1);
```
---
### **5. Poiščite ime strelca edinega gola v finalu skupaj s svojo državo in številko dresa** - Nedokončano
```sql=
SELECT player_name, soccer_country.country_name, player_mast.jersey_no
FROM player_mast JOIN goal_details
ON player_mast.player_id = goal_details.player_id
```
---
### **6. Poiščite državo, kjer je potekal EURO 2016. Poznate mesta in prizorišča.**
```sql= & !
SELECT soccer_country.country_name, soccer_city.city, soccer_venue.venue_name FROM soccer_country
INNER JOIN soccer_city ON soccer_city.country_id=soccer_country.country_id
JOIN soccer_venue
ON soccer_venue.city_id=soccer_city.city_id;
```
---
### **7. Poiščite igralca, ki je dosegel prvi zadetek na EURU 2016.**
```sql= & !
SELECT player_mast.player_name FROM player_mast
JOIN goal_details ON goal_details.player_id=player_mast.player_id
WHERE goal_details.goal_id=1;
```
---
### **8. Poiščite ime in državo sodnika, ki je vodil uvodno tekmo.**
```sql= & !
SELECT referee_mast.referee_name, soccer_country.country_name FROM soccer_country
JOIN referee_mast ON referee_mast.country_id=soccer_country.country_id
JOIN match_mast ON match_mast.referee_id=referee_mast.referee_id
WHERE match_mast.match_no=(SELECT MIN(match_no) FROM match_mast);
```
## **Vaja - Šport**
### **1. Izpišite priimek, ime in oddelek za vse zaposlene. Izpis uredi naraščajoče po abecednem redu priimkov zaposlenih**
```sql=
SELECT zaposleni.Ime, zaposleni.Priimek, oddelki.Oddelek FROM zaposleni
JOIN oddelki
ON zaposleni.ID_Oddelek = oddelki.ID_Oddelek
ORDER BY 2 ASC;
```
### **2. Izpišite priimke, imena in telefonske številke delavcev, ki delajo v oddelku Dodelava**
```sql=
SELECT zaposleni.Ime, zaposleni.Priimek, zaposleni.Telefon FROM zaposleni
JOIN oddelki
ON zaposleni.ID_oddelek = oddelki.ID_oddelek
WHERE oddelki.Oddelek = 'Dodelava';
```
### **3. V katerem oddelku dela Kovač Anton?**
```sql=
SELECT zaposleni.Ime, zaposleni.Priimek, oddelki.oddelek FROM zaposleni
JOIN oddelki
ON zaposleni.ID_oddelek = oddelki.ID_oddelek
WHERE zaposleni.priimek = 'Kovač' AND zaposleni.ime = 'Anton';
```
### **4. Koliko ljudi je zaposlenih v oddelku Servis?**
```sql=
```
### **5. Izpišite imena oddelkov in število zaposlenih v posameznem oddelku.**
```sql=
```
### **6. Izpišite vse izdelke in njihove cene iz skupine Nahrbtniki.**
```sql=
```
Sign in with Wallet
Connect another wallet