Adventureworks 37. Oblicz i wyświetl wartość pierwszego i ostatniego zamówienia.

ADVENTUREWORKS, SQL, , , , ,

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 37

Treść:  Oblicz i wyświetl wartość pierwszego i ostatniego zamówienia.

Polecenia/funkcje w zapytaniu: SELECT, SELECT TOP, FROM, ORDER BY, GROUP BY, JOIN, SUM()

SQL tutorial. AdventureWorks exercises no.36. SQL SERVER. SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises SQL SERVER.  SQL tutorial. AdventureWorks exercises no.36. SQL SERVER. SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises SQL SERVER. SQL tutorial. AdventureWorks exercises no.36. SQL SERVER. SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises SQL SERVER.

Rozwiązanie:

Pomocniczy schemat

Adventureworks 37. Oblicz i wyświetl wartość pierwszego i ostatniego zamówienia.

Zapytanie:

    SELECT
         'Pierwsze zamówienie' AS Info
        ,SUM(OD.OrderQty*OD.UnitPrice)AS Wartosc
    FROM    
        (
        SELECT TOP 1 O.SalesOrderID
        FROM Sales.SalesOrderHeader O
        ORDER BY O.OrderDate, SalesOrderID
        ) AS SQ
        JOIN Sales.SalesOrderHeader OH ON SQ.SalesOrderID = OH.SalesOrderID
        JOIN Sales.SalesOrderDetail OD ON SQ.SalesOrderID = OD.SalesOrderID
    GROUP BY
        OH.SalesOrderID

    UNION ALL

       SELECT
         'Ostatnie zamówienie' AS Info
        ,SUM(OD.OrderQty*OD.UnitPrice)AS Wartosc
    FROM    
        (
        SELECT TOP 1 O.SalesOrderID
        FROM Sales.SalesOrderHeader O
        ORDER BY O.OrderDate DESC, SalesOrderID DESC
        ) AS SQ
        JOIN Sales.SalesOrderHeader OH ON SQ.SalesOrderID = OH.SalesOrderID
        JOIN Sales.SalesOrderDetail OD ON SQ.SalesOrderID = OD.SalesOrderID
    GROUP BY
        OH.SalesOrderID

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql

SQL tutorial. AdventureWorks exercises no.36. SQL SERVER. SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises SQL SERVER.  SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises SQL SERVER. SQL tutorial. AdventureWorks exercises no.36. SQL SERVER. SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises SQL SERVER.