Vjezba 3

USE AdventureWorks2014

/*1. Za sve osobe koje imaju unesenu titulu kreirati poruku dobrodošlice u sljedećem formatu:
Dobrodošli [titula] [ime i prezime], trenutno je [trenutno vrijeme] sati
Upit napisati minimalno na 3 načina kombinirajući različite T-SQL funkcije!*/

--1

SELECT 'Dobrodošli ' + Title+ ' ' + FirstName + ' ' +LastName AS Dobrodoslica,
        +'trenutno je  '+ convert(nvarchar(5) ,
        (convert(time,SYSDATETIME ()))) + ' ' + 'sati' AS Vrijeme
FROM Person.Person
WHERE Title IS NOT NULL

SELECT 'Dobrodošli' + ' ' + Title + ' ' + FirstName + ' ' + LastName + ' ' + 'trenutno je'
        + ' ' + SUBSTRING((CONVERT(nvarchar,(CONVERT(TIME, SYSDATETIMEOFFSET())))),0,6) + ' ' + 'sati'
FROM Person.Person
WHERE Title IS NOT NULL

SELECT 'Dobrodošli' + ' ' + Title + ' ' + FirstName + ' ' + LastName + ' ' + 'trenutno je' + ' '
       + CONVERT(nvarchar(5),(CONVERT (time, SYSDATETIME()))) + ' ' + 'sati'
FROM Person.Person
WHERE Title IS NOT NULL

/*2. Prikazati minimalnu, maksimalnu i prosječnu cijenu proizvoda ali samo onih gdje je cijena
veća od 0 KM.*/

--2
SELECT  MAX(ListPrice) AS 'Maximalna cijena proizovda',
        MIN(ListPrice) AS 'Minimalna cijena proizovda',
        ROUND (AVG(ListPrice),2) AS 'Prosjecna cijena proizvoda'
FROM Production.Product
WHERE ListPrice >0

/*3. Kreirati upit koji prikazuje ukupan broj kupaca po teritoriji. Lista treba da sadrži sljedeće
kolone: ID teritorije, ukupan broj kupaca. Uzeti u obzir samo teritorije gdje ima više od 1000
kupaca.*/

--3

SELECT  TerritoryID ,count(CustomerID) AS 'Broj kupaca'
FROM Sales.Customer
GROUP BY TerritoryID
HAVING COUNT (CustomerID) >1000

/*4. Prikazati ukupan broj proizvoda po modelu. Lista treba da sadrži ID modela proizvoda i
ukupan broj proizvoda. Uslov je da proizvod pripada nekom modelu i da je ukupan broj
proizvoda po modelu veći od 1. Također, prebrojati samo proizvode čiji naziv počinje slovom 'S'.*/

--4 krivi pokusaj

SELECT Name,ProductModelID AS 'Model proizvoda', SafetyStockLevel AS 'Ukupan broj proizvoda'
FROM Production.Product
WHERE (ProductModelID IS NOT NULL ) AND (SafetyStockLevel >1 ) AND (Name LIKE '[S]%')
--4 Ispravno uradnjeno
SELECT ProductModelID AS Model , COUNT(ProductID) AS Ukupno
FROM Production.Product
WHERE ProductModelID IS NOT NULL AND Name LIKE 'S%'
GROUP BY ProductModelID
HAVING COUNT(ProductID) >1


/*5. Kreirati upit koji prikazuje 10 najprodavanijih proizvoda. Lista treba da sadrži ID proizvoda i
ukupnu količinu prodaje. Provjeriti da li ima proizvoda sa istom količinom prodaje kao zapis pod
rednim brojem 10?*/

--5

SELECT Top 10 WITH TIES ProductID, SUM(OrderQty)
FROM sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY SUM(OrderQty) DESC

/*6. Kreirati upit koji prikazuje zaradu od prodaje proizvoda. Lista treba da sadrži ID proizvoda,
ukupnu zaradu bez uračunatog popusta i ukupnu zaradu sa uračunatim popustom. Iznos zarade
zaokružiti na dvije decimale. Uslov je da se prikaže zarada samo za stavke gdje je bilo popusta.
Listu sortirati po zaradi opadajućim redoslijedom.*/


--6 Tacno

SELECT ProductID, ROUND (SUM(OrderQty *UnitPrice ),2) AS 'Zarada bez popusta',
        ROUND ((SUM((OrderQty *UnitPrice )-(OrderQty *UnitPrice * UnitPriceDiscount))),2) AS 'Cijena sa popustom'
FROM Sales.SalesOrderDetail
WHERE UnitPriceDiscount >0
GROUP BY ProductID
ORDER BY "Zarada bez popusta" DESC

--6*- Ovo je samo uracunalo i sumiralo samo one proizvode koji imaju isti popust,
 SELECT ProductID as "ID proizvoda", ROUND(SUM(OrderQty*UnitPrice),2) as "Bez popusta: ",
        ROUND(SUM((OrderQty*UnitPrice)-(OrderQty*UnitPrice*UnitPriceDiscount)),3) as "Sa popustom: ",
        UnitPriceDiscount as Popust
 FROM Sales.SalesOrderDetail
 WHERE UnitPriceDiscount>0
 GROUP BY ProductID, UnitPriceDiscount
 ORDER BY "Bez popusta: " desc

 /*7. Prikazati broj narudžbe, datum narudžbe i datum isporuke za narudžbe koje su isporučene u
Kanadu u 7. mjesecu 2014. godine. Uzeti u obzir samo narudžbe koje nisu plaćene kreditnom
karticom. Datume formatirati u sljedećem obliku: dd.mm.yyyy*/

--7

SELECT SalesOrderID AS 'Broj narudžbe',
       CONVERT(nvarchar(10),OrderDate,104) AS 'Datum narudžbe',
       CONVERT(nvarchar(10),DueDate,104) AS 'Datum isporuke'
FROM Sales.SalesOrderHeader
WHERE TerritoryID = 6 AND MONTH(DueDate) ='7' AND YEAR(DueDate) = '2014' AND CreditCardID IS NULL

/*8. Kreirati upit koji prikazuje minimalnu, maksimalnu, prosječnu te ukupnu zaradu po
mjesecima u 2013. godini.*/

--8

SELECT MONTH(DueDate) AS 'MONTH',
MIN(LineTotal) AS 'MINIMUM',
MAX(LineTotal) AS 'MAXIMUM',
AVG(LineTotal) AS 'AVERAGE',
SUM(LineTotal) AS 'TOTAL'
FROM Purchasing.PurchaseOrderDetail
WHERE YEAR(DueDate)='2013'
GROUP BY MONTH(DueDate)
ORDER BY MONTH(DueDate) ASC