Archiwa tagu: exercises

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


 

SQL DATEPART() function

SQL DATEPART() function


    


DEFINICJA

Funkcja DATEPART() zwraca część daty lub czasu, określoną przez użytkownika, z podanej wartości. W odniesieniu do daty zwracaną częścią może być np. rok, miesiąc, dzień a w odniesieniu do czasu może to być np. godzina, minuta itd. Należy pamiętać, że to czy możemy zwrócić daną część daty lub czasu jest uzależnione od typu danych na których będziemy pracować, np. z wyrażenia typu DATE otrzymamy rok ale nie otrzymamy godziny. Dzięki funkcji DATEPART() możemy także wyciągnąć z danego wyrażena, który to był dzień tygodnia (WEEKDAY), który to był tydzień (WEEK) itp. Lista dostępnych parametrów poniżej.

SQL DATEPART() składnia funkcji

SELECT
        DATEPART( parametr1 , parametr2 )

parametry:
parametr1 – określamy co chcemy zwrócić np. rok, dzień, godzinę itp.
parametr2 – tutaj określamy wartość z której chcemy tą część uzyskać


Przykład zastosowania funkcji DATEPART() w SQL Serverze

Wyświetl sam rok z daty przedstawionej w podanym formacie '2016-01-01 10:10:10.002'.

SELECT
    DATEPART( YEAR , '2016-01-01 10:10:10.002')


Lista dostępnych parametrów dla funkcji DATEPART():

year    rok
month   miesiąc
day   dzień
week   tydzień
weekday   dzień tygodnia
dayofyear   dzień w roku
hour   godzina
quarter   kwadrans
minute   minuty
second   sekundy
milisecond   milisekundy
microsecond   mikrosekundy
nanosecond   nanosekundy

 


    

SQL OVER(Partition by) function

SQL OVER(Partition by) function


    


DEFINICJA

Jednym z zastosowań funkcji OVER() jest wykorzystanie funkcji grupujących bez klauzuli GROUP BY. W jednym zapytaniu możemy pokazać wyniki "nie pogrupowanego" zapytania i "dorzycić" do tych wyników funkcje agregujące.
Jak to wygląda w praktyce? Wyobraźmy sobie sytuację, że mamy zaprezentować wyniki sprzedaży. Do tej pory sytuacja wyglądała tak. Jeśli chcieliśmy zaprezentować zamówienia z listą produktów/pozycji w tym zamówieniu to nie wykorzystywaliśmy klauzuli GROUP BY, ale jeśli chcieliśmy np. zaprezentować wartość poszczególnych pozycji (czyli np. ilość szt. danego produktu * cena jednostkowa) albo zliczyć ilość pozycji w zamówieniu lub policzyć ilość szt. produktów w zamówieniu to korzystaliśmy z GROUP BY. Niestety nie dało się tych wyników przedstawić za pomocą jednego zapytania. Za pomocą funkcji OVER() jest to możliwe. Poniżej składnia funkcji OVER(Partition by)

SQL OVER(Partition by) składnia funkcji

SELECT
         kolumna1
        ,kolumna2
        ,kolumna3
        ,funkcjaAgregująca(argumentFunkcjiAgregującej) OVER(Partition by argumentOver)

argumentFunkcjiAgregującej – to nic innego jak określenie (w zależności od użytej funkcji agregującej) co ma zrobić dana funkcja
argumentOver – tutaj określamy kryteria grupowania


Przykład zastosowania funkcji OVER(Partition by) (baza AdventureWorks).

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.

SE AdventureWorks2008R2

SELECT
     O.SalesOrderID AS [ID zamówienia]
    ,D.ProductID AS [ID produktu]
    ,D.UnitPrice AS [Cena jednostkowa]
    ,D.OrderQty AS [Liczba szt. produktu w pozycji]
    ,(D.UnitPrice * D.OrderQty) AS [Wartość pozycji w zamówieniu]
    ,SUM(D.UnitPrice * D.OrderQty) OVER(Partition by O.SalesOrderID ) AS [Wartość zamówienia]
    ,COUNT(*) OVER(Partition by O.SalesOrderID ) AS [Licza pozycji w zamówieniu]
    ,SUM(D.OrderQty) OVER(Partition by O.SalesOrderID ) AS [Licza produktów w zamówieniu]
