Septembarski rok - Klijenti - transakcije

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

USE sara2301
GO

CREATE TABLE Klijenti
(
KlijentiID 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
);

CREATE TABLE Transkacije
(
TransakcijaID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Datum smalldatetime NOT NULL,
TipTransakcije nvarchar (30) NOT NULL,
PosiljalacID int FOREIGN KEY REFERENCES Klijenti (KlijentiID) NOT NULL,
PrimalacID int FOREIGN KEY REFERENCES Klijenti (KlijentiID) NOT NULL,
Svrha nvarchar (50) NOT NULL,
Iznos decimal NOT NULL
);

/*2.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.
b) Putem jedne INSERT komande u tabelu Transakcije dodati minimalno 10 transakcija.*/
GO
INSERT INTO Klijenti (Ime, Prezime, Telefon, Mail, BrojRacuna, KorisnickoIme, Lozinka)
SELECT TOP 10 pp.FirstName,
       pp.LastName,
       ppp.PhoneNumber,
       pea.EmailAddress,
       sc.AccountNumber,
       pp.FirstName + '.' + pp.LastName AS [korinsicko ime],
       RIGHT(ppass.PasswordHash,8)
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 * FROM Klijenti

--b) Putem jedne INSERT komande u tabelu Transakcije dodati minimalno 10 transakcija.*/
GO
INSERT INTO Transkacije (Datum, TipTransakcije, PosiljalacID, PrimalacID, Svrha, Iznos)
VALUES
('2006-01-01 11:11:11', 'Slanje novca', '121', '122', 'Uplata', '100'),
('2007-02-02 12:12:12', 'Primanje novca', '122', '121', 'Isplata', '200'),
('2008-03-06 13:13:13', 'Slanje novca', '124', '123', 'Uplata', '300'),
('2009-04-04 14:14:14', 'Primanje novca', '125','124', 'Isplata' , '400'),
('2010-05-05 15:15:15', 'Slanje novca' , '126','125', 'Uplata', '500'),
('2011-06-06 16:16:16', 'Primanje novca', '127', '126', 'Isplata', '600'),
('2012-07-07 17:17:17', 'Slanje novca', '128', '127', 'Uplata', '700'),
('2013-08-08 18:18:18', 'Primanje novca ', '129', '128', 'Isplata', '800'),
('2014-09-09 19:19:19', 'Slanje novca' ,'130','129', 'Uplata', '900'),
('2015-10-10 20:20:20', 'Primanje novca', '121', '130' , 'Isplata', '1000')

SELECT * FROM Transkacije


/*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.
b) Napisati proizvoljni upit nad tabelom Klijenti koji u potpunosti iskorištava indeks iz prethodnog koraka.
Upit obavezno mora imati filter.
c) Uraditi disable indeksa iz koraka a)*/
--a
GO
CREATE NONCLUSTERED INDEX KlijentiIndex
ON Klijenti (Ime, Prezime)
INCLUDE (BrojRacuna)
--b
GO
SELECT Ime, Prezime, BrojRacuna
FROM Klijenti
--c
ALTER INDEX KlijentiIndex
ON Klijenti
DISABLE;

/*4.Kreirati uskladištenu proceduru koja će vršiti upis novih klijenata.
Kao parametre proslijediti sva polja. Provjeriti ispravnost kreirane procedure.*/
GO
CREATE PROCEDURE UpisNovihKlijenata
@ime nvarchar (30),
@prezime nvarchar (30),
@telefon nvarchar (20),
@mail nvarchar (50),
@brojRacuna nvarchar (15),
@korinsickoIme nvarchar (20),
@lozinka nvarchar (20)
AS
    BEGIN
        INSERT INTO Klijenti (Ime, Prezime, Telefon, Mail, BrojRacuna, KorisnickoIme, Lozinka)
        VALUES (@ime, @prezime, @telefon, @mail, @brojRacuna, @korinsickoIme, @lozinka)
    END;
EXECUTE UpisNovihKlijenata 'Sara', 'Saric', '112233', '[email protected]', '123456789', 'sara.saric', '123456sara'

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 AS [datum transakcije],
           t.TipTransakcije AS [tip transakcije],
           k.Ime + ' ' + k.Prezime AS [ime i prezime posiljaoca],
           k.BrojRacuna AS [broj racuna posiljaoca],
           kprimalac.Ime + ' ' + kprimalac.Prezime AS [ime i prezime primaoca],
           kprimalac.BrojRacuna AS [broj racuna primaoca],
           t.Svrha AS [svrha transakcije],
           t.Iznos AS [iznos transakcije]
    FROM Transkacije AS t
    INNER JOIN Klijenti AS k ON t.PosiljalacID=k.KlijentiID
    INNER JOIN Klijenti AS kprimalac ON t.PrimalacID=kprimalac.KlijentiID

/*6.Kreirati 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.*/
GO
CREATE PROCEDURE PrikazTransakcija
@brojracuna nvarchar (15)
AS
    BEGIN
        SELECT  Pogled.[ime i prezime posiljaoca],Pogled.[datum transakcije], Pogled.[iznos transakcije],
                Pogled.[svrha transakcije], Pogled.[tip transakcije]
        FROM Pogled
        WHERE Pogled.[broj racuna posiljaoca] LIKE @brojracuna
    END;
EXECUTE PrikazTransakcija 'AW00011089'

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  DATEPART(YEAR, Datum) AS [Kalendarska godina],
        SUM(Iznos) AS [Iznos]
FROM Transkacije
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.*/
GO
CREATE PROCEDURE Brisanje
@transakcija nvarchar (30),
@posiljalac nvarchar (30),
@primalac nvarchar (30)
AS
    BEGIN
        DELETE Transkacije
        FROM Transkacije
        INNER JOIN Klijenti ON Transkacije.TransakcijaID=Klijenti.KlijentiID
        WHERE (TransakcijaID=@transakcija AND PosiljalacID=@posiljalac)OR PrimalacID=@primalac AND
              (KlijentiID=@klijent AND KlijentiID=@posiljalac) OR KlijentiID=@primalac
    END;
EXECUTE Brisanje '2', '121', '122'
SELECT * FROM Transkacije


/*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.*/
GO
ALTER PROCEDURE Pretragapogledom
@brojracuna nvarchar (30)='',
@prezimePosiljaoca nvarchar (30)=''
AS
    BEGIN
        SELECT *
        FROM Pogled
        WHERE Pogled.[broj racuna posiljaoca] LIKE @brojracuna +'%' AND
        Pogled.[ime i prezime posiljaoca] LIKE @prezimePosiljaoca +'%'
    END;

SELECT * FROM Pogled
EXECUTE Pretragapogledom
EXECUTE Pretragapogledom 'AW00011076'
EXECUTE Pretragapogledom @prezimePosiljaoca='Blake Anderson'
EXECUTE Pretragapogledom 'AW00011076', 'Blake Anderson'

/*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 sara2301
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\sara2301.bak'

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