# SQL - NUB vaje
## SOCCER
### 1. Poiščite ime prizorišča in mesto, kjer so igrali finalno tekmo EURO 2016.
```sql=
SELECT soccer_venue.venue_name, soccer_city.city
FROM soccer_venue JOIN soccer_city
ON soccer_venue.city_id = soccer_city.city_id
JOIN match_mast
on match_mast.venue_id = soccer_venue.venue_id
WHERE match_mast.play_stage = "f";
```
### 2. Poiščite število zadetkov, ki jih je dosegla vsaka ekipa po rednem delu tekem.
```sql=
```
### 3. Izpišite lestvico strelcev po rednem delu tekem.
```sql=
```
### 4. Poiščite najboljšega strelca EURA 2016.
```sql=
SELECT player_name, COUNT(*) 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.
```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
JOIN soccer_country.country_id = player_mast.team_id
ON soccer_country.country_id =
WHERE
```
### 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
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 referee_mast
JOIN referee_mast ON soccer_country.country_id = referee_mast.country_id
JOIN match_mast.referee_id ON match_mast.referee_id = referee_mast.referee_id
WHERE match_mast.match_no = (SELECT MAX(match_no) FROM match_mast)
```
## ŠPORT
### 1. Izpišite priimek, ime in oddelek za vse zaposlene. Izpis uredi naraščajoče po abecednem redu priimkov zaposlenih
```sql=
SELECT zaposleni.Priimek, zaposleni.Ime, 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.Priimek, zaposleni.Ime, 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.Priimek, zaposleni.Ime, zaposleni.telefon FROM zaposleni
JOIN oddelki
ON zaposleni.id_oddelek = oddelki.ID_Oddelek
WHERE zaposleni.priimek = ''Kovač AND zaposleni.ime = 'Anton';
```
## 1.TEST (preverjanje)
## A)
### 1. Izpišite vse glasbenike, urejene po abecednem vrstnem redu. 1t
```sql=
SELECT ime, priimek FROM glasbeniki
ORDER BY ime ASC;
```
### 2. Izpišite vse bande in število let, ki so pretekla od njihovega nastanka. 2t
```sql=
SELECT ime, YEAR(NOW())-leto_ustanovitve FROM bandi;
```
### 3. V katerih bandih je zabeležen le en član? 2t
```sql=
SELECT count(glasbeniki.ID_banda), bandi.ime FROM glasbeniki
JOIN bandi ON bandi.ID_banda = glasbeniki.ID_banda
GROUP BY glasbeniki.ID_banda
HAVING COUNT(glasbeniki.ID_banda)=1;
```
### 4. Ali obstaja glasbenik, ki nima zabeležene nobene vloge v bandu? 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek FROM glasbeniki
LEFT JOIN glasbeniki_vloge ON glasbeniki_vloge.ID_glasbenika=glasbeniki.ID_glasbenika
WHERE glasbeniki_vloge.ID_glasbenika IS NULL;
```
### 5. Kateri glasbenik je najstarejši? 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek, glasbeniki.datum_rojstva FROM glasbeniki
WHERE datum_rojstva=(SELECT MIN(datum_rojstva) FROM glasbeniki);
```
### 6. V katerem bandu je David Gilmour in kakšno vlogo ima? 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek, bandi.ime, vloge.opis from bandi
JOIN glasbeniki ON glasbeniki.ID_banda=bandi.ID_banda
JOIN glasbeniki_vloge ON glasbeniki_vloge.ID_glasbenika=glasbeniki.ID_glasbenika
JOIN vloge ON vloge.ID_vloga_v_bandu=glasbeniki_vloge.ID_vloga_v_bandu
WHERE glasbeniki.ime='David' AND glasbeniki.priimek='Gilmour';
```
### 7. Izpišite število bandov po posameznih žanrih. 2t
```sql=
SELECT COUNT(bandi.ime), zanri.naziv_zanra FROM bandi
RIGHT JOIN zanri ON zanri.ID_zanra=bandi.ID_zanra
GROUP BY zanri.ID_zanra;
```
### 8. Izpiši vse slovenske bobnarje. 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek, vloge.opis, drzave.naziv_drzave FROM glasbeniki
JOIN glasbeniki_vloge ON glasbeniki_vloge.ID_glasbenika=glasbeniki.ID_glasbenika
JOIN vloge ON vloge.ID_vloga_v_bandu=glasbeniki_vloge.ID_vloga_v_bandu
JOIN bandi ON bandi.ID_banda=glasbeniki.ID_banda
JOIN drzave ON drzave.ID_drzava=bandi.ID_drzava
WHERE vloge.opis='bobni' AND drzave.naziv_drzave='Slovenija';
```
## B)
### 1. Izpiši vse angleške bande? 2t
```sql=
SELECT bandi.ime FROM bandi
JOIN drzave ON drzave.ID_drzava=bandi.ID_drzava
WHERE naziv_drzave='Anglija';
```
### 2. Izpišite vse kitariste (ne glede na to, kakšno vrsto kitare igrajo). 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek, vloge.opis FROM glasbeniki
JOIN glasbeniki_vloge ON glasbeniki_vloge.ID_glasbenika=glasbeniki.ID_glasbenika
JOIN vloge ON vloge.ID_vloga_v_bandu=glasbeniki_vloge.ID_vloga_v_bandu
WHERE opis LIKE('%kitara%');
```
### 3. Izpišite vse žanre, urejene po abecedi. 1t
```sql=
SELECT zanri.naziv_zanra FROM zanri
ORDER BY naziv_zanra ASC;
```
### 4. Izpišite države, iz katerih ne prihaja nobena glasbena skupina. 2t
```sql=
SELECT drzave.naziv_drzave FROM drzave
LEFT JOIN bandi ON bandi.ID_drzava=drzave.ID_drzava
WHERE bandi.ID_drzava IS NULL;
```
### 5. Kateri band ima največ izdanih albumov? 2t
```sql=
SELECT bandi.ime FROM bandi
ORDER BY bandi.st_izdanih_albumov DESC
LIMIT 1;
```
### 6. Izpišite vse slovenske pevce? 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek FROM glasbeniki
JOIN bandi ON bandi.ID_banda=glasbeniki.ID_banda
JOIN drzave ON drzave.ID_drzava=bandi.ID_drzava
WHERE naziv_drzave='Slovenija';
```
### 7. Kateri glasbenik se v bandu pojavlja v največ vlogah? 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek, count(glasbeniki_vloge.ID_vloga_v_bandu) from glasbeniki
JOIN glasbeniki_vloge ON glasbeniki_vloge.ID_glasbenika=glasbeniki.ID_glasbenika
GROUP BY glasbeniki_vloge.ID_glasbenika
ORDER BY glasbeniki_vloge.ID_glasbenika desc
LIMIT 1;
```
### 8. Izpiši vse bande in njihove glasbenike. 2t
```sql=
SELECT bandi.ime, glasbeniki.ime, glasbeniki.priimek FROM bandi
JOIN glasbeniki ON glasbeniki.ID_banda=bandi.ID_banda
ORDER by bandi.ID_banda, glasbeniki.ime ASC;
```
## 2. SKLOP
## A)
### 1.V podatkovni zbirki sta tabeli z imenom "nove_države" (prazna) in "države". Prepiši v tabelo "nove_države" vse podatke za evropske države iz tabele "države".
```sql=
CREATE TABLE nove_drzave LIKE `države`;
INSERT INTO nove_drzave
SELECT * FROM `države`
WHERE regija='Evropa';
```
### 2.V tabelo nove_države dodaj državi "Srbija" in "Črna gora"
```sql=
INSERT INTO nove_drzave(država)
VALUES('Srbija'),('Črna gora');
```
### 3.Poišči največjo vrednost v polju "št" in novima državama dodeli naslednji dve zaporedni številki.
```sql=
SELECT MAX(št) FROM nove_drzave;
UPDATE nove_drzave SET št=206
WHERE država = 'Srbija';
UPDATE nove_drzave SET št=207
WHERE država = 'Črna gora';
```
### 4.Na spletu poišči ostale podatke za ti dve državi in popravi podatke.
```sql=
SELECT površina FROM nove_drzave;
UPDATE nove_drzave SET regija='Evropa', površina=77474 , prebivalstvo=7000000 , BDP=51500000000
WHERE država = 'Srbija';
```
### 5.Zbriši državo "Srbija in Črna gora".
```sql=
DELETE FROM nove_drzave
WHERE država='Srbija in Črna gora';
```
### 6.V tabeli nove_države pretvori površino iz kvadratnih kilometrov v kvadratne milje. (1 kvadratni kilometer = 0.386102159 kvadratne milje, obratno 2.58998811)
```sql=
UPDATE nove_drzave SET površina = površina * 0.386102159;
```
### 7.V tabelo nove_države dodaj državo "Indija Koromandija" iz regije "Nije", ki ima eno kvadratno miljo in pet prebivalcev.
```sql=
INSERT INTO nove_drzave(država, regija, `površina`, prebivalstvo)
VALUES('Indija','nije',1,5);
```
### 8.V tabelo nove_države dodaj vse države iz tabele države, imajo površino manjšo kot 20000 kvadratnih kilometrov. Hkrati pretvori kvadratne kilometre v kvadratne milje.
```sql=
INSERT INTO nove_drzave
SELECT št,država,regija,površina*0.386102159, prebivalstvo, BDP FROM `države`
WHERE površina < 20000 AND regija != 'Evropa';
```
### 9.V tabeli nove_države za 100000 znižaj BDP vsem državam, ki imajo manj kot 3000000 prebivalcev.
```sql=
UPDATE nove_drzave SET BDP = BDP-10000
WHERE prebivalstvo < 3000000;
```
### 10.V tabeli nove_države povečaj prebivalstvo za 10% državam, ki se začnejo na črko A.
```sql=
UPDATE nove_drzave
SET BDP = BDP * 1.1
WHERE država LIKE 'A%';
```
### 11.V tabeli nove_države odstrani države, ki imajo površino med 4000 in 5000 kvadratnih milj.
```sql=
DELETE FROM nove_drzave
WHERE površina BETWEEN 4000 AND 5000;
```
### 12.V tabeli nove_države odstrani vse države, ki imajo BDP večji kot 1000000000.
```sql=
DELETE FROM nove_drzave
WHERE BDP > 1000000000;
```
### 13.V tabeli nove_države odstrani države, ki nimajo znanega BDP-ja.
```sql=
DELETE FROM nove_drzave
WHERE BDP IS NULL;
```
## B)
### 1. Vstavi v tabelo VLOGE naslednje vloge: bas kitara, ritem kitara, vokal, bobni.
```sql=
INSERT INTO vloge
VALUES(10,'orglice'),
(11,'harmonika');
```
### 2. Ustvari index na tabeli GLASBENIKI na artibutih priimek in ime.
```sql=
SHOW INDEX FROM glasba.glasbeniki;
CREATE INDEX glasbeniki_priimekIme
ON glasbeniki(priimek, ime);
```
### 3. Ustvari pogled Zasedbe, ki izpiše vsa imena bandov ter priimke in imena njihovih članov.
```sql=
CREATE VIEW zasedbe AS
SELECT bandi.ime AS 'band', glasbeniki.priimek, glasbeniki.ime
FROM glasbeniki JOIN bandi
ON bandi.ID_banda=glasbeniki.ID_banda
ORDER BY 1,2,3;
```
### 4. Izpišite zadetke pogleda Zasedbe.
```sql=
SELECT * FROM zasedbe;
```
5. Izpišite imena vseh angleških bandov.
6. Ustvarite pogled Vokalisti, ki vrne imena in priimke vseh pevcev.
### 7. Izpišite glasbenika z največ vlogami. (NI DOKONČANO)
```sql=
SELECT glasbeniki.priimek, glasbeniki.ime, COUNT(*)
FROM glasbeniki JOIN glasbeniki_vloge
ON glasbeniki_vloge.ID_glasbenika=glasbeniki.ID_glasbenika
HAVING COUNT(*)
GROUP BY glasbeniki_vloge.ID_glasbenika
HAVING COUNT(*)
ORDER BY 1 desc
LIMIT 1;
```
## C)
### 1. Poišči na spletu poljuben band in vstavi podatke o tem bandu in enem članu v tabele. 2t
```sql=
INSERT INTO bandi
VALUES(6, 'Siddharta',2,1,1995,12);
INSERT INTO glasbeniki
VALUES(11,'Meglič','Tomi','28.2.1977',6);
UPDATE glasbeniki
SET datum_rojstva = '1977-2-28'
WHERE id_glasbenika=11;
INSERT INTO glasbeniki_vloge
VALUES(11,1);
```
2. Ustvari index na tabeli države. 1t
3. Ustvari pogled, ki vrne imena in priimke vseh slovenskih bobnarjev. 2t
4. Izpiši zadetke iz pogleda 3. naloge. 1t
### 5. Izpiši imena bandov, pri katerih je v tabelah zabeležen le en član. 2t
```sql=
SELECT bandi.ime, COUNT(*) AS 'št.glasbenikov'
FROM bandi JOIN glasbeniki
ON glasbeniki.ID_banda = bandi.ID_banda
GROUP BY glasbeniki.ID_banda
HAVING COUNT(*) = 1;
```
### 6. Ustvari pogled Manjkajo_vloge, ki izpiše vse glasbenike, pri katerih nimamo zabeleženo, kakšno vlogo imajo v bandu. 2t
```sql=
SELECT glasbeniki.ime, glasbeniki.priimek
FROM glasbeniki LEFT JOIN glasbeniki_vloge
ON glasbeniki_vloge.ID_glasbenika=glasbeniki.ID_glasbenika
WHERE glasbeniki_vloge.ID_glasbenika IS NULL;
```
7. Zapiši ukaz, ki izpiše vse poglede, ki obstajajo v podatkovni bazi glasba. 1t
8. Izbriši index, ki si ga ustvaril pri 2. nalogi. 1t
9. Vnesi 13.2.1964 za datum rojstva pri glasbeniku Borutu Maroltu. 2t
10. Ustvari pogled Kitaristi, ki vrne vse glasbenike, ki igrajo kitaro (bas ali ritem) 2t
11. Kateri band ima največ glasbenikov in koliko? 2t
12. Izpiši ime banda, ki je najstarejši. 1t
## D)
```
SQL ukazi za ustvarjanje, popravljanje in brisanje tabel ter uvoz in izvoz podatkov
1. Ustvari podatkovno bazo Izleti in v njej tabelo z imenom Gardaland, ki bo vsebovala:
- ID izleta,
- datum izleta,
- število mest na avtobusu,
- število zasedenih mest,
- vrsta izleta. (N- nočni, D-dnevni)
Stolpec ID izleta naj bo primarni ključ.
Vsi stolpci, razen število zasedenih mest, morajo biti pri vnosu zapolnjena (torej not null).
Dodaj omejitev na število mest na avtobusu med 0 in 50, pri vrsti izleta pa naj bo možen samo vnos N ali D.
Poskrbi za kodiranje, ki dovoli vnos šumnikov.
2. Ustvari datoteko Gardaland2020.txt. Vsebina datoteke je sledeča:
1;16.5.2020;45;42;D
2;17.5.2020;45;33;D
3;23.5.2020;42;42;N
4;24.5.2020;60;57;N
5;30.5.2020;45;42;D
6;31.5.2020;45;33;D
7;6.6.2020;42;39;N
8;7.6.2020;30;9;D
9;13.6.2020;45;19;D
10;14.6.2020;45;25;D
11;20.6.2020;42;11;N
12;21.6.2020;30;12;N
13;27.6.2020;45;8;D
14;28.6.2020;45;3;D
15;4.7.2020;42;0;N
16;5.7.2020;30;0;N
17;11.7.2020;45;0;D
18;12.7.2020;45;0;D
19;18.7.2020;42;0;N
20;19.7.2020;30;0;N
Zapiši SQL ukaz, ki uvozi podatke iz datoteke v tabelo podatkovne baze.
3. Dodaj v tabelo še stolpec, ki bo vseboval ceno izleta. Vsem dnevnim izletom napolni ceno 55 eur, nočnim pa 63 eur.
4. Radi bi izvozili podatke iz tabele Gardaland v datoteko ZasedenostIzleta.txt. V vsaki vrstici datoteke naj bodo podatki o enem izletu, posamezna polja naj bodo ločena z navpično črto. Zadnji podatek v vsaki vrstici naj bo procent zasedenosti sedežev na avtobusu.
5. Izbriši stolpec število sedežev v tabeli.
Sintaksa za uvoz in izvoz podatkov:
LOAD DATA INFILE ime_datoteke INTO TABLE ime_tabele
[CHARACTER SET nabor_znakov] [FIELDS TERMINATED BY 'ločilo polj']
[LINES TERMINATED BY 'ločilo vrstic'] [IGNORE število LINES];
SELECT *
FROM ime_tabele
INTO OUTFILE ime_datoteke
[CHARACTER SET nabor_znakov]
[FIELDS TERMINATED BY 'ločilo polj']
[LINES TERMINATED BY 'ločilo vrstic'];
```
```sql=
DROP DATABASE IF EXISTS Izleti;
CREATE DATABASE IF NOT EXISTS Izleti;
USE Izleti;
CREATE TABLE Gardaland (
ID INT PRIMARY KEY,
Datum_izleta DATE NOT NULL,
St_mest INT NOT NULL,
Zasedeno INT,
Vrsta_izleta ENUM('N','D')
);
'
CREATE TABLE Gardaland (
ID INT NOT NULL AUTO_INCEREMENT,
Datum_izleta DATE NOT NULL,
St_mest INT NOT NULL,
Zasedeno INT,
Vrsta_izleta ENUM('N','D')
);
'
LOAD DATA INFILE 'C:\\Users\\Miha\\Documents\\Gardaland.txt'
INTO TABLE Gardaland
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n';
```
## E)
### 1. Ustvari tabelo Osebe s sledečimi polji: ID, ime, priimek, rojstni_dan, spol.
```sql=
CREATE TABLE osebe LIKE rojstni_dnevi;
```
### 2. Prenesi datoteko in zaženi ta script, ki ustvari tabelo rojstni_dnevi.
```sql=
SPLETNE UCILNICE
```
### 3. Iz tabele rojstni_dnevi prenesi v tabelo Osebe vse osebe, ki so
```sql=
INSERT INTO osebe (ime, priimek, rojstni_dan, spol)
SELECT ime, priimek, rojstni_dan, spol FROM rojstni_dnevi
WHERE rojstni_dan BETWEEN '1901-01-01' AND '2000-12-31'; # *
TRUNCATE osebe; #IZBRIŠE VSE V TABELI
```
4. Izpiši število moških in število žensk.
5. Dodaj v tabelo Osebe stolpec "stevilo_otrok".
6. Poljubno napolni vrednosti v stolpec "stevilo_otrok" z različnimi vrednostmi od nič do npr. 4.
7. Izpiši starost vseh oseb v obliki leta, meseci, dnevi (npr. 47 let 2 meseca 3 dni)
8. Izpiši vse osebe, ki se jim ime zaključi na črki 'na'.
9. Izpiši vse osebe, ki imajo v priimku vsaj en 'v'.
10. Dodaj v tabelo Osebe dva poljubna zapisa. Podatke si izmisli.
11. Izpiši vse osebe, ki bodo letos praznovale 60 let.
12. Kdo ima rojstni dan na Božič?
13. Izbriši stolpec priimek iz tabele Osebe.
## F) Vaja na Daljavo
### 1. Ustvari tabelo Umetniki s sledečimi polji: ID, ime, priimek, rojstni_dan, spol.
```sql=
DROP DATABASE IF EXISTS vajaNaDaljavo;
CREATE DATABASE IF NOT EXISTS vajaNaDaljavo;
USE vajaNaDaljavo;
CREATE TABLE Umetniki (
id INT NOT NULL AUTO_INCREMENT,
ime VARCHAR(15) DEFAULT NULL,
priimek VARCHAR(15) DEFAULT NULL,
rojstni_dan DATE DEFAULT NULL,
spol ENUM('M','Ž') DEFAULT NULL,
PRIMARY KEY (`id`)
);
```
### 2. Prenesi datoteko in zaženi ta script, ki ustvari tabelo rojstni_dnevi.
```sql=
-- --------------------------------------------------------
-- Host: 127.0.0.1
-- Server version: 5.5.16 - MySQL Community Server (GPL)
-- Server OS: Win32
-- HeidiSQL version: 7.0.0.4189
-- Date/time: 2012-09-06 14:07:42
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Dumping structure for table test2.rojstni_dnevi
CREATE TABLE IF NOT EXISTS `rojstni_dnevi` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ime` varchar(15) DEFAULT NULL,
`priimek` varchar(15) DEFAULT NULL,
`rojstni_dan` date DEFAULT NULL,
`spol` enum('M','Ž') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8;
-- Dumping data for table test2.rojstni_dnevi: ~76 rows (approximately)
/*!40000 ALTER TABLE `rojstni_dnevi` DISABLE KEYS */;
INSERT IGNORE INTO `rojstni_dnevi` (`id`, `ime`, `priimek`, `rojstni_dan`, `spol`) VALUES
(1, 'Svetlana', 'Makarovič', '1939-01-01', 'Ž'),
(2, 'Josip', 'Vandot', '1884-01-15', 'M'),
(3, 'Wayne', 'Gretzky', '1961-01-26', 'M'),
(4, 'José', 'Mourinho', '1963-01-26', 'M'),
(5, 'Zlatko', 'Zahovič', '1971-02-01', 'M'),
(6, 'Valentin', 'Vodnik', '1758-02-03', 'M'),
(7, 'Drew', 'Barrymore', '1975-02-22', 'Ž'),
(8, 'Roger', 'Daltrey', '1944-03-01', 'M'),
(9, 'Michelangelo', 'Buonarroti', '1475-03-06', 'M'),
(10, 'Sharon', 'Stone', '1958-03-10', 'Ž'),
(11, 'Aretha', 'Franklin', '1942-03-25', 'Ž'),
(12, 'Eric', 'Clapton', '1945-03-30', 'M'),
(13, 'Tone', 'Seliškar', '1900-04-01', 'M'),
(14, 'Katarina', 'Medičejska', '1519-04-13', 'Ž'),
(15, 'Samuel', 'Beckett', '1906-04-13', 'M'),
(16, 'Butch', 'Cassidy', '1866-04-13', 'M'),
(17, 'Adolf', 'Hitler', '1889-04-20', 'M'),
(18, 'William', 'Shakespeare', '1564-04-26', 'M'),
(19, 'Tina', 'Maze', '1983-05-02', 'Ž'),
(20, 'Katharine', 'Hepburn', '1907-05-12', 'Ž'),
(21, 'Pete', 'Townshend', '1945-05-19', 'M'),
(22, 'Naomi', 'Campbell', '1970-05-22', 'Ž'),
(23, 'Bob', 'Dylan', '1941-05-24', 'M'),
(24, 'Rafael', 'Nadal', '1986-06-03', 'M'),
(25, 'Jon', 'Lord', '1941-06-09', 'M'),
(26, 'Primož', 'Trubar', '1508-06-09', 'M'),
(27, 'Mary-Kate', 'Olsen', '1986-06-13', 'Ž'),
(28, 'Ashley', 'Olsen', '1986-06-13', 'Ž'),
(29, 'Zoran', 'Predin', '1958-06-16', 'M'),
(30, 'Neca', 'Falk', '1950-06-19', 'Ž'),
(31, 'Lionel', 'Messi', '1987-06-24', 'M'),
(32, 'Carl', 'Lewis', '1961-07-01', 'M'),
(33, 'Beno', 'Udrih', '1982-07-05', 'M'),
(34, 'Ringo', 'Starr', '1940-07-07', 'M'),
(35, 'Harrison', 'Ford', '1942-07-13', 'M'),
(36, 'Fidel', 'Castro', '1926-07-13', 'M'),
(37, 'Mira', 'Mihelič', '1912-07-14', 'Ž'),
(38, 'Monica', 'Lewinsky', '1973-07-23', 'Ž'),
(39, 'Kate', 'Bush', '1958-07-30', 'Ž'),
(40, 'Kathrin', 'Zettel', '1986-08-05', 'Ž'),
(41, 'Katja', 'Koren', '1975-08-06', 'Ž'),
(42, 'Renate', 'Götschl', '1975-08-06', 'Ž'),
(43, 'Mata', 'Hari', '1876-08-07', 'Ž'),
(44, 'Mark', 'Knopfler', '1949-08-12', 'M'),
(45, 'Pete', 'Sampras', '1971-08-12', 'M'),
(46, 'Coco', 'Chanel', '1883-08-19', 'Ž'),
(47, 'Bill', 'Clinton', '1946-08-19', 'M'),
(48, 'Jelka', 'Reichman', '1939-08-23', 'Ž'),
(49, 'Alenka', 'Gotar', '1977-08-23', 'Ž'),
(50, 'Keith', 'Moon', '1946-08-23', 'M'),
(51, 'Claudia', 'Schiffer', '1970-08-25', 'Ž'),
(52, 'Michael', 'Jackson', '1958-08-29', 'M'),
(53, 'Cameron', 'Diaz', '1977-08-30', 'Ž'),
(54, 'Gloria', 'Estefan', '1957-09-01', 'Ž'),
(55, 'Freddie', 'Mercury', '1946-09-05', 'M'),
(56, 'Miša', 'Molk', '1954-09-06', 'Ž'),
(57, 'Raquel', 'Welch', '1940-09-05', 'Ž'),
(58, 'Bruce', 'Springsteen', '1949-09-23', 'M'),
(59, 'Ciril', 'Kosmač', '1910-09-28', 'M'),
(60, 'John', 'Entwistle', '1944-10-09', 'M'),
(61, 'Bill', 'Gates', '1955-10-28', 'M'),
(62, 'Julia', 'Roberts', '1967-10-28', 'Ž'),
(63, 'Ana', 'Ivanović', '1987-11-06', 'Ž'),
(64, 'Indira', 'Gandhi', '1917-11-19', 'Ž'),
(65, 'Tina', 'Turner', '1939-11-26', 'Ž'),
(66, 'Jimi', 'Hendrix', '1942-11-27', 'M'),
(67, 'Jim', 'Morrison', '1943-12-08', 'M'),
(68, 'Steve', 'Buscemi', '1957-12-13', 'M'),
(69, 'Steven', 'Spielberg', '1947-12-18', 'M'),
(70, 'Ivana', 'Kobilca', '1861-12-20', 'Ž'),
(71, 'Petra', 'Majdič', '1979-12-22', 'Ž'),
(72, 'Humphrey', 'Bogart', '1899-12-25', 'M'),
(73, 'Gérard', 'Depardieu', '1948-12-27', 'M'),
(74, 'Berti', 'Vogts', '1946-12-30', 'M'),
(75, 'Patti', 'Smith', '1946-12-30', 'Ž'),
(76, 'Alex', 'Ferguson', '1941-12-31', 'M');
/*!40000 ALTER TABLE `rojstni_dnevi` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
```
### 3. Iz tabele rojstni_dnevi prenesi v tabelo Umetniki vse osebe, ki se NISO rodile v mesecu maju.
```sql=
INSERT INTO Umetniki (ime, priimek, rojstni_dan, spol)
SELECT ime, priimek, rojstni_dan, spol
FROM rojstni_dnevi
WHERE MONTH(rojstni_dan) !=5 OR rojstni_dan IS NULL;
```
### 4. Ugotovi in izpiši število žensk iz tabele Umetniki. Koliko je potem moških?
```sql=
SELECT 'Ženske' AS spol, COUNT(*) AS stevilo
FROM umetniki
WHERE spol = 'Ž'
UNION ALL
SELECT 'Moški' AS spol, COUNT(*) AS stevilo
FROM umetniki
WHERE spol = 'M';
```
### 5. Dodaj v tabelo Umetniki stolpec "datum_smrti".
```sql=
ALTER TABLE umetniki
ADD COLUMN datum_smrti DATE;
```
### 6. Stolpec "datum_smrti" napolni z ustreznim datumom pri poljubnih treh osebah..
```sql=
UPDATE Umetniki
SET datum_smrti = '1944-07-11'
WHERE ime = 'Josip' AND priimek = 'Vandot';
UPDATE Umetniki
SET datum_smrti = '1819-01-08'
WHERE ime = 'Valentin' AND priimek = 'Vodnik';
UPDATE Umetniki
SET datum_smrti = '1564-02-18'
WHERE ime = 'Michelangelo' AND priimek = 'Buonarroti';
```
### 7. Izpiši ime, priimek in njihovo starost ob smrti pri tistih treh osebah iz 6. naloge.
```sql=
SELECT ime, priimek, TIMESTAMPDIFF(YEAR, rojstni_dan, datum_smrti) AS starost_ob_smrti
FROM umetniki
WHERE (ime = 'Josip' AND priimek = 'Vandot')
OR (ime = 'Valentin' AND priimek = 'Vodnik')
OR (ime = 'Michelangelo' AND priimek = 'Buonarroti');
-- ali
SELECT ime, priimek, YEAR(datum_smrti) - YEAR(rojstni_dan) - (DATE_FORMAT(datum_smrti, '%m%d') < DATE_FORMAT(rojstni_dan, '%m%d')) AS starost_ob_smrti
FROM umetniki
WHERE datum_smrti IS NOT NULL;
```
### 8. Izpiši vse osebe, ki imajo na drugem mestu v priimku črko a.
```sql=
SELECT * FROM Umetniki
WHERE priimek LIKE '_a%';
```
### 9. Izpiši vse osebe, katerih priimek je dvakrat daljši od imena.
```sql=
SELECT * FROM Umetniki
WHERE CHAR_LENGTH(priimek) = 2 * CHAR_LENGTH(ime);
```
### 10. Dodaj v tabelo Umetniki poljuben zapis. Podatke si izmisli.
```sql=
INSERT INTO Umetniki (id,ime, priimek, rojstni_dan, spol, datum_smrti)
VALUES (72,'Elvis', 'Presley', '1935-01-08', 'M', '1977-08-16');
```
### 11. Vsakemu tretjemu zapisu v tabeli rojstni_dnevi popravi priimek na 'Rozman'.
```sql=
UPDATE rojstni_dnevi
SET priimek = 'Rozman'
WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_numFROM rojstni_dnevi)
AS numbered_rows
WHERE row_num % 3 = 0
);
```
### 12. Izpiši osebe, ki bodo letos praznovale 70 let!
```sql=
SELECT * FROM Umetniki
WHERE YEAR(CURRENT_DATE()) - YEAR(rojstni_dan) = 70;
```
### 13. Izbriši stolpec 'priimek' iz tabele Umetniki.
```sql=
ALTER TABLE Umetniki
DROP COLUMN priimek;
```
## G)
### 1. Ustvari podatkovno bazo z imenom Redovalnica.
```sql=
DROP DATABASE IF EXISTS Redovalnica;
CREATE DATABASE IF NOT EXISTS Redovalnica;
USE Redovalnica;
```
### 2.V PB Redovalnica ustvari dve tabeli:
- Dijaki (ID,ime dijaka, priimek dijaka, razred)
ID naj bo primarni ključ, ostali podatki pa ob vnosu ne smejo ostati prazni. Poskrbi še za slovenski nabor znakov.
- Ocene (ID dijaka, ime predmeta, ocena, datum pridobitve ocene)
Ta tabela nima primarnega ključa, je pa ID dijaka tuj ključ. To omejitev lahko ustvariš že ob kreiranju tabele ali pa potem, ko je tabela že ustvarjena.
Stolpec ocena naj ima omejitev CHECK, ki dovoli vnos samo celoštevilskih vrednosti med 1 in 5
Stolpec datum pridobitve ocene naj ima omejitev DEFAULT in sicer trenutni datum.
```sql=
CREATE TABLE Dijaki (
id INT NOT NULL AUTO_INCREMENT,
ime VARCHAR(15),
priimek VARCHAR(15),
razred VARCHAR(15),
PRIMARY KEY (id)
);
CREATE TABLE Ocene (
id_dijaka INT NOT NULL AUTO_INCREMENT,
ime_predmeta VARCHAR(15),
ocena INT,
datum DATE,
FOREIGN KEY (id_dijaka) REFERENCES dijaki(id),
CHECK(ocena>=1 AND ocena<=5)
);
```
### 3. Vnesi v tabelo Dijaki vsaj tri dijake in preveri, kako delujejo omejitve. (Poskusi vnesti zapis brez imena, priimka ali razreda in opazuj, kaj sistem vrne kot napako)
```sql=
```
### 4. Vnesi še nekaj zapisov v tabelo Ocene in preveri:
- Kaj se zgodi, če želiš vnesti ID dijak, ki ga v tabeli Dijaki še ni
- Ali lahko vneseš oceno, ki ni med 1 in 5
- Preveri, če se vnese trenutni datum, če datum pri vnosu pustiš prazen.
```sql=
INSERT INTO Dijaki (id, ime, priimek, razred)
VALUES (1,'Matic','Štucin','3.Rc'),
(2,'Blaž','Potočnik','3.Ra'),
(3,'Van','Voglar','3.Rb');
INSERT INTO Ocene (id_dijaka, ime_predmeta, ocena, datum)
VALUES (1,'slo',5,'2024-01-08'),
(2,'mat',2,'2024-02-24'),
(3,'slo',5,'2024-01-08');
```
## PREVERJANJE DDL, DML in OMEJITVE
### 1. Ustvari podatkovno zbirko 'Nobelova_nagrada' in v njej dve tabeli, ki bosta med seboj povezani:
Nobelovi_nagrajenci, v kateri bodo sledeči stolpci:
ID, ime in priimek nagrajenca, leto prejema nagrade,
ID države, iz katere prihaja ter področje, za katerega je nagrado prejel. (npr. fizika, književnost….)
Poskrbi za primarni ključ in slovenski nabor znakov.
Ime in priimek pri vnosu ne smeta ostati prazna, ID države je tuj ključ, ki se veže v tabelo Drzave.
Drzave, v kateri bosta dva stolpca: ID in ime države.
```sql=
DROP DATABASE IF EXISTS Nobelova_nagrada;
CREATE DATABASE IF NOT EXISTS Nobelova_nagrada;
USE Nobelova_nagrada;
CREATE TABLE Drzave (
ID INT NOT NULL AUTO_INCREMENT,
ime_drzave VARCHAR(50),
PRIMARY KEY (ID)
) DEFAULT CHARSET='utf8';
CREATE TABLE Nobelovi_nagrajenci (
ID INT NOT NULL AUTO_INCREMENT,
ime VARCHAR(50) DEFAULT NULL,
priimek VARCHAR(50) DEFAULT NULL,
leto INT DEFAULT NULL,
drzaveID INT,
podrocje VARCHAR(50),
PRIMARY KEY (ID),
FOREIGN KEY (drzaveID) REFERENCES Drzave(ID)
) DEFAULT CHARSET='utf8';
```
### 2. Dodaj v tabelo tri zapise o Nobelovih nagrajencih zadnjih dveh let.
```sql=
INSERT INTO drzave (ime_drzave)
VALUES ('Madžarska'),
('United States'),
('Norway')
INSERT INTO nobelovi_nagrajenci (ime, priimek, leto, drzaveID, podrocje)
VALUES ('Matic','Štucin',2023,3,'Medicine'),
('Van','Voglar',2023,1,'Physics'),
('Blaž','Potočnik',2022,2,'Physics')
```
### 3. Dodaj v tabelo stolpec datum_rojstva.
```sql=
ALTER TABLE nobelovi_nagrajenci
ADD COLUMN datum_rojstva DATE;
```
### 4. Zapiši v tabelo vsem nagrajencem še njegov datum rojstva.
```sql=
UPDATE nobelovi_nagrajenci
SET datum_rojstva = '1977-12-3'
WHERE ID = 1;
UPDATE nobelovi_nagrajenci
SET datum_rojstva = '1993-5-4'
WHERE ID = 2;
UPDATE nobelovi_nagrajenci
SET datum_rojstva = '1985-10-23'
WHERE ID = 3;
```
### 5. Dodaj omejitev, da bo pri letu prejema nagrade možno vnesti le letošnje ali pretekla leta.
```sql=
ALTER TABLE nobelovi_nagrajenci ADD CONSTRAINT leto CHECK(leto<=2024);
```
### 6. Izpiši vse nagrajence, ki so dobili nagrado s področja fizike.
```sql=
SELECT * FROM nobelovi_nagrajenci
WHERE podrocje = 'Physics';
```
### 7. Izpiši število nagrajencev iz posameznega države.
```sql=
SELECT drzave.ime_drzave, COUNT(*) FROM nobelovi_nagrajenci
JOIN drzave ON nobelovi_nagrajenci.drzaveID = drzave.ID
GROUP BY drzave.ime_drzave;
```
### 8. Kdo je letos dobil Nobelovo nagrado za matematiko?
```sql=
SELECT * FROM nobelovi_nagrajenci
WHERE podrocje = 'Math';
```
### 9. Spremeni širino stolpca področje na 45 znakov.
```sql=
ALTER TABLE nobelovi_nagrajenci CHANGE COLUMN podrocje podrocje VARCHAR(45);
```
### 10. Izbriši stolpec priimek.
```sql=
ALTER TABLE nobelovi_nagrajenci
DROP COLUMN priimek;
```
## PREVERJANJE - DML, DDL, indexi, pogledi, omejitve
### 1. Ustvari dve tabeli: Avtorji in LiterarnaDela. Tabela avtorji naj vsebuje stolpce, ki opisujejo podatke o avtorjih, tabela LiterarnaDela pa stolpce, ki se nanašajo na podatke o knjigah. Glej tabelo Knjige. Poskrbi za primarne in tuje ključe ter podporo za šumnike.
```sql=
USE nub;
DROP TABLE if EXISTS Avtorji;
DROP TABLE if EXISTS Literarna_dela;
CREATE TABLE Avtorji(
ID INT PRIMARY KEY AUTO_INCREMENT,
Priimek VARCHAR(50),
Ime VARCHAR(50)
)DEFAULT CHARSET='UTF8';
CREATE TABLE Literarna_dela(
ID INT PRIMARY KEY AUTO_INCREMENT,
Naslov VARCHAR(50),
St_strani INT,
Cena DECIMAL(6,2),
ID_avtorja INT,
Leto_izdaje INT,
FOREIGN KEY(ID_avtorja) REFERENCES Avtorji(ID)
)DEFAULT CHARSET='UTF8';
```
### Zapiši ukaze, ki v tabeli Avtorji in LiterarnaDela prenesejo ustrezne podatke iz tabele Knjige.
```sql=
INSERT INTO avtorji (priimek, ime)
SELECT distinct priimek_avtorja, ime_avtorja FROM knjige;
INSERT INTO literarna_dela (naslov, st_strani, cena, ID_avtorja, leto_izdaje)
SELECT naslov, strani, cena, avtorji.id, leto FROM knjige
JOIN avtorji ON knjige.ime_avtorja=avtorji.Ime AND knjige.priimek_avtorja=avtorji.Priimek;
SELECT literarna_dela.naslov, avtorji.ime, avtorji.priimek FROM literarna_dela
JOIN avtorji ON literarna_dela.id_avtorja = avtorji.ID;
```
### Pri eni knjigi je napaka v naslovu in sicer namesto 'Jadran Krt' je napisano 'Jandran Krt'. Popravi naslov.
```sql=
UPDATE literarna_dela
SET naslov='Jadran Krt'
WHERE naslov ='Jandran Krt';
```
### V tabelo Knjige dodaj smiseln indeks.
```sql=
CREATE INDEX naslov_leto ON literarna_dela(naslov, leto_izdaje);
SHOW INDEX FROM literarna_dela;
```
### Čemu služijo pogledi v PB?
### Dodaj stolpec narodnost.
```sql=
ALTER TABLE avtorji
ADD COLUMN narodnost VARCHAR(50);
```
### Izbriši vse avtorje z imenom John.
```sql=
DELETE FROM avtorji
where ime = ''
```
### Dodaj omejitev, da cena knjige ne more biti negativna.
```sql=
```
### Izbriši primarni ključ v tabeli Avtorji.
```sql=
```
### Katera knjiga ima največ strani in kdo jo je napisal.
```sql=
```
## ZADNJI TEST)
### Create & drop: database / table:
```sql=
DROP DATABASE IF EXISTS ime_database;
CREATE DATABASE IF NOT ime_database;
USE ime_database;
DROP TABLE if EXISTS table1_ime;
DROP TABLE if EXISTS table2_ime;
CREATE TABLE table1_ime (
ID INT NOT NULL AUTO_INCREMENT,
text_value VARCHAR(50),
PRIMARY KEY (ID)
) DEFAULT CHARSET='utf8';
CREATE TABLE table2_ime (
ID INT NOT NULL AUTO_INCREMENT,
datum DATE,
text_value_ID INT,
ocena INT,
PRIMARY KEY (ID),
FOREIGN KEY (text_value_ID) REFERENCES table1_ime(text_value),
CHECK(ocena>=1 AND ocena<=5)
) DEFAULT CHARSET='utf8';
```
### insert data into a table (different table examples)
```sql=
-- insert data from another table
INSERT INTO table3_ime (priimek, ime)
SELECT DISTINCT priimek_avtorja, ime_avtorja FROM table4_ime;
INSERT INTO original_table (naslov, st_strani, cena, ID_avtorja, leto_izdaje)
SELECT naslov, strani, cena, table3_ime.id, leto FROM table4_ime
JOIN table3_ime ON table4_ime.ime_avtorja=table3_ime.Ime AND table4_ime.priimek_avtorja=table3_ime.Priimek;
SELECT original_table.naslov, table3_ime.ime, table3_ime.priimek FROM original_table
JOIN table3_ime ON original_table.id_avtorja = table3_ime.ID;
--different example
INSERT INTO Umetniki (ime, priimek, rojstni_dan, spol)
SELECT ime, priimek, rojstni_dan, spol
FROM rojstni_dnevi
WHERE MONTH(rojstni_dan) !=5 OR rojstni_dan IS NULL;
-- insert original data
INSERT INTO nobelovi_nagrajenci (ime, priimek, leto, drzaveID, podrocje)
VALUES ('Matic','Štucin',2023,3,'Medicine'),
('Van','Voglar',2023,1,'Physics'),
('Blaž','Potočnik',2022,2,'Physics')
```
### alter table / drop, rename, modify columns / update table
```sql=
ALTER TABLE table_name
ADD column_name datatype;
UPDATE column_name
SET datum_rojstva = '1977-12-3'
WHERE ID = 1;
UPDATE column_name
SET datum_rojstva = '1993-5-4'
WHERE ID = 2;
--corect something
UPDATE literarna_dela
SET naslov='Jadran Krt'
WHERE naslov ='Jandran Krt';
--DELETE
DELETE FROM table_name
WHERE condition;
ALTER TABLE nobelovi_nagrajenci
ADD CONSTRAINT leto CHECK(leto<=2024)
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
-- IF IT DOESN'T WORK WRITE THE COLUMN NAME TWICE (old_name new_name)
```
### create / show index
```sql=
CREATE INDEX index_name
ON table_name (column1, column2, ...);
SHOW INDEX FROM literarna_dela;
```
### drop primary key / foreign key
```sql=
ALTER TABLE table_name
DROP PRIMARY KEY;
ALTER TABLE table_name
DROP FOREIGN KEY FK_PersonOrder;
```
### view
```sql=
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SELECT * FROM view_name;
```
# 4. letnik
## PROCEDURE & FUNCTION)
## Ustvarite proceduro, ki sešteje dve realni števili.
``` sql=
DELIMITER $$
CREATE PROCEDURE calculator(IN x FLOAT, IN y FLOAT, OUT izhod FLOAT)
BEGIN
SET izhod = x + y;
END;
$$
DELIMITER ;
-- koliko je 1/2 + 3
-- @s je spremenljivka
CALL calculator(1.5,3,@s);
SELECT @s;
```
## Ustvarite proceduro, ki sešteje dve realni števili.
```sql=
DELIMITER $$
CREATE FUNCTION funkcija(x FLOAT, y FLOAT)
RETURNS FLOAT
BEGIN
DECLARE izhod FLOAT DEFAULT 0;
SET izhod = x + y;
RETURN izhod;
END;
$$
DELIMITER ;
SELECT funkcija(1.5,3);
```
## izpiši in izračunaj podatke iz tabele
```sql=
SELECT *,funkcija(home_score,away_score) FROM football;
```
## Ustvari funkcijo, ki za izbrani spol vrne število oseb tega izbranega spola iz tabele rojstni dnevi.
```sql=
DELIMITER $$
CREATE FUNCTION st_oseb(x CHAR)
RETURNS INT
BEGIN
DECLARE stevilo INT;
SET stevilo = (SELECT COUNT(*) FROM rojstni_dnevi
WHERE spol = x);
RETURN stevilo;
END;
$$
DELIMITER ;
SELECT st_oseb('ž');
```
## Ustvari proceduro, ki vrne kub podanega števila s pomočjo INOUT.
```sql=
DELIMITER $$
CREATE PROCEDURE kub(INOUT x INT)
BEGIN
SET X=X*X*x;
END;
$$
DELIMITER ;
SET @st = 4;
CALL kub(@st);
SELECT @st;
```
## Preštejemo število oseb tabele rojstni dnevi, ki so rojeni določenega meseca.
```sql=
DELIMITER $$
CREATE PROCEDURE mesec(IN x VARCHAR(10), OUT stev_mesec INT)
BEGIN
SET stev_mesec = (SELECT COUNT(*) FROM rojstni_dnevi
WHERE MONTHNAME(rojstni_dan) = X);
END;
$$
DELIMITER ;
CALL mesec('january',@sm);
SELECT @sm;
```
## IF stavek & DROP FUNCTION)
## Napiši funkcijo, ki vrne absolutno vrednost podanega števila.
```sql=
DROP FUNCTION vrednost;
delimiter $$
CREATE FUNCTION vrednost(x INT)
RETURNS INT
BEGIN
if X < 0 then RETURN -x;
ELSE RETURN x;
END if ;
END;
$$
delimiter;
SELECT vrednost(-13), vrednost(5), vrednost(0);
```
## CASE
## Napiši funkcijo, ki za podano št. meseca izpiše staro ime meseca
```sql=
DROP FUNCTION meseci;
DELIMITER $$
CREATE FUNCTION meseci(x int)
RETURNS VARCHAR(10)
BEGIN
CASE x
WHEN 1 THEN RETURN "prosinec";
WHEN 2 THEN RETURN "svečan";
WHEN 3 THEN RETURN "sušec";
WHEN 4 THEN RETURN "mali traven";
WHEN 5 THEN RETURN "veliki traven";
WHEN 6 THEN RETURN "rožnik";
WHEN 7 THEN RETURN "mali serpen";
WHEN 8 THEN RETURN "veliki srpen";
WHEN 9 THEN RETURN "kimavec";
WHEN 10 THEN RETURN "vinotok";
WHEN 11 THEN RETURN "listopad";
WHEN 12 THEN RETURN "gruden";
END case;
END;
$$
DELIMITER ;
SELECT meseci(6);
SELECT meseci(MONTH(CURDATE()));
```
## Select izpiše mesece rojstnih dni iz tabele
```sql=
SELECT * , meseci(MONTH(rojstni_dan)) FROM rojstni_dnevi;
```
## Napiši proceduro, ki osebam v iz tabel rojstni dnevi zamenja spol
```sql=
DELIMITER $$
CREATE PROCEDURE sp()
BEGIN
UPDATE rojstni_dnevi
SET spol = NULL
WHERE spol = "m";
UPDATE rojstni_dnevi
SET spol = "m"
WHERE spol = "ž";
UPDATE rojstni_dnevi
SET spol = "ž"
WHERE spol IS NULL;
END;
$$
DELIMITER ;
SELECT * FROM rojstni_dnevi;
```
## Ustvari funkcijo, ki preveri če je število sodo ali liho
``` sql=
DELIMITER $$
CREATE FUNCTION preveri(x float)
RETURNS VARCHAR(10)
BEGIN
if X % 2 = 0 then RETURN 'Sodo';
ELSE RETURN 'liho' ;
END if;
END;
$$
DELIMITER ;
SELECT preveri(4);
```
## Ustvari proceduro, ki pretvori temperaturo iz stopinj v farenheit in obratno
``` sql=
delimiter $$
CREATE PROCEDURE con(inout x int, IN temp VARCHAR(1))
BEGIN
if temp = 'C' THEN SET x = (x * 9/5) + 32;
ELSE SET x = (x - 32) / (9/5);
END if;
END;
$$
delimiter ;
SET @t = 1;
CALL con(@t,'F');
SELECT(@t);
```
# ZA DELIMITER JE VEDNO PRESLEDEK !!! ;
## Ustvari funkcijo, ki vrne naključno celo število med izbranima vrodnostma
# RAND()
```sql=
CREATE FUNCTION ran(x INT, y int)
RETURNS INT
return floor(RAND()*(y-x+1))+x;
SELECT ran(1,10);
```
## Ustvari funkcijo, ki vrne vsoto prvih n števil z vsemi tremi zankami
# WHILE
```sql=
delimiter $$
CREATE FUNCTION vsota(n INT)
RETURNS INT
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v INT DEFAULT 0;
while i <= n DO SET v = v + i;
SET i = i + 1;
END while;
RETURN v;
END;
$$
delimiter ;
SELECT vsota(5);
```
# REPEAT
```sql=
delimiter $$
CREATE FUNCTION vsota_r(n INT)
RETURNS INT
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v INT DEFAULT 0;
repeat
SET v = v + i;
SET i = i + 1;
until i > n
END repeat;
RETURN v;
END;
$$
delimiter ;
SELECT vsota_r(5);
```
# LOOP
```sql=
delimiter $$
CREATE FUNCTION vsota_l(n INT)
RETURNS INT
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v INT DEFAULT 0;
oznaka : loop
SET v = v + i;
SET i = i + 1;
if i > n then leave oznaka;
END if;
END loop;
RETURN v;
END;
$$
delimiter ;
SELECT vsota_l(5);
```
## funkcija, ki obrne vrstni red besede
# SUBSTRING(), CONCAT()
```sql=
delimiter $$
CREATE FUNCTION vrstniRed(word VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE izhod VARCHAR(50) DEFAULT '';
DECLARE i INT DEFAULT CHAR_LENGTH(word);
while i > 0 DO
SET izhod = concat(izhod,SUBSTRING(word, i, 1));
SET i = i - 1;
END while;
RETURN izhod;
END;
$$
delimiter ;
SELECT vrstniRed('Slovenija');
```
# !!!! IF je lahko brez ELSE, Case pa ne
## Ustvari funkcijo, ki vrne število besed v stavku.
TA FUNKCIJA NE ŠTEJE BESEDE, KI SO DOLGE SAMO EN ZNAK.
```sql=
DROP FUNCTION besede;
delimiter $$
CREATE FUNCTION besede(stavek VARCHAR(50))
RETURNS int
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE stev INT DEFAULT 1;
while i < CHAR_LENGTH(stavek) DO
if SUBSTRING(stavek,i,1) NOT LIKE ' ' then
if SUBSTRING(stavek,i+1,1) NOT LIKE ' ' then
if SUBSTRING(stavek,i+2,1) LIKE ' ' then
SET stev = stev + 1;
END if;
END if;
END if;
set i = i + 1;
END while;
if SUBSTRING(stavek,CHAR_LENGTH(stavek),1) LIKE ' ' then set stev = stev - 1;
END if;
RETURN stev;
END;
$$
delimiter ;
SELECT besede('aaa bb ccc.');
```
# TRIGGER (, CREATE TABLE LIKE)
CREATE TRIGGER ime
BEFORE / AFTER
INSERT / UPDATE / DELETE
ON ime_tabele
BEGIN
END;
## Ustvari prožilec, ki bo ob brisanju osebe iz tabele rojstni_dnevi to osebo shranil v tabelo rojstni_dnevi_arhiv, ta tabela naj ima enako strukturo kot rojstni_dnevi
```sql=
CREATE TABLE rojstni_dnevi_arhiv LIKE rojstni_dnevi;
CREATE TABLE rd_copy SELECT * FROM rojstni_dnevi;
delimiter $$
CREATE TRIGGER izbris
AFTER DELETE ON rojstni_dnevi
FOR EACH ROW
BEGIN
INSERT INTO rojstni_dnevi_arhiv
VALUES(OLD.id,OLD.ime,OLD.priimek,OLD.rojstni_dan,OLD.spol);
END;
$$
delimiter ;
## napiši stvavek, ki ta prožilec sproži
DELETE FROM rojstni_dnevi
WHERE ime = 'Svetlana';
## izbriši vse rojene v februarju
DELETE FROM rojstni_dnevi
WHERE month(rojstni_dan) = 2;
```
## Tabeli rojstni_dnevi dodajte štiri stolpce, ki bojo beležili uporabnike in čase 1. dodajanja vrstice in posodabljanja vrstice.Ustvarite še pripadajoča prožilca
```sql=
SELECT CURRENT_USER();
ALTER TABLE rojstni_dnevi
ADD COLUMN insert_user VARCHAR(50),
ADD COLUMN insert_datetime DATETIME,
ADD COLUMN update_user VARCHAR(50),
ADD COLUMN update_datetime DATETIME;
delimiter $$
CREATE TRIGGER insert_rd
BEFORE INSERT ON rojstni_dnevi
FOR EACH ROW
BEGIN
SET NEW.insert_user = CURRENT_USER();
SET NEW.insert_datetime = NOW();
END;
$$
delimiter ;
INSERT INTO rojstni_dnevi(ime, priimek)
VALUES('Vili', 'Bašelj');
SELECT * FROM rojstni_dnevi
WHERE ime = 'Vili';
delimiter $$
CREATE TRIGGER insert_up
BEFORE UPDATE ON rojstni_dnevi
FOR EACH ROW
BEGIN
SET NEW.update_user = CURRENT_USER();
SET NEW.update_datetime = NOW();
END;
$$
delimiter ;
UPDATE rojstni_dnevi
SET ime = 'Blaž', priimek = 'Potočnik'
WHERE ime = 'Vili';
SELECT * FROM rojstni_dnevi
WHERE ime = 'Blaž';
```
## Ustvarite prožilec, ki bo ob spremembi imena države iz tabele države naredil ustrezno spremembotudi v tabeli kratice držav.
```sql=
delimiter $$
CREATE TRIGGER update_države
BEFORE UPDATE ON `države`
FOR EACH ROW
BEGIN
UPDATE kratice_držav
SET država = NEW.država
WHERE država = OLD.država;
END;
$$
delimiter ;
UPDATE `države`
SET država = 'Republika Slovenija'
WHERE država = 'Slovenija';
SELECT * FROM kratice_držav
WHERE država = 'Republika Slovenija';
```
# EVENTS
## Ustvari dogodek, ki bop čez 5 minut v tabelo knjige vnesel knjigo z naslovom 'Hvala bogu je petek'
```sql=
SHOW PROCESSLIST;
SET GLOBAL event_scheduler =1; # oz. ON
SELECT NOW()+ INTERVAL 5 minute;
delimiter $$
CREATE EVENT vnos_knjige
ON SCHEDULE AT NOW() + INTERVAL 5 MINUTE DO
BEGIN
INSERT INTO knjige(naslov)
VALUES('Hvala bogu je petek');
END;
$$
delimiter ;
ALTER EVENT vnos_knjige
ON SCHEDULE AT NOW()+ INTERVAL 5 SECOND;
```
## Ustvari dogodek, ki bo vsakih 5 sekund v tabelo knjige vnesel naključno število med 1 in 10. Dogodek naj se začne čez eno minuto, traja eno minuto in naj se ohrani.
```sql=
delimiter $$
CREATE EVENT vnos_knjige
ON SCHEDULE EVERY 10 SECOND
STARTS NOW() + INTERVAL 1 MINUTE
ENDS NOW() + INTERVAL 2 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
INSERT INTO knjige(naslov)
VALUES(FLOOR(RAND()*10+1));
END;
$$
delimiter ;
```
## Ustvari dogodek, ki bo vsako sekundo povečal št. prebivalcev tabele države za promil, dogodek nja traja za 1 min.
```sql=
delimiter $$
CREATE EVENT steje_prebivalstvo
ON SCHEDULE EVERY 1 SECOND
ENDS NOW() + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
UPDATE `države`
SET prebivalstvo = prebivalstvo*1.001;
END;
$$
delimiter ;
```
## ustvari funkcijo, ki izračuna n!
```sql=
DROP FUNCTION factorial;
delimiter $$
CREATE FUNCTION factorial(x int)
RETURNS INT
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE fact INT DEFAULT 1;
while i < x DO
SET fact = fact * i;
SET i = i+1;
END while;
RETURN fact;
END;
$$
delimiter ;
SELECT factorial(5);
delimiter $$
CREATE FUNCTION sinus(x FLOAT)
RETURN FLOAT
BEGIN
RETURN X/1 - POWER(X,3)/factorial(3)+ POWER(X,5)/factorial(5)- POWER(X,7)/factorial(7)+ POWER(X,9)/factorial(9);
END;
$$
delimiter ;
SELECT sinus(PI()/6);
```
^this is not finished
## Utvari funkcijo, ki vrne potenco, osnova naj bo realno število eksponenta pa celo število
```sql=
DROP FUNCTION potenca;
DELIMITER $$
CREATE FUNCTION potenca(os FLOAT, celo int)
RETURNS float
BEGIN
DECLARE vrednost float DEFAULT os;
DECLARE i INT DEFAULT 1;
if celo=0 then SET os = 1;
END if;
if celo>0 then
while i < celo DO
SET os = os*vrednost;
SET i = i + 1;
END WHILE;
END if;
if celo<0 then
SET i = -1;
while i > celo DO
SET os = os*vrednost;
SET i = i - 1;
END WHILE;
SET os = 1/os;
END if;
RETURN os;
END;
$$
DELIMITER ;
SELECT potenca(3,-3);
```
## ustvari funkcijo, ki spremeni podan datum za izbrano število let
```sql=
DELIMITER $$
CREATE FUNCTION datum(d DATETIME, y INT)
RETURNS DATETIME
BEGIN
RETURN d + INTERVAL y YEAR;
END;
$$
DELIMITER ;
SELECT datum(DATE('2024-09-25'), 1 );
```
# 1.PREVERJANJE ZA 1. TEST 2024/2025
-- 1.)
```sql=
DROP PROCEDURE stevilo_usluzbencev;
delimiter $$
CREATE PROCEDURE stevilo_usluzbencev(IN oddelek VARCHAR(50), OUT stev INT)
BEGIN
set stev = (SELECT COUNT(*) FROM usluzbenci
WHERE st_oddelka = oddelek);
if stev = 0 then SET stev = -1;
END if;
END;
$$
delimiter ;
CALL stevilo_usluzbencev('d1',@stev);
SELECT @stev;
CALL stevilo_usluzbencev('d4',@stev);
SELECT @stev;
-- 2.)
delimiter $$
CREATE FUNCTION cas(datum DATE, x INT)
RETURNS DATE
BEGIN
RETURN datum + INTERVAL x YEAR;
END;
$$
delimiter ;
-- B)
SELECT cas(DATE(NOW()), 2);
-- C)
UPDATE delovno_mesto
SET datum_zacetka = cas(datum_zacetka,-5);
-- 3)
DROP TRIGGER insert_oddelek;
delimiter $$
CREATE TRIGGER insert_oddelek
AFTER INSERT ON usluzbenci
FOR EACH ROW
BEGIN
if (SELECT COUNT(*) FROM oddelki WHERE st_oddelka=NEW.st_oddelka) = 0 then
INSERT INTO oddelki (st_oddelka, naziv_oddelka)
VALUES(NEW.st_oddelka,'nov oddelek');
END IF;
END;
$$
delimiter ;
INSERT INTO usluzbenci
VALUES(5467,'matic','štucin','d5');
-- 4.)
-- A)
SET GLOBAL event_scheduler = ON;
DROP EVENT place_datum;
delimiter $$
CREATE EVENT place_datum
ON SCHEDULE
AT NOW() + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
INSERT INTO oddelki (st_oddelka, naziv_oddelka)
VALUES('d7','plače');
ALTER TABLE projekt
ADD COLUMN datum DATE;
END;
$$
delimiter ;
-- B) (Unfinished)
DELIMITER $$
CREATE EVENT aaaa
STARTS EVERY 10 SECOND
ENDS AT NOW() + INTERVAL 2 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
while i < 10 DO
r = FLOOR(RAND()*1000)
INSERT INTO delovno_mesto(st_usluzbenca)
VALUE(r)
END;
$$
DELIMITER ;
```
# 2.PREVERJANJE ZA 1. TEST 2024/2025
```sql=
-- 1.)
DROP PROCEDURE tenis;
delimiter $$
CREATE PROCEDURE tenis(IN dr VARCHAR(50), OUT x INT)
BEGIN
if (SELECT COUNT(*) FROM wta WHERE država = dr) > 0 then
SET x = (SELECT SUM(točke) FROM wta WHERE država = dr);
ELSE
SET x = 0;
END if;
END;
$$
delimiter ;
CALL tenis('SLO',@s);
SELECT @s;
CALL tenis('HRV',@h);
SELECT @h;
-- 2.)
DROP FUNCTION kocka;
delimiter $$
CREATE FUNCTION kocka(a FLOAT)
RETURNS FLOAT
BEGIN
SET a = a * a * a;
RETURN a;
END;
$$
delimiter ;
SELECT kocka(1.5);
SELECT kocka(a) FROM telesa;
-- 3.)
delimiter $$
CREATE TRIGGER brisanje_poste
AFTER DELETE ON `pošte`
FOR EACH ROW
BEGIN
DELETE FROM stranke
WHERE poštna_št = OLD.poštna_št;
END;
$$
delimiter ;
DELETE FROM `pošte`
WHERE poštna_št = 1000;
-- 4.)
SET GLOBAL event_scheduler = ON;
delimiter $$
CREATE EVENT reg
ON SCHEDULE
AT NOW() + INTERVAL 30 SECOND
ON COMPLETION PRESERVE
DO
BEGIN
ALTER TABLE atp
ADD COLUMN regija VARCHAR(50);
DROP TABLE knjige;
END;
$$
delimiter ;
delimiter $$
CREATE EVENT bbbb
ON SCHEDULE EVERY 20 SECOND
ENDS NOW() + INTERVAL 2 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
DECLARE i INT DEFAULT 0;
while i < 20 do
INSERT INTO dogodek
VALUES(FLOOR(RAND()*20+1),DATE(NOW()),TIME(NOW()));
SET i = i+1;
END while;
END;
$$
delimiter ;
```
# vaje
## Ustvari proceduro, ki za izbrano državo vrne skupni procent prebivalcev, ki govori uradni ali neuradni jezik. To pa izberemo preko parametra.
```sql=
DROP PROCEDURE pre;
delimiter $$
CREATE PROCEDURE pre(IN dr VARCHAR(50),IN ur VARCHAR(50), OUT pr FLOAT)
BEGIN
SET pr = (SELECT SUM(countrylanguage.Percentage)
FROM countrylanguage JOIN country
ON countrylanguage.CountryCode = country.`Code`
WHERE country.`Name` = dr AND countrylanguage.IsOfficial = ur);
END;
$$
delimiter ;
CALL pre('Afghanistan','F',@p);
SELECT @p;
```
## Ustvari funkcijo, ki preveri, če se beseda začne in konča na isto črko
```sql
delimiter $$
CREATE FUNCTION beseda(be VARCHAR(50))
RETURNS VARCHAR(50)
BEGIN
DECLARE r VARCHAR(50) DEFAULT '';
if LEFT(be, 1) = RIGHT(be,1) then SET r = 'Beseda se začne in konča na isto črko.';
ELSE SET r = 'Beseda se NE začne in konča na isto črko.';
END if;
RETURN r;
END;
$$
delimiter ;
SELECT beseda('tacocat');
SELECT `Name`, beseda(`Name`) FROM country
WHERE beseda(`Name`) = 'Beseda se začne in konča na isto črko.';
```
# 3.PREVERJANJE ZA 1. TEST 2024/2025
```sql=
-- 1.)
DROP PROCEDURE dr;
delimiter $$
CREATE PROCEDURE dr(IN reg VARCHAR(50),OUT st INT)
BEGIN
if (SELECT COUNT(*) FROM `države` WHERE regija = reg) > 0
then SET st = (SELECT AVG(prebivalstvo) FROM `države` WHERE regija = reg);
ELSE SET st = 0;
END if;
END;
$$
delimiter ;
CALL dr('Oceanija', @st);
SELECT @st;
CALL dr('Balkan', @st);
SELECT @st;
-- 2.)
DROP FUNCTION fun;
delimiter $$
CREATE FUNCTION fun(evro FLOAT)
RETURNS FLOAT
RETURN evro * 239.64;
$$
delimiter ;
SELECT fun(80);
SELECT *, fun(cena) FROM knjige
WHERE leto = 2001;
-- 3.)
delimiter $$
CREATE TRIGGER vnos_osebe
AFTER INSERT ON rojstni_dnevi
FOR EACH ROW
BEGIN
INSERT INTO stranke(ime)
VALUES(NEW.ime);
END;
$$
delimiter ;
INSERT INTO rojstni_dnevi(ime)
VALUES('Vid');
-- 4.)
SET GLOBAL event_scheduler = ON;
delimiter $$
CREATE EVENT eve
ON SCHEDULE EVERY 30 SECOND
ENDS NOW() + INTERVAL 3 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
DECLARE i INT DEFAULT 0;
while i < 3000 DO
INSERT INTO koledar
VALUES(FLOOR(RAND()*30+1), NOW());
set i = i + 1;
END while;
END;
$$
delimiter ;
delimiter $$
CREATE EVENT eve2
ON SCHEDULE
AT NOW() + INTERVAL 2 MINUTE
ON COMPLETION PRESERVE
DO
BEGIN
ALTER TABLE knjige DROP COLUMN Priimek_avtorja;
DROP TABLE pošte;
END;
$$
delimiter ;
```
## vaje
```sql=
-- 1.) PROCEDURA Z VEČ TABELAMI
DROP PROCEDURE goal;
delimiter $$
CREATE PROCEDURE goal(IN drzava VARCHAR(50), OUT str VARCHAR(50))
BEGIN
SET str = (SELECT player_mast.player_name FROM soccer_country
JOIN soccer_team ON
soccer_country.country_id = soccer_team.team_id
JOIN player_mast ON
soccer_team.team_id = player_mast.team_id
JOIN goal_details ON
player_mast.player_id = goal_details.player_id
WHERE soccer_country.country_name = drzava
ORDER BY goal_details.match_no ASC
LIMIT 1);
END;
$$
delimiter ;
CALL goal('Germany',@s);
SELECT @s;
-- 2.) FUNKCIJA
DROP FUNCTION fun;
delimiter $$
CREATE FUNCTION fun(skupaj FLOAT, ddv FLOAT)
RETURNS FLOAT
BEGIN
RETURN skupaj - (skupaj / (1 + ddv / 100));
END;
$$
delimiter ;
SELECT fun(200,22);
SELECT knjige.Naslov, soccer.fun(knjige.Cena,22) FROM knjige
WHERE knjige.Leto > 2000;
SELECT knjige.Naslov, fun(knjige.Cena,22) FROM knjige
WHERE knjige.Leto > 2000;
```
# NOVO POGLAVJE, USER, TRANSACTION...
## (Dvig denarja) VAJA
COMMIT
SAVEPOINT
ROLLBACK - vrne se nazaj (na savepoint)
### sql:
```sql=
SELECT @@autocommit;
SET autocommit = OFF;
-- USTVARI TABELO
CREATE TABLE banka(a INT);
INSERT INTO banka
VALUES(1),(2);
COMMIT;
INSERT INTO banka
VALUES(55),(33);
ROLLBACK;
-- USTVARIMO TOČKO VRNITVE
SAVEPOINT tockavr1;
INSERT INTO banka
VALUES(69),(44);
ROLLBACK TO SAVEPOINT tockavr1;
-- točka vrnitve ne obstaja ob izvajanju DDL ukazov
CREATE TABLE bober( b INT);
```
#### cmd
```cmd=
C:\Users\Uporabnik>cd C:\xampp\mysql\BIN
C:\xampp\mysql\bin>mysql -u root
MariaDB [(none)]> show DATABASES
MariaDB [(none)]> use nub
MariaDB [nub]> show TABLES
MariaDB [nub]> select * from banka;
```
## VAJA
```sql=
-- Ustvarite tabelo imena s stolpcema id, ki je samoštevilo in primarni ključ in ime znakovnega tipa. Tabela naj podpira šumnike.
CREATE TABLE prva(
id INT NOT NULL AUTO_INCREMENT,
ime VARCHAR(50),
PRIMARY KEY (id)
) DEFAULT CHARSET='utf8';
-- Vstavite imeni Maks in Eva ter izpišite vsebino tabele.
INSERT INTO prva (ime)
VALUES('Maks'),('Eva');
COMMIT;
-- Začnite transakcijo.
-- Posodobite ime v Nace, kjer je id enak 1.
UPDATE prva
SET ime = 'Nace'
WHERE id = 1;
COMMIT;
-- Ustvarite mesto vrnitve.
SAVEPOINT tocka1;
-- Posodobite ime v Mica, kjer je id enak 2 ter izpišite vsebino tabele.
UPDATE prva
SET ime = ' Mica'
WHERE id = 2;
-- Zavrzite spremembe do ustvarjenega mesta vrnitve.
ROLLBACK TO tocka1;
-- Izpišite vsebino tabele in zaključite transakcijo.
-- Kateri dve imeni sta na koncu v tabeli?
```
## CMD & STUFF
```cmd=
C:>cd C:\xampp\mysql\bin
C:\xampp\mysql\bin>mysqldump.exe -u backup_user -p -h 194.249.229.81 -P 3305 nub2 > nub2.sql
```
## Nova naloga A)
MAKE A .txt & .bat FILES IN bin FOLDER
ALSO COPY BACKUP FOLDER (Backup2)
.txt
```notepad=
[mysqld]
port=3307
datadir=../backup2
```
.bat
```bat=
mysqld --defaults-file=nova.txt
```
cmd
```cmd=
C:\xampp\mysql\bin>mysqldump -u root --no-data sakila > struktura.sql
C:\xampp\mysql\bin>mysqldump -u root --no-create-info sakila > podatki.sql
C:\xampp\mysql\bin>mysql -u root -P 3307 -e "create database sakila"
C:\xampp\mysql\bin>mysql -u root -P 3307 sakila < struktura.sql
C:\xampp\mysql\bin>mysqldump -u root --no-create-info --skip-triggers sakila > podatki.sql
# izbišemo tqabelo sekila v MySQL
C:\xampp\mysql\bin>mysql -u root -P 3307 -e "create database sakila"
C:\xampp\mysql\bin>mysql -u root -P 3307 sakila < struktura.sql
C:\xampp\mysql\bin>
```
## B)
In xampp folder type cmd at the top
```cmd=
C:\xampp\mysql\bin>mysqldump
C:\xampp\mysql\bin>mysqldump -u root
C:\xampp\mysql\bin>mysqldump -u root --help
C:\xampp\mysql\bin>mysqldump -u root --help>help.txt
C:\xampp\mysql\bin>notepad++ help.txt
C:\xampp\mysql\bin>mysqldump -u root soccer --ignore-table=soccer.soccer_venue --ignore-table=soccer.soccer_city>soccer.sql
C:\xampp\mysql\bin>notepad++ soccer.sql
// kopira vse osebe ki so moški iz tabele rojstni dnevi
C:\xampp\mysql\bin>mysqldump -u root nub rojstni_dnevi --where="spol='m'" > rd.sql
C:\xampp\mysql\bin>notepad++ rd.sql
C:\xampp\mysql\bin>mysqldump -u root nub rojstni_dnevi --where="spol='m'" > rd.xml --xml
C:\xampp\mysql\bin>notepad++ rd.xml
C:\xampp\mysql\bin>mysql --help > mysql.txt
C:\xampp\mysql\bin>notepad++ mysql.txt
C:\xampp\mysql\bin>mysql -u root -e "select * from nub.rojstni_dnevi where spol = 'm'" > rd.html --html
C:\xampp\mysql\bin>notepad++ rd.html
// v html datoteki so lahko šumniki
C:\xampp\mysql\bin>mysql -u root -e "select * from nub.rojstni_dnevi where spol = 'm'" > rd.html --html --default-character-set=utf8
//
C:\xampp\mysql\bin>mysqldump -u root world --tab=.
C:\xampp\mysql\bin>mysqldump -u root world --tab=. --default-character-set=utf8 --fields-terminated-by=";" --lines-terminated-by="\r\n"
```
Datoteke ustvarimo v bin folderju
#### V excelu je nova datoteka z podatki damo jo v bin potem v nub, naredimo tabelo:
sql
```sql=
CREATE TABLE avtomobili
(Id INT NOT NULL,
Znamka VARCHAR(50),
Model VARCHAR(50),
Cena DECIMAL(10,2),
PRIMARY KEY(id))
```
cmd
```cmd=
C:\xampp\mysql\bin>mysqlimport -u root < avtomobili.txt nub --fields-terminated-by=";"
C:\xampp\mysql\bin>mysqlimport -u root nub avtomobili.txt nub --fields-terminated-by=";"
```
## C)
```cmd=
C:\xampp\mysql\bin>mysqldump -u root nub 10000oseb --where="poklic like '%mesar%'" --no-data > struktura.sql
C:\xampp\mysql\bin>mysqldump -u root nub 10000oseb --where="poklic like '%mesar%'" --no-create-info > podatki.sql
C:\xampp\mysql\bin>mysqldump -u root nub 10000oseb --tab=. --fields-terminated-by=","
C:\xampp\mysql\bin>mysqldump --help>help.txt
C:\xampp\mysql\bin>mysqldump -u root --ignore-database=mysql --ignore-database=information-schema --all-databases > baze.txt
C:\xampp\mysql\bin>mysql --help > help2.txt
C:\xampp\mysql\bin>mysql -u root -e "Select * from nub.10000oseb" --html > 10000oseb.html
C:\xampp\mysql\bin>mysql -u root -e "Select * from nub.10000oseb" --html > 10000oseb.html --default-character-set=utf8
```
## preverjanje
``` sql=
SELECT PASSWORD('geslo123');
CREATE USER van
IDENTIFIED BY PASSWORD '*8DF6BFCBEF6E9BFE42E98253C10169831A338059';
GRANT SELECT, INSERT, CREATE, TRIGGER
ON nub TO 'van';
SHOW GRANTS FOR van;
REVOKE INSERT ON nub FROM van;
delimiter $$
CREATE PROCEDURE
BEGIN
END
$$
delimiter
```
# NOVO POGLAVJE | MongoDB
## A) something about mangos
```cpp=
use baza;
show dbs;
db;
db.dijaki.insertOne({Ime:"Silk", Priimek:"Song"});
db.dijaki.insertOne({Ime:"Hollow", Priimek:"Knight"});
db.dijaki.find();
db.createCollection("Omejena2",{capped:true, size:5000,max:3});
```
## B) Knjige
### (cmd first)
```cpp=
use baza;
db.createCollection("Omejena",{capped:true, size:5000, max:3});
for(var i=0;i<5 ;i++){
db.Omejena.insertOne({"st":i});
}
db.Omejena.find();
// limit je 3, (overwrite the first two numbers)
```
### cmd za importanje datotek
```cmd=
C:\Windows\System32>cd C:\Program Files\MongoDB\Server\7.0\bin
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d baza /c knjige1 /type csv /f _id,Priimek,Ime,Naslov,Strani,Cena,Leto < knjige1.csv
2025-01-30T17:46:08.535+0100 connected to: mongodb://localhost/
2025-01-30T17:46:08.557+0100 33 document(s) imported successfully. 0 document(s) failed to import.
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d baza /c knjige2 /jsonArray <knjige2.json
2025-01-30T17:47:46.550+0100 connected to: mongodb://localhost/
2025-01-30T17:47:46.584+0100 33 document(s) imported successfully. 0 document(s) failed to import.
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d baza /c knjige3 /type tsv /f _id,Priimek,Ime,Naslov,Strani,Cena,Leto < knjige3.tsv
2025-01-30T17:50:29.214+0100 connected to: mongodb://localhost/
2025-01-30T17:50:29.241+0100 33 document(s) imported successfully. 0 document(s) failed to import.
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d baza /c knjige4 /type csv /headerline < knjige4.csv
2025-01-30T17:51:27.901+0100 connected to: mongodb://localhost/
2025-01-30T17:51:27.928+0100 33 document(s) imported successfully. 0 document(s) failed to import.
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d baza /c knjige5 < knjige5.json
2025-01-30T17:52:24.476+0100 connected to: mongodb://localhost/
2025-01-30T17:52:24.504+0100 33 document(s) imported successfully. 0 document(s) failed to import.
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d baza /c knjige6 /type tsv /headerline < knjige6.tsv
2025-01-30T17:52:55.597+0100 connected to: mongodb://localhost/
2025-01-30T17:52:55.626+0100 33 document(s) imported successfully. 0 document(s) failed to import.
```
### cmd za exportanje datotek
```cmd=
C:\Program Files\MongoDB\Server\7.0\bin>mongoexport /d baza /c knjige1 > knjige1izvoz.json
2025-01-30T17:56:38.095+0100 connected to: mongodb://localhost/
2025-01-30T17:56:38.111+0100 exported 33 records
C:\Program Files\MongoDB\Server\7.0\bin>mongoexport /d baza /c knjige1 /jsonArray > knjige2izvoz.json
2025-01-30T17:57:43.787+0100 connected to: mongodb://localhost/
2025-01-30T17:57:43.802+0100 exported 33 records
C:\Program Files\MongoDB\Server\7.0\bin>mongoexport /d baza /c knjige1 /type csv /f ime,priimek,naslov,cena,leto > knjige3izvoz.json
2025-01-30T17:59:25.927+0100 connected to: mongodb://localhost/
2025-01-30T17:59:25.942+0100 exported 33 records
```
## C) Countries
### Mongo (cmd first)
```cpp=
db.getCollection("drzave").find({});
db.drzave.find({"name.common":"Slovenia"});
db.drzave.find({"area":{$lt : 21000}}).count();
//države z črko f, IS CASE SENSITIVE!!!
db.drzave.find({"name.common":/F/},{"name.common":1,_id:0}); // namesto 1 je lahko true
//države, ki se začnejo z črko F
db.drzave.find({"name.common":/^F/},{"name.common":1,_id:0});
//države, ki se končajo z črko i
db.drzave.find({"name.common":/i$/},{"name.common":1,_id:0});
//države v Evropi in Afriki
db.drzave.find({"region":{$in:["Europe","Africa"]}});
db.drzave.find({"region":{$in:["Europe","Africa"]}}).count(); //koliko jih je
//je v Evropi ALI Afriki
db.drzave.find({$or:[{"region":"Europe"},{"region":"Africa"}]});
//je v manjše od 21000 IN večje od 20000
db.drzave.find({$and:[{"area":{$lt:21000}},{"area":{$gt:20000}}]});
//dolg pogoj
db.drzave.find({$and:[{"region":"Europe"},{$or:[{"area":{$lt:21000}},{"area":{$gt:20000}}]}]});
```
### cmd
```cmd=
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d baza /c drzave /jsonArray < countries.json
2025-01-30T18:17:33.526+0100 connected to: mongodb://localhost/
2025-01-30T18:17:33.572+0100 250 document(s) imported successfully. 0 document(s) failed to import.
```
## D) Restaurants
```cpp=
db.getCollection("restaurants").find({})
db.restaurants.distinct("cuisine");
db.restaurants.find({name: {$ne:" "}}).sort({name:1});
//limit = kok rezultatov
//idk
db.restaurants.find({name: {$ne:""}},{name:1}).sort({name:1}).limit(1).skip(4);
db.restaurants.updateMany({cuisine:"American"},{$set:{cuisine:"Slovenian"}});
// ne = not equal
db.restaurants.updateMany({name: {$ne:""}},{$unset:{cuisine:1}});
```
## E) Countries
### cmd
```cmd=
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d test /c countries /jsonArray /file:countries.json
```
### Mongo
```cpp=
db.getCollection("countries").find({});
//
db.countries.aggregate([{$group: {_id:"$region",stevilo:{$sum:1}}}]);
//sum velikosti držav v regijah
db.countries.aggregate([{$group: {_id:"$region",skupna_velikost:{$avg:"$area"}}}]);
//avg velikosti držav v regijah
db.countries.aggregate([{$group: {_id:"$region",skupna_velikost:{$avg:"$area"}}}]);
//seznam držav v vseh regijah
db.countries.aggregate([{$group: {_id:"$region",seznam:{$push:"$name.common"}}}]);
//seznam subregij v regijah
db.countries.aggregate([{$group: {_id:"$region",seznam:{$addToSet:"$subregion"}}}]);
//
db.countries.aggregate([{$group: {_id:"$region",seznam:{$addToSet:"$subr egion"}}},{$match:{"$seznam":/M/}}]);
```
## F) Drzave |
### C:\Program Files\MongoDB\Server\7.0\bin
```cpp=
//1.Izpišite vse podatke iz tabele države.
db.getCollection("drzave").find({});
//2.Izpišite vse podatke za Slovenijo.
db.drzave.find({"drzava":"Slovenija"});
//3.Izpišite vse podatke za Slovenijo in Avstrijo.
db.drzave.find({ $or: [ {drzava:"Slovenija"}, {drzava:"Avstrija"}]});
//4.Izpišite vse podatke za Slovenijo, Nizozemsko, Belgijo, Norveško in Španijo.
db.drzave.find({ $or: [ {drzava:"Slovenija"}, {drzava:"Nizozemska"}, {drzava:"Belgija"}, {drzava:"Norveška"}, {drzava:"Španija"}]});
//5.Izpišite imena vseh držav, ki so velike med 10.000 in 30.000 km2. Spisek uredite po abecednem vrstnem redu naraščajoče.
//6.Izpišite imena vseh evropskih držav v bazi. Spisek uredite po abecednem vrstnem redu padajoče.
//7.Izpišite vse podatke o drzavah, katerih ime se prične s črko H.
//8.Izpišite vse podatke o drzavah, ki imajo v regiji besedo Amerika (Južna Amerika, Severne Amerika, Srednja Amerika).
//9.Prikažite ime države ter število prebivalcev na km2 površine države. Spisek uredite enkrat padajoče in enkrat naraščajoče po gostoti prebivalstva. Stolpec, ki prikazuje število prebivalcev na km2, poimenujte »Gostota prebivalstva«.
//10.Prikažite gostoto prebivalstva za Slovenijo. V glavi stolpca naj piše: »Gostota prebivalstva za Slovenijo«.
//11.Prikažite ime države in BDP na prebivalca. Spisek naj bo urejen naraščajoče po BDP-ju na prebivalca. Stolpec, v katerem je prikazana izračunana vrednost, naj se imenuje: »BDP na prebivalca«.
//12.Stolpcu dajte ime »Število evropskih držav v bazi« in izpišite podatke o številu držav, pri katerih je kot regija navedena Evropa.
//13.Koliko je držav z več kot 50 milijoni prebivalcev?
//14.Koliko je skupno število prebivalcev v drzavah, ki so v bazi?
//15.Koliko je skupno število prebivalcev v evropski regiji?
//16.Prikažite skupno število prebivalcev za posamezno regijo. Seznam naj bo urejen padajoče po skupnem številu prebivalcev.
//17.Prikažite vse regije, ki imajo več kot 500.000.000 prebivalcev.
//18.Prikažite skupno število prebivalcev in gostoto prebivalstva na km2 za posamezno regijo. Seznam naj bo urejen padajoče po skupni gostoti prebivalcev.
//19.Prikažite imena držav in BDP na prebivalca za prvih 60 najbogatejših držav.
//20.Pokažite imena držav in BDP na prebivalca za 10 % najbogatejših držav. Najprej izračunajte, koliko je 10 % vseh držav, in rezultat uporabite pri poizvedbi.
//21.Prikažite povprečni BDP na prebivalca po regijah. Spisek naj bo urejen naraščajoče po povprečnem BDP.
//22.Izpišite ime regije in površino najmanjše države za Evropo, Azijo, Afriko in Oceanijo.
//23.Izpišite države in njihove površine za vse države v bazi, pri katerih je površina med polovico in dvakratnikom površine Slovenije.
```
## G)
### cmd
```cmd=
C:\Program Files\MongoDB\Server\7.0\bin>mongofiles /help
C:\Program Files\MongoDB\Server\7.0\bin>mongofiles /d datoteka put mongod.exe mongosh.exe
C:\Program Files\MongoDB\Server\7.0\bin>mongofiles /d datoteka put sampledata.archive
# izpiše vse dodane datoteke
C:\Program Files\MongoDB\Server\7.0\bin>mongofiles /d datoteka list
# začne se na m
C:\Program Files\MongoDB\Server\7.0\bin>mongofiles /d datoteka search m
C:\Program Files\MongoDB\Server\7.0\bin>mongofiles /d datoteka search s
C:\Program Files\MongoDB\Server\7.0\bin>mongofiles /d datoteka search h
```
# H)
### mongo
```cpp=
db.getCollection("countries").find({});
db.countries.drop();
db.countries.find({$and:[{"region":"Europe"},{"capital":{$regex:"e"}}]}).sort({"area":-1}).limit(1).skip(4);
db.countries.find({"name.common":/q$/})
//Iščemo običajni ruski prevod največjke izmed držav, ki ne mejijo na nobeno drugo in imajo valuto ameriški dolar
db.countries.find({$and:[{"borders":[]},{"currency":"USD"}]},{"translations.rus.common":1}).sort({"area":-1}).limit(1);
//Ustvarite kolekcijo “stevila” omejeno z 2MB in 100 dokumenti.
//Napišite trojno zanko.
//Vsaka naj gre med 0 in 99 vključno.
//Prva naj se povečuje za 1, druga za 2 in tretja za 3.
//Znotraj notranje zanke vstavite v kolekcijo stevila razliko produkta prvih dveh indeksov in drugih dveh indeksov.
//Katero število bo prvo izbrisano, ko bomo vnesli nov dokument?
//Katero po vrsti je bilo vstavljeno?
//Obe števili zlepite skupaj
db.createCollection("stevila",{ capped : true, size : 2000000, max : 100 });
for(i=0;i<99;i+1){
for(j=0;j<99;j+2){
for(k=0;k<99;k+3){
}
}
}
```
# I)
## Datoteke najprej daš v bin preden jih importaš
```cmd=
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d test /c knjige1 /type:tsv /headerline /file:knjige1.csv
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d test /c knjige2 /jsonArray /file:knjige2.json
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d test /c knjige3 /type:tsv /headerline /file:knjige3.tsv
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d test /c knjige4 /type:tsv /headerline /file:knjige4.csv
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d test /c knjige5 /file:knjige5.json
C:\Program Files\MongoDB\Server\7.0\bin>mongoimport /d test /c knjige6 /type:tsv /headerline /file:knjige6.tsv
```
```cpp=
db.getCollection("knjige3").find({})
// 1.
// a)
db.knjige3.find({$and:[{"1985":{$gt:1994}},{"1985":{$lt:2006}}]});
// b)
db.knjige3.find({"15.86":{$gt:29}},{"_id":0,"Izbrana lirika":1,"15.86":1}).sort({"15.86":-1});
// c)
db.knjige3.find({"Minatti":/^M/});
// d
db.knjige3.aggregate([{$group:{_id:"$1985",stKnjig:{$sum:1}}},{$sort:{"_id":+1}}]);
// e
db.knjige3.aggregate([{$match:{"1985":2001},{_id:"$1985",povprecnaCena:{$avg:"15.86"}}}]);
```
# J)
```cpp=
```
```cmd=
```
DDL - Data Definition Language
DML - Data Manipulation Language
Sign in with Wallet
Connect another wallet