klijenti tran. vježbanje

/*1. Kroz SQL kod, napraviti bazu podataka koja nosi ime vašeg broja dosijea. U postupku kreiranja u obzir uzeti samo DEFAULT postavke.
Unutar svoje baze podataka kreirati tabele sa sljedećom strukturom:
a) Klijenti
i. KlijentID, automatski generator vrijednosti i primarni ključ
ii. Ime, polje za unos 30 UNICODE karaktera (obavezan unos)
iii. Prezime, polje za unos 30 UNICODE karaktera (obavezan unos)
iv. Telefon, polje za unos 20 UNICODE karaktera (obavezan unos)
v. Mail, polje za unos 50 UNICODE karaktera (obavezan unos), jedinstvena vrijednost
vi. BrojRacuna, polje za unos 15 UNICODE karaktera (obavezan unos)
vii. KorisnickoIme, polje za unos 20 UNICODE karaktera (obavezan unos)
viii. Lozinka, polje za unos 20 UNICODE karaktera (obavezan unos)*/
CREATE DATABASE imeNeko

USE imeNeko
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
)
/*b) Transakcije
i. TransakcijaID, automatski generator vrijednosti i primarni ključ
ii. Datum, polje za unos datuma i vremena (obavezan unos)
iii. TipTransakcije, polje za unos 30 UNICODE karaktera (obavezan unos)
iv. PosiljalacID, referenca na tabelu Klijenti (obavezan unos)
v. PrimalacID, referenca na tabelu Klijenti (obavezan unos)
vi. Svrha, polje za unos 50 UNICODE karaktera (obavezan unos)
vii. Iznos, polje za unos decimalnog broja (obavezan unos)*/
CREATE TABLE Transakcije
(
TransakcijaID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Datum datetime 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
)

/*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.*/
INSERT INTO Klijenti (Ime, Prezime, Mail, Telefon, BrojRacuna, KorisnickoIme, Lozinka)
SELECT TOP 10
            pp.FirstName AS Ime,
            pp.LastName AS Prezime,
            pea.EmailAddress AS Email,
            pphone.PhoneNumber AS Telefon,
            sc.AccountNumber AS [Broj racuna],
            pp.FirstName+'.'+pp.LastName AS [Korinsicko ime],
            RIGHT(ppass.PasswordHash,8) AS Lozinka
FROM AdventureWorks2014.Person.Person AS pp
INNER JOIN AdventureWorks2014.Person.PersonPhone AS pphone ON pp.BusinessEntityID=pphone.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.
INSERT INTO Transakcije (Datum, TipTransakcije, PosiljalacID, PrimalacID, Svrha, Iznos)
VALUES
('2006-01-01 11:11:11', 'Slanje novca', '2', '11', 'Uplata', '100'),
('2007-02-02 12:12:12', 'Primanje novca', '3', '10', 'Isplata', '200'),
('2008-03-06 13:13:13', 'Slanje novca', '4', '9', 'Uplata', '300'),
('2009-04-04 14:14:14', 'Primanje novca', '5','8', 'Isplata' , '400'),
('2010-05-05 15:15:15', 'Slanje novca' , '6','7', 'Uplata', '500'),
('2011-06-06 16:16:16', 'Primanje novca', '7', '6', 'Isplata', '600'),
('2012-07-07 17:17:17', 'Slanje novca', '8', '5', 'Uplata', '700'),
('2013-08-08 18:18:18', 'Primanje novca ', '9', '4', 'Isplata', '800'),
('2014-09-09 19:19:19', 'Slanje novca' ,'10','3', 'Uplata', '900'),
('2015-10-10 20:20:20', 'Primanje novca', '11', '2' , 'Isplata', '1000')
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.
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
CREATE NONCLUSTERED INDEX index_Klijent
ON Klijenti (Ime, Prezime)
INCLUDE (BrojRacuna)
--b
SELECT Ime, Prezime, BrojRacuna
FROM Klijenti
--c
ALTER INDEX index_Klijent
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 usp_upisKlijenata
(
    @ime nvarchar(30),
    @prezime nvarchar (30),
    @telefon nvarchar (20),
    @mail nvarchar(50),
    @brojRacuna nvarchar (30),
    @korisnickoIme nvarchar(25),
    @lozinka nvarchar(15)
)
AS
    BEGIN
        INSERT INTO Klijenti (Ime, Prezime, Telefon, Mail, BrojRacuna, KorisnickoIme, Lozinka)
        VALUES (@ime, @prezime, @telefon, @mail, @brojRacuna, @korisnickoIme, @lozinka)
    END;
