BPII - Vjezba 3

/*Vježba 3 :: 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!*/

USE AdventureWorks2014
GO
--1
SELECT 'Dobrodosli ' + Title + ' ' + FirstName + ' ' +LastName + ', trenuto je '+
        CONVERT(varchar, GETDATE(),108) + ' sati'
FROM Person.Person
WHERE Title IS NOT NULL
--2
SELECT 'Dobrodosli ' + Title + ' ' + FirstName + ' ' +LastName + ', trenuto je '+
        RIGHT(CONVERT(varchar, GETDATE()),7) + ' sati'
FROM Person.Person
WHERE Title IS NOT NULL
--3
SELECT 'Dobrodosli ' + Title + ' ' + FirstName + ' ' +LastName + ', trenuto je'+
        SUBSTRING(CONVERT(varchar, GETDATE()),14,4) + ' 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.*/

SELECT MIN(ListPrice) AS [Minimalna], MAX(ListPrice) AS [Maksimalna], AVG(ListPrice) AS [Prosjecna]
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.*/

SELECT TerritoryID, COUNT (CustomerID) AS [Ukupan broj kupaca]
FROM Sales.SalesOrderHeader
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'.*/

SELECT ProductModelID, COUNT (ProductID) AS [Ukupan broj proizvoda]
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?*/

SELECT TOP 10 SUM(OrderQty) AS [Ukupna kolicina prodaje], ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY SUM(OrderQty)

/*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.*/

SELECT ProductID, UnitPrice,UnitPriceDiscount ,
       ROUND(SUM(UnitPrice),2) AS [Sume bez popusta],
       ROUND(SUM(UnitPrice) - (Count(ProductID) * SUM(UnitPriceDiscount)),2) AS [Suma s popustom]
FROM Sales.SalesOrderDetail
WHERE UnitPriceDiscount>0
GROUP BY ProductID, UnitPrice, UnitPriceDiscount
ORDER BY ROUND(SUM(UnitPrice) - (Count(ProductID) * SUM(UnitPriceDiscount)),2) 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*/

SELECT SalesOrderNumber, CONVERT(varchar, OrderDate, 104) AS [Order date],
       CONVERT(varchar,ShipDate,104) AS [Ship date]
FROM Sales.SalesOrderHeader
WHERE CreditCardID IS NOT NULL AND MONTH(ShipDate)='7' AND YEAR(ShipDate)='2014'
      AND TerritoryID='6'
--CONVERT(data_type(length),expression,style)
/*CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)*/
SELECT *
FROM Sales.SalesTerritory

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

SELECT MIN(SubTotal) AS [Minimalna], MAX (SubTotal) AS [Makismalna],
       AVG(SubTotal) AS [prosjecna], SUM(Subtotal) AS [Ukupna], MONTH(DueDate) AS [Po mjesecima]
FROM Sales.SalesOrderHeader
WHERE YEAR(DueDate)='2013'
GROUP BY DueDate