Untitled

CREATE DATABASE Ispit
GO
USE Ispit

--REDOSLIJED IZVRŠAVANJA JE VAŽAN RADI POVEZIVANJA TABELA, IDE: a,b,d,c

--2 a)
CREATE TABLE Izdavaci
(
	IzdavacID CHAR(4) CONSTRAINT PK_Izdavaci PRIMARY KEY,
	Naziv VARCHAR(40),
	Grad VARCHAR(20),
	Drzava VARCHAR(40),
	DodatneInformacije TEXT
)

--2 b)
CREATE TABLE Naslovi
(
	NaslovID VARCHAR(6) CONSTRAINT PK_Naslovi PRIMARY KEY,
	Naslov VARCHAR(80) NOT NULL,
	Tip CHAR(12) NOT NULL,
	Cijena MONEY,
	IzdavacID CHAR(4) NOT NULL CONSTRAINT FK_Naslovi_Izdavaci FOREIGN KEY REFERENCES Izdavaci(IzdavacID)
)

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

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

--REDOSLIJED IZVRŠAVANJA JE VAŽAN RADI VEZA IZMEĐU TABELA, IDE: a,b,d,c

--3 a)
INSERT INTO Izdavaci(IzdavacID,Naziv,Grad,Drzava,DodatneInformacije)
SELECT P.pub_id,P.pub_name,P.city,P.country,PI.pr_info
FROM pubs.dbo.publishers AS P
INNER JOIN pubs.dbo.pub_info AS PI
ON PI.pub_id=P.pub_id

--3 b)
INSERT INTO Naslovi(NaslovID,Naslov,Tip,Cijena,IzdavacID)
SELECT T.title_id,T.title,T.type,T.price,T.pub_id
FROM pubs.dbo.titles AS T

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

--3 c)
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_edit_izdavac
(
	@IzdavacID CHAR(4),
	@Naziv VARCHAR(40)=NULL,
	@Grad VARCHAR(20)=NULL,
	@Drzava VARCHAR(40)=NULL,
	@DodatneInformacije TEXT=NULL
)
AS
BEGIN
	UPDATE Izdavaci
	SET Naziv=ISNULL(@Naziv,Naziv),
		Grad=ISNULL(@Grad,Grad),
		Drzava=ISNULL(@Drzava,Drzava),
		DodatneInformacije=ISNULL(@DodatneInformacije,DodatneInformacije)
	WHERE IzdavacID LIKE @IzdavacID
END

SELECT *
FROM Izdavaci

EXEC sp_edit_izdavac @IzdavacID='0736', @Naziv='ProceduraTest'

SELECT *
FROM Izdavaci

--4 b)
CREATE TABLE Prodavnice_log2
(
	LogID INT CONSTRAINT PK_LogID PRIMARY KEY IDENTITY(1,1),
	ProdavnicaID CHAR(4),
	Naziv VARCHAR(40),
	Grad VARCHAR(40),
	Datum DATETIME,
	Opis VARCHAR(10)	
)

--4 c)
GO
CREATE OR ALTER TRIGGER t_del_Prodavnice
ON Prodavnice
AFTER DELETE
AS
BEGIN
	INSERT INTO Prodavnice_log2(ProdavnicaID,Naziv,Grad,Datum,Opis)
	SELECT ProdavnicaID, NazivProdavnice,Grad,GETDATE(),'DELETE'
	FROM deleted
END

INSERT INTO Prodavnice
VALUES (1,'TestZaDelete','Livno')

SELECT *
FROM Prodavnice

SELECT *
FROM Prodavnice_log2

DELETE
FROM Prodavnice
WHERE ProdavnicaID LIKE '1'

SELECT *
FROM Prodavnice

SELECT *
FROM Prodavnice_log2

--4 d)

USE pubs

SELECT TOP 10 WITH TIES S.ord_num, T.title, S.ord_date, S.qty
FROM pubs.dbo.sales AS S
INNER JOIN pubs.dbo.titles AS T
ON S.title_id=T.title_id
ORDER BY 4 DESC

--4 e)
USE Northwind

SELECT OD.OrderID, SUM(OD.Quantity) 'Ukupno proizvoda', ROUND(SUM(OD.Quantity*OD.UnitPrice),2) 'Bez popusta',
ROUND(SUM(OD.Quantity*OD.UnitPrice*(1-OD.Discount)),2) 'Sa popustom'
FROM Northwind.dbo.[Order Details] AS OD
INNER JOIN Northwind.dbo.Orders AS O
ON O.OrderID=OD.OrderID
WHERE DATEDIFF(DAY, O.OrderDate,O.ShippedDate)<=7 AND O.ShipCity IN ('München', 'Seattle', 'Madrid')
GROUP BY OD.OrderID
ORDER BY 2 DESC


--4 f)
USE prihodi

SELECT O.Ime, O.PrezIme, P.Naziv, TRP.NazivRedovnogPrihoda, RP.Neto, RP.Godina
FROM prihodi.dbo.Osoba AS O
INNER JOIN prihodi.dbo.Poslodavac AS P
ON P.PoslodavacID=O.PoslodavacID
INNER JOIN prihodi.dbo.RedovniPrihodi AS RP
ON RP.OsobaID=O.OsobaID
INNER JOIN prihodi.dbo.TipRedovnogPrihoda AS TRP
ON TRP.TipRedovnogPrihodaID=RP.TipRedovnogPrihodaID
LEFT JOIN prihodi.dbo.VanredniPrihodi AS VP
ON VP.OsobaID=O.OsobaID
WHERE O.Spol LIKE 'F' AND VP.VanredniPrihodiID IS NULL
ORDER BY 6 DESC, 4 ASC, 5 DESC

