BPII - Vjezba 6

/*1. U tabelu Customers dodati jednog kupca (testni podaci).
Također, u istu tabelu dodati 5 kupaca iz baze podataka AdventureWorks2014 (slučajnim odabirom).
Voditi računa o kompatibilnosti podataka.*/

SELECT *
FROM Customers

INSERT INTO Customers(CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region)
VALUES('TEST','TESTNI PODACI','TEST TEST', 'TTT', 'TESTADRESA','TESTGRAD','TEST REGIJA')

INSERT INTO Customers(CustomerID,ContactName,ContactTitle,Address, CompanyName)
SELECT TOP 5 sc.CustomerID, pp.FirstName, pp.Title, sc.TerritoryID, pp.LastName
FROM AdventureWorks2014.Person.Person AS pp
INNER JOIN AdventureWorks2014.Sales.Customer AS sc ON pp.BusinessEntityID=sc.PersonID

/*2. Dodati novu kategoriju proizvoda i dobavljača, te 3 nova proizvoda (testni podaci).
Proizvode pridružiti prethodno dodanoj kategoriji i dobavljaču.*/
SELECT * FROM Products  SELECT * FROM Categories    SELECT * FROM Shippers

INSERT INTO Categories (CategoryName, [Description])
VALUES ('NovaKategorija','Testni zapis kategorije')

INSERT INTO Shippers (CompanyName, Phone)
VALUES('NoviDobavljac', '(503)555-3621')

INSERT INTO Products (ProductName, SupplierID, CategoryID,UnitPrice)
VALUES
('PrJedan', '4', '9', '10'),
('PrDva' , '4', '9', '20'),
('PrTri', '4', '9', '30')

/*3. U tabelu Employees dodati 2 zaposlenika iz baze podataka AdventureWorks2014 (slučajnim odabirom). V
oditi računa o kompatibilnosti podataka.*/
SELECT * FROM Employees

INSERT INTO Employees (FirstName, LastName, Title, BirthDate, HireDate, Address, City, Country)
SELECT TOP 2 pp.FirstName,pp.LastName,pp.Title,hre.BirthDate,hre.HireDate,addr.AddressLine1, addr.City, creg.Name
FROM AdventureWorks2014.Person.Person AS pp
INNER JOIN AdventureWorks2014.HumanResources.Employee AS hre ON hre.BusinessEntityID=pp.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.BusinessEntity as bi ON pp.BusinessEntityID = bi.BusinessEntityID
INNER JOIN AdventureWorks2014.Person.BusinessEntityAddress as biad ON bi.BusinessEntityID = biad.BusinessEntityID
INNER JOIN  AdventureWorks2014.Person.Address as addr   ON biad.AddressID = addr.AddressID
INNER JOIN AdventureWorks2014.Person.StateProvince as sp ON addr.StateProvinceID = sp.StateProvinceID
INNER JOIN AdventureWorks2014.Person.CountryRegion as creg  ON sp.CountryRegionCode = creg.CountryRegionCode
ORDER BY NEWID()

/*4. Dodati novu narudžbu. Kao vrijednost polja OrderDate postaviti trenutno vrijeme, jednog od kupaca koji je dodan u zadatku 1,
te jednog od zaposlenika koji je dodan u zadatku 3. Za ostale kolone unijeti testne podatke.*/
INSERT INTO Orders(EmployeeID, OrderDate, RequiredDate, ShippedDate, Freight, ShipName, ShipAddress, ShipCity,
                    ShipRegion, ShipPostalCode, ShipCountry)
VALUES('11' , GETDATE(), '2017-02-01', '2017-01-01', '20', 'Ime', 'Adresa', 'Sarajevo', 'FBiH', '71000', 'BiH')
SELECT * FROM Customers SELECT * FROM Employees SELECT * FROM Orders

/*5. Za prethodno dodanu narudžbu dodati detalje (Order Details) uključujući sve proizvode koji su dodani u zadatku 2.
Cijenu, količinu i popust postaviti proizvoljno.*/
INSERT INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
SELECT  OrderID,ProductID, 2.8,20,0.2
        FROM Orders,Products
    WHERE  ProductName LIKE 'PrJedan' AND ProductName LIKE 'PrDva' AND ProductName LIKE 'PrTri'

SELECT * FROM Products SELECT * FROM Orders SELECT * FROM Customers  SELECT * FROM [Order Details]

/*6. Nekom od kupaca dodanih u zadatku 1 izmijeniti broj telefona i fax.*/
SELECT *
FROM Customers

UPDATE Customers
SET Phone='120-365-201', Fax='417-985-201'
WHERE CustomerID='11000'

/*7. Izmijeniti cijenu za nova 3 proizvoda (dodana u zadatku 2). Cijenu umanjiti za 10%.
Također, u svim detaljima narudžbe gdje su se pojavili proizvodi izvršiti umanjenje cijene.*/
SELECT * FROM Products
UPDATE Products
SET UnitPrice=UnitPrice*0.9
WHERE ProductName='PrJedan' AND ProductName='PrDva' AND ProductName='PrTri'

/*8. Obrisati sve zaposlenike koji nisu uradili niti jednu narudžbu.*/
    SELECT * FROM Employees

DELETE Employees
WHERE EmployeeID NOT IN (SELECT Orders.EmployeeID FROM Orders )

/*9. Obrisati sve kupce koji nisu uradili niti jednu narudžbu.*/
SELECT * FROM Customers

DELETE Customers
WHERE CustomerID NOT IN (SELECT Orders.CustomerID FROM Orders)

/*10. Obrisati narudžbu koja je dodana u zadatku 4 sa detaljima.*/
SELECT * FROM Employees

DELETE [Order Details]
WHERE OrderID=(SELECT OrderID FROM Orders WHERE CustomerID='TEST')

DELETE Orders
WHERE EmployeeID = (SELECT EmployeeID FROM Employees WHERE LastName = 'Randall')