29-JUN

--1. Kroz SQL kod kreirati bazu podataka sa imenom vašeg broja indeksa.
 
CREATE DATABASE IB200013
GO
USE IB200013
 
--2. U kreiranoj bazi podataka kreirati tabele sa sljedećom strukturom:
--a) Proizvodi
 
CREATE TABLE Proizvodi
(
--• ProizvodID, cjelobrojna vrijednost i primarni ključ, autoinkrement
--• Naziv, 50 UNICODE karaktera (obavezan unos)
--• SifraProizvoda, 25 UNICODE karaktera (obavezan unos)
--• Boja, 15 UNICODE karaktera
--• NazivKategorije, 50 UNICODE (obavezan unos)
--• Tezina, decimalna vrijednost sa 2 znaka iza zareza
​ProizvodID INT CONSTRAINT PK_Proizvodi PRIMARY KEY IDENTITY(1,1),
​Naziv NVARCHAR(50) NOT NULL,
​SifraProizvoda NVARCHAR(25) NOT NULL,
​Boja NVARCHAR(15),
​NazivKategorije NVARCHAR(50) NOT NULL,
​Tezina DECIMAL(18,2)
)
--b) ZaglavljeNarudzbe
CREATE TABLE ZaglavljeNarudzbe
(
--• NarudzbaID, cjelobrojna vrijednost i primarni ključ, autoinkrement
--• DatumNarudzbe, polje za unos datuma i vremena (obavezan unos)
--• DatumIsporuke, polje za unos datuma i vremena
--• ImeKupca, 50 UNICODE (obavezan unos)
--• PrezimeKupca, 50 UNICODE (obavezan unos)
--• NazivTeritorije, 50 UNICODE (obavezan unos)
--• NazivRegije, 50 UNICODE (obavezan unos)
--• NacinIsporuke, 50 UNICODE (obavezan unos)
​NarudzbaID INT CONSTRAINT PK_ZaglavljeNarudzbe PRIMARY KEY IDENTITY(1,1),
​DatumNarudzbe DATETIME NOT NULL,
​DatumIsporuke DATETIME,
​ImeKupca NVARCHAR(50) NOT NULL,
​PrezimeKupca NVARCHAR(50) NOT NULL,
​NazivTeritorije NVARCHAR(50) NOT NULL,
​NazivRegije NVARCHAR(50) NOT NULL,
​NacinIsporuke NVARCHAR(50) NOT NULL
)
--c) DetaljiNarudzbe
CREATE TABLE DetaljiNarudzbe
(
--• NarudzbaID, cjelobrojna vrijednost, obavezan unos i strani ključ
--• ProizvodID, cjelobrojna vrijednost, obavezan unos i strani ključ
--• Cijena, novčani tip (obavezan unos),
--• Kolicina, skraćeni cjelobrojni tip (obavezan unos),
--• Popust, novčani tip (obavezan unos)
--**Jedan proizvod se može više puta naručiti, dok jedna narudžba može sadržavati više proizvoda. U okviru jedne
--narudžbe jedan proizvod se može naručiti više puta.
​DetaljiNarudzbe INT CONSTRAINT PK_DetaljiNarudzbe PRIMARY KEY IDENTITY(1,1),
​NarudzbaID INT NOT NULL CONSTRAINT FK_DetaljiNarudzbe_ZaglavljeNarudzbe FOREIGN KEY REFERENCES ZaglavljeNarudzbe(NarudzbaID),
​ProizvodID INT NOT NULL CONSTRAINT FK_DetaljiNarudzbe_Proizvodi FOREIGN KEY REFERENCES Proizvodi(ProizvodID),
​Cijena MONEY NOT NULL,
​Kolicina SMALLINT NOT NULL,
​Popust MONEY NOT NULL
)
 
