sql vjezba 8

    USE NORTHWND
    /*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.*/


    INSERT INTO Customers(CustomerID,CompanyName,ContactName,City)
    VALUES ('DINO', 'Moja kompanija', 'Edin Ramic','Vogosca')

    SELECT * FROM Customers
    WHERE City LIKE 'Vogosca'

    USE AdventureWorks2014
    SELECT * FROM Person.Person

    INSERT INTO NORTHWND.dbo.Customers
    SELECT top 5 UPPER(SUBSTRING([FirstName],1,2)+SUBSTRING([LastName],1,3)),'Kompanija',[FirstName] + ' ' +[LastName],NULL,
                    NULL,NULL,NULL,NULL,NULL,NULL,NULL
    FROM [Person].[Person]
    ORDER BY newid()

    /* Dodati kategoriju proizvoda i pridružiti joj 3 nova proizvoda čiji dobavljač dolazi iz
    Bosne i Hercegovine (Categories, Products, Suppliers). */

    USE NORTHWND

    SELECT * FROM Categories

    INSERT INTO Categories
    VALUES('Nova kategorija','Kategorija koju sam ja dodao',NULL)

    INSERT INTO Suppliers(CompanyName,ContactName,Country)
    VALUES('Dobavljac iz BiH','Ime Prezime','BiH')

    SELECT * FROM Suppliers WHERE Country LIKE 'BiH'

    INSERT INTO Products(ProductName,SupplierID,CategoryID,UnitPrice)
    VALUES('Hljeb',30,9,1)
    INSERT INTO Products(ProductName,SupplierID,CategoryID,UnitPrice)
    VALUES('Grah',30,9,5)
    INSERT INTO Products(ProductName,SupplierID,CategoryID,UnitPrice)
    VALUES('Pita',30,9,3)

    SELECT * FROM Products WHERE SupplierID ='30'

    SELECT [ProductName],[CompanyName],[Country],[CategoryName]
    FROM Products AS P JOIN Suppliers AS S ON P.SupplierID = S.SupplierID
                    JOIN Categories AS C ON P.CategoryID = C.CategoryID
    WHERE S.Country LIKE 'BiH'

    /* U tabelu Employees dodati 2 zaposlenika iz baze podataka AdventureWorks2012.
     Voditi računa o kompatibilnosti podataka. */

     USE AdventureWorks2014

     INSERT INTO NORTHWND.dbo.Employees
     SELECT top 5 SUBSTRING( [LastName],1,20),SUBSTRING( [FirstName],1,10),[Title],NULL,NULL,[BirthDate],[HireDate],
                            NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
     FROM HumanResources.Employee AS EM JOIN Person.Person AS P
     ON EM.BusinessEntityID = P.BusinessEntityID
     ORDER BY rand()

     SELECT * FROM [dbo].[Employees]

     /*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 NORTHWND

     INSERT INTO Orders
     VALUES('DINO','18',getdate(),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)


      SELECT * FROM orders WHERE CustomerID = 'DINO'
    /*  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.*/

     INSERT INTO [ORDER Details]
     VALUES(11078,78,1,10,0.1)


    /*Nekom od kupaca dodanih u zadatku 1 izmijeniti broj telefona i fax.*/

    SELECT * FROM customers

    UPDATE Customers
    SET Phone = '123123', Fax = '225883'
    WHERE CustomerID = 'ALSTE'

    SELECT * FROM customers

    /*Izmijeniti cijenu za nova 3 proizvoda. Cijenu umanjiti za 10%.*/

    SELECT * FROM Products WHERE SupplierID = '31'

    UPDATE Products
    SET UnitPrice =UnitPrice-UnitPrice*0.1
    WHERE SupplierID = '31'

    /*Obrisati sve zaposlenike koji nisu uradili niti jednu narudžbu.*/

    SELECT * FROM Employees
    SELECT * FROM orders

    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
    )

    /* Obrisati narudžbu koja je dodana u zadatku 4 sa detaljima.*/

    SELECT * FROM orders

    DELETE FROM orders
    WHERE OrderID = '11080'

    DELETE FROM [ORDER Details]
    WHERE OrderID = '11080'