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

USE sara1801
GO

--1, 10bodova
--a)Proizvodi
CREATE TABLE Proizvodi
(
ProizvodID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Sifra nvarchar(10) UNIQUE NOT NULL,
Naziv nvarchar (50) NOT NULL,
Cijena decimal NOT NULL
);
--b)Skladista
CREATE TABLE Skladista
(
SkladisteID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
Naziv nvarchar (50) NOT NULL,
Oznaka nvarchar (10) UNIQUE NOT NULL,
Lokacija nvarchar (50) NOT NULL
);
--c)SkladisteProizvodi
CREATE TABLE SkladisteProizvodi
(
ProizvodID int FOREIGN KEY REFERENCES Proizvodi(ProizvodID)NOT NULL,
SkladisteID int FOREIGN KEY REFERENCES Skladista (SkladisteID) NOT NULL,
Stanje decimal NOT NULL,
PRIMARY KEY (ProizvodID, SkladisteID)
);

--2, 10bodova
--a)
INSERT INTO Skladista(Naziv, Oznaka, Lokacija)
VALUES
('Skladiste-Sarajevo', 'SA', 'Sarajevo'),
('Skladiste-Mostar', 'MO', 'Mostar'),
('Skladiste-Tuzla', 'TZ', 'Tuzla')

SELECT *
FROM Skladista

--b)
INSERT INTO Proizvodi(Sifra,Naziv,Cijena)
SELECT TOP 10 AdventureWorks2014.Production.Product.ProductNumber,
AdventureWorks2014.Production.Product.Name,
AdventureWorks2014.Production.Product.ListPrice
FROM AdventureWorks2014.Production.Product
WHERE AdventureWorks2014.Production.Product.ProductSubcategoryID IN(
    SELECT AdventureWorks2014.Production.ProductSubcategory.ProductSubcategoryID
    FROM AdventureWorks2014.Production.ProductSubcategory
    WHERE AdventureWorks2014.Production.ProductSubcategory.ProductCategoryID IN(
        SELECT AdventureWorks2014.Production.ProductCategory.ProductCategoryID
        FROM AdventureWorks2014.Production.ProductCategory
        WHERE AdventureWorks2014.Production.ProductCategory.Name ='Bikes'))
GROUP BY AdventureWorks2014.Production.Product.ProductNumber,
AdventureWorks2014.Production.Product.Name,
AdventureWorks2014.Production.Product.ListPrice
ORDER BY
    (SELECT SUM(AdventureWorks2014.Purchasing.PurchaseOrderDetail.OrderQty)
    FROM AdventureWorks2014.Purchasing.PurchaseOrderDetail
    WHERE AdventureWorks2014.Purchasing.PurchaseOrderDetail.ProductID =
        (SELECT AdventureWorks2014.Production.Product.ProductID
         FROM AdventureWorks2014.Production.Product
         WHERE AdventureWorks2014.Purchasing.PurchaseOrderDetail.ProductID =AdventureWorks2014.Production.Product.ProductID))DESC

SELECT *
FROM Proizvodi

--c)
GO
INSERT INTO SkladisteProizvodi (SkladisteID,ProizvodID, Stanje)
SELECT 1, Proizvodi.ProizvodID, 100
FROM Proizvodi

INSERT INTO SkladisteProizvodi(SkladisteID, ProizvodID, Stanje)
SELECT 2, Proizvodi.ProizvodID, 100
FROM Proizvodi

INSERT INTO SkladisteProizvodi(SkladisteID, ProizvodID, Stanje)
SELECT 3, Proizvodi.ProizvodID, 100
FROM Proizvodi

SELECT *
FROM SkladisteProizvodi

--3, 10 bodova
/*3. Kreirati uskladištenu proceduru koja će vršiti povećanje stanja skladišta za određeni proizvod na odabranom skladištu.
Provjeriti ispravnost procedure.*/

GO
CREATE PROCEDURE PovecanjaNaSkladistu
@sifraProizvoda nvarchar (20),
@lokacijaSkladista nvarchar (20),
@stanje decimal
AS
    BEGIN
        UPDATE SkladisteProizvodi
        SET Stanje+=@stanje

        WHERE SkladisteProizvodi.ProizvodID=(

            SELECT Proizvodi.ProizvodID
            FROM Proizvodi
            WHERE Proizvodi.Sifra=@sifraProizvoda
            )
            AND

            SkladisteProizvodi.SkladisteID=(

            SELECT Skladista.SkladisteID
            FROM Skladista
            WHERE Skladista.Lokacija=@lokacijaSkladista
            )
    END;

