View difference between Paste ID: 2zPPzEg0 and TwqYYCyY

 --Vježba 8 – INSERT, UPDATE, DELETE
 
 --NAPOMENA: Vježba se radi nad bazom podataka Northwind.
 --1. U tabelu Customers dodati sebe kao kupca. Također, u istu tabelu dodati 5 (Random) kupaca iz baze podataka AdventureWorks2012. Voditi računa o kompatibilnosti podataka.
 USE Northwind
 INSERT INTO Customers
 VALUES('SMIMI','Fresh beverages','Mike Smith','CEO','Neverwinter st. 666','London',NULL,'1337','UK',NULL,NULL)
 
 SELECT *
 FROM Customers
 WHERE CustomerID='SMIMI'
 
 USE AdventureWorks2012
 INSERT INTO Northwind.dbo.Customers
 SELECT TOP 5 UPPER(LEFT(PP.FirstName,3)+LEFT(PP.LastName,2)),'nepoznato',PP.FirstName+' '+PP.LastName,'nepoznato', PA.AddressLine1, NULL, PCR.CountryRegionCode,PA.PostalCode,LEFT(PCR.Name,15),PPP.PhoneNumber, NULL
 FROM Person.Person AS PP
 LEFT JOIN Person.BusinessEntityContact AS PBE ON PP.BusinessEntityID=PBE.BusinessEntityID
 LEFT JOIN Sales.Customer AS SC ON PBE.PersonID=SC.PersonID
 JOIN Person.BusinessEntityAddress AS BE ON PP.BusinessEntityID=BE.BusinessEntityID
 JOIN Person.Address AS PA ON BE.AddressID=PA.AddressID
 JOIN Person.StateProvince AS PSP ON PA.StateProvinceID=PSP.StateProvinceID
 JOIN Person.CountryRegion AS PCR ON PSP.CountryRegionCode=PCR.CountryRegionCode
 JOIN Person.PersonPhone AS PPP ON PP.BusinessEntityID=PPP.BusinessEntityID
 ORDER BY NEWID()--pravi random listu 5 podataka, svaki put nove izlistava
 
 --2. Dodati kategoriju proizvoda i pridružiti joj 3 nova proizvoda čiji dobavljač dolazi iz Bosne i Hercegovine (Categories, Products, Suppliers).
 USE Northwind
 
 SELECT *
 FROM Categories
 
 INSERT INTO Categories
 VALUES('Water','Clean bottled water',NULL)
 
 SELECT *
 FROM Suppliers
 
 INSERT INTO Suppliers
 VALUES('H20 Supplies','Mike Smith','CEO', 'Wonderstreet 222', 'Sarajevo', NULL,71000,'BiH',NULL,NULL,'http://h20supplies.com')
 
 SELECT *
 FROM Products
 
 INSERT INTO Products
 VALUES('Mineral water',30,9,'0,5l','3',20,10,10,0),
 ('Mineral carbonated water',30,9,'0,5l',5,20,10,10,0),
 ('Mineral water',30,9,'2l','6',20,10,10,0)
 
 --3. U tabelu Employees dodati 2 zaposlenika iz baze podataka AdventureWorks2012. Voditi računa o kompatibilnosti podataka.
 USE Northwind
 SELECT *
 FROM Employees
 
 USE AdventureWorks2012
 INSERT INTO Northwind.dbo.Employees
 SELECT TOP 2 PP.LastName, PP.FirstName, HE.JobTitle,PP.Title,HE.BirthDate,HE.HireDate, PA.AddressLine1,Pa.City, PS.StateProvinceCode, PA.PostalCode, PS.CountryRegionCode,PPP.PhoneNumber,NULL,NULL,'nema',NULL,NULL
 FROM HumanResources.Employee AS HE JOIN Person.Person AS PP
 ON HE.BusinessEntityID=PP.BusinessEntityID JOIN Person.BusinessEntityAddress AS PB
 ON PP.BusinessEntityID=PB.BusinessEntityID JOIN Person.Address AS PA
 ON PB.AddressID=PA.AddressID JOIN Person.StateProvince AS PS
 ON PA.StateProvinceID=PS.StateProvinceID JOIN Person.PersonPhone AS PPP
 ON PP.BusinessEntityID=PPP.BusinessEntityID
 
 --4. Dodati novu narudžbu (Orders). Postaviti trenutno vrijeme za datum narudžbe, nekog od kupaca koji je dodan u zadatku 1, te nekog od zaposlenika koji je dodan u zadatku 3.
 --   Za ostale kolone unijeti testne podatke.
 USE Northwind
 SELECT *
 FROM Orders
 
 USE Northwind
 INSERT INTO Orders
 VALUES('SMIMI',10,GETDATE(),'2014/6/26','2014/6/2',3,'20','test','something123','Amsterdam',NULL,73330,'Bosnia')
 
 --5. Za prethodno dodanu narudžbu dodati detalje (Order Details) uključujući proizvode koji su dodani u zadatku 2. Cijenu, količinu i popust postaviti proizvoljno.
 USE Northwind
 SELECT *
 FROM [Order Details]
 
 USE Northwind
 INSERT INTO [Order Details]
 VALUES(11080,80,6,10,0.20)
 
 --6. Nekom od kupaca dodanih u zadatku 1 izmijeniti broj telefona i fax.
 USE Northwind
 SELECT *
 FROM Customers
 WHERE CustomerID='SMIMI'
 
 USE Northwind
 UPDATE Customers
 SET Phone='033/444-555',Fax='033/444-777'
 WHERE CustomerID='SMIMI'
 
 --7. Izmijeniti cijenu za nova 3 proizvoda. Cijenu umanjiti za 10%.
 USE Northwind
 SELECT *
 FROM Products
 
 USE Northwind
 UPDATE Products
 SET UnitPrice=UnitPrice-(UnitPrice*0.1)
 WHERE ProductID>=82 --kod mene ovo obuhvata zadnja 3 reda (3 nova proizvoda)
 
 --8. Obrisati sve zaposlenike koji nisu uradili niti jednu narudžbu.
 DELETE FROM Employees
 WHERE EmployeeID IN(
 SELECT E.EmployeeID
 FROM Employees AS E LEFT JOIN Orders AS O
 ON E.EmployeeID=O.EmployeeID
 GROUP BY E.EmployeeID
 HAVING COUNT(O.EmployeeID)=0
 
 
 --9. Obrisati sve kupce koji nisu uradili niti jednu narudžbu.
 USE Northwind
 DELETE FROM Customers
 WHERE CustomerID IN(
 SELECT C.CustomerID
 FROM Customers AS C LEFT JOIN Orders AS O
 ON C.CustomerID=O.CustomerID
 GROUP BY C.CustomerID
 HAVING COUNT(O.CustomerID)=0)
 
 --10. Obrisati narudžbu koja je dodana u zadatku 4 sa detaljima.
 SELECT *
 FROM Orders
 
 DELETE FROM [Order Details]
 WHERE OrderID=11081
 
 DELETE FROM Orders
 WHERE OrderID=11078