ISPIT DBMS 04.07.15

/* Iz baze podataka AdventureWorks2014 prikazati sve kupce koji su u maju mjesecu 2014.
godine naručili proizvod Front Brakes u količini većoj od 5 komada. */

USE AdventureWorks2014

SELECT P.FirstName,P.LastName,PR.Name,SOH.OrderDate,SOD.OrderQty
FROM Person.Person AS P JOIN Sales.Customer AS C ON P.BusinessEntityID = C.PersonID
    JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID = SOH.CustomerID
    JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
    JOIN Production.Product AS PR ON SOD.ProductID = PR.ProductID
WHERE YEAR(SOH.OrderDate) = 2014 AND MONTH(SOH.OrderDate) = 05 AND PR.Name LIKE '%Front Brakes%' AND SOD.OrderQty>5


/*Iz baze podataka AdventureWorks2014 prikazati 5 najprodavanijih (količina prodaje)
 planinskih bicikala (Mountain Bikes) na području Kanade.*/

SELECT top 5 P.Name, SUM(SOD.OrderQty) AS kolicina, ST.Name
FROM Production.Product AS P JOIN Production.ProductSubcategory AS PS ON P.ProductSubcategoryID = PS.ProductCategoryID
        JOIN Sales.SalesOrderDetail AS SOD ON P.ProductID = SOD.ProductID
        JOIN Sales.SalesOrderHeader AS SOH ON SOD.SalesOrderID = SOH.SalesOrderID
        JOIN Sales.SalesTerritory AS ST ON SOH.TerritoryID = ST.TerritoryID
WHERE PS.Name LIKE 'Mountain Bikes' AND ST.Name = 'Canada'
GROUP BY P.Name, ST.Name
ORDER BY kolicina DESC

/*Kreirati bazu podataka koju ćete imenovati Vašim brojem dosijea. Fajlove baze smjestiti na sljedeće lokacije:
 - Data fajl -> D:\DBMS\Data
 - Log fajl -> D:\DBMS\Log*/

 CREATE DATABASE [2514] ON PRIMARY
 (
 NAME = '2514',
 FILENAME = 'c:\DBMS\2514.mdf',
 SIZE = 5MB,
 MAXSIZE = UNLIMITED,
 FILEGROWTH = 10%
 )
 LOG ON
 (
 NAME = '2514_log',
 FILENAME = 'c:\DBMS\2514log.ldf',
 SIZE = 5MB,
 MAXSIZE = UNLIMITED,
 FILEGROWTH = 10%
 )

 /* U bazi podataka kreirati sljedeće tabele:
a. Klijenti
- JMBG, polje za unos 13 karaktera (obavezan unos i jedinstvena vrijednost),
- Ime, polje za unos 30 karaktera (obavezan unos),
- Prezime, polje za unos 30 karaktera (obavezan unos),
- Adresa, polje za unos 100 karaktera (obavezan unos),
- Telefon, polje za unos 20 karaktera (obavezan unos),
- Email, polje za unos 50 karaktera (jedinstvena vrijednost),
- Kompanija, polje za unos 50 karaktera.*/

CREATE TABLE Klijenti(
    KlijentID INT IDENTITY(1,1) PRIMARY KEY,
    JMBG VARCHAR(13) UNIQUE nonclustered NOT NULL,
    Ime nvarchar(30) NOT NULL,
    Prezime nvarchar(30) NOT NULL,
    Adresa nvarchar(100) NOT NULL,
    Telefon nvarchar(20) NOT NULL,
    Email nvarchar(50) UNIQUE nonclustered,
    Kompanija nvarchar(50)
)


/* Krediti
- Datum, polje za unos datuma (obavezan unos),
- Namjena, polje za unos 50 karaktera (obavezan unos),
- Iznos, polje za decimalnog broja (obavezan unos),
- BrojRata, polje za unos cijelog broja (obavezan unos),
- Osiguran, polje za unos bit vrijednosti (obavezan unos),
- Opis, polje za unos dužeg niza karaktera. */

