CREATE TABLE - Vježba

--CREATE TABLE – Vježba
--1. Kreirati bazu podatka sa jednim Data i jednim Log fajlom. Imenovati je Vašim brojem dosijea. Prilikom kreiranje baze za Data fajl postaviti sljedeće parametre:
--Lokacija: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012\MSSQL\DATA
--Veličina: inicijalno 5 MB,
--maksimalna veličina neograničena,
--Rast: 10%

--Za Log fajl postaviti sljedeće paramtre:
--Lokacija: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012\MSSQL\DATA
--Veličina: inicijalno 2 MB,
--maksimalna veličina neograničena,
--Rast: 5%
CREATE DATABASE [6666]
ON PRIMARY
(NAME='3183',FILENAME='D:\DBMS_Data\3183.mdf',SIZE=5MB,FILEGROWTH=10%)
LOG ON
(NAME='3183_log',FILENAME='C:\DBMS_Data\3183_log.ldf',SIZE=2MB,FILEGROWTH=5%)

--2. Koristeći SQL kreirati sljedeće tabele i osigurati referencijalni integritet:
--Edukatori (Ime, Prezime, Titula, Status, CV, Slika)
--Predmeti (Naziv, Oznaka, ECTS (int))
--EdukatoriPredmeti (Edukator, Predmet, Zvanje, BrojSati, Syllabus)

--Također, kreirati tabelu Fakulteti (Naziv, DatumOsnivanja, KontaktTelefon, Fax) i povezati je sa postojećim tabelama Edukatori i EdukatoriPredmeti. U tabelu Edukatori dodati još jedno polje Email.
--Za polje ECTS iz tabele Predmeti promijeniti tip podatka u DECIMAL. (ALTER TABLE)

USE [6666]
CREATE TABLE Edukatori
(EdukatorID int IDENTITY(1,1) PRIMARY KEY,
Ime NVARCHAR(20) NULL,
Prezime NVARCHAR(40) NULL,
Titula NVARCHAR(5) NULL,
Status NVARCHAR(20) NULL,
CV VARBINARY(MAX) NULL,
Slika IMAGE NULL,
)
GO
CREATE TABLE Predmeti
(PredmetID INT IDENTITY(1,1) PRIMARY KEY,
Naziv NVARCHAR(50) NOT NULL,
Oznaka NVARCHAR(5) NULL,
ECTS int
)
GO
CREATE TABLE EdukatoriPredmeti
(EdukatorPredmetID INT IDENTITY(1,1) PRIMARY KEY,
EdukatorID INT FOREIGN KEY (EdukatorID) REFERENCES Edukatori(EdukatorID),
PredmetID INT FOREIGN KEY (PredmetID) REFERENCES Predmeti(PredmetID),
Zvanhe NVARCHAR(10) NULL,
BrojSati INT,
Syllabus NVARCHAR(100) NULL
)
GO

CREATE TABLE Fakulteti
(FakultetID INT IDENTITY(1,1)PRIMARY KEY,
Naziv NVARCHAR(50) NULL,
DatumOsnivanja DATETIME NULL,
KontaktTelefon NVARCHAR(20) NULL,
Fax NVARCHAR(20) NULL,
)
GO

ALTER TABLE Edukatori
ADD FakultetID int FOREIGN KEY (FakultetID) REFERENCES Fakulteti(FakultetID)
--EdukatoriPredmeti imaju FK edukatora tako da u njih ne trebamo dodavati fk fakulteta
ALTER TABLE Edukatori
ADD Email nvarchar(30)
GO

ALTER TABLE Predmeti
ALTER COLUMN ECTS decimal
GO
--3. Kreirati proceduru za insert podataka u tabelu Edukatori sa imenom proc_Edukatori_Insert. Proceduri proslijediti sve parametre.
--Također, kreirati procedure za UPDATE i DELETE podataka nad istom tabelom. Izvršiti proceduru INSERT i u tabelu ubaciti 5 zapisa.
CREATE PROCEDURE proc_Edukatori_Insert
@Ime NVARCHAR(20),
@Prezime NVARCHAR(40),
@Titula NVARCHAR(5),
@Status NVARCHAR(20),
@CV VARBINARY(MAX),
@Slika IMAGE,
@FakultetID int,
@Email nvarchar(30)
AS
INSERT INTO Edukatori
VALUES(@Ime,@Prezime,@Titula,@Status,@CV,@Slika,@FakultetID,@Email)
GO
INSERT INTO Fakulteti
VALUES('FIT',2004,036999666,036999667)

EXEC proc_Edukatori_Insert Hamo,Sedmi,prof,'Aktivan',NULL,NULL,1,'hamo_sedmi@mailg.com'
EXEC proc_Edukatori_Insert Hamo,Sedmi,prof,'Aktivan',NULL,NULL,1,'hamo_sedmi@mailg.com'
EXEC proc_Edukatori_Insert Hamo,Sedmi,prof,'Aktivan',NULL,NULL,1,'hamo_sedmi@mailg.com'
EXEC proc_Edukatori_Insert Hamo,Sedmi,prof,'Aktivan',NULL,NULL,1,'hamo_sedmi@mailg.com'
EXEC proc_Edukatori_Insert Hamo,Sedmi,prof,'Aktivan',NULL,NULL,1,'hamo_sedmi@mailg.com'
SELECT *
FROM Edukatori
SELECT*
FROM Fakulteti

