Miesięczne archiwum: Kwiecień 2017

baza HOTELS cz.1 tabela hotele „hotels” i pokoje „rooms” [SQL SERVER]

baza HOTELS cz.1 tabela hotele "HOTELS" i pokoje "ROOMS"

W tym cyklu zbudujemy bazę hoteli o nazwie "HOTELS", która będzie przykładową bazą przechowującą dane dotyczące HOTELI, czyli pokoi, rezerwacji, gości itp.

W części pierwszej zajmiemy się stworzeniem samej bazy HOTELS i  tabel hotele "hotels" i pokoje "rooms". No to zaczynamy.

Żeby móc tworzyć tabele uwtorzymy sobie na początek bazę danych w nazwie HOTELS. Poniżej krok po kroku jak to zrobić.
1. W tym celu uruchamiany Management Studio i w oknie "Object Explorer" klikamy prawym przyciskiem myszy na "Databases" i w podręcznego menu wybieramy opcję :"New Database…"
2. W oknie "New Database" w polu "Database name" wpisujemy nazwę bazy, w naszym przypadku będzie to HOTELS. Wszystko potwierdzamy przyciskiem "OK".
3. W tym momencie w Management Studio w Object Explorerze w gałęzi "Databases" powinna się pojawić baza HOTELS. Jeżeli tak się nie stanie, klikamy na węźle "Databases" prawym przyciskiem myszy i z menu wybieramy opcję "Refresh".

gotowe 🙂

sql database hotels baza danych hotele tutorial sql server

lub korzystamy z gotowego skryptu i uruchamiamy go w Management Studio

CREATE DATABASE HOTELS

Teraz możemy zająć się tabelami, zacznijmy od tabeli hotele "hotels" w której będziemy przechowywać informacje o poszczególnych hotelach. Na pewno będziemy potrzebowali takich pól jak:

  • będziemy potrzebowali unikalnego identyfikatora hotelu, nazwiemy go hotel_id
  • następnie każdy hotel ma nazwę więc pole hotel_name
  • bazę tworzymy jak najbardziej uniwersalną, więc zakładamy, że hotele mogą znajdować się w wielu krajach, więc musimy wiedzieć gdzie znajduje się hotel. Ale z uwagi na to, że nazwy krajów są powtarzalne będziemy je przechowywać w osobnej tabeli a w tabeli hotels będziemy przechowywć tylko ich identyfikatory – hotel_country_id
  • kolejna pozycja (pole) to miasto. Sytuacja analogiczna do krajów. Nazwy miast także będą się powtarzać, więc będziemy je przechowywać w osobnej tabeli a w tabeli hotels będziemy przechowywali tylko ich identyfikatory – hotel_city_id
  • każdy hotel oczywiście ma adres więc musimy stworzyć takie pole. Moglibyśmy rozbić adres na ulicę, nr domu i nr lokalu, ale dla potrzeb naszego projektu nie będziemy tego robić. Uznajemy, że adresy nie będą się powtarzać bo każdy hotel może się znajdować pod inną lokalizacją więc nasze pole nazywamy – hotel_address
  • hotel powinien także mieć ileś gwiazdek, tą informację będziemy przechowyać w polu – hotel_stars
  • oczywiście do hotelu powinniśmy mieć możliwość się dodzwonić – hotel_telephone
  • i napisać maila – hotel_email
  • nie wyobrażam sobie sytuacji gdzie hotel nie ma strony www – hotel_www

OK już wiemy jakich pól potrzebujemy. Teraz je storzymy w SQL SERVER (Management Studio).

W celu utworzenia tabeli "hotels" musimy w Management Studio wykonać następujące kroki.
1. Przechodzimy do Object Explorer-a.
2. Rozwijamy (plusikiem) gałąź przy Databases, następnie przy HOTELS. Rozwiną nam się jeszcze dodatkowe gałęzie: Database diagram, Tables, Views itd. Nas będzie interesowała gałąź Tables.
3. Klikamy prawym przyciskiem myszy na gałęzi Tables i z podręcznego menu wybieramy opcję "New Table…".
4. Wypełniamy tabelę zgodnie z obrazkiem poniżej.
sql database hotels baza danych hotele tutorial sql server
5. W celu zapisu tabeli klikamy na ikonkę "dyskiskietki" i wprowadzamy nazwę tabeli "hotels".

gotowe 🙂

lub korzystamy ze skryptu

USE HOTELS
GO

CREATE TABLE hotels(
     hotel_id int primary key not null
    ,hotel_name varchar(30) not null
    ,hotel_country_id int not null
    ,hotel_city_id int not null
    ,hotel_address varchar(50) not null
    ,hotel_stars int not null
    ,hotel_telephone varchar(20) not null
    ,hotel_email varchar(50) not null
    ,hotel_www varchar(50) not null
)

