integralni dbms

-- integralni

USE     [AdventureWorks2014]
SELECT  P.FirstName + ' ' + P.LastName AS ImePrezime,
        E.JobTitle AS Posao,
        CONVERT(nvarchar(10), HireDate, 103) AS DatumZaposlenja,
        ROUND(SUM(TotalDue),2) AS Zarada
FROM    [HumanResources].[Employee] AS E                JOIN        [Person].[Person] AS P
        ON E.BusinessEntityID = P.BusinessEntityID      JOIN        [Person].[EmailAddress] AS PEA
        ON P.BusinessEntityID = PEA.BusinessEntityID    LEFT JOIN   [Sales].[SalesPerson] AS SSP
        ON P.BusinessEntityID = SSP.BusinessEntityID     JOIN   [Sales].[SalesOrderHeader] AS SOH
        ON SSP.BusinessEntityID = SOH.SalesPersonID
GROUP BY    P.FirstName + ' ' + P.LastName,
            E.JobTitle,
            E.HireDate
ORDER BY 3 DESC

-- upit 2

SELECT  P.FirstName, P.LastName,
        CC.CardType,
        CC.CardNumber,
        COUNT(SOH.CreditCardApprovalCode) AS BrojNarudzbi,
        SUM(SOH.TotalDue) AS UkupnoPlaceno

FROM    [Person].[Person] AS P                          JOIN        [Sales].[PersonCreditCard] AS PCC
        ON P.BusinessEntityID = PCC.BusinessEntityID    JOIN        [Sales].[CreditCard] AS CC
        ON PCC.CreditCardID = CC.CreditCardID           JOIN        [Sales].[SalesOrderHeader] AS SOH
        ON CC.CreditCardID = SOH.CreditCardID

GROUP BY P.FirstName, P.LastName, CC.CardType, CC.CardNumber
HAVING  COUNT(SOH.CreditCardApprovalCode) > 20
ORDER BY 6 DESC

----------------------------/////////////////////////////////////////////----------------------------


CREATE DATABASE [IB130260v01]

CREATE DATABASE [testic] ON PRIMARY
(
    NAME = 'testic',
    FILENAME = 'E:\DBMS\testic.MDF',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = 'testic_log',
    FILENAME = 'E:\DBMS\testic_log.LDF',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)

USE testic

CREATE TABLE Kandidati (
    KandidatID      int             IDENTITY(1,1) PRIMARY KEY,
    Ime             nvarchar(30)    NOT NULL,
    Prezime         nvarchar(30)    NOT NULL,
    JBMG            nvarchar(13)    NOT NULL UNIQUE,
    DatumRodjenja   date            NOT NULL,
    MjestoRodjenja  nvarchar(30)    ,
    Telefon         nvarchar(20)    ,
    Email           nvarchar(50)    UNIQUE
)
DROP TABLE Testovi
CREATE TABLE Testovi (
    TestID          int             IDENTITY(1,1) PRIMARY KEY,
    KandidatID      int             NOT NULL FOREIGN KEY(KandidatID) REFERENCES dbo.Kandidati(KandidatID),
    Naziv           nvarchar(50)    NOT NULL,
    Datum           datetime2   NOT NULL,
    Oznaka          nvarchar(10)    NOT NULL UNIQUE,
    Oblast          nvarchar(50)    NOT NULL,
    MaxBrojBodova   int             NOT NULL,
    Opis            nvarchar(250)
)
DROP TABLE RezultatiTesta
CREATE TABLE RezultatiTesta (
    RezultatTestaID int             IDENTITY(1,1) PRIMARY KEY,
    TestID          int             NOT NULL FOREIGN KEY(TestID) REFERENCES dbo.Testovi(TestID),
    Polozio         bit             NOT NULL,
    OsvojeniBodovi  decimal(18,2)   NOT NULL,
    Napomena        nvarchar(max)
)
-- 3a/ Insert
USE testic
INSERT INTO Kandidati (Ime, Prezime, JBMG, DatumRodjenja, MjestoRodjenja, Telefon, Email)
SELECT TOP 10
        p.FirstName, p.LastName,
        REPLACE(RIGHT(p.rowguid, 13), '-', '0'),
        p.ModifiedDate,
        a.City,
        ppp.PhoneNumber,
        email.EmailAddress
FROM AdventureWorks2012.Person.Person as p JOIN AdventureWorks2012.Person.BusinessEntityAddress  as ea
    ON p.BusinessEntityID = ea.BusinessEntityID JOIN AdventureWorks2012.Person.Address  as a
    ON ea.AddressID = a.AddressID JOIN AdventureWorks2012.Person.PersonPhone as ppp
    ON p.BusinessEntityID = ppp.BusinessEntityID JOIN AdventureWorks2012.Person.EmailAddress as email
    ON p.BusinessEntityID = email.BusinessEntityID