-- TEST 1 => Prezime Fullager se nalazi u tabeli RP(ima redovne prihode)
SELECT *
FROM prihodi.dbo.Osoba AS O
INNER JOIN prihodi.dbo.RedovniPrihodi AS RP
ON RP.OsobaID=O.OsobaID
WHERE O.PrezIme LIKE 'Fullager'

-- TEST 2 => Prezime Fullager se nalazi u tabeli VP(ima vanredne prihode)
SELECT *
FROM prihodi.dbo.Osoba AS O
LEFT JOIN prihodi.dbo.VanredniPrihodi AS VP
ON VP.OsobaID=O.OsobaID
WHERE O.PrezIme LIKE 'Fullager' AND VP.VanredniPrihodiID IS NOT NULL

-- TEST 3 => Prezime Fullager se NE nalazi u tabeli jer ima redovni i vanredni prihod (uslov zadatka je da ima samo RP a VP nema)
SELECT *
FROM prihodi.dbo.Osoba AS O
INNER JOIN prihodi.dbo.RedovniPrihodi AS RP
ON RP.OsobaID=O.OsobaID
LEFT JOIN prihodi.dbo.VanredniPrihodi AS VP
ON VP.OsobaID=O.OsobaID
WHERE VP.VanredniPrihodiID IS NULL AND O.Spol LIKE 'F' AND O.PrezIme LIKE 'Fullager'


--5 a)

USE AdventureWorks2017

SELECT TOP 1 D.Name, COUNT(E.BusinessEntityID) 'Broj uposlenika'
FROM AdventureWorks2017.HumanResources.Department AS D
INNER JOIN AdventureWorks2017.HumanResources.EmployeeDepartmentHistory AS EDH
ON EDH.DepartmentID=D.DepartmentID
INNER JOIN AdventureWorks2017.HumanResources.Employee AS E
ON E.BusinessEntityID=EDH.BusinessEntityID
WHERE DATEDIFF(YEAR,E.BirthDate,GETDATE())>65
GROUP BY D.Name
ORDER BY 2 DESC


--5 b)
SELECT TOP 1 P.Name, SUM(SOD.OrderQty) 'Prodana kolicina'
FROM AdventureWorks2017.Sales.SalesOrderDetail AS SOD
INNER JOIN AdventureWorks2017.Sales.SalesOrderHeader AS SOH
ON SOH.SalesOrderID=SOD.SalesOrderID
INNER JOIN AdventureWorks2017.Production.Product AS P
ON P.ProductID=SOD.ProductID
INNER JOIN AdventureWorks2017.Production.ProductSubcategory AS PSC
ON PSC.ProductSubcategoryID=P.ProductSubcategoryID
INNER JOIN AdventureWorks2017.Production.ProductCategory AS PC
ON PC.ProductCategoryID=PSC.ProductCategoryID
WHERE YEAR(SOH.OrderDate)=2011 AND PC.Name LIKE '%Component%'
GROUP BY P.Name
ORDER BY 2 DESC

--5 c)
SELECT AVG(PODQ.Broj)
FROM(
SELECT SP.BusinessEntityID, COUNT(SOH.SalesOrderID)'Broj'
FROM AdventureWorks2017.Sales.SalesOrderHeader AS SOH
INNER JOIN AdventureWorks2017.Sales.SalesPerson AS SP			-- PODUPIT KOJI RADI AVG BROJA NARUDŽBI
ON SP.BusinessEntityID=SOH.SalesPersonID
GROUP BY SP.BusinessEntityID
) AS PODQ

SELECT P.FirstName,P.LastName, DATEDIFF(YEAR,E.HireDate,GETDATE())'Staz', COUNT(SOH.SalesOrderID) 'Broj narudzbi',
	IIF(COUNT(SOH.SalesOrderID)>(SELECT AVG(PODQ.Broj)					--
	FROM(																--
	SELECT SP.BusinessEntityID, COUNT(SOH.SalesOrderID)'Broj'			--
	FROM AdventureWorks2017.Sales.SalesOrderHeader AS SOH				-- PODUPIT COPY PASTE OD IZNAD, samo ubačeno u IIF da poredi sa Brojem narudžbi
	INNER JOIN AdventureWorks2017.Sales.SalesPerson AS SP				--
	ON SP.BusinessEntityID=SOH.SalesPersonID							--
	GROUP BY SP.BusinessEntityID										--
	) AS PODQ),'Iznadprosjecan','Ispodprosjecan') 'Grupa'				--

FROM AdventureWorks2017.Person.Person AS P
INNER JOIN AdventureWorks2017.HumanResources.Employee AS E
ON E.BusinessEntityID=P.BusinessEntityID
INNER JOIN AdventureWorks2017.Sales.SalesPerson AS SP
ON SP.BusinessEntityID=E.BusinessEntityID
INNER JOIN AdventureWorks2017.Sales.SalesOrderHeader AS SOH
ON SOH.SalesPersonID=SP.BusinessEntityID
GROUP BY P.FirstName,P.LastName,E.HireDate
ORDER BY 4 DESC