BPII-Integralni vježba

/*
1. Kroz SQL kod, napraviti bazu podataka koja nosi ime vašeg broja dosijea. U postupku kreiranja u obzir uzeti samo DEFAULT postavke.
Napomena: Student može dobiti ocjenu iz više predmeta, dok iz istog predmeta ocjenu može dobiti više studenata.
Student ne može dobiti više ocjena iz istog predmeta.
*/

CREATE DATABASE Integralni

USE Integralni
GO

--a) Studenti
--i. StudentID, automatski generator vrijednosti i primarni ključ
--ii. BrojDosijea, polje za unos 10 UNICODE karaktera (obavezan unos), jedinstvena vrijednost
--iii. Ime, polje za unos 35 UNICODE karaktera (obavezan unos)
--iv. Prezime, polje za unos 35 UNICODE karaktera (obavezan unos)
--v. Godina studija, polje za unos cijelog broja (obavezan unos)
--vi. NacinStudiranja, polje za unos 10 UNICODE karaktera (obavezan unos) DEFAULT je Redovan
--vii. Email, polje za unos 50 karaktera (nije obavezan)
CREATE TABLE Studenti
(
StudentID int IDENTITY (1,1) PRIMARY KEY NOT NULL,
BrojDosijea nvarchar (10) UNIQUE NOT NULL,
Ime nvarchar (35) NOT NULL,
Prezime nvarchar (35) NOT NULL,
GodinaStudija int NOT NULL,
NacinStudiranja nvarchar (10) NOT NULL default 'Redovan',
Email nvarchar (50)
)
SELECT * FROM Studenti

--b) Predmeti
--i. PredmetID, automatski generator vrijednosti i primarni ključ
--ii. Naziv, polje za unos 100 UNICODE karaktera (obavezan unos)
--iii. Oznaka, polje za unos 10 UNICODE karaktera (obavezan unos), jedinstvena vrijednost
CREATE TABLE Predmet
(
PredmetID int IDENTITY (1,1) PRIMARY KEY NOT NULL,
Naziv nvarchar (100) NOT NULL,
Oznaka nvarchar (10) UNIQUE NOT NULL
)
SELECT * FROM Predmet

--c) Ocjene
--i. Ocjena, polje za unos cijelih brojeva (obavezan unos)
--ii. Bodovi, polje za unos decimalnih brojeva (obavezan unos)
--iii. DatumPolaganja, polje za unos datuma (obavezan unos)
CREATE TABLE Ocjene
(
StudentID int FOREIGN KEY REFERENCES Studenti (StudentID),
PredmetID int FOREIGN KEY REFERENCES Predmet (PredmetID),
Ocjena int NOT NULL,
Bodovi decimal NOT NULL,
DatumPolaganja date NOT NULL,
PRIMARY KEY (StudentID, PredmetID)
)
SELECT * FROM Ocjene

--2. Popunjavanje tabela podacima:
--a) Putem jedne komande INSERT u tabelu Predmeti dodati minimalno 3 predmeta
INSERT INTO Predmet (Naziv, Oznaka)
VALUES
('Bosanski jezik', 'BOS'),
('Matematika', 'MAT'),
('Sport', 'TZO')
SELECT * FROM Predmet
--b) Koristeći bazu podataka AdventureWorks2014, preko INSERT i SELECT komande
--importovati 10 kupaca u tabelu Studenti i to sljedeće kolone:
--i. AccountNumber -> BrojDosijea
--ii. FirstName -> Ime
--iii. LastName -> Prezime
--iv. 2 -> GodinaStudija
--v. DEFAULT -> NacinStudiranja
--vi. EmailAddress -> Email
INSERT INTO Studenti (BrojDosijea, Ime, Prezime, GodinaStudija, Email)
SELECT TOP 10
            sc.AccountNumber AS [Broj dosijea],
            pp.FirstName AS Ime,
            pp.LastName AS Prezime,
            2 AS [Godina studija],
            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.EmailAddress AS pea ON pp.BusinessEntityID=pea.BusinessEntityID
SELECT * FROM Studenti

--3. Kreirati uskladištenu proceduru koja će vršiti upis podataka u tabelu Ocjene (sva polja).
--Provjerom ispravnosti procedure unijeti minimalno 5 zapisa u tabelu Ocjene.
CREATE PROCEDURE usp_upisPodatakauOcjene
(
    @StudentID int,
    @PredmetID int,
    @Ocjena int,
    @Bodovi decimal,
    @DatumPolaganja date
)
AS
    BEGIN
        INSERT INTO Ocjene(StudentID, PredmetID, Ocjena, Bodovi, DatumPolaganja)
        VALUES (@StudentID, @PredmetID, @Ocjena, @Bodovi, @DatumPolaganja)
    END

