Drugi_Septembarski

CREATE DATABASE Septembarski_22_09_2023
GO
USE Septembarski_22_09_2023
GO 

--2. a)
CREATE TABLE Uposlenici
(
	UposlenikID CHAR(9) CONSTRAINT PK_Uposlenici PRIMARY KEY,
	Ime VARCHAR(20) NOT NULL,
	Prezime VARCHAR(20) NOT NULL,
	DatumZaposlenja DATETIME NOT NULL,
	OpisPosla VARCHAR(50)
)

--b)
CREATE TABLE Naslovi
(
	NaslovID VARCHAR(6) CONSTRAINT PK_NaslovID PRIMARY KEY,
	Naslov VARCHAR(80) NOT NULL,
	Tip CHAR(12) NOT NULL,
	Cijena MONEY, 
	NazivIzdavaca VARCHAR(40),
	GradIzdavaca VARCHAR(20),
	DrzavaIzdavaca VARCHAR(30)
)




--d)
CREATE TABLE Prodavnice
(
	ProdavnicaID CHAR(4) CONSTRAINT PK_Prodavnice PRIMARY KEY,
	NazivProdavnice VARCHAR(40),
	Grad VARCHAR(40)
)


--c)
CREATE TABLE Prodaja
(
	ProdavnicaID CHAR(4),
	BrojNarudzbe VARCHAR(20),
	NaslovID VARCHAR(6),
	DatumNarudzbe DATETIME NOT NULL,
	Kolicina SMALLINT NOT NULL,
	CONSTRAINT PK_Prodaja PRIMARY KEY(ProdavnicaID,BrojNarudzbe,NaslovID),
	CONSTRAINT PK_Prodaja_Prodavnice FOREIGN KEY(ProdavnicaID) REFERENCES Prodavnice(ProdavnicaID),
	CONSTRAINT PK_Prodaja_Naslovi FOREIGN KEY(NaslovID) REFERENCES Naslovi(NaslovID)
)


--3. a)

INSERT INTO Uposlenici(UposlenikID,Ime,Prezime,DatumZaposlenja,OpisPosla)
SELECT E.emp_id, E.fname, E.lname, E.hire_date, J.job_desc
FROM pubs.dbo.employee AS E
INNER JOIN pubs.dbo.jobs AS J
ON E.job_id=J.job_id


--b)
INSERT INTO Naslovi(NaslovID,Naslov,Tip,Cijena,NazivIzdavaca,GradIzdavaca,DrzavaIzdavaca)
SELECT T.title_id, T.title, T.type, T.price, ISNULL(P.pub_name,'nepoznati izdavac'), P.city, P.country
FROM pubs.dbo.titles AS T
INNER JOIN pubs.dbo.publishers AS P
ON T.pub_id=P.pub_id


--c)
INSERT INTO Prodavnice(ProdavnicaID,NazivProdavnice,Grad)
SELECT S.stor_id, S.stor_name, S.city
FROM pubs.dbo.stores AS S


--d)
INSERT INTO Prodaja(ProdavnicaID,BrojNarudzbe,NaslovID,DatumNarudzbe,Kolicina)
SELECT S.stor_id, S.ord_num, S.title_id, S.ord_date, S.qty
FROM pubs.dbo.sales AS S


--4. a)
GO
CREATE PROCEDURE sp_update_naslov
(
	@NaslovID VARCHAR(6),
	@Naslov VARCHAR(80)=NULL,
	@Tip CHAR(12)=NULL,
	@Cijena MONEY=NULL,
	@NazivIzdavaca VARCHAR(40)=NULL,
	@GradIzdavaca VARCHAR(20)=NULL,
	@DrzavaIzdavaca VARCHAR(30)=NULL
)
AS
BEGIN
	UPDATE Naslovi
	SET
	Naslov = ISNULL(@Naslov,Naslov),
	Tip = ISNULL(@Tip,Tip),
	Cijena = ISNULL(@Cijena,Cijena),
	NazivIzdavaca = ISNULL(@NazivIzdavaca,NazivIzdavaca),
	GradIzdavaca = ISNULL(@GradIzdavaca,GradIzdavaca),
	DrzavaIzdavaca = ISNULL(@DrzavaIzdavaca,DrzavaIzdavaca)
	WHERE NaslovID=@NaslovID
END

EXEC sp_update_naslov @NaslovID='BU1032', @GradIzdavaca='Sarajevo'

SELECT *
FROM Naslovi


--b)
GO
USE AdventureWorks2017
GO


