DBMS - Rješenje integralnog ispita od 14. 9. 2015. god.

/*
   DBMS praktični ispit – Integralni (14.09.2015) - Rješenje
   Postavku ispita možete preuzeti sa: https://www.sendspace.com/file/5hvz0l
   Prilikom rješavanja ispita korištena je starija verzija baze AdventureWorks (2012, a ne 2014), s obzirom da nema značajnih razlika
   između baza - rezultati su identični.
*/

-- 1. kvalifikacijski

USE AdventureWorks2012

SELECT P.FirstName AS 'Ime',
       P.LastName AS 'Prezime',
       SUM(SOH.TotalDue) AS 'Ukupno utrošeno'
FROM Sales.Customer AS C JOIN Person.Person AS P
     ON C.PersonID = P.BusinessEntityID JOIN Sales.SalesOrderHeader AS SOH
     ON C.CustomerID = SOH.CustomerID
WHERE DATEPART(MONTH, SOH.OrderDate) = 7
GROUP BY P.FirstName, P.LastName
HAVING SUM(SOH.TotalDue) > 200000
ORDER BY [Ukupno utrošeno] DESC

-- 2. kvalifikacijski

USE AdventureWorks2012

SELECT P.FirstName AS 'Ime',
       P.LastName AS 'Prezime',
       CC.CardType AS 'Tip kartice',
       CC.CardNumber AS 'Broj kartice',
       SUM(SOH.TotalDue) AS 'Ukupno plaćeno'
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
GROUP BY P.FirstName, P.LastName, CC.CardType, CC.CardNumber
HAVING COUNT(SOH.CreditCardID) > 20

--=========================================================================================--

-- 1. zadatak

CREATE DATABASE IB130094

-- 2. zadatak

USE AdventureWorks2012

SELECT P.FirstName AS 'Ime',
       P.LastName AS 'Prezime',
       P.Title AS 'Titula',
       E.Gender AS 'Spol',
       EA.EmailAddress AS 'Email'
INTO IB130094.dbo.Predavaci
FROM HumanResources.Employee AS E JOIN Person.Person AS P
     ON E.BusinessEntityID = P.BusinessEntityID JOIN Person.EmailAddress AS EA
     ON P.BusinessEntityID = EA.BusinessEntityID
WHERE P.Title IS NOT NULL

-- 3. zadatak

USE IB130094

ALTER TABLE Predavaci
ADD PredavacID INT NOT NULL IDENTITY(1,1) PRIMARY KEY

CREATE TABLE LoginPodaci
(
    PredavacID INT NOT NULL PRIMARY KEY REFERENCES Predavaci(PredavacID),
    KorisnickoIme NVARCHAR(20) NOT NULL UNIQUE,
    Lozinka NVARCHAR(20) NOT NULL
)

-- 4. zadatak

USE AdventureWorks2012

INSERT INTO IB130094.dbo.LoginPodaci(PredavacID, KorisnickoIme, Lozinka)
SELECT PP.PredavacID AS 'PredavacID',
       SUBSTRING(EA.EmailAddress, 1, CHARINDEX('@', EA.EmailAddress)-1) AS 'KorisnickoIme',
       LEFT(PW.PasswordHash, 8) AS 'Lozinka'
FROM Person.Person AS P JOIN Person.EmailAddress AS EA
     ON P.BusinessEntityID = EA.BusinessEntityID JOIN IB130094.dbo.Predavaci AS PP
     ON EA.EmailAddress = PP.Email JOIN Person.[Password] AS PW
     ON P.BusinessEntityID = PW.BusinessEntityID

-- 5. zadatak

USE IB130094

GO
CREATE VIEW view_Predavaci
AS
SELECT P.*, LP.KorisnickoIme, LP.Lozinka
FROM Predavaci AS P JOIN LoginPodaci AS LP
     ON P.PredavacID = LP.PredavacID
GO

SELECT * FROM view_Predavaci

-- 6. zadatak

GO
CREATE PROCEDURE usp_Predavaci_SelectByKorisnickoIme
(
    @KorisnickoIme NVARCHAR(20)
)
AS
SELECT * FROM view_Predavaci
WHERE KorisnickoIme = @KorisnickoIme
GO

usp_Predavaci_SelectByKorisnickoIme 'hung-fu0'

-- 7. zadatak

GO
CREATE PROCEDURE usp_Predavaci_Insert
(
    @Ime NVARCHAR(50),
    @Prezime NVARCHAR(50),
    @Titula NVARCHAR(8),
    @Spol NCHAR(1),
    @Email NVARCHAR(50),
    @KorisnickoIme NVARCHAR(20),
    @Lozinka NVARCHAR(20)
)
AS
INSERT INTO Predavaci(Ime, Prezime, Titula, Spol, Email)
       VALUES(@Ime, @Prezime, @Titula, @Spol, @Email)
INSERT INTO LoginPodaci(PredavacID, KorisnickoIme, Lozinka)
       VALUES(@@IDENTITY, @KorisnickoIme, @Lozinka)
GO

usp_Predavaci_Insert 'Admir', 'Šehidić', 'asistent', 'M', 'admir@edu.fit.ba', 'asehidic', 'testnalozinka'
usp_Predavaci_Insert 'Denis', 'Mušić', 'profesor', 'M', 'denis@fit.ba', 'denis', 'drugalozinka'


SELECT * FROM view_Predavaci

-- 8. zadatak

GO
CREATE PROCEDURE usp_Predavaci_Update
(
    @PredavacID INT,
    @Ime NVARCHAR(50),
    @Prezime NVARCHAR(50),
    @Titula NVARCHAR(8),
    @Spol NCHAR(1),
    @Email NVARCHAR(50),
    @Lozinka NVARCHAR(20)
)
AS
UPDATE Predavaci SET Ime = @Ime,
             Prezime = @Prezime,
             Titula = @Titula,
                 Spol = @Spol,
             Email = @Email
WHERE PredavacID = @PredavacID
UPDATE LoginPodaci SET Lozinka = @Lozinka
WHERE PredavacID = @PredavacID
GO

usp_Predavaci_Update 9, 'Admir', 'Šehidić', 'asis.', 'M', 'admir@fit.ba', 'drugalozinka'

SELECT * FROM view_Predavaci

-- 9. zadatak

GO
CREATE PROCEDURE usp_Predavaci_Delete
(
    @PredavacID INT
)
AS
DELETE FROM LoginPodaci
WHERE PredavacID = @PredavacID
DELETE FROM Predavaci
WHERE PredavacID = @PredavacID
GO

usp_Predavaci_Delete 10

-- 10. zadatak

GO
CREATE TRIGGER tr_Predavaci_Delete
ON LoginPodaci INSTEAD OF DELETE
AS
PRINT 'Brisanje predavača je zabranjeno!'
ROLLBACK;
GO

DELETE FROM Predavaci
WHERE PredavacID = 6