Untitled
unknown
plain_text
a year ago
7.7 kB
2
Indexable
Never
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