SQL Azemovic

--Join
--Single result(tabela koja postoji samo u runtime-u)
--najcesce se koristi preko FK i PK (jer ima smisla)
-- inner join // vraca iste zapise
-- outer join // iste i razlicite zapise  (left or right)
-- UNION kombinuje rezultate dvije ili vise SELECT komandi u jedan izlaz
--EXCEPT eliminiše sve zapise iz prvog seta koji postoje u drugom rezultatu
--INTERSECT proizvodi izlaz tako što identifikuje iste zapise u oba skupa podataka
--TOP omogucava limitranje izlaza na odredjeni broj zapisa (u komb sa ORDER BY)
--TABLESAMPLE vraca slucajno odabrane zapise (u komb sa PERCENT ili ROWS)
USE AdventureWorks2014
GO
SELECT P.ProductNumber, R.Comments
FROM Production.Product P
INNER JOIN Production.ProductReview R
ON P.ProductID = R.ProductID
--2:

SELECT P.ProductNumber, R.Comments
FROM Production.Product P
LEFT OUTER JOIN Production.ProductReview R
ON P.ProductID = R.ProductID
ORDER BY R.Comments DESC

--3:
SELECT P.ProductNumber, R.Comments
FROM Production.Product P
RIGHT OUTER JOIN Production.ProductReview R
ON P.ProductID = R.ProductID

--4:
SELECT P.ProductNumber AS Broj, R.Comments
FROM Production.Product  P
FULL OUTER JOIN Production.ProductReview R
ON P.ProductID = R.ProductID

--5:
SELECT P.ProductNumber, R.Comments
FROM Production.Product P
CROSS JOIN Production.ProductReview R

--6:
SELECT P.Name AS Produkt, L.Name AS Lokacija, I.Quantity AS Kvantitet
FROM Production.Product P
INNER JOIN Production.ProductInventory I
ON P.ProductID=I.ProductID
INNER JOIN Production.Location L
ON L.LocationID = I.LocationID

--7:
SELECT P1.Name, P1.ListPrice
FROM Production.Product P1
		INNER JOIN Production.Product AS P2
		ON P1.ProductSubcategoryID=P2.ProductSubcategoryID
GROUP BY P1.Name, P1.ListPrice
HAVING P1.ListPrice > AVG (P2.ListPrice)

--8:
SELECT P.Name AS Product, V.Name AS Vendor, M.Name AS Measure
FROM Production.Product P
INNER JOIN Purchasing.ProductVendor PV
ON P.ProductID = PV.ProductID
INNER JOIN Purchasing.Vendor V
ON PV.BusinessEntityID=V.BusinessEntityID
INNER JOIN Production.UnitMeasure M
ON M.UnitMeasureCode=PV.UnitMeasureCode

--9:
SELECT ProductModelID AS [Product ID],
ModifiedDate AS [Date of modification]
FROM Production.ProductModelProductDescriptionCulture
UNION
SELECT ProductModelID AS [Product ID],
ModifiedDate AS [Date of modification]
FROM Production.ProductModel

--10:

SELECT ProductModelID AS [Product ID],
ModifiedDate AS [Date of modification]
FROM Production.ProductModelProductDescriptionCulture
EXCEPT
SELECT ProductModelID AS [Product ID],
ModifiedDate AS [Date of modification]
FROM Production.ProductModel

--11:

SELECT ProductModelID AS [Product ID],
ModifiedDate AS [Date of modification]
FROM Production.ProductModelProductDescriptionCulture
INTERSECT
SELECT ProductModelID AS [Product ID],
ModifiedDate AS [Date of modification]
FROM Production.ProductModel

--12:
SELECT TOP 10 P.Name AS Product, L.Name AS Location, I.Quantity
FROM Production.Product P
	INNER JOIN Production.ProductInventory I
	ON P.ProductID=I.ProductID
	INNER JOIN Production.Location L
	ON L.LocationID=I.LocationID
ORDER BY I.Quantity ASC

--13:
SELECT Name AS Product
FROM Production.Product TABLESAMPLE (40 PERCENT)

--Q1:
SELECT CR.Name AS Region, SP.Name AS Drzava, COUNT(PA.StateProvinceID) AS UKUPNO
FROM Person.CountryRegion AS CR INNER JOIN Person.StateProvince AS SP
ON CR.CountryRegionCode=SP.CountryRegionCode
INNER JOIN Person.Address AS PA
ON SP.StateProvinceID=PA.StateProvinceID
GROUP BY CR.Name, SP.Name
ORDER BY CR.Name

--Q2:
USE pubs
SELECT A.au_fname+A.au_fname
FROM dbo.authors AS A
INNER JOIN dbo.titles AS T
ON A.au_id	= T.pub_id


--Q3:
USE NORTHWND
SELECT P.ProductName AS Produkt, P.UnitPrice AS Cijena, SUM(P.UnitsInStock) AS [Na stanju],
COUNT (OD.ProductID) AS 'Prodano'
FROM Products AS P INNER JOIN [Order Details] AS OD
ON P.ProductID=OD.ProductID

--Q4:
SELECT K.Country, K.City, K.CompanyName, COUNT (OrderID) AS "Ukupno narudzbi:"
FROM dbo.Customers AS K INNER JOIN Orders AS O
ON K.CustomerID=O.CustomerID
GROUP BY Country, City, CompanyName