SELECT * FROM Testovi
-- 3b/ insert u testove
INSERT INTO Testovi (KandidatID, Datum,Naziv,Oznaka,Oblast,MaxBrojBodova,Opis)
 VALUES( 1, GETDATE(), 'DBMS integralni', 'DBMS', 'Oko baza', 100, 'Zadnji rok sa DBMS ispit')

INSERT INTO Testovi (KandidatID, Datum,Naziv,Oznaka,Oblast,MaxBrojBodova,Opis)
VALUES( 2, '2.2.1992', 'KT Integralni', 'KT', 'Mile voli Cisco', 100, 'Test')

INSERT INTO Testovi (KandidatID, Datum,Naziv,Oznaka,Oblast,MaxBrojBodova,Opis)
VALUES( 3, GETDATE(), 'Sport i zdravlje 1', 'SIZ1', 'beze', 100, NULL)

--- 4/ napraviti proceduru

CREATE PROCEDURE [usp_RezultatiTesta_Insert]
@TestID int,
@Polozio bit,
@OsvojeniBodovi decimal(18,2),
@Napomena nvarchar(max)
AS
BEGIN
    INSERT INTO RezultatiTesta
    VALUES(
    @TestID, @Polozio, @OsvojeniBodovi, @Napomena
    )
END
 -- test procedure i unos min 10 zapisa
EXEC usp_RezultatiTesta_Insert 6,0,75.60,'Prvi zapis';
EXEC usp_RezultatiTesta_Insert 6,0,12.60,'Drugi zapis';
EXEC usp_RezultatiTesta_Insert 6,0,23.63,'Treci zapis';
EXEC usp_RezultatiTesta_Insert 4,0,34.95,'Cetvrti zapis';
EXEC usp_RezultatiTesta_Insert 5,0,45.60,'Peti zapis';
EXEC usp_RezultatiTesta_Insert 6,0,56.70,'Sesti zapis';
EXEC usp_RezultatiTesta_Insert 4,0,67.60,'Sedmi zapis';
EXEC usp_RezultatiTesta_Insert 6,0,78.26,'Osmi zapis';
EXEC usp_RezultatiTesta_Insert 5,0,89.60,'Deveti zapis';
EXEC usp_RezultatiTesta_Insert 6,0,90.26,'Deseti zapis';
EXEC usp_RezultatiTesta_Insert 5,0,100,'11ti zapis';

-----------------------------------------------------------

-- 5 / napraviti view

CREATE VIEW [view_Rezultati_Testiranja]
AS
SELECT K.Ime, K.Prezime, K.JBMG, K.Telefon, K.Email, T.Datum, T.Naziv, T.Oznaka, T.Oblast, T.MaxBrojBodova, RT.Polozio, RT.OsvojeniBodovi,
        RT.OsvojeniBodovi * 100 / T.MaxBrojBodova AS ProcenatNaTestu
FROM    Kandidati AS K JOIN Testovi AS T
        ON K.KandidatID = T.KandidatID JOIN RezultatiTesta as RT
        ON T.TestID = RT.TestID
-- pokretanje viewa
SELECT * FROM view_Rezultati_Testiranja

--- 6

CREATE PROCEDURE [usp_RezultatiTesta_SelectByOznaka]
    @Oznaka         nvarchar(10),
    @Polozio        bit
AS
BEGIN
    SELECT * FROM view_Rezultati_Testiranja  as t
    WHERE @Oznaka = t.Oznaka AND @Polozio = t.Polozio
END

-- test procedure
EXEC usp_RezultatiTesta_SelectByOznaka DBMS,0;

------- 7 //////////////////////////////////////////////////////////////////
-- izmjena rezultata testiranja

CREATE PROCEDURE [usp_RezultatiTesta_Update]
@RezultatTestaID int, @TestID int, @Polozio bit, @OsvojeniBodovi decimal(18,2), @Napomena nvarchar(max)
AS
BEGIN
    UPDATE RezultatiTesta
    SET TestID = @TestID,
        Polozio = @Polozio,
        OsvojeniBodovi = @OsvojeniBodovi,
        Napomena = @Napomena
    WHERE @RezultatTestaID = RezultatTestaID
END
---
EXEC usp_RezultatiTesta_Update 1,6,1,85.60,'Prvi zapis drugi put';

-----   8

CREATE PROCEDURE [usp_Testovi_Delete]
AS
BEGIN
        DELETE RezultatiTesta
        DELETE Testovi
END

-- testiranje 8
SELECT * FROM RezultatiTesta
SELECT * FROM Testovi

EXEC usp_Testovi_Delete;

SELECT * FROM RezultatiTesta
SELECT * FROM Testovi