Baze podataka

LV1

Z1
CREATE TABLE smartphone(
    imei CHAR(15),
    naziv_proiz VARCHAR(20),
    naziv_model VARCHAR(20),
    kol_mem SMALLINT,
    brz_proc DECIMAL(3,2),
    naziv_os VARCHAR(20),
    dat_update DATETIME,
    ime_kor VARCHAR(30),
    prez_kor VARCHAR(30),
    CONSTRAINT smartphone_pk PRIMARY KEY(imei)
)

Z2

CREATE TABLE djelatnik(
    ime VARCHAR(30),
    prezime VARCHAR(30),
    matbr CHAR(13),
    CONSTRAINT djelatnik_pk PRIMARY KEY(matbr)
)

CREATE TABLE projekt(
    oznaka CHAR(10),
    naziv VARCHAR(30),
    CONSTRAINT projekt_pk PRIMARY KEY(oznaka)
)

CREATE TABLE RadiNa(
    matbr CHAR(13),
    oznaka CHAR(10),
    broj_dana SMALLINT,
    CONSTRAINT RadiNa_pk PRIMARY KEY(matbr, oznaka),
    CONSTRAINT RadiNa_fk_projekt FOREIGN KEY(oznaka) REFERENCES projekt(oznaka),
    CONSTRAINT RadiNa_fk_djelatnik FOREIGN KEY(matbr) REFERENCES djelatnik(matbr)
)

Z3

CREATE TABLE grupe(
    id_grupe CHAR(5),
    oznaka_grupe CHAR(6),
    smjer VARCHAR(15),
    broj_studenata TINYINT,
    CONSTRAINT grupe_pk PRIMARY KEY(id_grupe),
    CONSTRAINT grupe_uk UNIQUE(oznaka_grupe)
)

Z4

CREATE TABLE student(
    br_indeksa CHAR(6),
    ime VARCHAR(30),
    prezime VARCHAR(30),
    grupa CHAR(5),
    godina_upisa SMALLINT,
    godina_studija TINYINT,
    CONSTRAINT student_pk PRIMARY KEY(br_indeksa),
    CONSTRAINT student_fk FOREIGN KEY(grupa) REFERENCES grupe(id_grupe)
)

Z5

ALTER TABLE student ADD prosjek DECIMAL(3,2);
ALTER TABLE student ADD CONSTRAINT prosjek_chk CHECK(prosjek>1);
ALTER TABLE student DROP godina_studija;










LV2

Z1

CREATE TABLE osobe(
    ime VARCHAR(30),
    prezime VARCHAR(30),
    jmbg CHAR(13),
    dat_rod DATETIME,
    spol CHAR(1),
    visina DECIMAL(5,2),
    br_cip TINYINT,
    CONSTRAINT osobe_pk PRIMARY KEY(jmbg),
    CONSTRAINT spol_chk CHECK(spol IN('M', 'Z'))
)

INSERT INTO `osobe`(`ime`, `prezime`, `jmbg`, `dat_rod`, `spol`, `visina`, `br_cip`) VALUES
('Marko', 'Markovic', '2012980891234', '1980-12-20', 'M', '170.00', 42),
('Ivan', 'Ivanovic', '1502986891234', '1986-02-15', 'M', '180.00', 45),
('Goran', 'Goric', '0101991891234', '1991-01-01', 'M', '184.00', 44),
('Filip', 'Filipovic', '3008965891234', '1965-08-30', 'M', '181.00', 43),
('Luka', 'Lukic', '2406988891234', '1988-06-24', 'M', '188.00', 46)

Z2

UPDATE osobe SET ime='Nenad', prezime='Nenadic'
WHERE jmbg=1502986891234

UPDATE osobe SET br_cip=br_cip+1;

DELETE FROM osobe WHERE jmbg='0101991891234'

Z3

SELECT *
FROM student
ORDER BY datUpis DESC

SELECT ime, prezime, datRod AS "Datum rodenja"
FROM student
WHERE datRod<'1978-01-01'
ORDER BY prezime

Z4

SELECT ime, prezime, naziv AS "Naziv predmeta"
FROM student, predmet, ispit
WHERE student.mbr=ispit.mbrStud AND ispit.sifPredmeta=predmet.sifra AND ispit.ocjena>=4
















LV3

Z1

SELECT CONCAT(SUBSTRING(ime,1,1), '.', SUBSTRING(prezime,1,1)) AS Inicijali, YEAR(datRod) AS "Godina rodenja"
FROM student

SELECT *
FROM student
ORDER BY datRod ASC

SELECT ime, prezime
FROM student
WHERE spol='F' AND datRod=(SELECT MIN(datRod) FROM student WHERE spol='F')

Z2

SELECT COUNT(mbr) AS "Broj studenata"
FROM student

SELECT COUNT(DISTINCT pbrPreb) AS "Broj mjesta"
FROM student

Z3

SELECT naziv AS "Naziv predmeta", AVG(ocjena) AS Prosjek
FROM ispit, predmet
WHERE ocjena>1 AND ispit.sifPredmeta=predmet.sifra
GROUP BY sifPredmeta

Z4

SELECT ime, prezime, AVG(ocjena) AS Prosjek
FROM ispit, student
WHERE ocjena>1 AND ispit.mbrStud=student.mbr
GROUP BY mbrStud
ORDER BY Prosjek DESC

SELECT mbr
FROM student, ispit
WHERE ispit.mbrStud=student.mbr AND ispit.ocjena > 1
GROUP BY mbrStud
HAVING AVG(ispit.ocjena) > 2.5

SELECT CONCAT(ime, ' ', prezime) AS "Ime i prezime", naziv, ocjena, datIspita
FROM student, ispit, predmet
WHERE ispit.sifPredmeta=predmet.sifra AND ispit.mbrStud=student.mbr


LV4


CREATE FUNCTION palindrom(@string VARCHAR(35))
RETURNS VARCHAR(35)
AS
BEGIN
  DECLARE @temp VARCHAR(35);
  IF LEN(@string)>30
    BEGIN
      RETURN "String je duzi od 30 znakova";
    END
  SET @temp = REVERSE(@string);
  IF @string=@temp
    BEGIN
      RETURN "Palindrom";
    END
  RETURN @temp
END