Myślę, że jeżeli chodzi o tabelę hotels to już wszystko. Jeżeli zapomnimy o czymś dodamy to później. Do naszego skryptu dodałem wszędzie ograniczenie NOT NULL bo nie wyobrażam sobie żeby którekolwiek z pól mogło być puste.

Teraz przejdziemy od tabeli pokoje "rooms". Co jest potrzebne do opisania pokoju?:

  • na początu na pewno uniaklny identyfikator danego pokoju – room_id
  • jeżeli mamy kilka hoteli, a taką bazę tworzymy, to napewno musimy wpisać w którym hotelu znajduje się dany pokój czyli stworzymy pole – room_hotel_id
  • każdy pokój ma swój unialny numer więc utworzymy pole – room_number
  • i każdy pokój znajduje się na jakimś piętrze, bo może być ważną informacją dla klienta, czyli powstaje pole – room_floor
  • każdy pokój może pomieścić także określoną ilość osób – room_people

W celu utworzenia tabeli "hotels" musimy w Management Studio wykonać następujące kroki.
1. Przechodzimy do Object Explorer-a.
2. Rozwijamy (plusikiem) gałąź przy Databases, następnie przy HOTELS. Rozwiną nam się jeszcze dodatkowe gałęzie: Database diagram, Tables, Views itd. Nas będzie interesowała gałąź Tables.
3. Klikamy prawym przyciskiem myszy na gałęzi Tables i z podręcznego menu wybieramy opcję "New Table…".
4. Wypełniamy tabelę zgodnie z obrazkiem poniżej.
sql database hotels baza danych hotele tutorial sql server

5. W celu zapisu tabeli klikamy na ikonkę "dyskiskietki" i wprowadzamy nazwę tabeli "rooms".

gotowe 🙂

lub korzystamy ze skryptu

USE HOTELS
GO

CREATE TABLE rooms(
     room_id int primary key not null
    ,room_hotel_id int not null
    ,room_number int not null
    ,room_floor int not null
    ,room_people int not null
)

Narazie nie określamy, czy np. w pokoju można palić czy jest WiFi itd bo takie dane będziemy przechowywać w innej tabeli, ale to opiszę w kolejnych wpisach.
Utwórzmy także klucze główne (PK – primary key) i klucze obce (FK – foreign key). Oczywiście kluczami głównymi w obu tabelach będą unikalne identyfikatory i tak w tabeli hotels będzie to pole hotel_id (PK) i analogicznie w tabeli rooms będzie to pole room_id (PK). Ponieważ pokój musi być przyporządkowany do jakiegoś hotelu a wszytkie dane dot. hoteli przechowujemy w tabeli hotels to musielismy w tabeli rooms stworzyć pole "trzymające" identyfikator hotelu "wskazującego" na pole hotel_id (tab. hotels) którym jest właśnie pole room_hotel_id i to właśnie pole będzie kluczem obcym (FK).
W tym momencie schemat naszej bazy hotels wygląda następująco.

sql database hotels baza danych hotele tutorial sql server

Nie widać na schemacie żadnych relacji między tabelami bo jeszcze ich nie skonfigurowaliśmy. Zróbmy to teraz. Co musimy zrobić? "Połączyć" pole room_hotel_id z tabeli rooms i polem hotel_id z tabeli hotels. Pamiętajmy jednak, że to pole room_hotel_id "ma wskazywać na pole hotel_id, więce musimy ustawić pole room_hotel_id jako klucz obcy i "wskazać" na pole hotel_id. Możemy to zrobić poleceniem.

USE HOTELS
GO

ALTER TABLE rooms
ADD CONSTRAINT fk_rooms_hotels FOREIGN KEY (room_hotel_id) REFERENCES hotels(hotel_id)

sprawdźmy teraz jak wygląda nasz schemat

sql database hotels baza danych hotele tutorial sql server

gotowe 🙂


sql database hotels baza danych hotele tutorial sql server  sql database hotels baza danych hotele tutorial sql server 

PostgreSQL instalacja krok po kroku.

PostgreSQL instalacja krok po kroku.

W poprzednim wpisie ściągneliśmy sobie plik instalatora (zobacz tutaj). Teraz przejdziemy do instalcji PostgreSQL.

Krok 1. Uruchamiamy instalator

Odszukujemy na dysku plik instalatora i klikamy na nim dwukrotnie. Tym sposobem uruchomimy instalator.

PostgreSQL instalacja krok po kroku.

