Drugi_Septembarski
firex
sql
2 years ago
6.1 kB
7
Indexable
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)
Editor is loading...