OBP MI1 27.11.2015.

USE AdventureWorksOBP
GO

-- Zadatak 1.

SELECT
    P.Naziv         [Naziv proizvoda],
    SUM(S.Kolicina) [Prodana količina]
FROM Proizvod P
INNER JOIN Stavka S
    ON S.ProizvodID = P.IDProizvod
INNER JOIN Racun R
    ON R.IDRacun = S.RacunID
INNER JOIN Kupac K
    ON K.IDKupac = R.KupacID
INNER JOIN Grad G
    ON G.IDGrad = K.GradID
INNER JOIN Drzava D
    ON D.IDDrzava = G.IDGrad
WHERE
    D.Naziv = 'Hrvatska'
    AND
    (
        K.Telefon LIKE '1%'
        OR
        K.Telefon LIKE '3%'
        OR
        K.Telefon LIKE '5%'
    )
GROUP BY P.Naziv
GO

-- Zadatak 2.

INSERT INTO Drzava (Naziv)
VALUES
    ('Japan')

INSERT INTO Grad (Naziv, DrzavaID)
VALUES
    ('Yokohama', SCOPE_IDENTITY())

INSERT INTO Kupac (Ime, Prezime, Email, Telefon, GradID)
VALUES
    ('Đuro', 'Đurić', 'ddjuric@mail.jp', '555-327-123', SCOPE_IDENTITY())
GO

-- Zadatak 3.

CREATE TABLE NajmanjeProdavaniProivzodi
(
    IDProizvod INT,
    Naziv nvarchar(50),
    ProdanihKomada INT
)
GO

INSERT INTO NajmanjeProdavaniProivzodi
(
    IDProizvod,
    Naziv,
    ProdanihKomada
)
SELECT
    P.IDProizvod,
    P.Naziv,
    SUM(S.Kolicina)
FROM Stavka S
INNER JOIN Proizvod P
    ON P.IDProizvod = S.ProizvodID
GROUP BY P.IDProizvod, P.Naziv
GO

-- Zadatak 4.

CREATE TABLE Student
(
    IDStudent INT PRIMARY KEY IDENTITY,
    Ime nvarchar(50) NOT NULL,
    Prezime nvarchar(50) NOT NULL,
    DatumRodenja DATE NOT NULL,
    JMBAG CHAR(11) UNIQUE NOT NULL,
    CijenaSkolarine MONEY NOT NULL,
    JeLiRedovan BIT DEFAULT 1
)
GO

CREATE TABLE Kolegij
(
    IDKolegij INT PRIMARY KEY IDENTITY,
    Naziv nvarchar(50) NOT NULL,
    ECTS INT NOT NULL

    CHECK (ECTS BETWEEN 1 AND 7)
)
GO

CREATE TABLE Upis
(
    IDUpis INT PRIMARY KEY IDENTITY,
    StudentID INT NOT NULL,
    KolegijID INT NOT NULL

    FOREIGN KEY (StudentID) REFERENCES Student(IDStudent),
    FOREIGN KEY (KolegijID) REFERENCES Kolegij(IDKolegij)
)
GO

-- Zadatak 5.

CREATE VIEW vwKreditneKartice
AS
    SELECT *
    FROM KreditnaKartica KK
GO

SELECT
    KK.Tip [Tip kartice],
    KK.IstekGodina [Godina isteka],
    COUNT(*) [Koliko ih ističe]
FROM vwKreditneKartice KK
GROUP BY KK.Tip, KK.IstekGodina
GO

-- Zadatak 6.



-- Zadatak 12.

CREATE PROC prUmetanjeGradova
    @NazivDrzave nvarchar(50),
    @NazivGrada1 nvarchar(50),
    @NazivGrada2 nvarchar(50)
AS
    DECLARE @IDDrzave INT

    IF EXISTS(SELECT * FROM Drzava WHERE Naziv = @NazivDrzave) BEGIN
        SELECT @IDDrzave = IDDrzava
        FROM Drzava
        WHERE Naziv = @NazivDrzave
    END
    ELSE BEGIN
        INSERT INTO Drzava (Naziv)
        VALUES
            (@NazivDrzave)

        SET @IDDrzave = SCOPE_IDENTITY()
    END

    INSERT INTO Grad (Naziv, DrzavaID)
    VALUES
        (@NazivGrada1, @IDDrzave),
        (@NazivGrada2, @IDDrzave)
GO

EXEC prUmetanjeGradova 'Kolumbija', 'Gradic1', 'Gradak'
EXEC prUmetanjeGradova 'Kolumbija', 'Gradic1', 'Gradak'
GO

-- Zadatak 13.

CREATE PROC prCreateOrUpdate
    @IDPotkategorije INT, /* NULL - CREATE, OSTALO - UPDATE */
    @NazivPotkategorije nvarchar(50),
    @IDKategorije INT
AS
    IF @IDPotkategorije IS NULL BEGIN /* CREATE */
        INSERT INTO Potkategorija (KategorijaID, Naziv)
        VALUES
            (@IDKategorije, @NazivPotkategorije)
    END
    ELSE BEGIN /* UPDATE */
        UPDATE Potkategorija
        SET
            KategorijaID = @IDKategorije,
            Naziv = @NazivPotkategorije
        WHERE IDPotkategorija = @IDPotkategorije
    END
GO

CREATE PROC prReadOrDelete
    @IDPotkategorije INT,
    @DELETE bit /* 0 - READ, 1 - DELETE */
AS
    IF @DELETE = 0 BEGIN /* READ */
        SELECT *
        FROM Potkategorija
        WHERE IDPotkategorija = @IDPotkategorije
    END
    ELSE IF @DELETE = 1 BEGIN /* DELETE */
        DELETE
        FROM Potkategorija
        WHERE IDPotkategorija = @IDPotkategorije
    END
GO