[LV3] Baze podataka

//1. zadatak
CREATE TABLE osobeAI4583 (
    ime VARCHAR(15),
    prezime VARCHAR(15),
    jmbg CHAR(10) PRIMARY KEY,
    datum_rodenja DATETIME,
    spol CHAR(1) DEFAULT'M',
    visina DECIMAL(3,2),
    slika IMAGE,
    br_cipela TINYINT,
    CONSTRAINT provjera_brcip CHECK(br_cipela < 60),
    CONSTRAINT provjera_vis CHECK(visina < 255)
);


INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Dominik', 'Tkalčec', '5564749285', '10/13/1997', 'M', 1.83, 43);
INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Luka', 'Sporcic', '5564749286', '01/13/1997', 'M', 1.76, 44);
INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Ivan', 'Smilovic', '5564749287', '10/19/1998', 'M', 1.93, 43);
INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Krunoslav', 'Kovac', '5564749288', '05/03/1997', 'M', 1.53, 42);
INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Drago', 'Gelemanovic', '5564749289', '09/11/1966', 'M', 1.63, 47);

//2. zadatak
UPDATE osobeAI4583 SET prezime = 'Luka' WHERE prezime = 'Sporcic';
UPDATE osobeAI4583 SET ime = 'Sporcic' WHERE ime = 'Luka';
UPDATE osobeAI4583 SET br_cipela += 4;
DELETE FROM osobeAI4583 WHERE prezime = 'Smilovic';

//3. zadatak
SELECT *FROM student AS s ORDER BY s.datUpis ASC;
SELECT ime, prezime FROM student WHERE datRod < '01/01/1994';

//4. zadatak
SELECT ime AS 'Ime', prezime AS 'Prezime', naziv AS 'Predmeta' FROM student AS s, predmet AS p, ispit AS i WHERE s.mbr = i.mbrStud AND i.sifPredmeta = p.sifra AND ocjena > 3;

//5. zadatak
SELECT *FROM mjesto ORDER BY pbr ASC;

//6. zadatak
SELECT ime AS 'Ime' , prezime AS 'Prezime', koef AS 'Koeficjent', naziv AS 'Naziv organizacijske jedinice' FROM nastavnici AS n, organizacijska_jedinica AS oj WHERE n.org_jed = oj.id ORDER BY koef DESC;