SELECT SUM(SOD.OrderQty) 'Ukupna prodana kolicina', SUM(SOD.UnitPrice*SOD.OrderQty) 'Ukupna zarada bez popusta',
PC.Name
FROM Sales.SalesOrderDetail AS SOD
INNER JOIN Sales.SpecialOfferProduct AS SOP
ON SOD.ProductID=SOP.ProductID AND SOD.SpecialOfferID=SOP.SpecialOfferID
INNER JOIN Production.Product AS P
ON SOP.ProductID=P.ProductID
INNER JOIN Production.ProductSubcategory AS PS
ON P.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS PC
ON PS.ProductCategoryID=PC.ProductCategoryID
WHERE PC.Name NOT LIKE '%Bikes%' AND P.Color IN ('White','Black')
GROUP BY PC.Name
HAVING SUM(SOD.OrderQty)<=20000
ORDER BY 2 DESC


--c)

SELECT P.FirstName + ' ' + P.LastName, EA.EmailAddress 'Email', SUM(SOD.OrderQty) 'Narucena kolicina', 
FORMAT(SOH.OrderDate, 'dd.MM.yyyy') 'Datum narudzbe'
FROM Person.Person AS P
INNER JOIN Person.EmailAddress AS EA
ON P.BusinessEntityID=EA.BusinessEntityID
INNER JOIN Sales.Customer AS C
ON P.BusinessEntityID=C.PersonID
INNER JOIN Sales.SalesOrderHeader AS SOH
ON C.CustomerID=SOH.CustomerID
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID=SOD.SalesOrderID
INNER JOIN Sales.SpecialOfferProduct AS SOP
ON SOD.ProductID = SOP.ProductID AND SOD.SpecialOfferID=SOP.SpecialOfferID
INNER JOIN Production.Product AS PROD
ON SOP.ProductID=PROD.ProductID
WHERE PROD.Name LIKE 'Front Brakes' AND YEAR(SOH.OrderDate) IN ('2013','2014') AND MONTH(SOH.OrderDate)=5
GROUP BY P.FirstName + ' ' + P.LastName, EA.EmailAddress, FORMAT(SOH.OrderDate, 'dd.MM.yyyy')
HAVING SUM(SOD.OrderQty)>5


--d
GO
USE Northwind
GO

SELECT TOP 1 S.CompanyName, SUM(OD.Quantity) 'Ukupna prodana kolicina'
FROM Suppliers AS S
INNER JOIN Products AS P
ON S.SupplierID=P.SupplierID
INNER JOIN [Order Details] AS OD
ON P.ProductID=OD.ProductID
INNER JOIN Categories AS C
ON P.CategoryID=C.CategoryID
INNER JOIN Orders AS O
ON OD.OrderID=O.OrderID
WHERE C.CategoryName LIKE 'Seafood' AND O.ShippedDate IS NOT NULL AND OD.Discount>0
GROUP BY S.CompanyName
ORDER BY 2 DESC



--e)
GO
USE AdventureWorks2017

SELECT SOH.SalesOrderID 'Broj narudzbe', CONCAT(P.FirstName, ' ', P.LastName) 'Ime prezime', 
CAST(SUM(SOD.LineTotal) AS decimal(18,2)) 'Stvarna vrijednost narudzbe'
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID=SOD.SalesOrderID
INNER JOIN Sales.Customer AS C
ON SOH.CustomerID=C.CustomerID 
INNER JOIN Person.Person AS P
ON C.PersonID=P.BusinessEntityID
GROUP BY SOH.SalesOrderID, CONCAT(P.FirstName, ' ', P.LastName)
HAVING SUM(SOD.UnitPrice*SOD.OrderQty*SOD.UnitPriceDiscount)>=2000
ORDER BY 3 DESC


--5. a)

SELECT TOP 1 SM.Name 'Ime kompanije', COUNT(SOH.SalesOrderID) 'Broj narudzbi', SUM(SOD.OrderQty) 'Broj proizvoda'
FROM Purchasing.ShipMethod AS SM
INNER JOIN Sales.SalesOrderHeader AS SOH
ON SM.ShipMethodID=SOH.ShipMethodID
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID=SOD.SalesOrderID
GROUP BY SM.Name
ORDER BY 2 DESC, 3 DESC


--b)
SELECT TOP 1IIF(COUNT(DISTINCT SM.ShipMethodID) = 1, 'Jedna kompanija', 'Više kompanija') [Broj Kompanija],
SM.Name AS 'Ime kompanije', COUNT(SOH.SalesOrderID) 'Broj narudžbi', SUM(SOD.OrderQty) 'UkupnaKolicinaProizvoda'
FROM Purchasing.ShipMethod AS SM
INNER JOIN Sales.SalesOrderHeader AS SOH
ON SM.ShipMethodID = SOH.ShipMethodID
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
GROUP BY SM.ShipMethodID, SM.Name
ORDER BY 3 DESC, 4 DESC



--c)
GO
USE Septembarski_22_09_2023

CREATE INDEX IX_Naslovi_Naslov
ON Naslovi(Naslov)