ispitni - 13.06.2015 -kandidati - testovi -rezultatiTesta

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

USE ispitna
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 smalldatetime NOT NULL,
MjestoRodjenja nvarchar (30) NOT NULL,
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) NOT NULL,
Oznaka nvarchar (10) UNIQUE NOT NULL,
Oblast nvarchar (50) NOT NULL,
MaxBrojBodova int NOT NULL,
Opis nvarchar (250)
);
CREATE TABLE RezultatiTesta
(
KandidatID int FOREIGN KEY REFERENCES Kandidati(KandidatID),
TestID int FOREIGN KEY REFERENCES Testovi (TestID),
Polozio bit NOT NULL,
OsvojeniBodovi decimal NOT NULL,
Napomena text,
PRIMARY KEY (KandidatID, TestID)
);

/*3.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(sc.rowguid,13),'-', '0') AS [JMBG],
             sc.ModifiedDate AS [Datum rodjenja],
             pea.City AS [Mjesto rodjenja],
             ppp.PhoneNumber AS [Telefon],
             peaa.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.PersonPhone AS ppp ON pp.BusinessEntityID=ppp.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.Address AS pea ON pp.BusinessEntityID=pea.AddressID
INNER JOIN AdventureWorks2014.Person.EmailAddress AS peaa ON pp.BusinessEntityID=peaa.BusinessEntityID

SELECT * FROM Kandidati

INSERT INTO Testovi (Datum, Naziv, Oznaka, Oblast, MaxBrojBodova, Opis)
VALUES
('2006-01-01 13:13:13', 'Bosanski jezik', 'BJ', 'Jezicka', '100', 'Testni opis'),
('2007-02-02 14:14:14', 'Matematika', 'MM', 'Tehnicka', '100', 'Testni opis'),
('2008-03-03 15:15:15', 'Fizika', 'FIZ', 'Tehnicka', '100', 'Testni opis')

SELECT * FROM Testovi

/*4.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 Polozili
@polozio bit,
@osvojenihbodova decimal,
@napomena nvarchar(250),
@kandidat int,
@test int
AS
    BEGIN
    INSERT INTO RezultatiTesta (KandidatID, TestID, Polozio,OsvojeniBodovi, Napomena)
    VALUES(@kandidat, @test,@polozio, @osvojenihbodova, @napomena)
    END;

EXECUTE Polozili '1', '60', 'polozio', '1', '1'
EXECUTE Polozili '0' ,'40', 'poa', '2', '1'
EXECUTE Polozili '1', '70' ,'polozio', '3' ,'1'
EXECUTE Polozili '0', '30' ,'pao', '4' ,'2'
EXECUTE Polozili '1', '80' ,'polozio', '5', '2'
EXECUTE Polozili '0', '35','pao','6', '2'
EXECUTE Polozili '1','85','polozio', '7','3'
EXECUTE Polozili '0', '25' ,'pao', '8', '3'
EXECUTE Polozili '1', '99' ,'polozio' ,'9','3'
EXECUTE Polozili '0', '15', 'pao', '10', '1'
SELECT * FROM RezultatiTesta

/*5.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], k.Prezime AS [prezime], k.JMBG AS [JMBG], k.Telefon AS [Telefon], k.Email AS [Email],
           t.Datum AS [Datum], t.Naziv AS [Naziv], t.Oznaka AS [Oznaka], t.Oblast AS [Oblast],
           t.MaxBrojBodova AS [Max broj bodova],
           rt.Polozio AS [Polozio], rt.OsvojeniBodovi AS [Osvojeni bodovi],
           (rt.OsvojeniBodovi/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, view_Rezultati_Testiranja.prezime,
                 view_Rezultati_Testiranja.Polozio, view_Rezultati_Testiranja.Naziv,
                 view_Rezultati_Testiranja.[Procentualni rezultat testa]
        FROM view_Rezultati_Testiranja
        WHERE view_Rezultati_Testiranja.Oznaka LIKE @oznakaTesta AND view_Rezultati_Testiranja.Polozio=@Polozio
    END;
EXECUTE usp_RezultatiTesta_SelectByOznaka 'BJ' ,'1'

/*7.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 UpdateTabele
@kandidat int,
@test int,
@polozio bit,
@osvojenihBodova decimal,
@napomena nvarchar (250)
AS
    BEGIN
        UPDATE RezultatiTesta
        SET KandidatID=@kandidat, TestID=@test, Polozio=@polozio, OsvojeniBodovi=@osvojenihBodova, 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 UpdateTabele '1', '1','0', '35' ,'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 Brisanje
@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 Brisanje '1'

SELECT * FROM Testovi
SELECT * FROM RezultatiTesta

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

DELETE RezultatiTesta WHERE OsvojeniBodovi=30
SELECT * FROM RezultatiTesta

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

BACKUP DATABASE ispitna
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ispitna.bak'
WITH DIFFERENTIAL