BP2_Integralni

--1
CREATE DATABASE VASA_BAZA

USE VASA_BAZA

CREATE TABLE Studenti(
StudentID int PRIMARY KEY IDENTITY(1,1),
BrojDosijea nvarchar (10) CONSTRAINT UQ_BrojDosijea 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)
)
GO

CREATE TABLE Predmeti (
PredmetID int PRIMARY KEY IDENTITY(1,1),
Naziv nvarchar(100) NOT NULL,
Oznaka nvarchar (10) CONSTRAINT UQ_Oznaka UNIQUE NOT NULL,
)
GO

CREATE TABLE Ocjene (
StudentID int CONSTRAINT FK_Student_Ocjene FOREIGN KEY (StudentID) REFERENCES Studenti(StudentID),
PredmetID int CONSTRAINT FK_Predemt_Ocjene FOREIGN KEY (PredmetID) REFERENCES Predmeti(PredmetID),
Ocjena int NOT NULL,
Bodovi decimal NOT NULL,
DatumPologanja datetime NOT NULL
)
GO

--2
INSERT INTO Predmeti
VALUES ('Baze Podataka 2', 'BP2'),
('Programiranje III', 'PR3'),
('Komunikacijske Tehnologije', 'KT')
GO

INSERT INTO Studenti (BrojDosijea, Ime, Prezime, GodinaStudija, Email)
SELECT C.AccountNumber, P.FirstName, P.LastName, 2, EA.EmailAddress
FROM AdventureWorks2014.Person.Person AS P
INNER JOIN AdventureWorks2014.Sales.Customer AS C
ON P.BusinessEntityID = C.CustomerID
INNER JOIN AdventureWorks2014.Person.EmailAddress AS EA
ON P.BusinessEntityID = EA.BusinessEntityID
GO

--3
CREATE PROCEDURE usp_Insert_Ocjene
@StudentID int,
@PredmetID int,
@Ocjena int,
@Bodovi decimal,
@DatumPologanja datetime

AS

INSERT INTO Ocjene
VALUES (@StudentID, @PredmetID, @Ocjena, @Bodovi, @DatumPologanja)
GO


SELECT * FROM Studenti
GO

EXEC usp_Insert_Ocjene 1,1,10,90, '1994-10-11'
GO
EXEC usp_Insert_Ocjene 2,1,10,90, '1994-10-11'
GO
EXEC usp_Insert_Ocjene 3,1,10,90, '1994-10-11'
GO
EXEC usp_Insert_Ocjene 4,1,10,90, '1994-10-11'
GO
EXEC usp_Insert_Ocjene 5,1,10,90, '1994-10-11'
GO

/*
ZADATAK 4 MORATE IMPORTOVATI TABELE IZ ADVENTUREWORKS2014 (Person.Person, Sales.PersonCreditCard, Sales.CreditCard)
*/


--5
CREATE NONCLUSTERED INDEX IX_Person_FName_LName
    ON Person.Person
    (FirstName,LastName)
	INCLUDE (Title)
GO

SELECT * FROM Person.Person WITH(INDEX(IX_Person_FName_LName))
GO

ALTER INDEX IX_Person_FName_LName ON Person.Person DISABLE
GO


CREATE CLUSTERED INDEX IX_CreditCard_CreditCardID
    ON Sales.CreditCard
    (CreditCardID)
GO

CREATE NONCLUSTERED INDEX IX_CreditCard_CardNumber
	ON Sales.CreditCard
	(CardNumber)
	INCLUDE (ExpMonth, ExpYear)
GO

--6
CREATE VIEW View_Person_Credit
AS
SELECT P.LastName, P.FirstName, CC.CardNumber, CC.CardType
FROM Person.Person AS P
INNER JOIN Sales.PersonCreditCard AS PCC
ON P.BusinessEntityID = PCC.BusinessEntityID
INNER JOIN Sales.CreditCard AS CC
ON PCC.CreditCardID = CC.CreditCardID
WHERE CC.CardType LIKE 'Vista'
GO

--7
BACKUP DATABASE VASA_BAZA
TO DISK = 'LOKACIJA NA DISKU'
WITH NOFORMAT
GO

BACKUP DATABASE VASA_BAZA
TO TAPE = 'LOKACIJA NA DISKU'
WITH DIFFERENTIAL
GO

--8
CREATE LOGIN student WITH PASSWORD ='nesto!123', DEFAULT_DATABASE = VASA_BAZA
GO

CREATE USER VAS_INDEX FROM LOGIN student
GO

--9
CREATE PROCEDURE usp_PersonCard_Search
@LastName nvarchar(50) = NULL,
@FirstName nvarchar (50) = NULL,
@CardNumber nvarchar(25) = NULL
AS
SELECT *
FROM View_Person_Credit
WHERE LastName LIKE @LastName+'%' OR @LastName = NULL
AND FirstName LIKE @FirstName+'%' OR @FirstName = NULL
AND CardNumber = @CardNumber OR @CardNumber = NULL
GO

EXEC usp_PersonCard_Search 'Uddin'
GO

--10
CREATE PROCEDURE usp_PersonCredit_DELETE
@CardNumber nvarchar(50)
AS

DECLARE @testID int =
(SELECT CreditCardID FROM Sales.CreditCard AS C WHERE CardNumber = @CardNumber)

DELETE FROM Sales.CreditCard
WHERE CardNumber = @CardNumber

DELETE FROM Sales.PersonCreditCard
WHERE CreditCardID = @testID
GO