skladista-proizvodi

/*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 tabelu sa sljedećom strukturom:
a) Proizvodi:
I. ProizvodID, automatski generatpr vrijednosti i primarni ključ
II. Sifra, polje za unos 10 UNICODE karaktera (obavezan unos), jedinstvena vrijednost
III. Naziv, polje za unos 50 UNICODE karaktera (obavezan unos)
IV. Cijena, polje za unos decimalnog broja (obavezan unos)*/

CREATE DATABASE sara19
USE sara19

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
I. SkladisteID, automatski generator vrijednosti i primarni ključ
II. Naziv, polje za unos 50 UNICODE karaktera (obavezan unos)
III. Oznaka, polje za unos 10 UNICODE karaktera (obavezan unos), jedinstvena vrijednost
IV. Lokacija, polje za unos 50 UNICODE karaktera (obavezan unos)*/
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)
)

/*c) SkladisteProizvodi
I) Stanje, polje za unos decimalnih brojeva (obavezan unos)
Napomena: Na jednom skladištu može biti uskladišteno više proizvoda, dok isti proizvod može biti uskladišten na
više različitih skladišta. Onemogućiti da se isti proizvod na skladištu može pojaviti više puta.*/
CREATE TABLE SkladisteProizvodi
(
ProizvodID int FOREIGN KEY REFERENCES Proizvodi(ProizvodID),
SkladisteID int FOREIGN KEY REFERENCES Skladista(SkladisteID),
Stanje decimal NOT NULL,
PRIMARY KEY (ProizvodID, SkladisteID)
)

/*2. Popunjavanje tabela podacima
a) Putem INSERT komande u tabelu Skladista dodati minimalno 3 skladišta.*/
INSERT INTO Skladista (Naziv, Oznaka, Lokacija)
VALUES
('Skladiste Sarajevo', 'SKSA', 'Sarajevo'),
('Skladiste Mostar', 'SKMO', 'Mostar'),
('Skladiste Tuzla', 'SKTZ', 'TUZLA')

/*b) Koristeći bazu podataka AdventureWorks2014, preko INSERT i SELECT komande importovati 10 najprodavanijih bicikala
(kategorija proizvoda 'Bikes' i to sljedeće kolone:
I. Broj proizvoda (ProductNumber) - > Sifra,
II. Naziv bicikla (Name) -> Naziv,
III. Cijena po komadu (ListPrice) -> Cijena,*/
INSERT INTO Proizvodi (Sifra, Naziv, Cijena)
SELECT TOP 10
    pp.ProductNumber AS 'Sifra',
    pp.Name AS 'Naziv',
    pp.ListPrice AS 'Cijena'
FROM AdventureWorks2014.Production.Product AS pp
INNER JOIN AdventureWorks2014.Production.ProductSubcategory AS pps ON pp.ProductSubcategoryID=pps.ProductSubcategoryID
INNER JOIN AdventureWorks2014.Production.ProductCategory AS ppc ON pps.ProductCategoryID=ppc.ProductCategoryID
WHERE ppc.Name LIKE 'Bikes'
ORDER BY ppc.Name

SELECT * FROM AdventureWorks2014.Production.ProductCategory
SELECT * FROM AdventureWorks2014.Production.ProductSubcategory
SELECT * FROM AdventureWorks2014.Production.Product

SELECT * FROM Proizvodi

/*c) Putem INSERT i SELECT komandi u tabelu SkladisteProizvodi za sva dodana skladista importovati
sve proizvode tako da stanje bude 100*/
INSERT INTO SkladisteProizvodi (ProizvodID, SkladisteID, Stanje)
SELECT Proizvodi.ProizvodID, 1, 100
FROM Proizvodi

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

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

SELECT * FROM SkladisteProizvodi

/*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 usp_povecanjeStanja
(
    @proizvodID int,
    @skladisteID int,
    @stanje decimal
)
AS
    BEGIN
        UPDATE SkladisteProizvodi
        SET Stanje+=@stanje
        WHERE ProizvodID=@proizvodID AND SkladisteID=@skladisteID
    END;
EXEC usp_povecanjeStanja '1', '1', '500'

SELECT * FROM SkladisteProizvodi

/*4. Kreiranje indeksa u bazi podataka nad tabelama
a) Non-clustered indeks nad tabelom Proizvodi. Potrebno je indeksirati Sifru i Naziv. Također, potrebno je uključiti kolonu Cijena*/
GO
CREATE NONCLUSTERED INDEX _indexProizvodi
ON Proizvodi (Sifra, Naziv)
INCLUDE (Cijena)

/*b) Napisati proizvoljni upit nad tabelom Proizvodi koji u potpunosti iskorištava indeks iz prethodnog koraka*/
SELECT  Sifra, Naziv, Cijena
FROM Proizvodi