--3. Iz baze podataka AdventureWorks u svoju bazu podataka prebaciti sljedeće podatke:
--a) U tabelu Proizvodi dodati sve proizvode, na mjestima gdje nema pohranjenih podataka o težini
--zamijeniti vrijednost sa 0
--• ProductID -> ProizvodID
--• Name -> Naziv
--• ProductNumber -> SifraProizvoda
--• Color -> Boja
--• Name (ProductCategory) -> NazivKategorije
--• Weight -> Tezina
SET IDENTITY_INSERT Proizvodi ON
INSERT INTO Proizvodi (ProizvodID, Naziv, SifraProizvoda, Boja, NazivKategorije, Tezina)
SELECT P.ProductID, P.Name, P.ProductNumber, P.Color, PC.Name, ISNULL(P.Weight,0)
FROM AdventureWorks2017.Production.Product AS P
INNER JOIN AdventureWorks2017.Production.ProductSubcategory AS PS
ON P.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN AdventureWorks2017.Production.ProductCategory AS PC
ON PS.ProductCategoryID=PC.ProductCategoryID
SET IDENTITY_INSERT Proizvodi OFF
 
--b) U tabelu ZaglavljeNarudzbe dodati sve narudžbe
--• SalesOrderID -> NarudzbaID
--• OrderDate -> DatumNarudzbe
--• ShipDate -> DatumIsporuke
--• FirstName (Person) -> ImeKupca
--• LastName (Person) -> PrezimeKupca
--• Name (SalesTerritory) -> NazivTeritorije
--• Group (SalesTerritory) -> NazivRegije
--• Name (ShipMethod) -> NacinIsporuke
 
SET IDENTITY_INSERT ZaglavljeNarudzbe ON
INSERT INTO ZaglavljeNarudzbe(NarudzbaID,DatumNarudzbe,DatumIsporuke,ImeKupca,PrezimeKupca,NazivTeritorije,NazivRegije,NacinIsporuke)
SELECT SOH.SalesOrderID, SOH.OrderDate, SOH.ShipDate, P.FirstName, P.LastName, ST.Name, ST.[Group], SM.Name
FROM AdventureWorks2017.Sales.SalesOrderHeader AS SOH
INNER JOIN AdventureWorks2017.Sales.Customer AS C
ON SOH.CustomerID=C.CustomerID
INNER JOIN AdventureWorks2017.Person.Person AS P
ON C.PersonID=P.BusinessEntityID
INNER JOIN AdventureWorks2017.Sales.SalesTerritory AS ST
ON SOH.TerritoryID=ST.TerritoryID
INNER JOIN AdventureWorks2017.Purchasing.ShipMethod AS SM
ON SOH.ShipMethodID=SM.ShipMethodID
SET IDENTITY_INSERT ZaglavljeNarudzbe OFF
 
--c) U tabelu DetaljiNarudzbe dodati sve stavke narudžbe
--• SalesOrderID -> NarudzbaID
--• ProductID -> ProizvodID
--• UnitPrice -> Cijena
--• OrderQty -> Kolicina
--• UnitPriceDiscount -> Popust
 
INSERT INTO DetaljiNarudzbe
SELECT SOD.SalesOrderID, SOD.ProductID, SOD.UnitPrice, SOD.OrderQty, SOD.UnitPriceDiscount
FROM AdventureWorks2017.Sales.SalesOrderDetail AS SOD
 
--4.
--a) (6 bodova) Kreirati upit koji će prikazati ukupan broj uposlenika po odjelima. Potrebno je prebrojati
--samo one uposlenike koji su trenutno aktivni, odnosno rade na datom odjelu. Također, samo uzeti u obzir
--one uposlenike koji imaju više od 10 godina radnog staža (ne uključujući graničnu vrijednost). Rezultate
--sortirati prema broju uposlenika u opadajućem redoslijedu. (AdventureWorks2017)
 
USE AdventureWorks2017
SELECT D.Name, COUNT(*) 'Ukupan broj uposlenika'
FROM HumanResources.Employee AS E
INNER JOIN HumanResources.EmployeeDepartmentHistory AS EDH
ON E.BusinessEntityID=EDH.BusinessEntityID
INNER JOIN HumanResources.Department AS D
ON EDH.DepartmentID=D.DepartmentID
WHERE EDH.EndDate IS NULL AND DATEDIFF(YEAR,E.HireDate,GETDATE())>10
GROUP BY D.Name
ORDER BY 2 DESC
 