potwierdzamy przyciskiem "next"

Krok 2. Wybór miejsca instalacji.

Teraz wskazujemy gdzie chcemy zainstalować PostgreSQL.

PostgreSQL instalacja krok po kroku.

jeżeli nie chcemy zmieniać podanej lokalizacji, potwierdzamy przyciskiem "next"

Krok 3. Wybieramy dodatkowe komponenty do instalacji.

Standardowo instaluje się tylko komponent "PostgreSQL Database Server". W celu doinstalowania komponentów "pgDevOps" i "pgAdmin3" zaznacz przy nich checkboxy.

PostgreSQL instalacja krok po kroku.

Potwierdź swój wybór przyciskiem "next".

Krok 4. Ustawiamy hasło dla superusera.

 

PostgreSQL instalacja krok po kroku.

Opcjonalnie możemy ustawić lokalizację dla plików PostreSQL i port. W tym celu zaznaczamy checkbox przy opcji "Advenced PostreSQL Configuration Options".

Krok 5. Ustawiamy nazwę i hasło dla administratora.

PostgreSQL instalacja krok po kroku.

potwierdzamy przyciskiem "next"

Krok 6. Okno podsumowania.

PostgreSQL instalacja krok po kroku.

tutaj prezentowane są wszystkie opcje które wybraliśmy. Potwierdzamy przyciskiem "next".

Krok 7. Rozpoczyna się instalacja.

PostgreSQL instalacja krok po kroku.

Krok 8. Następuje automatyczne sprawdzanie aktualizacji.

PostgreSQL instalacja krok po kroku.

Krok 9. Automatyczna instalacja dodatkowych komponentów (jeśli wcześniej wybrane)

PostgreSQL instalacja krok po kroku.

Krok 10. Informacja o zakończeniu instalacji PostgreSQL.

PostgreSQL instalacja krok po kroku.

gotowe 🙂


postgresqlpostgresqlpostgresql

PostgreSQL download. Skąd ściągnąć krok po kroku.

PostgreSQL download. Skąd ściągnąć krok po kroku.

Krok 1. Wchodzimy na stronę PostgreSQL.org

Wchodzimy na stronę www.postgresql.org i klikamy w menu na opcję "download".

PostgreSQL download. Skąd ściągnąć krok po kroku.

Krok 2. Wybieramy instalator dla systemu Windows

PostgreSQL download. Skąd ściągnąć krok po kroku.

Krok 3. Wybieramy graficzny instaler.

PostgreSQL download. Skąd ściągnąć krok po kroku.

Krok 4. Ściągamy plik instalatora.

PostgreSQL download. Skąd ściągnąć krok po kroku.

Krok 5. Potwierdzamy chęć ściągnięcia pliku instalatora.

PostgreSQL download. Skąd ściągnąć krok po kroku.

Krok 6. Wybieramy lokalizację gdzie chcemy zapisac plik instalatora.

PostgreSQL download. Skąd ściągnąć krok po kroku.

Gotowe 🙂 w następnym wpisie przejdziemy do instalacji PostgreSQL


postgresqlpostgresql

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


 

Tworzymy swoją testową bazę danych (1000000 rekordów) [SQL Server].

Na "małych" bazach danych wszystkie zapytania trwają "chwilkę" 🙂 Co zrobić jeżeli chcielibyśmy sprawdzić nasze zapytanie na "większej" bazie danych np. 1000000 rekordów. Możemy ją sobie sami stworzyć. W tym wpisie, krok po kroku, opiszę jak w łatwy sposób, za pomocą pętli WHILE, stworzyć przykładową bazę danych, o tak dużej liczbie rekordów. Ja stworzę bazę z przykładową jedną tabelą ale Wy możecie stworzyć sobie bazę jaką chcecie z większą ilością tabel i innymi kolumnami itd.

Moja baza (o nazwie "tmp") będzie się składała z jednej tabeli (o nazwie "MyTab") z zamówieniami, która będzie miała następujące kolumny:

  • ID – identyfikator rekordu
  • OrderDate – data zamówienia
  • OrderNumber – numer zamówienia
  • PositionsNumber – liczba pozycji w danym zamówieniu

Przyjmiemy sobie jakieś dodatkowe założenia

  • Pole identyfikator będzie zawierało kolejne liczby całkowite
  • Pole OrderDate będzie zawierało datę zamówienia ale z przedziału od 1900-01-01 do 2017-12-31 i będziemy je nadawać od daty początkowej, zwiększając w każdym rekordzie o jeden dzień. Jak dojdziemy do daty końcowej to wracamy do daty początkowej.
  • OrderNumber – numer zamówienia będzie złączeniem kolejnego numeru rozpoczynającego się od 1 przełamany na rok np. 1/1900, czyli zamówienie nr 1 z 1900 r.
  • PositionsNumber – liczba pozycji w danym zamówieniu także będzie liczbą całkowita z przedziału od 1 do 20. Rozpoczynamy od 1 i dodajemy, w każdym rekordzie, 1 aż do 20 i wtedy powracamy do wartości 1 i tak w kółko.

