ISPIT DBMS 13.06.15

/*Iz baze podataka AdventureWorks2014 prikazati ukupnu ostvarenu zaradu
po zaposleniku na području Evrope u januaru mjesecu 2014. godine.
Lista treba da sadrži ime i prezime zaposlenika (spojeno),
datum zaposlenja u formatu dd.mm.yyyy, mail adresu,
te ukupnu ostvarenu zaradu zaokruženu na dvije decimale.
Podatke sortirati po zaradi opadajućim redoslijedom. */

USE AdventureWorks2014

SELECT [FirstName] + ' ' + [LastName], CONVERT(nvarchar,DAY(HireDate)) + '.' + CONVERT(VARCHAR,MONTH(HireDate)) + '.'
            + CONVERT(VARCHAR,YEAR(HireDate)) AS datum ,[EmailAddress], round(SUM([OrderQty]*[UnitPrice]),2) AS zarada
FROM HumanResources.Employee AS E JOIN Person.Person AS P ON E.BusinessEntityID = P.BusinessEntityID
        JOIN Person.EmailAddress AS EA ON E.BusinessEntityID = EA.BusinessEntityID
        JOIN Sales.SalesOrderHeader AS SOH ON E.BusinessEntityID = SOH.SalesPersonID
        JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
        JOIN Sales.SalesTerritory AS ST ON SOH.TerritoryID = ST.TerritoryID
WHERE MONTH([OrderDate]) = 01 AND YEAR([OrderDate]) = 2014 AND ST.[GROUP] = 'Europe'
GROUP BY [FirstName],[LastName],[HireDate], [EmailAddress]
ORDER BY zarada DESC

/*Iz baze podataka AdventureWorks2014 prikazati kreditne kartice kojima je plaćeno više od 20 narudžbi.
 U listu uključiti ime i prezime vlasnika kartice, tip kartice, broj kartice, ukupan iznos plaćen karticom.*/

 SELECT [FirstName],[LastName],[CardType],[CardNumber], SUM([OrderQty]*[UnitPrice]) AS ukupno
 FROM Person.Person AS P JOIN Sales.PersonCreditCard AS PCC ON P.BusinessEntityID = PCC.BusinessEntityID
    JOIN Sales.CreditCard AS CC ON PCC.CreditCardID = CC.CreditCardID
    JOIN Sales.SalesOrderHeader AS SOH ON CC.CreditCardID = SOH.CreditCardID
    JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
GROUP BY [FirstName],[LastName],[CardType],[CardNumber]
HAVING COUNT(SOH.CreditCardID)>20

/*Kreirati bazu podataka koju ćete imenovati Vašim brojem dosijea. Fajlove baze smjestiti na sljedeće lokacije:
 Data fajl -> D:\DBMS\Data
 Log fajl -> D:\DBMS\Log */

CREATE DATABASE [2514] ON PRIMARY
(
NAME = '2514',
FILENAME = 'C:\DBMS\2514.mdf',
SIZE = 5 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)
LOG ON
(
NAME = '2514log',
FILENAME = 'C:\DBMS\2514log.ldf',
SIZE = 5 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
)

USE [2514]

/*Kandidati
 Ime, polje za unos 30 karaktera (obavezan unos),
 Prezime, polje za unos 30 karaktera (obavezan unos),
 JMBG, polje za unos 13 karaktera (obavezan unos i jedinstvena vrijednost),
 DatumRodjenja, polje za unos datuma (obavezan unos),
 MjestoRodjenja, polje za unos 30 karaktera,
 Telefon, polje za unos 20 karaktera,
 Email, polje za unos 50 karaktera (jedinstvena vrijednost).*/

CREATE TABLE Kandidati(
    KandidatID INT IDENTITY(1,1) PRIMARY KEY,
    Ime nvarchar(30) NOT NULL,
    Prezime nvarchar(30) NOT NULL,
    JMBG nvarchar(30) NOT NULL UNIQUE nonclustered,
    DatumRodjenja DATE NOT NULL,
    MjestoRodjenja nvarchar(30),
    Telefon nvarchar(20) NULL,
    Email nvarchar(50) NOT NULL UNIQUE nonclustered
)

/*Testovi
 Datum, polje za unos datuma i vremena (obavezan unos),
 Naziv, polje za unos 50 karaktera (obavezan unos),
 Oznaka, polje za unos 10 karaktera (obavezan unos i jedinstvena vrijednost),
 Oblast, polje za unos 50 karaktera (obavezan unos),
 MaxBrojBodova, polje za unos cijelog broja (obavezan unos),
 Opis, polje za unos 250 karaktera.*/

CREATE TABLE Testovi(
    TestID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Datum DATE NOT NULL,
    Oznaka nvarchar(10) NOT NULL UNIQUE nonclustered,
    Oblast nvarchar(50) NOT NULL,
    MaxBrojBodova INT NOT NULL,
    Opis nvarchar(250)
)

/*RezultatiTesta
 Polozio, polje za unos ishoda testiranja – DA/NE (obavezan unos)
 OsvojeniBodovi, polje za unos decimalnog broja (obavezan unos),
 Napomena, polje za unos dužeg niza karaktera.*/