CREATE PROCEDURE upd_edukatori
@EdukatorID int,
@Ime NVARCHAR(20),
@Prezime NVARCHAR(40),
@Titula NVARCHAR(5),
@Status NVARCHAR(20),
@CV VARBINARY(MAX),
@Slika IMAGE,
@FakultetID int,
@Email nvarchar(30)
AS
UPDATE Edukatori
SET Ime=@Ime,Prezime=@Prezime,Titula=@Titula,Status=@Status,CV=@CV,Slika=@Slika,FakultetID=@FakultetID,Email=@Email
FROM Edukatori
WHERE EdukatorID=@EdukatorID
GO

EXEC upd_edukatori 3,hamica,deveti,dr,zbunjen,NULL,NULL,1,'zbunjen@normalan.lud'

SELECT *
FROM Edukatori
GO

CREATE PROCEDURE del_edukatori
@EdukatorID int
AS
DELETE FROM Edukatori
WHERE EdukatorID=@EdukatorID
GO

EXEC del_edukatori 6
GO
--4. U tabele Predmeti i Fakulteti insertovati po 5 zapisa. Također, u tabelu EduktoriPredmeti insertovati testne podatke (ko predaje određeni predmet na kojem fakultetu).
INSERT INTO Predmeti
VALUES('Algoritmi i strukture podataka','ASP',7),
('Reporting','RP',2),
('Sociologija','SC',1),
('Programiranje','PR',7),
('Baze podataka','BP',8)

INSERT INTO Fakulteti
VALUES('Fakultet informacijskih tehnologija','1.1.2003 16:00',063767676,063737373),
('Fakultet informacijskih tehnologija','2.21.2003 16:00',063767676,063737373),
('Fakultet informacijskih tehnologija','1.1.2003 16:00',063767676,063737373),
('Fakultet informacijskih tehnologija','1.1.2003 16:00',063767676,063737373),
('Fakultet informacijskih tehnologija','1.1.2003 16:00',063767676,063737373)

SELECT *
FROM Fakulteti

SELECT*
FROM Edukatori
SELECT*
FROM Predmeti

INSERT INTO EdukatoriPredmeti
VALUES(2,1,'prof.dr',2,'workshop'),
(2,1,'prof.dr',2,'workshop'),
(3,4,'prof.dr',2,'workshop'),
(5,1,'prof.dr',2,'workshop'),
(5,5,'prof.dr',2,'workshop')

SELECT *
FROM EdukatoriPredmeti
GO
--5. Kreirati pogled (VIEW) koji će obuvatiti sve tabele i imenovati ga view_EdukatoriPredmeti.
CREATE VIEW view_EdukatoriPredmeti --Skraceno da ne pisem sve kolone
AS
SELECT Ime'Ime edukatora',Prezime'Prezime edukatora',Zvanhe'Zvanje',Naziv'Predmet',Oznaka,BrojSati'Broj sati'
FROM Edukatori JOIN EdukatoriPredmeti
ON Edukatori.EdukatorID=EdukatoriPredmeti.EdukatorID JOIN Predmeti
ON Predmeti.PredmetID=EdukatoriPredmeti.PredmetID
GO

SELECT *
FROM view_EdukatoriPredmeti
GO
--6. Kreirati proceduru sa imenom proc_EdukatoriPredmetiSelectByPredmet koja će na osvnovu proslijeđenog parametra (naziv predmeta) vraćati sve angažovane na predmetu.
CREATE PROCEDURE proc_EdukatoriPredmetiSelectByPredmet
@Naziv nvarchar(50)
AS
SELECT E.Ime
FROM Predmeti AS P JOIN EdukatoriPredmeti AS EP
ON P.PredmetID=EP.PredmetID JOIN Edukatori AS E
ON EP.EdukatorID=E.EdukatorID
WHERE P.Naziv=@Naziv
GO

EXEC proc_EdukatoriPredmetiSelectByPredmet 'Algoritmi i strukture podataka'

--7. Koristeći proceduru proc_Edukatori_Delete (iz zadatka 3) obrisati 2 edukatora. Ukoliko edukatori imaju definisane predmete,
--modifikovati proceduru tako da prethodno obriše sve predmete od edukatora, pa tek onda obriše zapis iz tabele Edukatori.
SELECT *
FROM EdukatoriPredmeti
SELECT * FROM Edukatori

ALTER PROCEDURE del_edukatori
@EdukatorID int
AS
DELETE FROM EdukatoriPredmeti
WHERE EdukatorID=@EdukatorID
DELETE FROM Edukatori
WHERE EdukatorID=@EdukatorID
GO

EXEC del_edukatori 5
GO
--8. Koristeći proceduru proc_Edukatori_Update (iz zadatka 3) izmijeniti titulu za proizvoljnog edukatora.
ALTER PROCEDURE upd_edukatori
@EdukatorID int,
@Titula NVARCHAR(5)
AS
UPDATE Edukatori
SET Titula=@Titula
FROM Edukatori
WHERE EdukatorID=@EdukatorID
GO

EXEC upd_edukatori 2,'Mr.'

SELECT *
FROM Edukatori