Krok 1. Tworzymy strukturę bazy "tmp", czyli tak naprawdę tabeli "MyTab"

CREATE DATABASE tmp
GO
USE tmp
GO
CREATE TABLE MyTab(
     ID bigint
    ,OrderDate datetime
    ,OrderNumber varchar(20)
    ,PositionsNumber int
)

Jeżeli ze stworzeniem takiej struktury masz jakieś problemy zajrzyj na stronkę z poleceniami i funkcjami SQL i poczytaj nt. wykorzystanych poleceń.

Krok 2. Deklarujemy zmienne początkowe.

Jeszcze przed zastosowaniem pętli WHILE musimy zadeklarować zmienne które zainicjalizujemy nadając im początkowe wartości.

DECLARE @id bigint = 1;
DECLARE @OrderDate Datetime = '1900-01-01 00:00:00';
DECLARE @OrderInt int = 1;
DECLARE @PositionsNumber bigint = 1;
DECLARE @OrderNumber varchar(10) = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

 Krok 3. Pętla WHILE i dodawanie nowych rekordów.

Dodajemy kawałek kodu dotyczącego pętli WHILE z instrukcją INSERT INTO, "wrzucającą" rekordy do tabeli "MyTab"

WHILE @id <= 1000000
    BEGIN
        INSERT INTO MyTab VALUES (@id , @OrderDate, @OrderNumber , @PositionsNumber)

   END

Krok 4. Inkrementacja wartości pola ID

Tutaj sprawa jest prosta, wartości pola ID mają rosnąć od 1 do 1000000. Na początku jeszcze przed pętlą zadeklarowaliśmy zmienną @id i nadaliśmy jej wartość 1

DECLARE @id bigint = 1;

Teraz musimy za każdym, "obrotem" pętli WHILE, zwiększać wartość tej zmiennej o 1. Odpowiedni kod musimy wrzucić wewnątrz pętli.

SET @id += 1

Krok 5. Inkrementacja wartości pola OrderDate.

Tutaj sprawa jest nieco trudniejsza. W naszych założeniach zapisaliśmy, że

Pole OrderDate będzie zawierało datę zamówienia ale z przedziału od 1900-01-01 do 2017-12-31 i będziemy je nadawać od daty początkowej, zwiększając w każdym rekordzie o jeden dzień. Jak dojdziemy do daty końcowej to wracamy do daty początkowej.

kod oczywiście wrzucamy wewnątrz pętli WHILE

        IF (@OrderDate < '2017-12-31')
            BEGIN
                SET @OrderDate = dateadd(day,1,@OrderDate)
            END
        ELSE
            BEGIN
                SET @OrderDate = '1900-01-01'
            END

Skorzystałem tutaj z instrukcji warunkowej IF.  W warunku sprawdzam czy wartość zmiennej @OrderDate jest mniejsza niż 2017-12-31 IF (@OrderDate < '2017-12-31'). Jeżeli tak to zwiększam wartość tej zmiennej o jedej dzień SET @OrderDate = dateadd(day,1,@OrderDate) za pomocą funkcji dateadd(). Jeżeli wartość tej zmiennej będzie już równa 2017-12-31 (warunek nie będzie spełniony) to wtedy ustawiam wartość tej zmiennej na datę 1900-01-01 (SET @OrderDate = '1900-01-01').

Krok 6. Inkrementacja wartości pola OrderNumber.

Przypominam założenia dotyczące pola OrderNumber

OrderNumber – numer zamówienia będzie złączeniem kolejnego numeru rozpoczynającego się od 1 przełamany na rok np. 1/1900, czyli zamówienie nr 1 z 1900 r.

Żeby stworzyć taką strukturę numeru zamówienia posłużyłem się zmienną pomocniczą  @OrderInt którą będę zwiększał od 1 do … no właśnie skąd będę wiedział, ze skończył się już rok ?
Żeby wiedzieć kiedy "kończy" się rok i żeby zmienić numer z 365 (bo tyle jest dni w roku, no chyba że będzie to rok przestępny) znowu na 1, zastosowałem poniższy kod

