Vježba 8

--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