SQL - Ispit

/*

1. Kroz SQL kod, napraviti bazu podataka koja nosi ime vašeg broja dosijea.
U postupku kreiranja u obzir uzeti samo DEFAULT postavke.

*/



GO
CREATE DATABASE IB140294_vjezba


ON(

    NAME = IB140294_dat, FILENAME = 'C:\BPII\DATA\IB140294.mdf', SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5%

)

LOG ON (

    NAME = IB140294_log, FILENAME = 'C:\BPII\LOG\IB140294.ldf', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 2%

);


USE IB140294_vjezba

/*

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)

*/


GO
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

);

GO
SELECT *
FROM Proizvodi


/*

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)

*/


GO
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
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.

*/

GO
CREATE TABLE SkladisteProizvodi(

    SkladisteID INT FOREIGN KEY REFERENCES Skladista(SkladisteID) NOT NULL,
    ProizvodID INT FOREIGN KEY REFERENCES Proizvodi(ProizvodID) NOT NULL,

    PRIMARY KEY(SkladisteID, ProizvodID),
    Stanje decimal NOT NULL

);


/*

2. Popunjavanje tabela podacima
a) Putem INSERT komande u tabelu Skladista dodati minimalno 3 skladišta.
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,

c) Putem INSERT i SELECT komandi u tabelu SkladisteProizvodi za sva dodana skladista importovati
sve proizvode tako da stanje bude 100

*/


GO
INSERT INTO Skladista
(Naziv, Oznaka, Lokacija)
VALUES ('Skladiste-Sarajevo', 'SK-SA', 'Sarajevo'),
       ('Skladiste-Banja-Luka', 'SK-BL', 'Banja Luka'),
       ('Skladiste-Mostar', 'SK-MO', 'Mostar')


GO

INSERT INTO Proizvodi
(Sifra, Naziv, Cijena)
SELECT TOP 10

    AdventureWorks2014.Production.Product.ProductNumber AS [Sifra],
    AdventureWorks2014.Production.Product.Name AS [Naziv],
    AdventureWorks2014.Production.Product.ListPrice AS [Cijena]

FROM AdventureWorks2014.Production.Product


WHERE AdventureWorks2014.Production.Product.ProductSubcategoryID IN

    (

        SELECT AdventureWorks2014.Production.ProductSubcategory.ProductSubcategoryID
        FROM AdventureWorks2014.Production.ProductSubcategory
        WHERE AdventureWorks2014.Production.ProductSubcategory.ProductSubcategoryID = AdventureWorks2014.Production.Product.ProductSubcategoryID AND AdventureWorks2014.Production.ProductSubcategory.ProductCategoryID IN (

            SELECT AdventureWorks2014.Production.ProductCategory.ProductCategoryID
            FROM AdventureWorks2014.Production.ProductCategory
            WHERE AdventureWorks2014.Production.ProductCategory.ProductCategoryID = AdventureWorks2014.Production.ProductSubcategory.ProductCategoryID AND AdventureWorks2014.Production.ProductCategory.Name = 'Bikes'

        )

    )

ORDER BY (

    SELECT SUM(AdventureWorks2014.Purchasing.PurchaseOrderDetail.OrderQty)
    FROM AdventureWorks2014.Purchasing.PurchaseOrderDetail
    WHERE AdventureWorks2014.Purchasing.PurchaseOrderDetail.ProductID IN (

        SELECT AdventureWorks2014.Production.Product.ProductID
        FROM AdventureWorks2014.Production.Product
        WHERE AdventureWorks2014.Production.Product.ProductID = AdventureWorks2014.Purchasing.PurchaseOrderDetail.ProductID
    )

    ) DESC



/*

Putem INSERT i SELECT komandi u tabelu SkladisteProizvodi za
sva dodana skladista importovati sve proizvode tako da stanje bude 100

*/

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

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

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



