Archiwa tagu: join

Northwind 35. Wyświetl klientów którzy złożyli największą liczbę zamówień.

Baza : Northwind

Ćwiczenie nr: 35

Treść ćwiczenia: Wyświetl klientów którzy złożyli największą liczbę zamówień.

Polecenia/funkcje w zapytaniu: SELECT, SELECT TOP, FROM, WHERE, COUNT(), DECLARE SET, GROUP BY, ORDER BY


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server 


Rozwiązanie:

Zapytanie:

Na początku zadeklarujmy sobie zmienną @ile do które "wrzucimy" największą liczbę zamówień, bo może się zdarzyć, że kilku klientów złożyło tyle samo zamówień.

DECLARE @ile int =
    (
        SELECT TOP 1
            COUNT(*)
        FROM    
            Orders O
        GROUP BY
            O.CustomerID
        ORDER BY
            COUNT(*) DESC
    );

Teraz utworzymy główne zapytanie w którym wykorzystamy zmienną @ile

SELECT
     O.CustomerID AS IdentyfikatorKlienta
    ,COUNT(*) AS Ile
FROM    
    Orders O
GROUP BY
    O.CustomerID
HAVING
    COUNT(*) = @ile

Wynik uruchomienia zapytania

Liczba rekordów: 1

northwind tutorial practical exercises examples samples queries sql sql server

Pobierz skrypt sql


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server 


 

Northwind 34. Wyświetl informacje o pierwszym i ostatnim zamówieniu.

Baza : Northwind

Ćwiczenie nr: 34

Treść ćwiczenia: Wyświetl dane dotyczace pierwszego i ostatniego zamówienia. Zakres danych: Identyfikator zamówienia, Liczba pozycji w zamówieniu, Wartość zamówienia.

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


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server northwind tutorial practical exercises examples samples queries sql sql server


Rozwiązanie:

Zapytanie:

SELECT
          'Pierwsze zamówienie' AS Info
         ,D.OrderID AS IdentyfikatorZamowienia
         ,COUNT(*) AS LiczbaPozycji
         ,SUM(D.Quantity*D.UnitPrice) AS WartoscZamowienia
    FROM    
        (
        SELECT TOP 1
            O.OrderID
        FROM
            Orders O
        ORDER BY
            O.OrderDate, O.OrderID
        ) AS SQ
        JOIN [Order Details] D ON SQ.OrderID = D.OrderID
    GROUP BY
        D.OrderID
 
 UNION ALL
 
  SELECT
          'Ostatnie zamówienie' AS Info
         ,D.OrderID AS IdentyfikatorZamowienia
         ,COUNT(*) AS LiczbaPozycji
         ,SUM(D.Quantity*D.UnitPrice) AS WartoscZamowienia
    FROM    
        (
        SELECT TOP 1
            O.OrderID
        FROM
            Orders O
        ORDER BY
            O.OrderDate DESC, O.OrderID DESC
        ) AS SQ
        JOIN [Order Details] D ON SQ.OrderID = D.OrderID
    GROUP BY
        D.OrderID

Wynik uruchomienia zapytania

Liczba rekordów: 2

northwind tutorial practical exercises examples samples queries sql sql server

Pobierz skrypt sql


northwind tutorial practical exercises examples samples queries sql sql server  northwind tutorial practical exercises examples samples queries sql sql server northwind tutorial practical exercises examples samples queries sql sql server


 

Adventureworks 32. Znajdź pierwsze i ostatnie zamówienie.

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 32

Treść: Znajdź pierwsze i ostatnie zamówienie.

Polecenia/funkcje w zapytaniu: SELECT, FROM, SELECT TOP, UNION ALL, ORDER BY, DESC


SQL tutorial. AdventureWorks exercises no.32. SQL SERVER.  SQL tutorial. AdventureWorks exercises no.32. SQL SERVER. SQL tutorial. AdventureWorks exercises no.32. SQL SERVER.


Rozwiązanie:

Zapytanie:

SELECT
     OH.SalesOrderID
    ,OH.OrderDate AS DataZamowienia
    ,OH.CustomerID AS ID_Klienta
    ,OH.SalesPersonID AS ID_Sprzedawcy
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

UNION ALL

SELECT
     OH.SalesOrderID
    ,OH.OrderDate AS DataZamowienia
    ,OH.CustomerID AS ID_Klienta
    ,OH.SalesPersonID AS ID_Sprzedawcy
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   

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises no.32. SQL SERVER.  SQL tutorial. AdventureWorks exercises no.32. SQL SERVER. SQL tutorial. AdventureWorks exercises no.32. SQL SERVER.


 

Adventureworks 31. Która kategoria ma najwięcej produktów.

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 31

Treść: Która kategoria ma najwięcej produktów.

Polecenia/funkcje w zapytaniu: SELECT, FROM, WHERE, Alias, COUNT(), FULL OUTER JOIN, GROUP BY, ORDER BY


SQL tutorial. AdventureWorks exercises no.31. SQL SERVER.  SQL tutorial. AdventureWorks exercises no.31. SQL SERVER. SQL tutorial. AdventureWorks exercises no.31. SQL SERVER.


