SQL Test-Kandidat-RezultatiTesta

CREATE DATABASE saraa
ON
(NAME=saraa_dat,FILENAME='C:\BP2\DATA\saraa.mdf', SIZE=20MB, MAXSIZE=UNLIMITED, FILEGROWTH=5%)
LOG ON
(NAME=saraa_log, FILENAME='C:\BP2\LOG\saraa.ldf', SIZE=2MB, MAXSIZE=UNLIMITED, FILEGROWTH=5%)

USE saraa
GO

CREATE TABLE Kandidati
(
KandidatID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Ime nvarchar(30) NOT NULL,
Prezime nvarchar (30) NOT NULL,
JMBG nvarchar (13) UNIQUE NOT NULL,
DatumRodjenja date NOT NULL,
MjestoRodjenja nvarchar(30),
Telefon nvarchar (20),
Email nvarchar (50) UNIQUE
);

CREATE TABLE Testovi
(
TestID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Datum smalldatetime NOT NULL,
Naziv nvarchar (50),
Oznaka nvarchar (10) UNIQUE NOT NULL,
Oblast nvarchar (50) NOT NULL,
MaxBrojBodova int NOT NULL,
Opis nvarchar (250)
);

CREATE TABLE RezultatiTesta
(
TestID int FOREIGN KEY REFERENCES Testovi(TestID),
KandidatID int FOREIGN KEY REFERENCES Kandidati (KandidatID),
Polozio bit NOT NULL,
OsovojeniBodovi decimal NOT NULL,
Napomena text,
PRIMARY KEY (TestID, KandidatID)
);

/*Koristeći AdventureWorks2014 bazu podataka, importovati 10 kupaca u tabelu Kandidati i to sljedeće kolone:
a. FirstName (Person) -> Ime,
b. LastName (Person) -> Prezime,
c. Zadnjih 13 karaktera kolone rowguid iz tabele Customer (Crticu zamijeniti brojem 0) -> JMBG,
d. ModifiedDate (Customer) -> DatumRodjenja,
e. City (Address) -> MjestoRodjenja,
f. PhoneNumber (PersonPhone) -> Telefon,
g. EmailAddress (EmailAddress) -> Email.
Također, u tabelu Testovi unijeti minimalno tri testa sa proizvoljnim podacima.*/
INSERT INTO Kandidati
SELECT TOP 10
            pp.FirstName AS [Ime],
            pp.LastName AS [Prezime],
            REPLACE(RIGHT(pp.rowguid,13),'-', '0') AS [JMBG],
            sc.ModifiedDate AS [Datm rodjenja],
            pa.City AS [Mjesto rodjenja],
            ppp.PhoneNumber AS [Telefon],
            pea.EmailAddress AS [Email]
FROM AdventureWorks2014.Person.Person AS pp
INNER JOIN AdventureWorks2014.Sales.Customer AS sc ON pp.BusinessEntityID=sc.PersonID
INNER JOIN AdventureWorks2014.Person.Address AS pa ON pp.BusinessEntityID=pa.AddressID
INNER JOIN AdventureWorks2014.Person.PersonPhone AS ppp ON pp.BusinessEntityID=ppp.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.EmailAddress AS pea ON pp.BusinessEntityID=pea.BusinessEntityID
SELECT * FROM Kandidati
INSERT INTO Testovi (Datum, Naziv, Oznaka, Oblast, MaxBrojBodova, Opis)
VALUES
('2017-01-02 12:12:13' , 'Matematika', 'MAT', 'Tehnicka', '100', 'Ovaj predmet se zove matematika.'),
('2016-03-23 13:13:13', 'Bosanski jezik', 'BOS', 'Jezicka', '100', 'Ovaj predmet se zove bosanski jezik.'),
('2015-04-05 14:14:14', 'Moja okolina', 'MO', 'Priroda', '100', 'Ovaj predmet se zove moja okolina.')
SELECT * FROM Testovi

/*Kreirati stored proceduru koja će na osnovu proslijeđenih parametara služiti za unos podataka u tabelu RezultatiTesta.
Proceduru pohraniti pod nazivom usp_RezultatiTesta_Insert. Obavezno testirati ispravnost kreirane procedure
(unijeti proizvoljno minimalno 10 rezultata za različite testove).*/
GO
CREATE PROCEDURE usp_RezultatiTesta_Insert
@test int,
@kandidat int,
@polozio bit,
@osvojeniBodovi decimal,
@napomena text
AS
    BEGIN
        INSERT INTO RezultatiTesta (TestID, KandidatID, Polozio, OsovojeniBodovi, Napomena)
        VALUES(@test, @kandidat, @polozio, @osvojeniBodovi, @napomena)
    END;

EXECUTE usp_RezultatiTesta_Insert '3', '1', '1', '99', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '4', '2', '0', '33', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '5', '3', '1', '77', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '3', '4', '0', '54', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '4', '5', '1', '88', 'Ovo je napomena.'

EXECUTE usp_RezultatiTesta_Insert '5', '6', '0', '22', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '3', '7', '1', '75', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '4', '8', '0', '36', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '5', '9', '1', '100', 'Ovo je napomena.'
EXECUTE usp_RezultatiTesta_Insert '3', '10', '0', '25', 'Ovo je napomena.'