/*

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 PovecajStanje
@sifraProizvoda nvarchar(10), @nazivSkladista nvarchar(15), @stanje decimal
AS

    BEGIN
        UPDATE SkladisteProizvodi
        SET SkladisteProizvodi.Stanje += @stanje
        WHERE SkladisteProizvodi.SkladisteID = (

            SELECT Skladista.SkladisteID
            FROM Skladista
            WHERE Skladista.Naziv = @nazivSkladista
        )

        AND SkladisteProizvodi.ProizvodID = (

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

        )
    END;

GO

ALTER PROCEDURE PovecajStanje
@sifraProizvoda nvarchar(20), @nazivSkladista nvarchar(20), @stanje decimal
AS

    BEGIN
        UPDATE SkladisteProizvodi
        SET SkladisteProizvodi.Stanje += @stanje
        WHERE SkladisteProizvodi.SkladisteID = (

            SELECT Skladista.SkladisteID
            FROM Skladista
            WHERE Skladista.Naziv = @nazivSkladista
        )

        AND SkladisteProizvodi.ProizvodID = (

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

        )
    END;

GO
SELECT *
FROM Proizvodi
WHERE Proizvodi.Sifra = 'BK-R93R-62'

-- BK-R93R-62


USE IB140294_vjezba

EXEC PovecajStanje 'BK-R93R-62', 'Skladiste-Sarajevo', 50


GO
/*

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

    b) Napisati proizvoljni upit nad tabelom Proizvodi koji u potpunosti iskorištava indeks iz prethodnog koraka
    c) Uradite disable indeksa iz koraka a)

*/


CREATE NONCLUSTERED INDEX ProizvodiIndex
ON Proizvodi(Sifra, Naziv)
INCLUDE (Cijena)


GO
SELECT Sifra, Naziv, Cijena
FROM Proizvodi


GO
ALTER INDEX ProizvodiIndex
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

CREATE VIEW ProizvodiPogled
AS

    SELECT

    (
        SELECT Proizvodi.Naziv
        FROM Proizvodi
        WHERE Proizvodi.ProizvodID = SkladisteProizvodi.ProizvodID

    ) AS [Naziv proizvoda],

    (
        SELECT Proizvodi.Sifra
        FROM Proizvodi
        WHERE Proizvodi.ProizvodID = SkladisteProizvodi.ProizvodID

    ) AS [Sifra],

    (
        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],

    (
        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],

    (SkladisteProizvodi.Stanje) AS [Stanje]

    FROM SkladisteProizvodi


GO
SELECT *
FROM ProizvodiPogled



/*


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 PrikazStanjaNaSkladistu
@sifra nvarchar (20)

AS

    BEGIN

    SELECT

    ProizvodiPogled.[Naziv proizvoda],
    ProizvodiPogled.Sifra,
    ProizvodiPogled.[Cijena proizvoda],
    ProizvodiPogled.Stanje


    FROM ProizvodiPogled
    WHERE ProizvodiPogled.Sifra = @sifra

    END;

GO
ALTER PROCEDURE PrikazStanjaNaSkladistu
@sifra nvarchar (20)

AS

    BEGIN

    SELECT

    ProizvodiPogled.[Naziv proizvoda],
    ProizvodiPogled.Sifra,
    ProizvodiPogled.[Cijena proizvoda],
    ProizvodiPogled.Stanje


    FROM ProizvodiPogled
    WHERE ProizvodiPogled.Sifra = @sifra

    END;


EXEC PrikazStanjaNaSkladistu 'BK-R93R-62'



/*

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 UpisProizvodSkladiste
@sifra nvarchar(20), @naziv nvarchar(50), @cijena decimal, @oznakaSkladista nvarchar(10)

AS


    BEGIN
        INSERT INTO Proizvodi
        (Sifra, Naziv, Cijena)
        VALUES (@sifra, @naziv, @cijena)

        INSERT INTO SkladisteProizvodi
        (SkladisteID, ProizvodID, Stanje)


        VALUES (

            (
                SELECT Skladista.SkladisteID
                FROM Skladista
                WHERE Skladista.Oznaka = @oznakaSkladista
            ),

            (
                SELECT Proizvodi.ProizvodID
                FROM Proizvodi
                WHERE Proizvodi.Sifra = @sifra
            ),

            0

        )
    END;


SELECT *
FROM Proizvodi

EXEC UpisProizvodSkladiste '145SDSS852', 'Sky Cola', 3578, 'SK-SA'


SELECT *
FROM SkladisteProizvodi
INNER JOIN Proizvodi
ON Proizvodi.ProizvodID = SkladisteProizvodi.ProizvodID

WHERE Proizvodi.Sifra = '145SDSS852'