--b) (10 bodova) Kreirati upit koji prikazuje po mjesecima ukupnu vrijednost poručene robe za skladište, te
--ukupnu količinu primljene robe, isključivo u 2012 godini. Uslov je da su troškovi prevoza bili između
--500 i 2500, a da je dostava izvršena CARGO transportom. Također u rezultatima upita je potrebno
--prebrojati stavke narudžbe na kojima je odbijena količina veća od 100. (AdventureWorks2017)
 
SELECT MONTH(POH.OrderDate) 'Mjesec', SUM(POD.LineTotal) 'Ukupna vrijednost', SUM(POD.ReceivedQty) 'Ukupna količina primljene robe', SUM(IIF(POD.RejectedQty>100, 1, 0)) 'Ukupno stavki rejectedQty veće od 100'
FROM Purchasing.PurchaseOrderDetail AS POD
INNER JOIN Purchasing.PurchaseOrderHeader AS POH
ON POD.PurchaseOrderID=POH.PurchaseOrderID
INNER JOIN Purchasing.ShipMethod AS SM
ON POH.ShipMethodID=SM.ShipMethodID
WHERE YEAR(POH.OrderDate)=2012 AND POH.Freight BETWEEN 500 AND 2500 AND SM.Name LIKE '%CARGO%'
GROUP BY MONTH(POH.OrderDate)
 
 
 
--c) (10 bodova) Prikazati ukupan broj narudžbi koje su obradili uposlenici, za svakog uposlenika
--pojedinačno. Uslov je da su narudžbe kreirane u 2011 ili 2012 godini, te da je u okviru jedne narudžbe
--odobren popust na dvije ili više stavki. Također uzeti u obzir samo one narudžbe koje su isporučene u
--Veliku Britaniju, Kanadu ili Francusku. (AdventureWorks2017)
 
SELECT P.FirstName, P.LastName, COUNT(*) 'Ukupan broj narudžbi'
FROM Person.Person AS P
INNER JOIN HumanResources.Employee AS E
ON P.BusinessEntityID=E.BusinessEntityID
INNER JOIN Sales.SalesPerson AS SP
ON E.BusinessEntityID=SP.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader AS SOH
ON SP.BusinessEntityID=SOH.SalesPersonID
INNER JOIN Sales.SalesTerritory AS ST
ON SOH.TerritoryID=ST.TerritoryID
WHERE YEAR(SOH.OrderDate) IN (2011, 2012) AND ST.Name IN ('France', 'Canada', 'United Kingdom') AND
(
​SELECT COUNT(*)
​FROM Sales.SalesOrderDetail AS SOD
​WHERE SOD.UnitPriceDiscount>0 AND SOD.SalesOrderID=SOH.SalesOrderID
)>=2
GROUP BY P.FirstName, P.LastName
 
SELECT *
FROM Sales.SalesTerritory AS ST
 
--d) (11 bodova) Napisati upit koji će prikazati sljedeće podatke o proizvodima: naziv proizvoda, naziv
--kompanije dobavljača, količinu na skladištu, te kreiranu šifru proizvoda. Šifra se sastoji od sljedećih
--vrijednosti: (Northwind)
--1) Prva dva slova naziva proizvoda
--2) Karakter /
--3) Prva dva slova druge riječi naziva kompanije dobavljača, uzeti u obzir one kompanije koje u
--nazivu imaju 2 ili 3 riječi
--4) ID proizvoda, po pravilu ukoliko se radi o jednocifrenom broju na njega dodati slovo 'a', u
--suprotnom uzeti obrnutu vrijednost broja
--Npr. Za proizvod sa nazivom Chai i sa dobavljačem naziva Exotic Liquids, šifra će btiti Ch/Li1a.
USE Northwind
SELECT P.ProductName, S.CompanyName, P.UnitsInStock,
LEFT(P.ProductName,2) + '/' + SUBSTRING(S.CompanyName,CHARINDEX(' ', S.CompanyName)+1,2) +
IIF(LEN(P.ProductID)=1, CAST(P.ProductID AS NVARCHAR)+'a', CAST(REVERSE(P.ProductID) AS NVARCHAR))
FROM Products AS P
INNER JOIN Suppliers AS S
ON P.SupplierID=S.SupplierID
WHERE LEN(S.CompanyName) - LEN(REPLACE(S.CompanyName, ' ','')) IN(1,2)
 
 
--37 bodova
--5.
--a) (3 boda) U kreiranoj bazi kreirati index kojim će se ubrzati pretraga prema šifri i nazivu proizvoda.
--Napisati upit za potpuno iskorištenje indexa.
USE IB200013
CREATE INDEX IX_Proizvodi_Sifra_NazivP
ON Proizvodi(SifraProizvoda, Naziv)
 
