sql upiti vjezba 10

/* 1. Kreirati bazu podatka sa jednim data i jednim log fajlom.
 Imenovati je Vašim brojem dosijea.
 Prilikom kreiranje baze podataka za data fajl postaviti sljedeće parametre:
Lokacija: D:\DBMS_Data
Veličina: inicijalno 5 MB,  maksimalna veličina neograničena
Uvećanje: 10%

Za Log fajl postaviti sljedeće paramtre:
Lokacija: C:\DBMS_Data\Log
Veličina: inicijalno 2 MB,  maksimalna veličina neograničena
Uvećanje: 5% */

CREATE DATABASE [2514]
ON PRIMARY(
NAME = '2514', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\2514.mdf',
SIZE = 5MB, maxsize = unlimited, filegrowth = 10%
)
log ON (
NAME = N'2514_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\2514_log.ldf',
SIZE = 2MB, maxsize = unlimited, filegrowth = 10%
)
 USE [2514]

 /*Koristeći SQL::DDL komande kreirati sljedeće tabele i osigurati referencijalni integritet:
Edukatori (Ime, Prezime, Titula, Email, Telefon, CV, Slika)
Predmeti (Naziv, Oznaka, ECTS [int])
EdukatoriPredmeti (Edukator, Predmet, Zvanje, BrojSati)*/

DROP TABLE Edukatori
DROP TABLE EdukatoriPredmeti
CREATE TABLE Edukatori(
    EdukatorID INT IDENTITY(1,1) PRIMARY KEY,
    Ime nvarchar(30) NOT NULL,
    Prezime nvarchar(30) NOT NULL,
    Titula nvarchar(20) NULL,
    Email nvarchar(50) NULL,
    Telefon nvarchar(20),
    CV varbinary(MAX) NULL,
    Slika image  NULL
)
CREATE TABLE Predmeti (
    PredmetID INT IDENTITY(1,1) PRIMARY KEY,
    Naziv nvarchar(30) UNIQUE nonclustered NOT NULL,
    Oznaka nvarchar(10) NOT NULL,
    ECTS INT NOT NULL
 )

 CREATE TABLE EdukatoriPredmeti(
    EdukatorID INT FOREIGN KEY(EdukatorID) REFERENCES Edukatori(EdukatorID),
    PredmetID INT FOREIGN KEY(PredmetID) REFERENCES Predmeti(PredmetID),
    PRIMARY KEY(EdukatorID,PredmetID),
    zvanje nvarchar(20) NULL,
    BrojSati INT NULL
 )


/* Također, kreirati tabelu Fakulteti (Naziv, Telefon, Fax)
i povezati je sa prethodno kreiranim tabelama Edukatori i EdukatoriPredmeti.
U tabelu Edukatori dodati polje Adresa. Za polje ECTS u tabeli Predmeti izmijeniti tip podatka u DECIMAL.*/

CREATE TABLE Fakulteti(
    FakultetID INT IDENTITY(1,1) PRIMARY KEY,
    Naziv VARCHAR(30) NOT NULL,
    Telefon VARCHAR(20) NULL,
    Fax VARCHAR(20) NULL
)

ALTER TABLE Edukatori
ADD FakultetID INT FOREIGN KEY(FakultetID) REFERENCES Fakulteti(FakultetID)

ALTER TABLE EdukatoriPredmeti
ADD FakultetID INT FOREIGN KEY (FakultetID) REFERENCES Fakulteti(FakultetID)

ALTER TABLE Predmeti
ALTER COLUMN ECTS DECIMAL(18,2)


/* Kreirati stored proceduru za upis podataka u tabelu Edukatori.
Također, kreirati procedure za izmjenu i brisanje podataka u istoj tabeli.
Koristeći procedure za upis podataka u tabelu dodati 5 novih edukatora. */

CREATE PROCEDURE sp_Edukatori_update (
    @EdukatorID INT,
    @Ime nvarchar(30),
    @Prezime nvarchar(30),
    @Titula nvarchar(20) = NULL,
    @Email nvarchar(50) = NULL,
    @Telefon nvarchar(20) = NULL,
    @CV varbinary(MAX) = NULL,
    @Slika image = NULL
)
AS
BEGIN
    UPDATE Edukatori
    SET Ime = @Ime,Prezime=@Prezime,Titula=@Titula,Email=@Email,Telefon=@Telefon,CV=@CV,Slika=@Slika
    WHERE EdukatorID = @EdukatorID
