sql upiti vjezba 6

USE pubs
/*Iz baze podataka Pubs prikazati spojeno ime i prezime autora sa ukupnim brojem objavljenih djela.
Izlaz sortirati abecedno po prezimenu.*/

SELECT [au_lname] AS prezime,[au_fname] AS ime,COUNT(TA.au_id) AS ' objavljeno djela'
FROM [dbo].[authors] AS A JOIN [dbo].[titleauthor] AS TA ON A.au_id = TA.au_id
GROUP BY [au_lname],[au_fname]
ORDER BY  [au_lname]

/*
Koristeći tabele CountryRegion, StateProvince i Address iz baze podataka AdwentureWorks,
kreirati izvještaj koji za svaku državu prikazuje sve njene regione i za svaki region ukupan broj osoba.*/

USE AdventureWorks2014

SELECT CR.[Name],SP.[Name], COUNT(BEA.AddressID) AS 'broj osoba'
FROM [Person].[Address] AS A JOIN [Person].[StateProvince] AS SP ON
        A.StateProvinceID = SP.StateProvinceID JOIN [Person].[CountryRegion] CR ON
        CR.CountryRegionCode = SP.CountryRegionCode JOIN [Person].[BusinessEntityAddress] AS BEA ON
        A.AddressID = BEA.AddressID
GROUP BY SP.[Name],CR.[Name]
ORDER BY CR.[Name]

/*Iz baze podataka Northwind prikazati ime i prezime zaposlenika (spojeno)
te količinu novca koju su zaradili u 7 mjesecu 1997. godine. */

USE NORTHWND

SELECT [FirstName]+ ' ' + [LastName] AS 'ime i prezime', [OrderDate],SUM([Quantity]*[UnitPrice]) AS zarada
FROM [dbo].[Employees] AS E JOIN [dbo].[Orders] AS O ON
        E.EmployeeID = O.EmployeeID JOIN [dbo].[ORDER Details] AS OD ON
        O.OrderID = OD.OrderID
WHERE datepart(mm,[OrderDate]) = 07 AND datepart(yy,[OrderDate]) = 1997
GROUP BY [FirstName],[LastName],[OrderDate]
ORDER BY zarada DESC

/*Kreirati upit koji će prikazati naziv modela proizvoda i opis modela proizvoda (AdventureWorksLT).
 Uslovi koji se trebaju zadovoljiti su:
a)  Naziv modela sadrži riječ „Mountain“, i
b)  Godina izmjene modela je 2001 (koristiti funkciju), i
c)  Opis mora biti na engleskom jeziku.*/

USE AdventureWorksLT2008R2

SELECT [Name],[Description],PD.[ModifiedDate]
FROM [SalesLT].[ProductModel] AS PM JOIN [SalesLT].[ProductModelProductDescription] AS PMPD ON
        PM.ProductModelID = PMPD.ProductModelID JOIN [SalesLT].[ProductDescription] AS PD ON
        PMPD.ProductDescriptionID = PD.ProductDescriptionID
WHERE [Name] LIKE '%Mountain%' AND datepart(yy,PD.[ModifiedDate]) = 2007 --2001 nema nijedan