IF (YEAR(@OrderDate) = YEAR(dateadd(day,-1,@OrderDate)))
            BEGIN
                SET @OrderInt += 1
            END
        ELSE
            BEGIN
                SET @OrderInt = 1
            END

Znowu zastosowałem IF-a. W warunku sprawdzam czy rok z aktualnej daty jest równy rokowi z aktualnej daty + 1 dzień, innymi słowy czy kolejny dzień jest jeszcze z tego samego roku czy już jest kolejny rok. Jeżeli jest ten sam rok to tylko zwiększamy o 1 wartość zmiennej @OrderInt a jeśli warunek nie będzie spełniony to ustawiamy wartość zmiennej @OrderDate znowu na 1. Do "wyciągnięcia" roku z daty stosuję funkcję YEAR()
OK. Ale narazie załatwiliśmy sprawę numeru zamówienia a jeszcze musi on być przełamany przez rok. W tym celu korzystam z poniższego kodu

SET @OrderNumber = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

Zwródźcie uwagę, że zmienna @OrderNumber ma być typu VARCHAR a zmienna @OrderInt jest typu INT, dlatego muszę tu skorzystać z dwóch funkcji YEAR(), ta funkcja posłuży mi do "wyciągnięcia" roku z daty oraz CAST(), ta funkcja posłuży mi do zamiany obu części numeru (samego numeru i roku) na typ VARCHAR.

Krok 7. Inkrementacja ilości pozycji w każdym zamówieniu.

Do tego celu także wykorzystam instrukcję IF.

IF (@PositionsNumber < 20)
            BEGIN
                SET @PositionsNumber += 1
            END
        ELSE
            BEGIN
                SET @PositionsNumber = 1
            END

założyliśmy że

PositionsNumber – liczba pozycji w danym zamówieniu także będzie liczbą całkowita z przedziału od 1 do 20. Rozpoczynamy od 1 i dodajemy, w każdym rekordzie, 1 aż do 20 i wtedy powracamy do wartości 1 i tak w kółko.

W warunku sprawdzam więc czy zmienna @PositionsNumber jest mniejsza od 20. Jeżeli tak to zwiększamy tylko jej wartość o 1, a jeśli już dojdzie do wartości 20 to ustawiamy jej wartość z powrotem na 1.

Takim oto sposobem skończyliśmy nasz skrypt którym utworzymy sktukturę bazy "tmp" (w naszym przypadku to tylko utworzenie tabeli "MyTab") a później "wrzucimy" wartości do tabeli "MyTab". Cały kod naszego skryptu poniżej.

CREATE DATABASE tmp
GO
USE tmp
GO
CREATE TABLE MyTab(
     ID bigint
    ,OrderDate datetime
    ,OrderNumber varchar(20)
    ,PositionsNumber int
)

DECLARE @id bigint = 1;
DECLARE @OrderDate Datetime = '1900-01-01 00:00:00';
DECLARE @OrderInt int = 1;
DECLARE @PositionsNumber bigint = 1;
DECLARE @OrderNumber varchar(10) = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

WHILE @id <= 1000000
    BEGIN
        INSERT INTO MyTab VALUES (@id , @OrderDate, @OrderNumber , @PositionsNumber)

SET @id += 1

       IF (@OrderDate < '2017-12-31')
            BEGIN
                SET @OrderDate = dateadd(day,1,@OrderDate)
            END
        ELSE
            BEGIN
                SET @OrderDate = '1900-01-01'
            END

IF (YEAR(@OrderDate) = YEAR(dateadd(day,-1,@OrderDate)))
            BEGIN
                SET @OrderInt += 1
            END
        ELSE
            BEGIN
                SET @OrderInt = 1
            END

SET @OrderNumber = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

IF (@PositionsNumber < 20)
            BEGIN
                SET @PositionsNumber += 1
            END
        ELSE
            BEGIN
                SET @PositionsNumber = 1
            END

   END

Po uruchomieniu skryptu (F5) ja przy swoim sprzęcie spędziłem aż 13 minut żeby skrypt sie wykonał (na kompie ASUS X53TA, 6GB RAMu, SSD). Zrzut poniżej.

t-sql, sql, temporary database

Teraz uruchomimy proste zapytanie które wyświetli nam wszystkie rekordy z naszej tabeli "MyTab".

SELECT
   *
FROM
   MyTab

Wynik zapytania poniżej

t-sql, sql, temporary database

Widzimy, że wszystkie rekordy z tabeli wyświetlił w 16 s i jest ich 1 MLN tak jak chcieliśmy. Jeszcze tylko sprawdźmy, czy na przełomie lat 2017-12-31 i 1990-01-01 mamy prawidłową numerację, czyli znowu zaczynamy numerację zamówień od 1.

