Advertisement
Domy131097

[LV3] Baze podataka

Apr 4th, 2018
185
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.90 KB | None
  1. //1. zadatak
  2. CREATE TABLE osobeAI4583 (
  3.     ime VARCHAR(15),
  4.     prezime VARCHAR(15),
  5.     jmbg CHAR(10) PRIMARY KEY,
  6.     datum_rodenja DATETIME,
  7.     spol CHAR(1) DEFAULT'M',
  8.     visina DECIMAL(3,2),
  9.     slika IMAGE,
  10.     br_cipela TINYINT,
  11.     CONSTRAINT provjera_brcip CHECK(br_cipela < 60),
  12.     CONSTRAINT provjera_vis CHECK(visina < 255)
  13. )
  14.  
  15.  
  16. INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Dominik', 'Tkalčec', '5564749285', '10/13/1997', 'M', 1.83, 43);
  17. INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Luka', 'Sporcic', '5564749286', '01/13/1997', 'M', 1.76, 44);
  18. INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Ivan', 'Smilovic', '5564749287', '10/19/1998', 'M', 1.93, 43);
  19. INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Krunoslav', 'Kovac', '5564749288', '05/03/1997', 'M', 1.53, 42);
  20. INSERT INTO osobeAI4583(ime, prezime, jmbg, datum_rodenja, spol, visina, br_cipela)VALUES('Drago', 'Gelemanovic', '5564749289', '09/11/1966', 'M', 1.63, 47);
  21.  
  22. //2. zadatak
  23. UPDATE osobeAI4583 SET prezime = 'Luka' WHERE prezime = 'Sporcic';
  24. UPDATE osobeAI4583 SET ime = 'Sporcic' WHERE ime = 'Luka'
  25. UPDATE osobeAI4583 SET br_cipela += 4;
  26. DELETE FROM osobeAI4583 WHERE prezime = 'Smilovic';
  27.  
  28. //3. zadatak
  29. SELECT *FROM student AS s ORDER BY s.datUpis ASC;
  30. SELECT ime, prezime FROM student WHERE datRod < '01/01/1994';
  31.  
  32. //4. zadatak
  33. 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;
  34.  
  35. //5. zadatak
  36. SELECT *FROM mjesto ORDER BY pbr ASC;
  37.  
  38. //6. zadatak
  39. 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;
Advertisement
RAW Paste Data Copied
Advertisement