FROM
    Sales.SalesOrderHeader O
    JOIN Sales.SalesOrderDetail D ON O.SalesOrderID = D.SalesOrderID
WHERE
    O.OrderDate BETWEEN '2005-12-01' AND '2005-12-31'


Przykład zastosowania OVER(Partition by) (baza Northwind)

Wyświetl informacje o zamówieniach z lipca 1996 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.

SELECT
     OD.OrderID AS [ID zamówienia]
    ,ProductID AS [ID produktu]
    ,UnitPrice AS [Cena jednostkowa]
    ,Quantity AS [Liczba szt. produktu w pozycji]
    ,(UnitPrice * Quantity) AS [Wartość pozycji w zamówieniu]
    ,SUM(UnitPrice*Quantity) OVER(Partition by OD.OrderID ) AS [Wartość zamówienia]
    ,COUNT(*) OVER(Partition by OD.OrderID ) AS [Licza pozycji w zamówieniu]
    ,SUM(Quantity) OVER(Partition by OD.OrderID ) AS [Licza produktów w zamówieniu]
FROM
    [Order Details] OD
    JOIN Orders O ON OD.OrderID = O.OrderID
WHERE
    O.OrderDate BETWEEN '1996-07-01' AND '1996-07-31'


   

SQL DATEDIFF() function

SQL DATEDIFF() function


    


DEFINICJA

Funkcja DATEDIFF() zwraca różnicę, w zdefiniowanych jednostkach, między dwiema datami.
!!! UWAGA !!! nie we wszystkich środowiskach bazodanowych funkcja DATEDIFF() działa tak samo. Poniższe przykłady pochodzą z SQL SERVER-a.

SQL DATEDIFF() składnia funkcji

SELECT
        DATEDIFF(parametr1 , parametr2 , parametr3)

parametry:
parametr1 – definiujemy w jakich jednostkach funkcja ma zwrócić różnicę np. jeśli w latach to YEAR, w miesiącach to MONTH itd
parametr2 – data początkowa (wcześniejsza)
parametr3 – data końcowa (późniejsza)


Przykład zastosowania DATEDIFF() (baza Adventureworks)

Wyświetl wszystkie zamówienia klienta o identyfikatorze 29825 i pokaż ile lat temu było składane każde z jego zamówień.

SELECT
     O.CustomerID AS [Identyfikator klienta]
    ,O.SalesOrderID AS [Identyfikator zamówienia]
    ,DATEDIFF( YEAR , O.OrderDate , GETDATE()) AS [Różnica w latach]
FROM
    Sales.SalesOrderHeader O
WHERE
    O.CustomerID = 29825


 Przykład zastosowania DATEDIFF() (baza Northwind)

Oblicz staż pracy dla każdego pracownika.

SELECT
     E.LastName + ' ' + E.FirstName AS [Nazwisko i Imię]
    ,DATEDIFF( YEAR , E.HireDate , GETDATE()) AS [Staż Pracy]
FROM
    Employees E


   

SQL NOW() function

SQL NOW() function


    


DEFINICJA

Funkcja NOW() zwraca aktualną datę i godzinę (systemową). Funkcja często wykorzystywana do obliczania różnic między aktualną datą a datą z przeszłości.
!!! UWAGA !!! funkcja NOW() nie występuje we wszystkich środowiskach bazodanowych np. nie jest rozpoznawalna w SQL SERVER-ze i przy próbie jej zastosowania otrzymamy komunikat "'NOW' is not a recognized built-in function name.". Odpowiednikiem funkcji NOW() w SQL SERVER-ze jest funkcja GETDATE().

SQL NOW() składnia funkcji

SELECT
    NOW()

wynik powyższego zapytania na bazie MySQL (PhpMyAdmin)

sql now function sql course tutorial practical exercises

 

 


   

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


   


 

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

Baza : Northwind

Ćwiczenie nr: 25

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

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


   


Rozwiązanie:

 

Zapytanie:

Wynik uruchomienia zapytania

Liczba rekordów: 1

Pobierz skrypt sql


   


 

Northwind 24. Który spedytor obsłużył zamówienia na największą wartość.

Baza : Northwind

Ćwiczenie nr: 24

Treść ćwiczenia: Który spedytor obsłużył zamówienia na największą wartość.

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


   


Rozwiązanie:

 

Zapytanie:

Wynik uruchomienia zapytania

Liczba rekordów: 1 dla każdego sposobu

Pobierz skrypt sql