SELECT * FROM Testovi
SELECT * FROM RezultatiTesta

/*Kreirati view (pogled) nad podacima koji će sadržavati sljedeća polja:
 ime i prezime, jmbg, telefon i email kandidata,
 zatim datum, naziv, oznaku, oblast i max. broj bodova na testu,
 te polje položio, osvojene bodove i procentualni rezultat testa. View pohranite pod nazivom view_Rezultati_Testiranja.*/
 GO
 CREATE VIEW view_Rezultati_Testiranja
 AS
    SELECT k.Ime AS [Ime kandidata], k.Prezime AS [Prezime kandidata], k.JMBG AS[JMBG kandidata],
           k.Telefon AS [Telefon kandidata], k.Email AS [email kandidata],
           t.Datum AS [Datum testa], t. Naziv AS [Naziv testa], t.Oznaka AS [Oznaka testa],
           t.Oblast AS [Oblast testa], t.MaxBrojBodova AS [Max broj bodova na testu],
           rt.Polozio AS [Polozio], rt.OsovojeniBodovi AS [Osvojeni bodovi],
           (rt.OsovojeniBodovi/t.MaxBrojBodova)*100 AS [Procentualni rezultat testa]
    FROM RezultatiTesta AS rt
    INNER JOIN Kandidati AS k ON rt.KandidatID=k.KandidatID
    INNER JOIN Testovi AS t ON rt.TestID=t.TestID
SELECT * FROM view_Rezultati_Testiranja

/*6. Kreirati stored proceduru koja će na osnovu proslijeđenih parametara
@OznakaTesta i @Polozio prikazivati rezultate testiranja.
Kao izvor podataka koristiti prethodno kreirani view.
Proceduru pohraniti pod nazivom usp_RezultatiTesta_SelectByOznaka.
Obavezno testirati ispravnost kreirane procedure.*/
GO
CREATE PROCEDURE usp_RezultatiTesta_SelectByOznaka
@oznakaTesta nvarchar(10),
@polozio bit
AS
    BEGIN
        SELECT view_Rezultati_Testiranja.[Ime kandidata], view_Rezultati_Testiranja.[Prezime kandidata],
                view_Rezultati_Testiranja.[Naziv testa], view_Rezultati_Testiranja.Polozio,
                view_Rezultati_Testiranja.[Osvojeni bodovi]
        FROM view_Rezultati_Testiranja
        WHERE view_Rezultati_Testiranja.[Oznaka testa] LIKE @oznakaTesta AND view_Rezultati_Testiranja.Polozio LIKE @polozio
    END;
SELECT * FROM Testovi
EXECUTE usp_RezultatiTesta_SelectByOznaka 'MAT', '1'

/*Kreirati proceduru koja će služiti za izmjenu rezultata testiranja.
Proceduru pohraniti pod nazivom usp_RezultatiTesta_Update.
Obavezno testirati ispravnost kreirane procedure.*/
GO
CREATE PROCEDURE usp_RezultatiTesta_Update
@test int,
@kandidat int,
@polozio bit,
@osvojeniBodovi decimal,
@napomena text
AS
    BEGIN
        UPDATE RezultatiTesta
        SET TestID=@test, KandidatID=@kandidat, Polozio=@polozio, OsovojeniBodovi=@osvojeniBodovi, Napomena=@napomena
        WHERE RezultatiTesta.KandidatID
        =(
        SELECT Kandidati.KandidatID
        FROM Kandidati
        WHERE Kandidati.KandidatID=@kandidat
        )
        AND
        RezultatiTesta.TestID
        =(
        SELECT Testovi.TestID
        FROM Testovi
        WHERE Testovi.TestID=@test
        )
    END;
EXECUTE usp_RezultatiTesta_Update '3', '1', '0', '25', 'Polozio, pa je pao.'
SELECT * FROM RezultatiTesta

/*8. Kreirati stored proceduru koja će služiti za brisanje testova zajedno sa svim rezultatima testiranja.
Proceduru pohranite pod nazivom usp_Testovi_Delete. Obavezno testirati ispravnost kreirane procedure.*/
GO
CREATE PROCEDURE usp_Testovi_Delete
@test int
AS
    BEGIN
        DELETE
        FROM RezultatiTesta
        WHERE RezultatiTesta.TestID
        =(
        SELECT Testovi.TestID
        FROM Testovi
        WHERE Testovi.TestID=@test
        )
        DELETE
        FROM Testovi
        WHERE Testovi.TestID=@test
    END;
EXECUTE usp_Testovi_Delete '3'
SELECT * FROM Testovi
SELECT * FROM RezultatiTesta

/*9. Kreirati trigger koji će spriječiti brisanje rezultata testiranja. Obavezno testirati ispravnost kreiranog triggera.*/
GO
CREATE TRIGGER Brisanje
ON RezultatiTesta
FOR DELETE
    AS
    PRINT 'Zabranjeno brisanje!!!'
    ROLLBACK;

DELETE RezultatiTesta
SELECT * FROM RezultatiTesta

/*10. Uraditi full backup Vaše baze podataka na lokaciju D:\DBMS\Backup.
    */
BACKUP DATABASE saraa
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\saraa.bak'