BPII - LAB2

/* 1. Prikazati ime kompanije kupca, grad i fax. Uslov je da kupci u svome imenu posjeduju riječ 'Restaurant' ili dolaze iz Madrida.
Takodjer, uslov je da kupci imaju unesen fax. Listu sortirati abacedeno po imenu kompanije. (northwind - 5 zapisa)*/
SELECT CompanyName AS 'Ime kompanije', City AS 'Grad', Fax
FROM Customers
WHERE (CompanyName LIKE '%Restaurant%' OR City='Madrid') AND Fax IS NOT NULL

/* 2. Prikazati dobavljače koji dolaze iz Njemačke ili Francuske a njihovo ime kompanije
počinje slovima A, E ili P. Upit napisati na 2 načina. (Northwind - 3 zapisa)*/
--1
SELECT CompanyName, Country
FROM Suppliers
WHERE (Country='Germany' OR Country='France') AND (CompanyName LIKE 'A%' OR CompanyName LIKE 'E%' OR CompanyName LIKE 'P%')

--2
SELECT CompanyName, Country
FROM Suppliers
WHERE Country IN ('Germany', 'France') AND CompanyName LIKE '[AEP]%'

/* 3. Prikazati naslov djela, tip djela i cijenu. Kao novu kolonu dodati cijenu s popustom od 20%.
Uslov je da nova cijena (sa popustom) bude između 10 i 20. Listu sortirati abecedno po tipu djela,
te po novoj cijeni opadajućim redoslijedom. (Pubs - 9 zapisa)*/
USE pubs
GO
SELECT title AS Nalov, type AS 'Tip djela', price AS Cijena, price- (price*0.2) AS 'Cijena s popustom'
FROM titles
WHERE price- (price*0.2) BETWEEN 10 AND 20
ORDER BY [Tip djela], [Cijena s popustom] DESC

/* 4. Prikazati minimalnu, prosječnu i maksimalnu cijenu proizvoda. (adventureworks)*/
USE AdventureWorks2014
GO
SELECT MIN(ListPrice) AS Minimalna, AVG(ListPrice) AS Prosjecna, MAX(ListPrice) AS Maksimalna
FROM Production.Product


/* 5. Potrebno je prikazati 10 najprodavanijih proizvoda i zaradu od prodaje proizvoda. (adv.)*/
SELECT TOP 10 ProductID, SUM(OrderQty) AS Kolicina, SUM(OrderQty*UnitPrice) AS Zarada
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY Kolicina DESC
/*Modifikovati prethodni upit tako da se prikaze samo zarada po proizvodu, ali gdje je zarada veca id 30.000. ()*/
SELECT SUM(OrderQty*UnitPrice) AS Zarada
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(OrderQty*UnitPrice) > 30000


/* 6. Prikazati broj proizvoda po kategoriji. Listu sortirati opadajucim redoslijedom po broju proizvoda.
Prikazati samo 10 kategorija. Također, provjeriti da li ima kategorija koji imaju isti broj proizvoda kao ona na 10. mjestu.
(adv.)*/
SELECT TOP 10 WITH TIES ProductCategoryID, COUNT (ProductCategoryID) AS 'Broj proizvoda'
FROM Production.ProductCategory
GROUP BY ProductCategoryID
ORDER BY [Broj proizvoda] DESC

/* 7. Kreirati poruku dobrodoslice za kupce u sljedecem formatu (adv.)
Dobrodosli, ime Prezime, trenutno vrijeme je: 09:45 (trenutno vrijeme). */
--1
SELECT 'Dobrodosli, ' + FirstName + ' ' + LastName + ' trenutno vrijeme je: ' + RIGHT(CONVERT(nvarchar(20), GETDATE()), 7)
FROM Person.Person

--2
SELECT 'Dobrodosli, ' + FirstName + ' ' + LastName + ' trenutno vrijeme je: '
+ CONVERT(nvarchar(2), DATEPART(HOUR, GETDATE())) + ':' +CONVERT (nvarchar (2), DATEPART(MINUTE ,GETDATE()))
FROM Person.Person

/* 8. Kompanija je odlučila da svojim zaposlenicima dodijeli mail adrese. Za tu svrhu će iskoristiti
postojeće podatke iz baze podataka. Izlaz treba biti u formatu tri nove kolone: email, lozinka, starost zaposlenika. (northwind)

Email se formira od podataka u kolonama: lastName, firstName, City i to sl formatu:
lastName.firstName@city.com (sve malim slovima)

lozinka se formira od podatka iz kolona: notes, title i adress na sl nacin:
spajanjem kolona(notes, title, adress) sl korak jeste da se sadrzaj spajanja
okrene obrnuto (npr dmbs2013 - 3102smbd)
nakon tiga iz dobivenog stringa oreskačemo prvih 10 karaktera i uzimamo sl 15. na pojedinim mjestima ce se pojaviti razmak,
isti je potrebno zamijeniti sa znakom #. uzeti zadnjih 8 karaktera.

starost se formira na osnovu kolone birthDate i trenutnog datuma.*/
USE Northwind
Go
SELECT LOWER(LastName) + '.' + LOWER(FirstName) + '@' + LOWER(City) + '.com' AS email,
        RIGHT(REPLACE(SUBSTRING(REVERSE(CONVERT(nvarchar(MAX),Notes) +Title + Address),10,15), ' ', '#'),8) AS Lozinka,
        DATEDIFF(YEAR, BirthDate, GETDATE()) AS Starost
FROM [dbo].[Employees]