t-sql, sql, temporary database

OK. Jest w porządku 🙂 To oczywiście pewnie jedna z możliwości utworzenia takiej bazy. Na dzień tworzenia wpisu nie znam innej metody utworzenia takiej bazy, jak poznam to się z Wami podzielę 🙂

Teraz wykorzystując wiedzę przestawioną po niniejszym artykule możecie sobie stworzyć swoją testową bazę jakomkolwiek chcecie.


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

Adventureworks 40. Wyświetl zamówienia (najmniejsza i największa wartość + ich suma)

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 40

Treść:  Wyświetl zamówienia (najmniejsza i największa wartość). Użyj tabeli tymczasowej.

Polecenia/funkcje w zapytaniu: SELECT, FROM, CREATE TABLE, INSERT INTO, UNION ALL, SUM(), Tabela tymczasowa


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

  

— Tworzymy tabelę tymczasową do zapisu wyników poszczególnyc zapytań
CREATE TABLE #tymczasowa(
     info varchar(40)
    ,wartosc money
)

— Znajdujemy zamówienie o najmniejsze wartości
— i wrzucamy wynik do tabeli tymczasowej

INSERT INTO #tymczasowa
        SELECT
             'Zamówienie o najmniejszej wartości'
            ,MIN(O.SubTotal)
        FROM
            Sales.SalesOrderHeader O    

— Znajdujemy zamówienie o największej wartości
— i wrzucamy wynik do tabeli tymczasowej

INSERT INTO #tymczasowa
        SELECT
             'Zamówienie o największej wartości'
            ,MAX(O.SubTotal)
        FROM
            Sales.SalesOrderHeader O

— prezentujemy wynik
SELECT
    *
FROM
    #tymczasowa    

UNION ALL                