END

CREATE PROCEDURE sp_Edukatori_insert (
    @Ime nvarchar(30),
    @Prezime nvarchar(30),
    @Titula nvarchar(20) = NULL,
    @Email nvarchar(50) = NULL,
    @Telefon nvarchar(20) = NULL,
    @CV varbinary(MAX) = NULL,
    @Slika image = NULL
)
AS
BEGIN
    INSERT INTO Edukatori
    VALUES (@Ime,@Prezime,@Titula,@Email,@Telefon,@CV,@Slika)
END

CREATE PROCEDURE sp_Edukatori_delete(
@EdukatorId INT
)
AS
DELETE FROM Edukatori WHERE EdukatorID = @EdukatorId

EXEC sp_Edukatori_insert 'Edukator1','Prezime1',NULL,NULL,NULL,NULL,NULL
EXEC sp_Edukatori_insert 'Edukator2','Prezime2',NULL,NULL,NULL,NULL,NULL
EXEC sp_Edukatori_insert 'Edukator3','Prezime3',NULL,NULL,NULL,NULL,NULL
EXEC sp_Edukatori_insert 'Edukator4','Prezime4',NULL,NULL,NULL,NULL,NULL
EXEC sp_Edukatori_insert 'Edukator5','Prezime5',NULL,NULL,NULL,NULL,NULL

SELECT * FROM Edukatori

/*U tabele Predmeti i Fakulteti dodati po 5 zapisa.
Također, u tabelu EduktoriPredmeti dodati proizvoljne podatke
(ko predaje određeni predmet, te na kojem fakultetu).*/

INSERT INTO Predmeti
VALUES ('predmet 1', 'p1', 5)

INSERT INTO Predmeti
VALUES ('predmet 2', 'p2', 8)

INSERT INTO Predmeti
VALUES ('predmet 3', 'p3', 20)

INSERT INTO Predmeti
VALUES ('predmet 4', 'p4', 22)

INSERT INTO Predmeti
VALUES ('predmet 5', 'p5', 22.5)

INSERT INTO Fakulteti
VALUES('Fakultet 1','123456','1123 132')

INSERT INTO Fakulteti
VALUES('Fakultet 2','123456','1123 132')
INSERT INTO Fakulteti
VALUES('Fakultet 3','123456','1123 132')
INSERT INTO Fakulteti
VALUES('Fakultet 4','123456','1123 132')
INSERT INTO Fakulteti
VALUES('Fakultet 5','123456','1123 132')

INSERT INTO EdukatoriPredmeti
VALUES(1,2,'prof',50 )
INSERT INTO EdukatoriPredmeti
VALUES(1,3,'prof',50 )
INSERT INTO EdukatoriPredmeti
VALUES(1,4,'prof',50 )
INSERT INTO EdukatoriPredmeti
VALUES(3,4,'prof',50 )

SELECT * FROM EdukatoriPredmeti


/*Kreirati pogled (View) koji obuhvata sljedeće podatke:
 ime i prezime edukatora (spojeno), titula, oznaka predmeta, naziv predmeta, broj ECTS kredita, broj sati.*/

 CREATE VIEW view_edukatori
 AS
 SELECT Ime + ' ' Prezime, Titula,[Naziv], [ECTS], [BrojSati]
 FROM [dbo].[Edukatori] AS E JOIN EdukatoriPredmeti AS EP ON E.EdukatorID = EP.EdukatorID
  JOIN Predmeti AS P ON EP.PredmetID = P.PredmetID

  SELECT * FROM view_edukatori

 /* Kreirati stored proceduru koja će na osnovu proslijeđenog parametra
  NazivPredmeta prikazti nastavno osoblje angažovano na predmetu.
  Iskoristiti prethodno kreirani view.
  Izmijeniti prethodno kreiranu stored proceduru tako da prima dva parametra:
  NazivPredmeta i ImePrezime edukatora. U zavisnosti od proslijeđenog/ih parametra,
  procedura treba da prikaže podatke o predmetima i nastavnom osoblju. */