CREATE TABLE Krediti(
    KreditID INT IDENTITY (1,1) PRIMARY KEY,
    Datum DATE NOT NULL,
    Iznos DECIMAL(18,2) NOT NULL,
    BrojRata INT NOT NULL,
    Osiguran BIT NOT NULL,
    Opis nvarchar(300)
)

/*Otplate
 Datum, polje za unos datuma (obavezan unos)
 Iznos, polje za unos decimalnog broja (obavezan unos),
 Rata, polje za unos cijelog broja (obavezan unos),
 Opis, polje za unos dužeg niza karaktera. */

CREATE TABLE Otplate(
    KreditID INT FOREIGN KEY(KreditID) REFERENCES Krediti(KreditID),
    KlijentID INT FOREIGN KEY(KlijentID) REFERENCES Klijenti(KlijentID),
    PRIMARY KEY (KreditID,KlijentID),
    Datum DATE NOT NULL,
    Iznos DECIMAL(18,2) NOT NULL,
    Rata INT NOT NULL,
    Opis nvarchar(300)
)


/*Koristeći AdventureWorks2014 bazu podataka, importovati 10 kupaca u tabelu Klijenti i to sljedeće kolone:
a. Zadnjih 13 karaktera kolone rowguid (Crticu '-' zamijeniti brojem 1)-> JMBG,
b. FirstName (Person) -> Ime,
c. LastName (Person) -> Prezime,
d. AddressLine1 (Address) -> Adresa,
e. PhoneNumber (PersonPhone) -> Telefon,
f. EmailAddress (EmailAddress) -> Email,
g. 'FIT' -> Kompanija
Također, u tabelu Krediti unijeti minimalno tri zapisa sa proizvoljnim podacima.*/

INSERT INTO Klijenti(JMBG,Ime,Prezime,Adresa,Telefon,Email, Kompanija)
SELECT top 10
        REPLACE(RIGHT(P.[rowguid],13),'-','1' ),
        FirstName,
        LastName,
        AddressLine1,
        PhoneNumber,
        EmailAddress,
        'FIT'

FROM AdventureWorks2014.Person.Person AS P JOIN AdventureWorks2014.Person.BusinessEntityAddress  AS PBA
        ON P.BusinessEntityID =  PBA.BusinessEntityID JOIN AdventureWorks2014.Person.Address AS PA ON
        PBA.AddressID = PA.AddressID JOIN AdventureWorks2014.Person.PersonPhone PHONE ON
        P.BusinessEntityID = PHONE.BusinessEntityID JOIN AdventureWorks2014.Person.EmailAddress AS EM ON
        P.BusinessEntityID = EM.BusinessEntityID

SELECT * FROM Klijenti

INSERT INTO Krediti(Datum,Iznos,BrojRata,Osiguran,Opis)
VALUES ('2011-03-03',100000,10,1,'Super kredit')

INSERT INTO Krediti(Datum,Iznos,BrojRata,Osiguran,Opis)
VALUES ('2012-03-03',200000,20,1,'Extra ponuda')

INSERT INTO Krediti(Datum,Iznos,BrojRata,Osiguran,Opis)
VALUES ('2014-03-03',300000,30,1,'Tristo hiljada ')

SELECT * FROM Krediti