SELECT P.SifraProizvoda,P.Naziv
FROM Proizvodi AS P
WHERE P.SifraProizvoda LIKE 'M%' OR Naziv LIKE 'S%'
 
--b) (7 bodova) U kreiranoj bazi kreirati proceduru sp_search_products kojom će se vratiti podaci o
--proizvodima na osnovu kategorije kojoj pripadaju ili težini. Korisnici ne moraju unijeti niti jedan od
--parametara ali u tom slučaju procedura ne vraća niti jedan od zapisa. Korisnicima unosom već prvog
--slova kategorije se trebaju osvježiti zapisi, a vrijednost unesenog parametra težina će vratiti one
--proizvode čija težina je veća od unesene vrijednosti.
GO
CREATE PROCEDURE sp_search_products
(
​@Kategorija NVARCHAR(50)=NULL,
​@Tezina DECIMAL(18,2)=NULL
)
AS
BEGIN
​SELECT *
​FROM Proizvodi AS P
​WHERE P.NazivKategorije LIKE @Kategorija + '%' OR P.Tezina>@Tezina
END
GO
 
EXEC sp_search_products @Kategorija='B'
 
--c) (18 bodova) Zbog proglašenja dobitnika nagradne igre održane u prva dva mjeseca drugog kvartala 2013
--godine potrebno je kreirati upit. Upitom će se prikazati treća najveća narudžba (vrijednost bez popusta)
--za svaki mjesec pojedinačno. Obzirom da je u pravilima nagradne igre potrebno nagraditi 2 osobe
--(muškarca i ženu) za svaki mjesec, potrebno je u rezultatima upita prikazati pored navedenih stavki i o
--kojem se kupcu radi odnosno ime i prezime, te koju je nagradu osvojio. Nagrade se dodjeljuju po
--sljedećem pravilu:
--• za žene u prvom mjesecu drugog kvartala je stoni mikser, dok je za muškarce usisivač
--• za žene u drugom mjesecu drugog kvartala je pegla, dok je za muškarc multicooker
--Obzirom da za kupce nije eksplicitno naveden spol, određivat će se po pravilu: Ako je zadnje slovo imena
--a, smatra se da je osoba ženskog spola u suprotnom radi se o osobi muškog spola. Rezultate u formiranoj
--tabeli dobitnika sortirati prema vrijednosti narudžbe u opadajućem redoslijedu. (AdventureWorks2017)
--28 bodova
 