EXEC usp_upisPodatakauOcjene '1', '1', '5', '100', '2017-6-16'
EXEC usp_upisPodatakauOcjene '2', '2', '4', '80', '2017-2-15'
EXEC usp_upisPodatakauOcjene '3', '3', '3', '50', '2017-3-14'
EXEC usp_upisPodatakauOcjene '4', '1', '2', '15', '2017-4-13'
EXEC usp_upisPodatakauOcjene '5', '2', '5', '95', '2017-5-12'
SELECT * FROM Ocjene

--4. Također, u svoju bazu podataka putem Import/Export alata prebaciti sljedeće tabele sa podacima:
--CreditCard, PersonCreditCard i Person koje se nalaze u AdventureWorks2014 bazi podataka
/*Postupak:
    -> Right click na ime baze (Integralni),
    -> Tasks
    -> Import data... (ili export ako se npr kaze da se u excel nesto spremi ili u drugu bazu)
    -> Odabere se iz padajuce liste zadnja opcija (SQL Server Native bla bla),
    -> Unese se ime servera, .(tacka) ili (local) ili ako nam je data IP adresa unesemo nju
        i tek nakon toga se u padajucoj listi DATABASE odabere baze iz koje uzimamo podatke (AW2014)
    -> Odaberemo destinaciju, u ovom slucaju opet se radi o SQL Server native...
       Defaultno bi nam trebao biti oznacen nas server i baza na koju smo kliknuli,
       u ovom slucaju nista ne treba dirati.
    -> Odaberemo prvu opciju, jer zelimo odabrati tabele koje zelimo prebaciti
    -> "Nakliknamo" zeljene tabele
    -> Next -> FINISH
*/

/*5. 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
CREATE NONCLUSTERED INDEX index_Person
ON Person.Person (LastName, FirstName)
INCLUDE (Title)
--b
SELECT LastName, FirstName, Title
FROM Person.Person
--c
ALTER INDEX index_Person
ON Person.Person
DISABLE;
--d
CREATE CLUSTERED INDEX index_CreditCard
ON Sales.CreditCard (CreditCardID)
--e
CREATE NONCLUSTERED INDEX index_CreditCardInclude
ON Sales.CreditCard(CreditCardID)
INCLUDE (ExpMonth, ExpYear)

/*6. 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.*/
GO
ALTER VIEW view_prikaz
AS
    SELECT pp.FirstName AS Ime,
            pp.LastName AS Prezime,
            scc.CardNumber AS [Broj kartice],
            scc.CardType AS [Tip 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 LIKE 'Vista' AND pp.Title IS NULL

SELECT * FROM view_prikaz

/*7. Napraviti full i diferencijalni backup baze podataka na default lokaciju servera:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup*/

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

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

/*8. Mapirati login sa SQL Server-a pod imenom „student“ u svoju bazu kao korisnika pod svojim imenom.*/
CREATE LOGIN student
WITH PASSWORD ='--*12345ab'

CREATE USER Sara FOR LOGIN student

/*9. 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).*/
GO
ALTER PROCEDURE usp_pretragaViewa
(
    @prezime nvarchar (50) = NULL,
    @ime nvarchar (50)=NULL,
    @brojKartice nvarchar (25)=NULL
)
AS
    BEGIN
        SELECT *
        FROM view_prikaz
        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 @brojKartice IS NULL)
    END;

--a
EXEC usp_pretragaViewa
--b
EXEC usp_pretragaViewa @prezime = 'Zeng'
--c
EXEC usp_pretragaViewa @prezime = 'Tang', @ime='Warren'
--d
EXEC usp_pretragaViewa 'Tang', 'Warren', '11111516664744'
--*************************************************
EXEC usp_pretragaViewa @prezime = 'Z%'
EXEC usp_pretragaViewa @prezime = 'T$', @ime='W%'
EXEC usp_pretragaViewa 'T%', 'W%', '11111516664744'

/*
10. 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_dltCeditCard
(
    @CardNumber nvarchar (25)
)
AS
    BEGIN
    DELETE FROM Sales.PersonCreditCard
    FROM Sales.PersonCreditCard AS pcc
        INNER JOIN Sales.CreditCard AS scc ON pcc.CreditCardID=scc.CreditCardID
    DELETE FROM Sales.CreditCard
    WHERE CardNumber LIKE @CardNumber
    END;

EXEC usp_dltCeditCard '11111516664744'