Septembarski rok

CREATE DATABASE sara1901
ON
(NAME=sara1901_dat, FILENAME='C:\BP2\DATA\sara1901.mdf', SIZE=20MB, MAXSIZE=UNLIMITED, FILEGROWTH=10%)
LOG ON
(NAME=sara1901_log, FILENAME='C:\BP2\LOG\sara1901.ldf', SIZE=2MB, MAXSIZE=UNLIMITED, FILEGROWTH=5%)

USE sara1901
GO
CREATE TABLE Klijenti
(
KlijentID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Ime nvarchar (30) NOT NULL,
Prezime nvarchar(30) NOT NULL,
Telefon nvarchar (20) NOT NULL,
Mail nvarchar (50) UNIQUE NOT NULL,
BrojRacuna nvarchar (15) NOT NULL,
KorisnickoIme nvarchar (20) NOT NULL,
Lozinka nvarchar (20) NOT NULL
);

GO
CREATE TABLE Transakcije
(
TransakcijaID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Datum SMALLDATETIME NOT NULL,
TipTransakcije nvarchar (30) NOT NULL,
PosiljalacID int FOREIGN KEY REFERENCES Klijenti (KlijentID) NOT NULL,
PrimalacID int FOREIGN KEY REFERENCES Klijenti (KlijentID) NOT NULL,
Svrha nvarchar (50) NOT NULL,
Iznos decimal NOT NULL
);

/*Popunjavanje tabela podacima:
a) Koristeći bazu podataka AdventureWorks2014,
preko INSERT i SELECT komande importovati 10 kupaca u tabelu Klijenti.
Ime, prezime, telefon, mail i broj računa (AccountNumber) preuzeti od kupca,
korisničko ime generisati na osnovu imena i prezimena u formatu ime.prezime,
a lozinku generisati na osnovu polja PasswordHash, i to uzeti samo zadnjih 8 karaktera.*/

GO
INSERT INTO Klijenti
(Ime, Prezime, Telefon, Mail, BrojRacuna, KorisnickoIme, Lozinka)
SELECT TOP 10
			pp.FirstName AS [ime],
			pp.LastName AS [prezime],
			ppp.PhoneNumber AS [telefon],
			pea.EmailAddress AS [mail],
			sc.AccountNumber AS [broj racuna],
			pp.FirstName + '.' + pp.LastName AS [korisnicko ime],
			RIGHT(ppass.PasswordHash,8) AS [lozinka]
FROM AdventureWorks2014.Person.Person AS pp
INNER JOIN AdventureWorks2014.Person.PersonPhone AS ppp ON pp.BusinessEntityID=ppp.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.EmailAddress AS pea ON pp.BusinessEntityID=pea.BusinessEntityID
INNER JOIN AdventureWorks2014.Sales.Customer AS sc ON pp.BusinessEntityID=sc.PersonID
INNER JOIN AdventureWorks2014.Person.Password AS ppass ON pp.BusinessEntityID=ppass.BusinessEntityID

SELECT RIGHT(PasswordHash,8)
FROM AdventureWorks2014.Person.Password

SELECT AccountNumber
FROM AdventureWorks2014.Sales.Customer

SELECT *
FROM Klijenti
/*b) Putem jedne INSERT komande u tabelu Transakcije dodati minimalno 10 transakcija.*/
GO
INSERT INTO Transakcije (Datum, TipTransakcije, PosiljalacID, PrimalacID, Svrha, Iznos)
VALUES
('2016-10-31 18:05:00', 'povrat','2', '2', 'kupovina', '1021'),
('2014-10-31 19:05:00', 'povrat','1', '1', 'kupovina', '1001'),
('2015-08-14 18:06:01', 'uplata','2', '2', 'rata', '205'),
('2015-12-26 15:07:12', 'povrat','3', '3', 'kupovina', '333'),
('2017-01-19 16:45:31', 'povrat','1', '1', 'kupovina', '11'),
('2013-01-01 19:05:00', 'povrat','1', '1', 'kupovina', '10'),
('2014-02-02 18:06:01', 'uplata','2', '2', 'rata', '20'),
('2015-03-03 15:07:12', 'povrat','3', '3', 'kupovina', '3'),
('2016-04-01 19:14:53', 'uplata','4', '4', 'rata', '50'),
('2013-11-11 11:15:00', 'povrat','1', '1', 'kupovina', '100')

SELECT *
FROM Transakcije

/*3. Kreiranje indeksa u bazi podataka nada tabelama:
a) Non-clustered indeks nad tabelom Klijenti.
Potrebno je indeksirati Ime i Prezime. Također, potrebno je uključiti kolonu BrojRacuna.
*/
GO
CREATE NONCLUSTERED INDEX Klijenti_index
ON Klijenti (Ime, Prezime)
INCLUDE (BrojRacuna)
/*b) Napisati proizvoljni upit nad tabelom Klijenti koji u potpunosti iskorištava indeks iz prethodnog koraka.
Upit obavezno mora imati filter.*/
GO
SELECT Ime, Prezime, BrojRacuna
FROM Klijenti

/*c) Uraditi disable indeksa iz koraka a)*/
GO
ALTER INDEX Klijenti_index
ON Klijenti
DISABLE;