SELECT
     'SUMA'
    ,SUM(#tymczasowa.wartosc)
FROM
    #tymczasowa    

— opcjonalnie możemy usunąć tablę tymczasową    
DROP TABLE #tymczasowa   

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER


 

Adventureworks 39. Pracownicy pogrupowani wg dat zatrudnienia.

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 39

Treść:  Pracownicy pogrupowani wg dat zatrudnienia.

Polecenia/funkcje w zapytaniu: SELECT, FROM, ORDER BY, GROUP BY, YEAR(), COUNT()


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

    SELECT
     SQ.GRUPA AS Grupa
    ,COUNT(*) AS LiczbaPracowników
FROM
    (    
        SELECT
             YEAR(E.HireDate) AS Rok
            ,(CASE
                WHEN YEAR(E.HireDate) < 2000
                    THEN '1. Zatrudnieni przed 2000 r.'
                WHEN (YEAR(E.HireDate) >= 2000 AND YEAR(E.HireDate) <= 2005)
                    THEN '2. Zatrudnieni pomiędzy 2000-2005 r.'
                WHEN YEAR(E.HireDate) > 2005
                    THEN '3. Zatrudnieni po 2005 r.'
            END)AS GRUPA                
        FROM
            HumanResources.Employee E            
    )AS SQ
GROUP BY
    SQ.GRUPA    
ORDER BY
    1

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

Adventureworks 38. Zaprezentuj zestawienie sprzedaży w latach (liczba zamówień, wartość zamówień).

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 38

Treść:  Zaprezentuj zestawienie sprzedaży w latach (liczba zamówień, wartość zamówień).

Polecenia/funkcje w zapytaniu: SELECT, FROM, ORDER BY, GROUP BY, YEAR(), COUNT(), SUM()


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


Rozwiązanie:

Zapytanie:

    SELECT
     YEAR(O.OrderDate) AS Rok
    ,COUNT(*) AS LiczbaZamowien
    ,SUM(O.SubTotal) AS WartoscZamowien
FROM    
    Sales.SalesOrderHeader O    
GROUP BY
    YEAR(O.OrderDate)
ORDER BY
    1,2 DESC,3 DESC   

Wynik:

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Pobierz skrypt sql


SQL tutorial. AdventureWorks exercises SQL SERVER  SQL tutorial. AdventureWorks exercises SQL SERVER SQL tutorial. AdventureWorks exercises SQL SERVER


 

Optymalizacja zapytań SQL, dobre praktyki cz.1

Co ma wpływa na długość wykonywania się zapytań.

Tym wpisem rozpoczynam serię (mam nadzieję 🙂 ) artykułów dotyczących czynników wpływających na czas wykonywania zapytań SQL oraz techniki lub mechanizmy ich optymalizacji. W tym wpisie chciałbym skupić się na podstawach, czyli jakie czynniki wpływają na czas wykonywania się zapytań SQL.

Struktura bazy danych

To jak jest zbudowana baza danych ma znaczenie podczas tworzenia zapytań do niej. Jeżeli dane które chcemy rejestrować w bazie danych rozbijemy na "tysiąc kawałków/tabel" tym częściej będziemy musieli później korzystać z JOIN-ów a tym samym wpłynie to na czas wykonywania zapytania. Z drugiej jednak strony jeżeli istnieją dane które można "zesłownikować" to należy to zrobić. Przy tworzeniu struktury na pewno musisz zwrócić szczególną uwagę na kilka rzeczy:
– jeżeli w danej tabeli są dane które można "zesłownikować" zrób to
– dokładnie przemyśl typy relacji pomiędzy tabelami
– dobrze skonfiguruj klucze główne i klucze obce
– rozrysuj sobie strukturę na kartce i postaraj się przemyśleć wszystkie możliwe zapytania – oczywiście nikt nie mówi o ślęczeniu godzinami i wymyślaniu wszystkich, nawet teoretycznych zapytań, nie jesteś też w stanie od razu wymyślić wszystkich możliwych zapytań które przyjdzie Ci zrobić ale z reguły wiesz o jakie statystyki szef prosi najczęściej i pod tym kątem przeanalizuj Twoją strukturę np. masz bazę sklepu to wiadomo, że prędzej czy później ktoś będzie chciał zrobić analizę sprzedaży np. najczęściej kupowane produkty, jak kształtowałą się sprzedaż w ogóle (trendy), targetowanie klientów itp.
Pamiętaj, że liczba tabel których później będziemy używać w zapytaniu także ma znaczenie.

Wielkość bazy danych (liczba rekordów)

Tutaj sprawa jest prosta. Wiadomo, że z biegiem czasu danych w bazie przybywa i im więcej danych będziesz przechowywał tym zapytania będą działały wolniej. O ile przy "małych" bazach nie musisz się zbytnio martwić bo zapytania będą działały całkiem sprawnie to w "dużych" bazach należy już wziąć ten czynnik pod uwagę. Widzę tutaj dwie płaszczyzny do przemyśleń. Po pierwsze musisz się zastanowić jak poprawnie ustawić klucze główne i klucze obce oraz poprawne indeksowanie. Drugim czynnikiem jest sprzęt. Przy tworzeniu bazy musisz mniej więcej wziąć po uwagę przyrost danych w ciągu jakiejś jednostki czasu np. roku i dobrać do tego maszynę na której będzie stała baza. Przykład: tworzysz bazę dla sklepu. Inaczej podejdziesz do osiedlowego sklepu wędkarskiego a inaczej do dyskontu spożywczego. Wielkości baz po któtkim czasie będą różniły się diametralnie (inną sprawą jest liczba użytkowników ale o tym później).

Ilość użytkowników

Temat ten potraktuję (w tym wpisie) bardzo ogólnei i tylko chcę zasygnalizować, że coś takiego jest. Temat jest szeroki i przeplata się tutaj wiele wątków np. współbieżność, przepustowość itp.
Logicznie rzecz biorą im więcej użytkowników tym większe problemy z dostępem do zasobów bazy. W momencie kiedy jednej użytkownik korzysta z jakiegoś zasobu i pojawia się drugi użytkownik mogą wystąpić różnego rodzaju blokad które w efekcie mogą doprowadzić do utraty spójności danych.


Poziomy dostępu do danych

Wiadomo, że użytkownicy jeżeli mogą to w każdej sytuacji pobierali by jak najwięcej danych, czy potrzebują czy nie, nie patrząc na to czy rzeczywiście ich potrzebują. Rozwiązaniem sytuacji możę być pogrupowanie użytkowników w grupy i dla każdej z nich stworzyć odpowiedni raport.
Ograniczenia czasowe
To, że Pania Kasia potrzebuje danych sprzedażowych to nie znaczy, że musi je pobierać od 1999 r. może wystarczą jej dane za ostatni rok lub nawet kwartał. W raportach z parametrami które podaje użytkownik także można zastosować metodę podawania np. domyślnych dat. Jak Pani Kasia ma wpisać datę sama to wpisze od 2000 r. a jak domyślnie pojawi się jej data na rok wstecz to nie będzie się zastanawiała i kliknie z taką datą jaka jest domyślnie. Z doświadczenia wiem, że różnica między zrobieniem zapytania za ostatnie 5 lat i za ostatni rok może być ogromna (oczywiście mówimy o bardzo dużych bazach danych).
Właściwość miejscowa
To, że Pania Kasia potrzebuje danych sprzedażowych to nie znaczy, że musi je pobierać za wszystkie sklepy z całego kraju. Może potrzebne jej są tylko dane z województwa Małopolskiego. Stworzenie odpowiednich parametrów w raportach ograniczających dane do jakiejś mniejszej struktury organizacyjnej jest bardzo dobrym pomyśłem.

Filtrowanie danych

Przy tworzeniu zapytań dużo uwagi poświęć na analizę warunków w WHERE i HAVING. Staraj się odrzucić tutaj jak największy zbiór danych. Inną techniką (którą czasami stosuję) jest wybranie interesujących mnie identyfikatorów z interesującej mnie tabeli a później dowiązywanie do nich reszty danych. Z doświadczenia wiem, że czasami ta technika się sprawdza, oczywiście zawsze musisz sprawdzić co w Twoim przypadku będzie lepszym rozwiązaniem.

Zakres zwracanych danych

Ten temat już troszeczkę omówiłem w punkcie "Poziomy dostępu do danych". Jest jednak jeszcze jedna kwestia którą chciałem zasygnalizować. Niektórzy projektanci zapytań mają manię korzystania ze struktury SELECT * FROM …itd. W takim przypadku wiadomo, że zwrócimy wszystkie kolumny z danej tabeli lub tabel (jeżeli korzystamy ze złączeń). Nie zawsze jest to konieczne. Widziałem już takie zapytanie które zwracały po 15 a nawet 20 kolumn, ale jak drążyłem temat i pytałem użytkowników (korzystających z raportów) czego tak naprawdę potrzebują to okazywało się, że tak naprawdę oni wykorzystują dane tylko z 5 kolumn. Sami widzicie, ile treści jest czasami generowane niepotrzebnie.

Podsumowując
Opisane powyżej pojęcia nie wyczerpują oczywiście tematu, są jedynie pokazaniem (moim zdaniem) podstawowych problemów z którymi zetknie się każdy kto pisze zapytania SQL. Wpis ma tylko za zadanie abyś spojrzał na tworzoenie zapytań SQL z innej strony (jeżeli nigdy się nad tym nie zastanawiałeś) i przy ich tworzeniu analizował także inne sprawy niż tylko żeby zwróciło poprawny wynik. Jako programista masz świadomość, że baza musi zwrócić wynik jak najszybciej się da ale pamiętaj również, że czas otrzymania wyniku jest także uzależniony od ilości zwracanych danych.

Już zapraszam na dalsze części serii o optymalizacji zapytań i dobrych praktykach przy ich pisaniu. 🙂


   


 

SQL SELECT INTO

W tym wpisie opiszę Wam zastosowanie składni SELECT INTO, która służy do kopiwania danych (kolumn) z jednej tabeli do drugiej tabeli. Bez zbędnych wstępów poniżej prezentuję Wam składnię takiej konstrukcji. Dobrą rzeczą jest to, że nie musimy mieć utworzonej tabeli_docelowej, zostanie ona utworzona automatycznie w chwili uruchomienia zapytania.


    


Składnia SELECT INTO

SELECT
    * — * oznacza wszystkie kolumny
INTO
    nowa_tabela
FROM
    tabela_źródłowa
— opcjonalnie warunek po WHERE
WHERE
    warunek(-ki)


Przykład zastosowania SELECT INTO (baza: Adventureworks2008R2)

No to przechodzimy do przykładu i na warsztat bieżemy bazę AdventureWorks2008R2.

Będziemy kopiowali dane z tabeli "Person.Person" (osoby o typie EM czyli Employees/Pracownicy) do tabeli "Person_tmp". W tym celu zbudujemy zapytanie.

SELECT
    *
INTO
    Person_tmp
FROM
    Person.Person
WHERE
    PersonType = 'EM'

Uruchamiamy zapytanie (F5) i powinniśmy otrzymać informację, że wszystko wykonało się poprawnie.

SQL SELECT INTO1

Widzimy, że konsturkcja wykonała się poprawnie i w tym momencie powinniśmy mieć nową tabelę o nazwie "Person_tmp" (najprawdopodobniej żeby ją zobaczyć musisz odświeżyć, strukturę bazy AdventureWorks2008R2 w drzewie baz danych, w tym celu klikasz prawym przyciskiem myszy na bazie AdventureWorks2008R2 i z menu wybierasz opcję Refresh) w której powinno znajdować się 273 rekordów które zostały do niej skopiowane.

Sprawdzamy to zapytaniem

SELECT
    *
FROM
    Person_tmp

Wynik


SQL SELECT INTO

Wszystko się zgadza w tabeli "Person_tmp" znajduje się 273 rekordy.