EXECUTE PovecanjaNaSkladistu 'BK-M82B-42' , 'Sarajevo',100

--4, 10 bodova
/*Kreiranje indeksa u bazi podataka nad tabelama
a)Non-clusteredindeks nad tabelom  Proizvodi. Potrebno je indeksirati Sifru i Naziv.
Također, potrebno je uključiti kolonu Cijena
b)Napisati proizvoljni upit nad tabelom Proizvodikoji u potpunosti iskorištava indeks iz prethodnog koraka
c)Uradite disableindeksa iz koraka a)*/

--a
GO
CREATE NONCLUSTERED INDEX Proizvodi_index
ON Proizvodi (Sifra, Naziv)
INCLUDE (Cijena)
--b
GO
SELECT Sifra, Naziv, Cijena
FROM Proizvodi
--c
GO
ALTER INDEX Proizvodi_index
ON Proizvodi
DISABLE;

--5, 5 bodova
/*5.Kreirati view sa sljedećom definicijom.
Objekat treba da prikazuje sifru, naziv i cijenu proizvoda, oznaku, naziv i lokaciju skladišta, te stanje na skladištu*/
GO
CREATE VIEW SkladisteProizvodi_Pogled
AS
SELECT
    (
    SELECT Proizvodi.Sifra
    FROM Proizvodi
    WHERE Proizvodi.ProizvodID=SkladisteProizvodi.ProizvodID
    )AS [Sifra proizvoda],

    (
    SELECT Proizvodi.Naziv
    FROM Proizvodi
    WHERE Proizvodi.ProizvodID=SkladisteProizvodi.ProizvodID
    )AS [Naziv proizvoda],

    (
    SELECT Proizvodi.Cijena
    FROM Proizvodi
    WHERE Proizvodi.ProizvodID=SkladisteProizvodi.ProizvodID
    )AS [Cijena proizvoda],

    (
    SELECT Skladista.Oznaka
    FROM Skladista
    WHERE Skladista.SkladisteID=SkladisteProizvodi.SkladisteID
    )AS [Oznaka skladista],

    (
    SELECT Skladista.Naziv
    FROM Skladista
    WHERE Skladista.SkladisteID=SkladisteProizvodi.SkladisteID
    )AS [Naziv skladista],

    (
    SELECT Skladista.Lokacija
    FROM Skladista
    WHERE Skladista.SkladisteID=SkladisteProizvodi.SkladisteID
    )AS [Lokacija skladista],

    (
    SkladisteProizvodi.Stanje
    )AS [Stanje na skladistu]
FROM SkladisteProizvodi

/*6, 10 bodova
Kreirati uskladištenu proceduru koja će na osnovu unesene
šifre proizvoda prikazati ukupno stanje zaliha na svim skladištima.
U rezultatu prikazati sifru, naziv i cijenu proizvoda te ukupno stanje zaliha.
U proceduri koristiti prethodno kreirani view. Provjeriti ispravnost kreirane procedure. */
GO
CREATE PROCEDURE StanjeZalihaNaSkladistu
@sifraProizvoda nvarchar (50)
AS
    BEGIN
        SELECT SkladisteProizvodi_Pogled.[Sifra proizvoda],
               SkladisteProizvodi_Pogled.[Naziv proizvoda],
               SkladisteProizvodi_Pogled.[Cijena proizvoda],
               SkladisteProizvodi_Pogled.[Lokacija skladista],
               SkladisteProizvodi_Pogled.[Stanje na skladistu]

        FROM SkladisteProizvodi_Pogled

        WHERE SkladisteProizvodi_Pogled.[Sifra proizvoda]=@sifraProizvoda
END;

EXECUTE StanjeZalihaNaSkladistu 'BK-M82B-42'

--7, 10 bodova
/*7. Kreirati uskladištenu proceduru koja će vršiti upis novih proizvoda,
te kao stanje zaliha za uneseni proizvod postaviti na 0 za sva skladišta.
Provjeriti ispravnost kreirane procedure.*/