/*Kreirati stored proceduru koja će na osnovu proslijeđenih parametara služiti za unos podataka u tabelu Otplate.
Proceduru pohraniti pod nazivom usp_Otplate_Insert.
 Obavezno testirati ispravnost kreirane procedure (unijeti minimalno 5 zapisa sa proizvoljnim podacima).*/

 CREATE PROCEDURE usp_Otplate_Isert(
    @KreditID INT,
    @KlijentID INT,
    @Datum DATE,
    @Iznos DECIMAL(18,2),
    @Rata INT,
    @Opis nvarchar(300)
 )
 AS
 BEGIN
 INSERT INTO Otplate(KreditID,KlijentID,Datum,Iznos,Rata,Opis)
 VALUES (@KreditID,@KlijentID,@Datum,@Iznos,@Rata,@Opis)
 END

 EXEC usp_Otplate_Isert 2,1,'2014-05-06',100000,10,'Dugorocni kredit'

 /*Kreirati view (pogled) nad podacima koji će prikazivati sljedeća polja:
 jmbg, ime i prezime, adresa, telefon i email klijenta, zatim datum, namjenu i iznos kredita,
 te ukupan broj otplaćenih rata i ukupan otplaćeni iznos. View pohranite pod nazivom view_Krediti_Otplate.*/

 ALTER VIEW view_Krediti_Otplate
 AS
 SELECT [JMBG],[Prezime],[Adresa],[Telefon],[Email],O.[Datum],O.[Opis],O.[Iznos],
 datediff(mm,O.[Datum],getdate()) AS 'broj rata',datediff(mm,O.[Datum],getdate()) * [Rata] AS 'otplaceni iznos'
 FROM Klijenti AS K JOIN Otplate AS O ON K.KlijentID = O.KlijentID JOIN Krediti AS KR ON O.KreditID = KR.KreditID

 SELECT * FROM view_Krediti_Otplate

 /*Kreirati stored proceduru koja će na osnovu proslijeđenog parametra @JMBG
 prikazivati podatke o otplati kredita. Kao izvor podataka koristiti prethodno kreirani view.
 Proceduru pohraniti pod nazivom usp_Krediti_Otplate_SelectByJMBG. Obavezno testirati ispravnost kreirane procedure.*/

 CREATE PROCEDURE usp_Krediti_Otplate_SelectByJMBG(@JMBG VARCHAR(13) )
 AS
 SELECT * FROM view_Krediti_Otplate
 WHERE JMBG = @JMBG

 EXEC usp_Krediti_Otplate_SelectByJMBG '1EF6D13AFCBC6'

 /*Kreirati proceduru koja će služiti za izmjenu podataka o otplati kredita.
 Proceduru pohraniti pod nazivom usp_Otplate_Update.
 \ Obavezno testirati ispravnost kreirane procedure. */

 CREATE PROCEDURE usp_Otplate_Update(
    @KreditID INT,
    @KlijentID INT,
    @Datum DATE,
    @Iznos DECIMAL(18,2),
    @Rata INT,
    @Opis nvarchar(300)
 )
 AS
 BEGIN
 UPDATE Otplate
 SET Datum = @Datum,Iznos = @Iznos,Rata = @Rata,Opis = @Opis
 WHERE KreditID = @KreditID AND KlijentID = @KlijentID
 END

 SELECT * FROM Otplate

 EXEC usp_Otplate_Update 2,1,'2014-05-05',200000,5000,'opis otplate'

 /*Kreirati stored proceduru koja će služiti za brisanje kredita zajedno sa svim otplatama.
 Proceduru pohranite pod nazivom usp_Krediti_Delete. Obavezno testirati ispravnost kreirane procedure. */

 CREATE PROCEDURE usp_Krediti_Delete(
    @KreditID INT
 ) AS
 BEGIN

 DELETE FROM Otplate
 WHERE KreditID = @KreditID
 DELETE FROM Krediti
 WHERE KreditID = @KreditID

 END

 SELECT * FROM Otplate

 EXEC usp_Krediti_Delete 2

 /*Kreirati trigger koji će spriječiti brisanje zapisa u tabeli Otplate.
  Trigger pohranite pod nazivom tr_Otplate_IO_Delete.
  Obavezno testirati ispravnost kreiranog triggera. */

  CREATE TRIGGER tr_Otplate_IO_Delete ON Otplate
  FOR DELETE AS
  BEGIN
  print'nema brisanja'
  ROLLBACK
  END

  SELECT * FROM Otplate

  EXEC usp_Otplate_Isert 3,3,'2013-02-03',10000,100,'bla bla'

  DELETE FROM Otplate

 /* Uraditi full backup Vaše baze podataka na lokaciju D:\DBMS\Backup.*/

 backup DATABASE [2514]
 TO disk = 'c:\DBMS\2514.bak'
 WITH noformat