/*4. Kreirati uskladištenu proceduru koja će vršiti upis novih klijenata.
Kao parametre proslijediti sva polja. Provjeriti ispravnost kreirane procedure.*/
CREATE PROCEDURE Upis
@imeklijenta nvarchar (30),
@prezimeKlijenta nvarchar (30),
@telefonKlijenta nvarchar (20),
@mailKlijenta nvarchar (50),
@brojRacunaKlijenta nvarchar (15),
@korisnickoImeKlijenta nvarchar (20),
@lozinkaKlijenta nvarchar (20)
AS
	BEGIN
		INSERT INTO Klijenti
		(Ime, Prezime, Telefon, Mail, BrojRacuna, KorisnickoIme, Lozinka)
		VALUES
		(@imeklijenta, @prezimeKlijenta, @telefonKlijenta, @mailKlijenta,@brojRacunaKlijenta,
		@korisnickoImeKlijenta,@lozinkaKlijenta)
END;

EXECUTE Upis 'Sara', 'Saric' ,'033222111', '[email protected]', '11AA', 'sara.saric','sara123-'

SELECT *
FROM Klijenti

/*5. Kreirati view sa sljedećom definicijom.
Objekat treba da prikazuje datum transakcije, tip transakcije, ime i prezime pošiljaoca (spojeno),
broj računa pošiljaoca, ime i prezime primaoca (spojeno), broj računa primaoca, svrhu i iznos transakcije.*/
GO
CREATE VIEW Pogled
AS
SELECT  t.Datum,
		t.TipTransakcije,
		kposiljalac.Ime+''+kposiljalac.Prezime AS [ime i prezime pošiljaoca],
		kposiljalac.BrojRacuna AS [broj računa pošiljaoca],
		kprimaoc.Ime+''+kprimaoc.Prezime AS [ime i prezime primaoca],
		kprimaoc.BrojRacuna AS [broj računa primaoca],
		t.Svrha,
		t.Iznos
FROM Transakcije AS t
INNER JOIN Klijenti AS kposiljalac ON t.PosiljalacID=kposiljalac.KlijentID
INNER JOIN Klijenti AS kprimaoc ON t.PrimalacID=kprimaoc.KlijentID

/*6.reirati uskladištenu proceduru koja će na osnovu unesenog broja računa pošiljaoca prikazivati
sve transakcije koje su provedene sa računa klijenta. U proceduri koristiti prethodno kreirani view.
Provjeriti ispravnost kreirane procedure.*/
CREATE PROCEDURE UnosRacuna
@brojRacunaPosiljaoca nvarchar (30)
AS
	BEGIN
		SELECT Pogled.[ime i prezime pošiljaoca],
			   Pogled.Datum,
			   Pogled.Iznos,
			   Pogled.Svrha,
			   Pogled.TipTransakcije
		FROM Pogled
		WHERE Pogled.[broj računa pošiljaoca]=@brojRacunaPosiljaoca
	END;

EXECUTE UnosRacuna 'AW00011062'

SELECT BrojRacuna
FROM Klijenti

/*7. Kreirati upit koji prikazuje sumaran iznos svih transakcija po godinama,
sortirano po godinama. U rezultatu upita prikazati samo dvije kolone:
kalendarska godina i ukupan iznos transakcija u godini.*/

SELECT SUM(Iznos) AS [ukupan iznos transakcija u godini],
	   DATEPART(YYYY, Datum) AS [kalendarska godina]
FROM Transakcije
GROUP BY Datum
ORDER BY Datum DESC

/*8. Kreirati uskladištenu proceduru koje će vršiti brisanje klijenta uključujući sve njegove transakcije,
bilo da je za transakciju vezan kao pošiljalac ili kao primalac. Provjeriti ispravnost kreirane procedure.*/
CREATE PROCEDURE BrisanjeKlijenata
@kkkk nvarchar(30)
AS
	BEGIN

	END;
/*9. Kreirati uskladištenu proceduru koja će na osnovu unesenog broja računa ili prezimena pošiljaoca vršiti
pretragu nad prethodno kreiranim view-om (zadatak 5). Testirati ispravnost procedure u sljedećim situacijama:
a) Nije postavljena vrijednost niti jednom parametru (vraća sve zapise)
b) Postavljena je vrijednost parametra broj računa,
c) Postavljena je vrijednost parametra prezime,
d) Postavljene su vrijednosti oba parametra.*/
CREATE PROCEDURE Pretraga
@brojRacunPosiljaoca nvarchar (30) ='',
@prezimePosiljaoca nvarchar (30)=''
AS
	BEGIN

		SELECT*
		FROM Pogled
		WHERE Pogled.[broj računa pošiljaoca] LIKE @brojRacunPosiljaoca
		OR Pogled.[ime i prezime pošiljaoca] LIKE @prezimePosiljaoca
	END;

ALTER PROCEDURE Pretraga
@brojRacunPosiljaoca nvarchar (30) ='',
@prezimePosiljaoca nvarchar (30)=''
AS
	BEGIN

		SELECT*
		FROM Pogled
		WHERE Pogled.[broj računa pošiljaoca] LIKE @brojRacunPosiljaoca
		OR Pogled.[ime i prezime pošiljaoca] LIKE @prezimePosiljaoca
	END;

--a) Nije postavljena vrijednost niti jednom parametru (vraća sve zapise)
EXECUTE Pretraga
--ne radi

--b) Postavljena je vrijednost parametra broj računa,
EXECUTE Pretraga 'AW00011062'

--c) Postavljena je vrijednost parametra prezime,
EXECUTE Pretraga 'NoahPowell'
--ne radi

--d) Postavljene su vrijednosti oba parametra.
EXECUTE Pretraga @brojRacunPosiljaoca='AW00011062' , @prezimePosiljaoca='NoahPowell'


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

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

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