Priprema2

--1

create database Priprema2

use Priprema2

create table Klijenti (
KlijentID int not null identity(1,1) constraint PK_Klijenti primary key,
Ime nvarchar(30) not null,
Prezime nvarchar(30) not null,
Telefon nvarchar(20) not null,
Email nvarchar(50) not null constraint UQ_Email UNIQUE ,
BrojRacuna nvarchar(15) not null,
KorisnickoIme nvarchar(20) not null,
Lozinka nvarchar(20) not null
)

create table Transakcije(
TransakcijaID int not null identity(1,1) constraint PK_Transakcije primary key,
Datum datetime not null,
TipTransakcije nvarchar(30) not null,
PosiljalacID int not null constraint FK_Klijenti_KlijentID foreign key references
            Klijenti(KlijentID),
PrimalacID int not null constraint FK_Klijenti_KlijentID_2 foreign key references
            Klijenti(KlijentID),
Svrha nvarchar(50) not null,
Iznos decimal(18,2) not null
)

--2
--a
use AdventureWorks2017
go

insert into Priprema2.dbo.Klijenti
select top 10 P.FirstName, P.LastName, PP.PhoneNumber, EA.EmailAddress,
C.AccountNumber, LOWER(P.FirstName+'.'+P.LastName),
 right(Pa.PasswordHash,8)
from Person.Person as P inner join Sales.Customer as C
    on P.BusinessEntityID=C.PersonID INNER JOIN Person.PersonPhone AS PP
    ON PP.BusinessEntityID=P.BusinessEntityID INNER JOIN
    Person.EmailAddress AS EA
    ON EA.BusinessEntityID=P.BusinessEntityID inner join
    Person.Password as Pa
    on P.BusinessEntityID=Pa.BusinessEntityID

--b
use Priprema2

select*from Klijenti

INSERT INTO Transakcije
values (getdate(), 'jednostrana' , 241, 242, 'poklon', 500),
(getdate(), 'dvostrana' , 243, 244, 'naknada', 350.50),
(getdate(), 'jednostrana' , 245, 246, 'poklon', 500),
(getdate(), 'dvostrana' , 241, 243, 'poklon', 400),
(getdate(), 'jednostrana' , 241, 243, 'poklon', 25),
(getdate(), 'jednostrana' , 247, 244, 'poklon', 530),
(getdate(), 'jednostrana' , 245, 243, 'poklon', 500),
(getdate(), 'jednostrana' , 246, 243, 'poklon', 500),
(getdate(), 'jednostrana' , 244, 243, 'poklon', 346),
(getdate(), 'jednostrana' , 241, 243, 'poklon', 222)

--3

create nonclustered index IX_Klijenti on Klijenti (Ime, Prezime)
include (BrojRacuna)

select K.Ime, K.Prezime, K.BrojRacuna
from Klijenti as K
where K.BrojRacuna like '%0' or K.Ime like 'E%'

ALTER INDEX IX_Klijenti on Klijenti
disable

--4

create procedure usp_Klijenti_insert
( @Ime nvarchar(30),
@Prezime nvarchar(30),
@Telefon nvarchar(20),
@Email nvarchar(50),
@BrojRacuna nvarchar(15),
@KorisnickoIme nvarchar(20),
@Lozinka nvarchar(20)
)as begin
insert into Klijenti
values (@Ime, @Prezime, @Telefon, @Email, @BrojRacuna, @KorisnickoIme, @Lozinka)
end

exec usp_Klijenti_insert 'Neki', 'Neko', '111-111-111',
'neki.neko@nekic', 'AA-1903-2017', 'AA', 'AA'

SELECT*FROM Klijenti

--5
CREATE VIEW view_transakcije_klijent AS
    SELECT T.Datum, T.TipTransakcije, K1.Ime + ' ' + K1.Prezime AS Posiljaoc, K1.BrojRacuna,
    K2.Ime + ' ' + K2.Prezime AS Primaoc, K2.BrojRacuna AS BrRacunaPrimaoca, T.Svrha, T.Iznos
    FROM Transakcije AS T INNER JOIN Klijenti AS K1
        ON T.PosiljalacID = K1.KlijentID INNER JOIN Klijenti AS K2
        ON T.PrimalacID = K2.KlijentID

SELECT * FROM Transakcije

--6

CREATE PROCEDURE usp_transakcije
(
    @BrojRacuna NVARCHAR(15)
)
AS
BEGIN
    SELECT *
    FROM view_transakcije_klijent AS V
    WHERE BrojRacuna = @BrojRacuna
END

EXEC usp_transakcije 'AW00011135'

SELECT * FROM Klijenti

--7

SELECT YEAR(T.Datum) AS Godina, SUM(T.Iznos) AS Iznos
FROM Transakcije AS T
GROUP BY T.Datum
ORDER BY YEAR(T.Datum)

--8

CREATE PROCEDURE usp_delete_klijent5
(
    @KlijentID INT
)
AS
BEGIN
    DELETE Transakcije
    FROM Transakcije AS T INNER JOIN Klijenti AS K
        ON T.PosiljalacID = K.KlijentID AND KlijentID = @KlijentID

    DELETE Transakcije
    FROM Transakcije AS T INNER JOIN Klijenti AS K
        ON T.PrimalacID = K.KlijentID AND KlijentID = @KlijentID

    DELETE FROM Klijenti
    WHERE KlijentID = @KlijentID
END

EXEC usp_delete_klijent5 7

--9
CREATE PROCEDURE usp_pretraga_view
(
@BrojRacuna NVARCHAR(15) = NULL,
@Prezime NVARCHAR(30) = NULL,

)
AS
BEGIN
    SELECT * FROM view_transakcije_klijent
 WHERE (SUBSTRING(Posiljaoc, CHARINDEX(' ', Posiljaoc)+1, 50) = @Prezime OR @Prezime is null) and (BrojRacuna = @BrojRacuna or @BrojRacuna is null)
END


EXEC usp_pretraga_view
EXEC usp_pretraga_view 'AW00011135'
EXEC usp_pretraga_view 'Lopez'
EXEC usp_pretraga_view 'Harris'
EXEC usp_pretraga_view 'AW00011135', 'Harris'

--10

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

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