Rozwiązanie:

Zapytanie:

SELECT
     C.Name AS [Nazwa kategorii]
    ,COUNT(*) AS [Liczba produktow w kategorii]
FROM
    Production.Product P
    FULL OUTER JOIN Production.ProductSubcategory S
        ON P.ProductSubcategoryID = S.ProductSubcategoryID
    FULL OUTER JOIN Production.ProductCategory C
        ON S.ProductCategoryID = C.ProductCategoryID
GROUP BY
    C.Name    
ORDER BY
    2 DESC   

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises no.31. SQL SERVER.  SQL tutorial. AdventureWorks exercises no.31. SQL SERVER. SQL tutorial. AdventureWorks exercises no.31. SQL SERVER.


 

Adventureworks 22. Wyświetl informacje o zamówieniach z grudnia 2005 r. Użyj funkcji OVER(Partition by).

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 22

Treść: Wyświetl informacje o zamówieniach z grudnia 2005 r. Wyniki przedstaw w postaci: ID zamówienia , ID produktu , Cena jednostkowa , Liczba szt. produktu w pozycji , Wartość pozycji w zamówieniu , Wartość zamówienia , Licza pozycji w zamówieniu , Licza produktów w zamówieniu.

Polecenia/funkcje w zapytaniu: SELECT, FROM, WHERE, Alias, SUM(), COUNT(), JOIN, BETWEEN, OVER(), OVER(Partition by)


   


Rozwiązanie:

Zapytanie:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql


   


 

Adventureworks 20. Policz ilu pracowników pracuje w każdym dziale. Wyniki posortuj wg ilości pracowników malejąco.

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 20

Treść: Policz ilu pracowników pracuje w każdym dziale. Wyniki posortuj wg ilości pracowników malejąco.

Polecenia/funkcje w zapytaniu: SELECT, FROM, WHERE, Alias, GROUP BY, ORDER BY, COUNT(), INNER JOIN, JOIN, IS NULL


   


Rozwiązanie:

Zapytanie:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql


   


 

Adventureworks 19. Wyświetl 5 zamówień które mają najwięcej pozycji. Wyniki posortuj malejąco wg ilości pozycji.

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 19

Treść: Wyświetl 5 zamówień które mają najwięcej pozycji. Posortuj malejąco wg ilości pozycji.

Polecenia/funkcje w zapytaniu: SELECT, FROM, WHERE, Alias, GROUP BY, COUNT(), INNER JOIN, JOIN, ORDER BY


   


Rozwiązanie:

Zapytanie:

Wynik:

Pobierz skrypt sql


   


 

Adventureworks 25. Oblicz sumę zamówień o największej i najmniejszej wartości.

Baza : Adventureworks

Ćwiczenie nr: 25

Treść ćwiczenia: Oblicz sumę zamówień o największej i najmniejszej wartości.

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


   


Rozwiązanie:

 

Zapytanie:

SELECT
    (
        SELECT TOP 1
            SUM(D.OrderQty * D.UnitPrice)AS Suma
        FROM
            Sales.SalesOrderHeader Z JOIN Sales.SalesOrderDetail D
                ON Z.SalesOrderID = D.SalesOrderID
        GROUP BY
            Z.SalesOrderID        
        ORDER BY
            Suma DESC
    )
    +
    (
        SELECT TOP 1
            SUM(D.OrderQty * D.UnitPrice)AS Suma
        FROM
            Sales.SalesOrderHeader Z JOIN Sales.SalesOrderDetail D
                ON Z.SalesOrderID = D.SalesOrderID
        GROUP BY
            Z.SalesOrderID        
        ORDER BY
            Suma     
    )
    AS [SumaZamówień(Min.Max.)]   

Zdanie wyjaśnienia

to zadanie możemy rozwiązać na dwa sposoby.
1. Uwzględniając kwotę rabatu
2. Nie uwzględniając kwotę rabatu.

Powyższe zapytania nie uwzględnia kwoty rabatu. Jeżeli chcielibyśmy ją uwzględnić musimy posłużyć się innymi kolumnami. Jeżeli interesuje nas cena za wszystkie sztuki danego produktu w zamówieniu, to musimy posłużyć się kolumną "LineTotal" z tabeli "Sales.SalesOrderDetail", obliczenia dla tej kolumny uzyskujemy ze wzoru:

UnitPrice * (1-UnitPriceDiscount) * OrderQty

gdzie:
UnitPrice – to cena sprzedaży za sztukę produtku,
UnitPriceDiscount – to kwota rabatu a
OrderQty – to ilość zamówionych produktów.

czyli reasumując LineTotal to wartość zamówienia dla pojedynczego produktu w zamówieniu (uzwględniająca rabat i ilość zamówionego produktu).

Jeżeli interesuje nas ogólna wartość zamówienia (z uwzględnieniem rabatu) to możemy posłużyć się kolumną "SubTotal" a tabeli "Sales.SalesOrderHeader". Ta kolumną przechowuje wartości obliczone za pomocą formuły SUM(SalesOrderDetail.LineTotal)

Wynik uruchomienia zapytania

Liczba rekordów: 1

Pobierz skrypt sql