GO
CREATE PROCEDURE UpisNovihProizvoda
@sifraProizvoda nvarchar (20),
@nazivProizvoda nvarchar (50),
@cijenaProizvoda decimal
AS
    BEGIN
        INSERT INTO Proizvodi
        (Sifra, Naziv, Cijena)
        VALUES (@sifraProizvoda, @nazivProizvoda, @cijenaProizvoda)

        INSERT INTO SkladisteProizvodi
        (SkladisteID, ProizvodID, Stanje)

        VALUES
        (1, (SELECT Proizvodi.ProizvodID
             FROM Proizvodi
             WHERE Proizvodi.Sifra=@sifraProizvoda),0
        ),
        (2, (SELECT Proizvodi.ProizvodID
             FROM Proizvodi
             WHERE Proizvodi.Sifra=@sifraProizvoda),0
        ),
        (3, (SELECT Proizvodi.ProizvodID
             FROM Proizvodi
             WHERE Proizvodi.Sifra=@sifraProizvoda),0
        )
END;

EXECUTE UpisNovihProizvoda '11AA', 'Milka', 3

SELECT *
FROM Proizvodi

--8, 10 bodova
/*8. Kreirati uskladištenu proceduru koja će za unesenu šifru proizvoda vršiti brisanje proizvoda uključujući
stanje na svim skladištima. Provjeriti ispravnost procedure.*/
GO
CREATE PROCEDURE Brisanjeproizvoda
@sifraProizvoda nvarchar (20)
AS
    BEGIN
        DELETE
        FROM SkladisteProizvodi
        WHERE SkladisteProizvodi.ProizvodID = (

            SELECT Proizvodi.ProizvodID
            FROM Proizvodi
            WHERE Proizvodi.Sifra=@sifraProizvoda
            )

        DELETE
        FROM Proizvodi
        WHERE Proizvodi.Sifra=@sifraProizvoda
END;
EXECUTE Brisanjeproizvoda '11AA'

SELECT *
FROM Proizvodi

--9, 20 bodova
/*9. Kreirati uskladištenu proceduru koja će za unesenu
šifru proizvoda, oznaku skladišta ili lokaciju skladišta
vršiti pretragu prethodno kreiranim view-om (zadatak 5).

Procedura obavezno treba da vraća rezultate bez obrzira da li su vrijednosti parametara postavljene.
Testirati ispravnost procedure u sljedećim situacijama:
a)Nije postavljena vrijednost niti jednom parametru (vraća sve zapise)
b)Postavljena je vrijednost parametra šifra proizvoda, a ostala dva parametra nisu
c)Postavljene su vrijednosti parametra šifra proizvodai oznaka skladišta, a lokacijanije
d)Postavljene su vrijednosti parametara šifre proizvodai lokacije, a oznaka skladištanije
e)Postavljene su vrijednosti sva tri parametra
*/
GO
ALTER PROCEDURE Pretraga
@sifraProizvoda nvarchar (20) =' ',
@oznakaSkladista nvarchar (10) =' ',
@lokacijaSkladista nvarchar (30) =' '
AS
    BEGIN
        SELECT *
        FROM SkladisteProizvodi_Pogled
        WHERE (SkladisteProizvodi_Pogled.[Sifra proizvoda] LIKE @sifraProizvoda +
        '%' AND SkladisteProizvodi_Pogled.[Oznaka skladista] LIKE @oznakaSkladista + '%')
        OR SkladisteProizvodi_Pogled.[Lokacija skladista] LIKE @lokacijaSkladista
END;

GO
--a
EXECUTE Pretraga
--b
EXECUTE Pretraga 'BK-M82S-42'
--c
EXECUTE Pretraga 'BK-M82S-42', 'SA'
--d
EXECUTE Pretraga 'BK-M82S-42', 'SA', 'Sarajevo'
--e
EXECUTE Pretraga @sifraProizvoda ='BK-M82S-42', @lokacijaSkladista ='Sarajevo'

--10, 5 bodova
/*0. Napraviti fulli diferencijalnibackup baze podataka na defaultlokaciju servera:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup*/

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

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