EXEC usp_upisKlijenata 'Sara', 'Saric', '123456', 'sara@gmail.com', '1234wer', 'sara12','5554ll'
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 view_prikaz
AS
    SELECT  kposiljalac.Ime+' '+kposiljalac.Prezime AS [Ime i prezime posiljaoca],
            kposiljalac.BrojRacuna AS [Broj racuna posiljaoca],
            kprimaoc.Ime+' '+kprimaoc.Prezime AS [Ime i prezime primaoca],
            kprimaoc.BrojRacuna AS [Broj racuna primaoca],
            t.Datum AS [Datum transakcije],
            t.TipTransakcije AS [Tip transakcije],
            t.Svrha AS [Svrha transakcije],
            t.Iznos AS [iznos transakcije]
    FROM Transakcije AS t
    INNER JOIN Klijenti AS kprimaoc ON t.PrimalacID=kprimaoc.KlijentID
    INNER JOIN Klijenti AS kposiljalac ON t.PosiljalacID=kposiljalac.KlijentID
SELECT * FROM view_prikaz

/*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
ALTER PROCEDURE usp_prikazView
(
    @brojracunaPosiljaoca nvarchar(25)
)
AS
    BEGIN
        SELECT view_prikaz.[Datum transakcije], view_prikaz.[Tip transakcije], view_prikaz.[Svrha transakcije],
                view_prikaz.[Ime i prezime posiljaoca], view_prikaz.[Broj racuna primaoca], view_prikaz.[Svrha transakcije],
                view_prikaz.[iznos transakcije]
        FROM view_prikaz
        WHERE view_prikaz.[Broj racuna posiljaoca] LIKE @brojracunaPosiljaoca
    END;
SELECT * FROM Klijenti
EXEC usp_prikazView 'AW00011000'

/*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(t.Iznos) AS [Suma svih iznosa], YEAR(t.Datum) AS Godina
FROM Transakcije AS t
GROUP BY t.Datum
ORDER BY 2 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 usp_deleteKlijenta
(
    @posiljalacID nvarchar(30),
    @primalacID nvarchar(30),
    @transakcije nvarchar (30)
)
AS
    BEGIN
        DELETE FROM Transakcije
        WHERE PosiljalacID=@posiljalacID

        DELETE FROM Transakcije
        WHERE PrimalacID=@primalacID

        DELETE FROM Transakcije
        WHERE TransakcijaID=@transakcije

    END;
EXEC usp_deleteKlijenta '2', '1', '2'
SELECT * FROM Transakcije

/*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.*/
GO
ALTER PROCEDURE usp_viewPretraga
(
    @brojRacuna nvarchar (30) ='',
    @prezimePosailjaoca nvarchar (30) =''
)
AS
    BEGIN
        SELECT*
        FROM view_prikaz
        WHERE view_prikaz.[Broj racuna posiljaoca] LIKE @brojRacuna +'%'
         OR view_prikaz.[Ime i prezime posiljaoca] LIKE @prezimePosailjaoca +'%'
    END;
--a
SELECT * FROM view_prikaz
EXECUTE usp_viewPretraga
EXECUTE usp_viewPretraga 'AW00011076'
EXECUTE usp_viewPretraga @prezimePosailjaoca='Blake Anderson'
EXECUTE usp_viewPretraga 'AW00011076', 'Blake Anderson'