USE AdventureWorks2017
SELECT PODQ.FirstName, PODQ.LastName, PODQ.Mjesec, PODQ.[Ukupno bez popusta], PODQ.Nagrada
FROM(SELECT MONTH(SOH.OrderDate) 'Mjesec', P.FirstName, P.LastName, SUM(SOD.OrderQty*SOD.UnitPrice) 'Ukupno bez popusta',
​ROW_NUMBER() OVER(ORDER BY SUM(SOD.OrderQty*SOD.UnitPrice) DESC) 'RedniBroj', 'Stoni mikser' Nagrada
​FROM Person.Person AS P
​INNER JOIN Sales.Customer AS C
​ON P.BusinessEntityID=C.PersonID
​INNER JOIN Sales.SalesOrderHeader AS SOH
​ON C.CustomerID=SOH.CustomerID
​INNER JOIN Sales.SalesOrderDetail AS SOD
​ON SOH.SalesOrderID=SOD.SalesOrderID
​WHERE MONTH(SOH.OrderDate)=4 AND YEAR(SOH.OrderDate)=2013 AND RIGHT(P.FirstName,1) LIKE 'a'
​GROUP BY MONTH(SOH.OrderDate), P.FirstName, P.LastName) AS PODQ
WHERE PODQ.RedniBroj=3
UNION
SELECT PODQ.FirstName, PODQ.LastName, PODQ.Mjesec, PODQ.[Ukupno bez popusta], PODQ.Nagrada
FROM(SELECT MONTH(SOH.OrderDate) 'Mjesec', P.FirstName, P.LastName, SUM(SOD.OrderQty*SOD.UnitPrice) 'Ukupno bez popusta',
​ROW_NUMBER() OVER(ORDER BY SUM(SOD.OrderQty*SOD.UnitPrice) DESC) 'RedniBroj', 'Pegla' Nagrada
​FROM Person.Person AS P
​INNER JOIN Sales.Customer AS C
​ON P.BusinessEntityID=C.PersonID
​INNER JOIN Sales.SalesOrderHeader AS SOH
​ON C.CustomerID=SOH.CustomerID
​INNER JOIN Sales.SalesOrderDetail AS SOD
​ON SOH.SalesOrderID=SOD.SalesOrderID
​WHERE MONTH(SOH.OrderDate)=5 AND YEAR(SOH.OrderDate)=2013 AND RIGHT(P.FirstName,1) LIKE 'a'
​GROUP BY MONTH(SOH.OrderDate), P.FirstName, P.LastName) AS PODQ
WHERE PODQ.RedniBroj=3
UNION
SELECT PODQ.FirstName, PODQ.LastName, PODQ.Mjesec, PODQ.[Ukupno bez popusta], PODQ.Nagrada
FROM(SELECT MONTH(SOH.OrderDate) 'Mjesec', P.FirstName, P.LastName, SUM(SOD.OrderQty*SOD.UnitPrice) 'Ukupno bez popusta',
​ROW_NUMBER() OVER(ORDER BY SUM(SOD.OrderQty*SOD.UnitPrice) DESC) 'RedniBroj', 'Usisivac' Nagrada
​FROM Person.Person AS P
​INNER JOIN Sales.Customer AS C
​ON P.BusinessEntityID=C.PersonID
​INNER JOIN Sales.SalesOrderHeader AS SOH
​ON C.CustomerID=SOH.CustomerID
​INNER JOIN Sales.SalesOrderDetail AS SOD
​ON SOH.SalesOrderID=SOD.SalesOrderID
​WHERE MONTH(SOH.OrderDate)=4 AND YEAR(SOH.OrderDate)=2013 AND RIGHT(P.FirstName,1) NOT LIKE 'a'
​GROUP BY MONTH(SOH.OrderDate), P.FirstName, P.LastName) AS PODQ
WHERE PODQ.RedniBroj=3
UNION
SELECT PODQ.FirstName, PODQ.LastName, PODQ.Mjesec, PODQ.[Ukupno bez popusta], PODQ.Nagrada
FROM(SELECT MONTH(SOH.OrderDate) 'Mjesec', P.FirstName, P.LastName, SUM(SOD.OrderQty*SOD.UnitPrice) 'Ukupno bez popusta',
​ROW_NUMBER() OVER(ORDER BY SUM(SOD.OrderQty*SOD.UnitPrice) DESC) 'RedniBroj', 'Multicooker' Nagrada
​FROM Person.Person AS P
​INNER JOIN Sales.Customer AS C
​ON P.BusinessEntityID=C.PersonID
​INNER JOIN Sales.SalesOrderHeader AS SOH
​ON C.CustomerID=SOH.CustomerID
​INNER JOIN Sales.SalesOrderDetail AS SOD
​ON SOH.SalesOrderID=SOD.SalesOrderID
​WHERE MONTH(SOH.OrderDate)=5 AND YEAR(SOH.OrderDate)=2013 AND RIGHT(P.FirstName,1) NOT LIKE 'a'
​GROUP BY MONTH(SOH.OrderDate), P.FirstName, P.LastName) AS PODQ
WHERE PODQ.RedniBroj=3
ORDER BY 4 DESC