lab1 i lab4

LAB 1

// 1.

SET AUTOCOMMIT = 0;
BEGIN WORK;
UPDATE rezervacija SET oznRadionica = 'R3' WHERE oznRadionica = 'R2';
DELETE FROM radionica WHERE oznRadionica = 'R2';
COMMIT;
SET AUTOCOMMIT = 1;

// 2.

SET AUTOCOMMIT = 0;
BEGIN WORK;
UPDATE radnik SET sifOdjel = '10' WHERE sifOdjel = '9';
DELETE FROM odjel WHERE sifOdjel = '9';
ROLLBACK WORK;
SET AUTOCOMMIT = 1

// 3.

DELIMITER //
CREATE PROCEDURE radnici_radionica(IN rad VARCHAR(10), OUT ime VARCHAR(20), OUT prez VARCHAR(20), OUT placa INT)
BEGIN
    SELECT imeRadnik, prezimeRadnik, MAX(IznosOsnovice*KoefPlaca) INTO ime, prez, placa FROM radnik NATURAL JOIN odjel NATURAL JOIN kvar NATURAL JOIN rezervacija WHERE oznRadionica = rad;
END; //
DELIMITER ;

CALL radnici_radionica('R3', @a, @b, @c);

// 4.

DELIMITER //
CREATE PROCEDURE broj_naloga_radnika(INOUT var INT)
BEGIN
    SELECT COUNT(*) INTO var FROM radnik NATURAL JOIN nalog WHERE radnik.sifRadnik = var;
END; //
DELIMITER ;

SET @a = 122;
CALL broj_naloga_radnika(@a);
SELECT @a;

// 5.

DELIMITER //
CREATE FUNCTION povecaj_koef(var INT) RETURNS INT
DETERMINISTIC
BEGIN
    UPDATE radnik SET KoefPlaca = KoefPlaca + 0,5 WHERE sifOdjel = var;
    RETURN 1;
END; //
DELIMITER ;

SELECT povecaj_koef(5);

// 6

DELIMITER //
CREATE FUNCTION podatak_radionica(odabraniKlijent INT) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE rad VARCHAR(10);
    SELECT oznRadionica INTO rad FROM radionica NATURAL JOIN rezervacija JOIN nalog ON rezervacija.sifKvar = nalog.sifKvar NATURAL JOIN klijent WHERE sifKlijent = odabraniKlijent;
    RETURN rad;
END; //
DELIMITER ;

SELECT podatak_radionica(1210);



LAB 4

1.

LOCK TABLE kvar READ;
UPDATE kvar SET nazivKvar = "Test1" WHERE sifKvar = 1;
/* u drugoj sesiji nije moguce zakljucati tablicu */
UNLOCK TABLE kvar;

2.

LOCK TABLE kvar WRITE;
/* nije moguce citanje s nove sesije */
UNLOCK TABLE kvar;

3.

CREATE USER "marko"@"localhost" IDENTIFIED BY "lozinka123";

4.

GRANT INSERT ON baza.rezervacija TO "marko"@"localhost";
GRANT DELETE ON baza.rezervacija TO "marko"@"localhost";
GRANT GRANT OPTION ON baza.rezervacija TO "marko"@"localhost";

5.

REVOKE ALL PRIVILEGES ON baza.rezervacija FROM "marko"@"localhost";
DELETE USER "marko"@"localhost";