/*c) Uradite disable indeksa iz koraka a)*/
ALTER INDEX _indexProizvodi
ON Proizvodi
DISABLE;

/*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
ALTER VIEW view_prikaz
AS
    SELECT Proizvodi.Sifra AS [Sifra proizvoda],
            Proizvodi.Naziv AS [Naziv poizvoda],
            Proizvodi.Cijena AS [Cijena proizvoda],
            Skladista.Naziv AS [Naziv skladista],
            Skladista.Lokacija AS [Lokacija skladista],
            SkladisteProizvodi.Stanje AS [Stanje proizvoda na skladistu],
            Skladista.Oznaka AS [Oznaka skladista]
    FROM SkladisteProizvodi
    INNER JOIN Proizvodi ON SkladisteProizvodi.ProizvodID=Proizvodi.ProizvodID
    INNER JOIN Skladista ON SkladisteProizvodi.SkladisteID=Skladista.SkladisteID
SELECT * FROM view_prikaz

/*6. 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 usp_prikazStanjaZaliha
(
    @sifra nvarchar (10)
)
AS
    BEGIN
        SELECT view_prikaz.[Sifra proizvoda],
                view_prikaz.[Naziv poizvoda],
                view_prikaz.[Cijena proizvoda],
                view_prikaz.[Stanje proizvoda na skladistu]
        FROM view_prikaz
        WHERE view_prikaz.[Sifra proizvoda]=@sifra
    END;
SELECT * FROM Proizvodi
EXEC usp_prikazStanjaZaliha 'BK-M82S-38'

/*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 usp_upisNovihProizvoda
(
    @sifra nvarchar (10),
    @naziv nvarchar (50),
    @cijena decimal
)
AS
    BEGIN
        INSERT INTO Proizvodi (Sifra, Naziv, Cijena)
        VALUES (@sifra, @naziv,@cijena)

        INSERT INTO SkladisteProizvodi (ProizvodID, SkladisteID, Stanje)
        VALUES
        ((SELECT Proizvodi.ProizvodID FROM Proizvodi WHERE Sifra=@sifra), 1, 0),
        ((SELECT Proizvodi.ProizvodID FROM Proizvodi WHERE Sifra=@sifra),2, 0),
        ((SELECT Proizvodi.ProizvodID FROM Proizvodi WHERE Sifra=@sifra),3,0)
    END;
EXEC usp_upisNovihProizvoda '11AA', 'Milka','2'
SELECT * FROM Proizvodi
SELECT * FROM SkladisteProizvodi

/*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 usp_deleteProizvod
(
    @sifra nvarchar(10)
)
AS
    BEGIN
        DELETE FROM SkladisteProizvodi
        WHERE SkladisteProizvodi.ProizvodID
        =(
            SELECT Proizvodi.ProizvodID
            FROM Proizvodi
            WHERE Proizvodi.Sifra=@sifra
        )
        DELETE FROM Proizvodi
        WHERE Proizvodi.Sifra=@sifra
    END;
EXEC usp_deleteProizvod '11AA'
SELECT * FROM Proizvodi

/*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:
*/
GO
CREATE PROCEDURE ups_pretraga
(
    @sifra nvarchar (10)='',
    @oznaka nvarchar (10)='',
    @lokacija nvarchar (50)=''
)
AS
    BEGIN
        SELECT view_prikaz.[Sifra proizvoda], view_prikaz.[Lokacija skladista],view_prikaz.[Lokacija skladista]
        FROM view_prikaz
        WHERE
        (view_prikaz.[Sifra proizvoda] LIKE @sifra + '%'
        AND
        view_prikaz.[Oznaka skladista] LIKE @oznaka + '%'
        OR
        view_prikaz.[Lokacija skladista] LIKE @lokacija + '%')
    END;
--a) Nije postavljena vrijednost niti jednom parametru (vraća sve zapise)
EXEC ups_pretraga
--b) Postavljena je vrijednost parametra šifra proizvoda, a ostala dva parametra nisu
EXEC ups_pretraga 'BK-M68S-42'
SELECT * FROM Proizvodi
--c) Postavljene su vrijednosti parametra šifra proizvoda i oznaka skladišta, a lokacija nije
EXEC ups_pretraga 'BK-M68S-42' , 'SKSA'
--d) Postavljene su vrijednosti parametara šifre proizvoda i lokacije, a oznaka skladišta nije
EXEC ups_pretraga 'BK-M68S-42' , @lokacija='Sarajevo'
--e) Postavljene su vrijednosti sva tri parametra
EXEC ups_pretraga 'BK-M68S-42', 'SKSA', 'Sarajevo'

/*10. Napraviti full i diferencijalni backup baze podataka na default lokaciju servera:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup*/
BACKUP DATABASE sara19
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\sara19.bak'

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