BPII-Parcijalni II vjezba

/*1. Kroz SQL kod, napraviti bazu podataka koja nosi ime vašeg broja dosijea. U postupku kreiranja u obzir uzeti samo DEFAULT postavke.*/

CREATE DATABASE ParcijalniII

/*2. U svoju baze podataka putem Import/Export alata prebaciti sljedeće tabele sa podacima:
CreditCard, PersonCreditCard i Person koje se nalaze u AdventureWorks2014 bazi podataka.*/
USE ParcijalniII
GO

/*3. Kreiranje indeksa u bazi podataka nada tabelama koje ste importovali u zadatku broj 2:
a) Non-clustered indeks nad tabelom Person. Potrebno je indeksirati Lastname i FirstName. Također, potrebno je uključiti kolonu Title.
b) Napisati proizvoljni upit nad tabelom Person koji u potpunosti iskorištava indeks iz prethodnog koraka
c) Uraditi disable indeksa iz koraka a)
d) Clustered indeks nad tabelom CreditCard i kolonom CreditCardID
e) Non-clustered indeks nad tabelom CreditCard i kolonom CardNumber. Također, potrebno je uključiti kolone ExpMonth i ExpYear.*/
--a
GO
CREATE NONCLUSTERED INDEX index_Zadatak
ON Person.Person (LastName, FirstName)
INCLUDE (Title)
--b
SELECT LastName, FirstName, Title
FROM ParcijalniII.Person.Person
--c
ALTER INDEX index_Zadatak
ON Person.Person
DISABLE
--d
CREATE CLUSTERED INDEX index_Zadatak1
ON Sales.CreditCard (CreditCardID)
--e
CREATE NONCLUSTERED INDEX index_Zadatak2
ON Sales.CreditCard (CardNumber)
INCLUDE (ExpMonth, ExpYear)

/*4. Kreirati view sa sljedećom definicijom. Objekat treba da prikazuje: Prezime, ime, broj kartice i tip kartice,
ali samo onim osobama koje imaju karticu tipa Vista i nemaju titulu.*/
ALTER VIEW view_Zadatak
AS
SELECT  LastName AS Prezime,
        FirstName AS Ime,
        CardType AS [Tip kartice],
        CardNumber AS [Broj kartice]
    FROM Person.Person AS pp
    INNER JOIN Sales.PersonCreditCard AS pcc ON pp.BusinessEntityID = pcc.BusinessEntityID
    INNER JOIN Sales.CreditCard AS scc ON pcc.CreditCardID =scc.CreditCardID
    WHERE scc.CardType='Vista' AND pp.Title IS NULL
SELECT * FROM view_Zadatak

/*5. Kreirati uskladištenu proceduru koja vrši modifikaciju prezimena osobe za uneseni BusinessEntityID.
Nakon toga izvršiti proceduru i jednostavnom SELECT komandom provjeriti rezultat.*/
CREATE PROCEDURE usp_modifikacija
(
    @businessEntityID int,
    @prezime nvarchar (35)
)
    AS
    BEGIN
        UPDATE Person.Person
        SET LastName=@prezime
        WHERE BusinessEntityID=@businessEntityID
    END
EXEC usp_modifikacija

SELECT * FROM Person.Person WHERE BusinessEntityID=2190
--lastname: Rapier
EXEC usp_modifikacija @businessEntityID=2190, @prezime='Spaho'

/*6. Napraviti full i diferencijalni backup baze podataka na default lokaciju servera:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup*/
BACKUP DATABASE ParcijalniII
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ParcijalniII.bak'

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

/*7. Mapirati login sa SQL Server-a pod imenom „student“ u svoju bazu kao korisnika pod svojim imenom.*/
CREATE LOGIN _student
WITH PASSWORD='0512ad'

CREATE USER sara FOR LOGIN _student

/*8. Kreirati uskladištenu proceduru koja će za uneseno prezime, ime ili broj kartice vršiti pretragu nad prethodno kreiranim
view-om (zadatak 4). Procedura obavezno treba da vraća rezultate bez obzira da li su vrijednosti parametara postavljene.
Testirati ispravnost procedure u sljedećim situacijama:
a) Nije postavljena vrijednost niti jednom parametru (vraća sve zapise)
b) Postavljena je vrijednost parametra prezime, a ostala dva parametra nisu (pretraga po prezimenu)
c) Postavljene su vrijednosti parametara prezime i ime, a broj kartice nije (pretraga po prezimenu i imenu)
d) Postavljene su vrijednosti sva tri parametra (pretraga po svim parametrima)
Također, procedura treba da pretragu prezimena i imena vrši parcijalno (počinje sa).*/
CREATE PROCEDURE usp_unos
(
    @prezime nvarchar (50)=NULL,
    @ime nvarchar (35)=NULL,
    @brojKartice nvarchar (20)=NULL
)
AS
    BEGIN
        SELECT *
        FROM view_Zadatak
        WHERE
        (Prezime LIKE @prezime OR Prezime LIKE @prezime + '%' OR @prezime IS NULL) AND
        (Ime LIKE @ime OR Ime LIKE @ime + '%' OR @ime IS NULL) AND
        ([Broj kartice] LIKE @brojKartice OR [Broj kartice] LIKE @brojKartice + '%' OR @brojKartice IS NULL)
    END;
--a
EXEC usp_unos
--b
EXEC usp_unos @prezime='Huang'
--c
EXEC usp_unos @prezime='Huang', @ime='Laura'
--d
EXEC usp_unos @prezime='Huang', @ime='Laura',@brojKartice='11112693724565'

--b
EXEC usp_unos @prezime='H%'
--c
EXEC usp_unos @prezime='H%', @ime='L%'
--d
EXEC usp_unos @prezime='H%', @ime='L%',@brojKartice='11112693724565'
SELECT * FROM view_Zadatak

/*9. Kreirati uskladištenu proceduru koje će za uneseni broj kartice vršiti brisanje kreditne kartice (CreditCard).
Također, u istoj proceduri (u okviru jedne transakcije) prethodno obrisati sve zapise o vlasništvu kartice (PersonCreditCard).
Obavezno testirati ispravnost kreirane procedure.*/
CREATE PROCEDURE usp_delete
(
    @brojKartice nvarchar (25)
)
    AS
    BEGIN
        DELETE FROM Sales.PersonCreditCard
        FROM Sales.PersonCreditCard AS pcc
        INNER JOIN Sales.CreditCard AS scc ON pcc.CreditCardID = scc.CreditCardID
        WHERE CardNumber=@brojKartice

        DELETE FROM Sales.CreditCard
        WHERE CardNumber=@brojKartice
    END;
EXEC usp_delete 33332664695310
SELECT * FROM Sales.CreditCard

/*10. Kreirati trigger koji će spriječiti brisanje zapisa u tabeli PersonCreditCard. Testirati ispravnost kreiranog triggera.*/
CREATE TRIGGER zastita
ON Sales.PersonCreditCard
FOR DELETE
    AS
    PRINT 'Zabranjeno brisanje!!!'
    ROLLBACK;

DELETE Sales.PersonCreditCard
SELECT * FROM Sales.PersonCreditCard