CREATE TABLE RezultatiTesta(
    KandidatID INT FOREIGN KEY(KandidatID) REFERENCES Kandidati(KandidatID),
    TestID INT FOREIGN KEY(TestID) REFERENCES Testovi(TestID),
    Polozio BIT NOT NULL,
    OsvojeniBodovi DECIMAL(18,2) NOT NULL,
    Napomena nvarchar(300)
)

/*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(Ime,Prezime,JMBG,DatumRodjenja,MjestoRodjenja,Telefon,Email)
SELECT top 10 P.FirstName, P.LastName, REPLACE(RIGHT(P.[rowguid],13),'-','0'), P.ModifiedDate,[City], [PhoneNumber],[EmailAddress]
FROM AdventureWorks2014.Person.Person AS P JOIN AdventureWorks2014.Person.BusinessEntityAddress AS BSE
        ON P.BusinessEntityID = BSE.BusinessEntityID JOIN AdventureWorks2014.Person.Address AS A ON
        BSE.AddressID = A.AddressID
        JOIN AdventureWorks2014.Person.PersonPhone AS PP ON P.BusinessEntityID = PP.BusinessEntityID
        JOIN AdventureWorks2014.Person.EmailAddress AS EMAIL ON P.BusinessEntityID = EMAIL.BusinessEntityID
ORDER BY newid()

SELECT * FROM Kandidati

INSERT INTO Testovi(Datum,Oznaka,Oblast,MaxBrojBodova,Opis)
VALUES ('2015-03-06','DBMS','Baze',100,'Integralni ispit')

INSERT INTO Testovi(Datum,Oznaka,Oblast,MaxBrojBodova,Opis)
VALUES ('2015-03-07','PR2','Programiranje',100,'Integralni ispit')

INSERT INTO Testovi(Datum,Oznaka,Oblast,MaxBrojBodova,Opis)
VALUES ('2015-03-08','SIV','Statistika',100,'Integralni ispit')

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).*/

CREATE PROCEDURE usp_RezultatiTesta_Insert(
    @KandidatID INT,
    @TestID INT,
    @Polozio BIT,
    @OsvojeniBodovi DECIMAL(18,2),
    @Napomena nvarchar(300)
)AS
BEGIN
INSERT INTO RezultatiTesta
VALUES (@KandidatID,@TestID,@Polozio,@OsvojeniBodovi,@Napomena)
END

SELECT * FROM Kandidati

SELECT * FROM Testovi

SELECT * FROM RezultatiTesta

EXEC usp_RezultatiTesta_Insert 18806,3,1,90,NULL

/*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.*/

CREATE VIEW view_Rezultati_Testiranja AS
SELECT [Ime], [Prezime], [Telefon],[Email],T.[Datum],Oznaka,[Oblast],[MaxBrojBodova],[Polozio],[OsvojeniBodovi]
FROM Kandidati AS K JOIN RezultatiTesta AS RT ON K.KandidatID = RT.KandidatID
        JOIN Testovi AS T ON RT.TestID = T.TestID

/*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.*/

 CREATE PROCEDURE usp_RezultatiTesta_SelectByOznaka (
  @OznakaTesta nvarchar(10) = NULL,
  @Polozio bit = NULL
 )AS
 BEGIN
 SELECT [Ime], [Prezime],[Datum],Oznaka,[Oblast],[MaxBrojBodova],[Polozio],[OsvojeniBodovi]
 FROM view_Rezultati_Testiranja AS VRT
 WHERE ([Oznaka] = @OznakaTesta OR @OznakaTesta IS NULL) AND ([Polozio] = @Polozio OR @Polozio IS NULL)
 END

 EXEC usp_RezultatiTesta_SelectByOznaka @OznakaTesta=DBMS

/* Kreirati proceduru koja će služiti za izmjenu rezultata testiranja.
Proceduru pohraniti pod nazivom usp_RezultatiTesta_Update.
Obavezno testirati ispravnost kreirane procedure. */

CREATE PROCEDURE usp_RezultatiTesta_Update(
    @KandidatID INT,
    @TestID INT,
    @Polozio bit,
    @OsvojeniBodovi DECIMAL(18,2),
    @Napomena nvarchar(300) = NULL
)AS
BEGIN
UPDATE RezultatiTesta
SET Polozio = @Polozio, OsvojeniBodovi = @OsvojeniBodovi, Napomena = @Napomena
WHERE KandidatID=@KandidatID AND TestID = @TestID
END


SELECT * FROM RezultatiTesta

EXEC usp_RezultatiTesta_Update @KandidatID = 18799,@TestID = 1,@Polozio = 1, @OsvojeniBodovi = 100,@Napomena = NULL

/*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. */

CREATE PROCEDURE usp_Testovi_Delete (
    @TestID INT
)AS
BEGIN
DELETE FROM RezultatiTesta WHERE TestID = @TestID

DELETE FROM Testovi WHERE TestID = @TestID

END

SELECT * FROM Testovi

EXEC usp_Testovi_Delete 2

/* Kreirati trigger koji će spriječiti brisanje rezultata testiranja.
 Obavezno testirati ispravnost kreiranog triggera. */


 CREATE TRIGGER preventiva ON [dbo].[RezultatiTesta] FOR DELETE
  AS
  Print 'Brisanje nije dozvoljeno'
  ROLLBACK;

  DELETE FROM RezultatiTesta

  backup DATABASE [2514] TO disk = 'c:\DBMS\2514back.bck'