--04.07.2015

--04.07.2015

CREATE DATABASE IB150XX3
ON
(NAME=IB150XX3_mdf,FILENAME='C:\BP2\DATA1.mdf',Size=100 MB, MaxSize=500MB,FileGrowth=15%)
LOG ON
(NAME=IB150XX3_ldf,FILENAME='C:\BP2\LOG1.ldf',Size=10MB,MaxSize=Unlimited,FileGrowth=5MB)
GO

USE IB150XX3

--2
CREATE TABLE Klijenti
(
KlijentID int identity (1,1) constraint KlijentID Primary Key,
JMBG nvarchar (13) not null constraint UQ_JMBG unique,
Ime nvarchar (30) not null,
Prezime nvarchar (30) not null,
Adresa nvarchar (100) not null,
Telefon nvarchar (20) not null,
Email nvarchar (50) constraint UQ_Email unique,
Kompanija nvarchar (50)
);
GO
CREATE TABLE Krediti
(
KreditID int identity (1,1) constraint PK_KreditID Primary Key,
Datum date not null,
Namjena nvarchar (50) not null,
Iznos decimal not null,
BrojRata int not null,
Osiguran bit not null,
Opis ntext
);

CREATE TABLE Otplate
(
KlijentID int constraint FK_OtplateKlijentID foreign key references Klijenti(KlijentID),
KreditID int constraint FK_OtplateKreditID foreign key references Krediti(KreditID),
Constraint PK_Otplate_Klijenti_Krediti Primary Key (KlijentID,KreditID),
Datum date not null,
Iznos decimal not null,
Rata int not null,
Opis ntext
);

--3
INSERT INTO Klijenti (JMBG,Ime,Prezime,Adresa,Telefon,Email,Kompanija)
SELECT TOP 10 REPLACE(RIGHT(PP.rowguid,13),'-','1'),PP.FirstName,PP.LastName,PA.AddressLine1,PPP.PhoneNumber,PEA.EmailAddress,'FIT'
FROM AdventureWorks2014.Person.Person AS PP
INNER JOIN
AdventureWorks2014.Person.EmailAddress AS PEA
ON PP.BusinessEntityID=PEA.BusinessEntityID
INNER JOIN
AdventureWorks2014.Person.PersonPhone AS PPP
ON PP.BusinessEntityID=PPP.BusinessEntityID
INNER JOIN
AdventureWorks2014.Person.BusinessEntityAddress AS BPEA
ON PP.BusinessEntityID=BPEA.BusinessEntityID
INNER JOIN
AdventureWorks2014.Person.Address AS PA
ON BPEA.AddressID=PA.AddressID

SELECT * FROM Klijenti

GO
--B
INSERT INTO Krediti (Datum,Namjena,Iznos,BrojRata,Osiguran,Opis)
VALUES
('01.01.2017','kred za auto',15000,15,0,'kredit_za_auto'),
('02.01.2017','kred za stan',1500,10,1,'kredit_za_stan'),
('03.01.2017','kred za faks',150,55,0,'kredit_za_faks')

SELECT * FROM Krediti

--4
CREATE PROCEDURE usp_Otplate_Insert
(@klijentID int,@kreditID int,@Datum date,@iznos decimal,@Rata int,@Opis ntext)
AS
    BEGIN
    INSERT INTO Otplate (KlijentID,KreditID,Datum,Iznos,Rata,Opis)
    VALUES (@klijentID,@kreditID,@Datum,@iznos,@Rata,@Opis)
    END;

SELECT * FROM Krediti
SELECT * FROM Klijenti

SELECT * FROM Otplate

EXEC usp_Otplate_Insert 18799,1,'12.01.2017',500,5,'KREDIT'
EXEC usp_Otplate_Insert 18800,1,'11.01.2017',300,3,'KREDIT'
EXEC usp_Otplate_Insert 18800,2,'10.01.2017',100,1,'KREDIT'

--5
CREATE VIEW view_Krediti_Otplate
AS
SELECT KK.JMBG AS JMBG,KK.Ime AS ImeKlijenta,KK.Prezime as PrezimeKlijenta,KK.Adresa as AdresaKlijenta,KK.Telefon as TelefonKlijenta,KK.Email as EmailKlijenta,K.Datum as DatumKredita,K.Namjena as NamjenaKredita,
K.Iznos as IznosKredita,O.Rata as BrojRata,O.Iznos as Iznos
FROM Otplate AS O
INNER JOIN
Krediti AS K
ON K.KreditID=O.KreditID
INNER JOIN
Klijenti AS KK
ON O.KlijentID=KK.KlijentID

SELECT * FROM view_Krediti_Otplate
GO

--6
CREATE PROCEDURE usp_Krediti_Otplate_SelectByJMBG
@jmbg nvarchar (13)
AS
    BEGIN
    SELECT DatumKredita,NamjenaKredita,IznosKredita,BrojRata,Iznos
    FROM view_Krediti_Otplate
    WHERE view_Krediti_Otplate.JMBG=@jmbg
    END;

SELECT * FROM Klijenti
EXEC usp_Krediti_Otplate_SelectByJMBG '169CCF47C1168'

--7

SELECT * FROM Otplate
CREATE PROCEDURE usp_Otplate_Update
AS
BEGIN
UPDATE Otplate
SET Iznos=1200
WHERE
KlijentID=18800
END;

EXEC usp_Otplate_Update

--8
CREATE PROCEDURE usp_Krediti_Delete
@kreditID int
AS
BEGIN
DELETE FROM Otplate
WHERE Otplate.KreditID
=
(SELECT Krediti.KreditID
FROM Krediti
WHERE Krediti.KreditID=@kreditID)

DELETE FROM Krediti
WHERE KreditID=@kreditID
END;

SELECT * FROM Krediti
SELECT * FROM Otplate

EXEC usp_Krediti_Delete 1

--9
CREATE TRIGGER PREVENTIVNO
ON Otplate
FOR DELETE
AS
PRINT 'NEMA BRISANJA!'
ROLLBACK;

DELETE Otplate

--10

BACKUP DATABASE IB150XX3
TO DISK
=
'C:\BP2\IB150XX3_FULL.bak'