CREATE PROCEDURE sp_osoblje_predmeti(
    @NazivPredmeta nvarchar(30)
  )
  AS
 SELECT Ime + ' ' + Prezime, Titula,[Naziv]
 FROM [dbo].[Edukatori] AS E JOIN EdukatoriPredmeti AS EP ON E.EdukatorID = EP.EdukatorID
  JOIN Predmeti AS P ON EP.PredmetID = P.PredmetID
  WHERE P.Naziv LIKE @NazivPredmeta

  EXEC sp_osoblje_predmeti 'predmet 3'

  CREATE PROCEDURE sp_osoblje_predmeti2(
    @NazivPredmeta nvarchar(30) = NULL,
    @ImePrezime nvarchar(61) = NULL
  )
  AS
 SELECT Ime + ' ' + Prezime, Titula,[Naziv]
 FROM [dbo].[Edukatori] AS E JOIN EdukatoriPredmeti AS EP ON E.EdukatorID = EP.EdukatorID
  JOIN Predmeti AS P ON EP.PredmetID = P.PredmetID
  WHERE (P.Naziv LIKE @NazivPredmeta OR @NazivPredmeta IS NULL) AND
        ((E.Ime + ' ' + Prezime) LIKE @ImePrezime OR @ImePrezime IS NULL)

        EXEC sp_osoblje_predmeti2 @ImePrezime = 'Edukator1 Prezime1'

/*Koristeći proceduru za brisanje zapisa u tabeli Edukatori (Zadatak 3) obrisati 2 edukatora.
Ukoliko edukatori imaju dodijeljene predmete,
modifikovati proceduru tako da prethodno obriše sve edukatoru dodijeljene predmete,
a zatim obriše zapis iz tabele Edukatori.*/

CREATE PROCEDURE sp_Edukatori_delete2(
@EdukatorId INT
)
AS
BEGIN

DELETE FROM EdukatoriPredmeti WHERE EdukatoriPredmeti.EdukatorID = @EdukatorId
DELETE FROM Edukatori WHERE Edukatori.EdukatorID = @EdukatorId

END

SELECT * FROM Edukatori

EXEC sp_Edukatori_delete2 1

/*Kreirati trigger koji će u tabelu EdukatoriAudit zapisati detalje izmjene podataka nad tabelom Edukatori. */

CREATE TRIGGER tr_edukatoriAudit
ON [dbo].[Edukatori]
after UPDATE AS
BEGIN
INSERT INTO  [dbo].[EdukatoriAudit]
([Korisnik],[Datum],[EdukatorId],[Ime],[Prezime],[Titula],[Email],[Telefon],[CV],[Slika],[Komanda])
SELECT  SYSTEM_USER,
        getdate(),
        d.EdukatorID,
        d.Ime,
        d.Prezime,
        d.Titula,
        d.Email,
        d.Telefon,
        d.CV,
        NULL,
        'update'
FROM deleted AS d

END

CREATE TABLE EdukatoriAudit
(
    EdukatorAuditID INT IDENTITY(1,1) PRIMARY KEY,
    Korisnik nvarchar(50),
    Datum datetime,
    EdukatorId INT,
    Ime nvarchar(30) NOT NULL,
    Prezime nvarchar(30) NOT NULL,
    Titula nvarchar(20) NULL,
    Email nvarchar(50) NULL,
    Telefon nvarchar(20),
    CV varbinary(MAX) NULL,
    Slika image  NULL
)

SELECT * FROM Edukatori

/*
Koristeći proceduru za izmjenu zapisa u tabeli Edukatori (Zadatak 3) izmijeniti mail i telefon određenom edukatoru. */

EXEC sp_Edukatori_update 2,'Edukator','Prazime',NULL,'bla@bla.ba','21231321',NULL,NULL

/*Uraditi full backup baze podataka na lokaciju D:\DB_Backup.*/
BACKUP DATABASE [2514]
TO DISK='D:\DB_Backup\3020.bak'
WITH NOFORMAT