Vježba 7

--UPITI – Vježba 7
--NAPOMENA: Sve primjere riješiti koristeći podupite.
--1. Koristeći bazu podataka Northwind napisati upit koji prikazuje listu proizvoda i najveću količinu narudžbe za svaki proizvod.
USE Northwind
SELECT ProductName,
(SELECT MAX(Quantity)
FROM [dbo].[Order Details]
WHERE [dbo].[Order Details].ProductID=[dbo].[Products].ProductID) AS "Najveca kolicina narudzbe"
FROM [dbo].[Products]

--2. Koristeći bazu podataka Northwind prikazati ime kompanije kupca, ali samo onih koji su utrošili više od 10 000.
USE Northwind
SELECT [CompanyName]
FROM [dbo].[Customers]
WHERE 10000<
(SELECT SUM(Quantity*UnitPrice)
FROM [dbo].[Order Details]
WHERE [dbo].[Customers].CustomerID=
(SELECT CustomerID
FROM [dbo].[Orders]
WHERE OrderID=[dbo].[Order Details].OrderID))


--3. Koristeći bazu podataka Northwind prikazati vremensku razliku između pojedinih narudžbi i zadnje obavljene naružbe (razliku prikazati u danima).
--   Obavezni format datuma u prikazu: 08.05.2013.
USE Northwind
SELECT CONVERT(VARCHAR(10),OrderDate,104) AS 'Narudzba', CONVERT(VARCHAR(10),(SELECT MAX([OrderDate])FROM [dbo].[Orders]),104)AS 'Zadnja narudzba',
DATEDIFF(DAY, OrderDate,(SELECT MAX([OrderDate])FROM [dbo].[Orders]))AS 'razlika u danima'
FROM [dbo].[Orders]

--4. Koristeći bazu podataka AdventureWorks2012 prikazati četvrtu najveću platu u preduzeću (po visini primanja). Tabela EmployeePayHistory.
USE [AdventureWorks2012]
SELECT TOP 1 Rate AS '4. Najveca plata'
FROM [HumanResources].[EmployeePayHistory]
WHERE Rate IN
(SELECT TOP 4 Rate
FROM [HumanResources].[EmployeePayHistory]
ORDER BY Rate DESC)
ORDER BY Rate ASC


--5. Koristeći bazu podataka AdwentureWorks2012, kreirati upit koji treba da prikaže ukupnu količinu utrošenog novca za kupce. Izlaz treba da sadrži sljedeće kolone:
--   ime i prezime kupca, tip kreditne kartice, broj kartice i ukupno utrošeno. Pri tome voditi računa da izlaz sadrži:
--a)    Samo troškove koje su kupci napravili koristeći kredite kartice,
--b)    Samo one kupce koji imaju više od jedne kartice,
--c)    Prikazati i one kartice sa kojima kupac nije obavljao narudžbe,
--d)    Ukoliko vrijedost kolone utrošeno bude nepoznata, zamijeniti je brojem 0 (nula),
--e)    Izlaz treba biti sortiran po prezimenu kupca abecedno i količini utrošenog novca opadajućim redoslijedom.
--Tabele: Customer, Person, PersonCreditCard, CreditCard, SalesOrderHeader, SalesOrderDetail
--Napomena: Zadatak 5 je bonus zadatak. Za ispravan prikaz rezultata potrebno je izvršiti skriptu u prilogu (AWCreditCards.sql).
USE [AdventureWorks2012]
SELECT
(SELECT [FirstName]+' '+[LastName]
FROM [Person].[Person] AS PP
WHERE [BusinessEntityID]=PCD.BusinessEntityID
--GROUP BY FirstName,LastName
)AS 'Ime i prezime kupca',
(SELECT [CardType]
FROM [Sales].[CreditCard] AS CC
WHERE CC.[CreditCardID]=PCD.CreditCardID)AS 'Tip kreditne kartice',
(SELECT [CardNumber]
FROM [Sales].[CreditCard] AS CC
WHERE CC.[CreditCardID]=PCD.CreditCardID)AS 'Broj kreditne kartice',
ISNULL((SELECT SUM([OrderQty]*[UnitPrice])--d)  Ukoliko vrijedost kolone utrošeno bude nepoznata, zamijeniti je brojem 0 (nula)
FROM [Sales].[SalesOrderDetail] AS SOD
WHERE SOD.SalesOrderID=
(SELECT TOP 1 [SalesOrderID]
FROM [Sales].[SalesOrderHeader] AS SOH
WHERE SOH.CreditCardID=PCD.CreditCardID AND
(SOH.CreditCardID IS NOT NULL))),0) AS 'Ukupno utroseno' --a)   Samo troškove koje su kupci napravili koristeći kredite kartice,
FROM [Sales].[PersonCreditCard] AS PCD
GROUP BY BusinessEntityID, CreditCardID
--HAVING COUNT(PCD.CreditCardID)>1 --b) Samo one kupce koji imaju više od jedne kartice [NE RADI]
ORDER BY (SELECT [LastName]
FROM [Person].[Person] AS PP
WHERE [BusinessEntityID]=PCD.BusinessEntityID) ASC, 4 DESC --e) Izlaz treba biti sortiran po prezimenu kupca abecedno i količini utrošenog novca opadajućim redoslijedom.