Miesięczne archiwum: maj 2017

baza HOTELS cz.5 tabele „parking” i tabela pomocnicza „hotel_parking” [SQL SERVER]

baza HOTELS cz.5 tabele "parking" i tabela pomocnicza "hotel_parking" [SQL SERVER]

Witam, doszliśmy do piątej części cyklu. Zajmiemy się teraz dodaniem tabeli "parking", w której będziemy przechowywać dane nt. miejsc parkingowych w danym hotelu.

Do utworzenia tabeli "parking" będziemy potrzebowali takich pól jak:

  • unikalny identyfikator w tabeli – parking_id
  • identyfikator hotelu w którym jest miejsce parkingowe – parking_hotel_id
  • numer miejsca parkingowego – parking_number

    
Tworzymy tabelę "parking" w Management Studio (SQL SERVER):
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 "parking".

gotowe 🙂

Poniżej gotowy skrypt do uruchomienia w Management Studio do utworzenia tabeli "parking".

    CREATE TABLE parking(
         parking_id int primary key not null
        ,parking_hotel_id int not null
        ,parking_number int not null
    )
     

Tabela "parking" jest nam potrzebna do połączenia z tabelą "reservations". Poniżej kod który połączy nam obie tabele polami reservations.reservation_parking_id -> parking.parking.id

ALTER TABLE reservations
ADD CONSTRAINT fk_reservations_parking FOREIGN KEY (reservation_parking_id) REFERENCES parking(parking_id)

Po połączeniu nasz schemat bazy wygląda tak.

sql database hotels baza danych hotele tutorial sql server


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

 

baza HOTELS cz.4 tabele kraje „countries” i miasta „cities” [SQL SERVER]

baza HOTELS cz.4 tabele kraje "countries" i miasta "cities" [SQL SERVER]

W czwartek części cyklu zajmiemy się dodaniem tabel: countries (kraje) i cities (miasta). W utworzonych już wcześniej tabelach mamy pola które muszą być kluczami obcymi właśnie do tych nowo utworzonych tabel. Nie będę tłumaczył jakie dane będziemy tam przechowywać bo nazwy tabel mówią same za siebie.

Zaczniemy od tabeli "countries". Będziemy potrzebowali takich pól jak:

  • unikalny identyfikator kraju – country_id
  • nazwa kraju – country_name
  • kod kraju – country_code

    
Tworzymy tabelę w Management Studio (SQL SERVER):
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 "countries".

gotowe 🙂

Poniżej gotowy skrypt do uruchomienia w Management Studio do utworzenia powyższej tabeli.

    USE HOTELS
    GO

    CREATE TABLE countries(
         country_id int primary key not null
        ,country_name varchar(50) not null
        ,country_code varchar(2) not null
    )

Tabela "countries" jest nam potrzebna do połączenia z tabelami "hotels" i "clients". Dlaczego? Bo każdy hotel musi znajdować się w jakimś kraju i każdy klient gdzieś mieszka (w jakimś kraju), ale nie chcemy przy każdym z tych obiektów trzymać pełnej nazwy kraju tylko będziemy przechowywali wskaźnik na wartość w tabeli "countries".

Do dzieła. Tworzymy połączenie między "hotels" i "countries". W tabeli "hotels" znajduje się pole "hotel_country_id" które musi wskazywać na pole "country_id" w tabeli "countries".

    ALTER TABLE hotels
    ADD CONSTRAINT fk_hotels_countries FOREIGN KEY (hotel_country_id) REFERENCES countries(country_id)

Teraz zajmijmy się połączeniem tabel "clients" i "countries". W tabeli "clients" znajduje się pole "client_country_id" które musi wskazywać na pole "country_id" w tabeli "countries".

    ALTER TABLE clients
    ADD CONSTRAINT fk_clients_countries FOREIGN KEY (client_country_id) REFERENCES countries(country_id)

Teraz utworzymy tabelę "cities". Będziemy potrzebowali takich pól jak:

  • unikalny identyfikator miasta – city_id
  • nazwa miasta – city_name
  • kod pocztowy miasta – city_code

    
Tworzymy tabelę w Management Studio (SQL SERVER):
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 "cities".

gotowe 🙂

Poniżej gotowy skrypt do uruchomienia w Management Studio do utworzenia powyższej tabeli.

    USE HOTELS
    GO

    CREATE TABLE cities(
         city_id int primary key not null
        ,city_name varchar(50) not null
        ,city_code varchar(20) not null
    )

Tabela "cities" jest nam także potrzebna do połączenia z tabelami "hotels" i "clients". Z takiego samego powodu jak tabela "countries", każdy hotel musi znajdować się w jakimś mieście i każdy klient musi mieszkać w jakimś mieście.

Zaczynamy. Tworzymy połączenie między "hotels" i "countries". W tabeli "hotels" znajduje się pole "hotel_city_id" które musi wskazywać na pole "city_id" w tabeli "cities".

    ALTER TABLE hotels
    ADD CONSTRAINT fk_hotels_cities FOREIGN KEY (hotel_city_id) REFERENCES cities(city_id)

Teraz zajmijmy się połączeniem tabel "clients" i "cities". W tabeli "clients" znajduje się pole "client_city_id" które musi wskazywać na pole "city_id" w tabeli "cities".

    ALTER TABLE clients
    ADD CONSTRAINT fk_clients_cities FOREIGN KEY (client_city_id) REFERENCES cities(city_id)

Po tych wszystkich działaniach nasz schemat wygląda jak poniżej.

sql database hotels baza danych hotele tutorial sql server


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

T-SQL ISNULL function

Jak już wiemy IS NULL to wartość nieokreślona i możemy "za jej pomocą" np. filtrować rekordy w zapytaniu (czytaj tutaj). Ale istnieje także funkcja ISNULL(). Funkcja ta zawiera dwa parametry ISNULL(sprawdzane_wyrażenie , wartość_do_zamiany). Funkcja ta na początku sprawdza czy w pierwszym parametr przyjmuje wartość NULL, jeśli tak to podmienia jego wartość na wartość drugiego parametru. Zobaczmy to na przykładzie.

Do tego przykłądu stworzyłem bazę "test" w której stworzyłem tabelę "table_test", a w niej trzy kolumny: kolA, kolB, kolC.
kolumna kolA – przechowuje kolory
kolumna kolB – przechowuje liczby
kolumna kolC – przechowuje liczebniki główne

Wyświetlmy teraz tabelę "table_test" zwykłym SELECT-em.

SELECT
     kolA
    ,kolB
    ,kolC
FROM
    table_test

Tabela po wyświetleniu wygląda tak:

sql t-sql commands isnull function

Zwróc uwagę, że w kolumnie "kolA" jest jedna wartość NULL i w kolumnie "kolB" jest jedna wartość NULL.
Jak zrobić, żeby zastąpić te wartości właściwymi wartościami np. w kolumnie która przechowuje kolory (kolA) wstawimy wartość 'brak koloru' a w kolumnie która przechowuje liczby (kolB) wstawimy wartość "0". Zaprogramujemy także, że jeżeli w kolumnie "kolC" znalazłaby się wartość NULL to zamienimy ją na "brak".

Do wykonania tego zadania użyjemy funkcji ISNULL i nasze zapytanie przybieże postać.

SELECT
     ISNULL(kolA , 'brak koloru') AS kolA
    ,ISNULL(kolB , 0) AS kolB
    ,ISNULL(kolC , 'brak') AS kolC
FROM
    table_test

Poniżej wynik uruchomienia zapytania.

sql t-sql commands isnull function

Jak widzimy na zrzucie wartości NULL w kolumnach "kolA" i "kolB" zostały zamienione na wskazane. W kolumnie "kolC" nie ma żadnych zmian bo nie było żadnej wartości NULL którą należało zamienić.


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

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 


 

baza HOTELS cz.3 tabela klienci „clients” [SQL SERVER]

baza HOTELS cz.3 tabela klienci "clients"

W trzeciej części cyklu zajmiemy się tabelą klienci "clients",w której będziemy przechowywać dane osób, które złożyły rezerwacje ale także te które mieszkają w hotelu (bo zwróćmy uwagę, że nie każda osoba która przyjedzie do hotelu będzie osobą która złożyła rezerwację).

Do opisu klienta (osoby) na pewno będziemy potrzebowali takich pól jak:

  • unikalny identyfikator klienta – client_id
  • na pewno imię – client_first_name
  • no i nazwisko – client_last_name
  • będziemy chcieli znać takżę narodowość – client_country_id (FK)
    dane nt. państw będziemy przechowywać w osobnej tabeli
  • miasto – client_city_id (FK)
    dane nt. miast będziemy przechowywać w osobnej tabeli
  • adres – client_address
  • będziemy chcieli także przechowywać informację ile razy dany klient skorzystał z naszych usług – client_counter
    np. w celu specjalnych rabatów, zniżek
  • w hotelach zawsze podajemy jakiś dokument i jego numer – client_document_number
    tutaj także są dwie drogi: pierwsza łatwiejsza gdzie w tabeli klientów (clients) przechowujemy w polu tekstowym informację nt. typu dowodu tożsamości i jego numer i druga gdzie rozbijamy informację o dowodzie tożsamości na dwie części: pierwsza część to typu dokumentu tożsamości gdzie identyfikator przechowujemy w tabeli klienci (clients) a typ dokumentu przechowujemy w osobnej tabeli a druga część to numer i seria dowodu tożsamości. Informacje te przechowujemy w tabeli klientów (clients). My w naszym przykładzie skorzystamy z pierszej drogi, czyli będziemy przechowywać dane nt. typu, serii i numeru dokumetu tożsaości w jednym polu tekstowym w tabeli klienci (clients).

Analogicznie jak poprzednich częściach stworzymy tabelę clients w management studio.

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 "clients".

gotowe 🙂

lub korzystamy ze skryptu

USE HOTELS
GO

CREATE TABLE clients(
     client_id int primary key not null
    ,client_first_name varchar(20) not null
    ,client_last_name varchar(40) not null
    ,client_country_id int not null
    ,client_city_id int not null
    ,client_address varchar(50) not null
    ,client_counter int    not null
    ,client_document_number varchar(40) not null)

Naszą tabelę klienci (clients) łączymy z tabelą rezerwacje (reservations) polami: reservations.reservation_client_id i clients.client_id. Możemy to zrobić za pomoca skryptu.

USE HOTELS
GO

ALTER TABLE reservations
ADD CONSTRAINT fk_reservations_clients FOREIGN KEY (reservation_client_id) REFERENCES clients(client_id)

Aktualnie nasz schemat przybieże postać.

sql database hotels baza danych hotele tutorial sql server


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

baza HOTELS cz.2 tabela rezerwacje „reservations” [SQL SERVER]

baza HOTELS cz.2 tabela rezerwacje "reservations"

Witam w drugiej części cyklu, w której przejdziemy do utworzenia tabeli rezerwacje "reservations". Jak sama nazwa wskazuje będziemy tam "trzymać" rezerwacje naszych klientów.

Zastanówmy się czego potrzebujemy do opisania rezerwacji. Na pewno będziemy potrzebowali takich pól jak:

  • na pewno nasza rezerwacja będzie musiała mieć unikalny numer rezerwacji po której będziemy ja później wyszukiwali, czyli – reservation_id
  • tą rezerwację złożył jakiś konkretny klient, czyli – reservation_client_id (FK)
    Tutaj od razu wyjaśnienie. W tabeli reservations nie będziemy przechowywali danych klientów, bo te dane będziemy przechowywali w kolejnej tabeli clients, którą stworzymy później. W obecnej tabeli bęziemy tylko przechowywali identyfikator klienta z tabeli clients. Analogicznie jak przy tabelach hotels -> rooms tutaj także pole reservation_client_id będzie kluczem obcym.
  • każda rezerwacje musi mieć jakąś datę początkową, czyli – reservation_starting_date
  • i datę końcową – reservation_final_date
  • musimy także wiedzieć do którego pokoju odnosi się rezerwacja, czyli – reservation_room_id (FK)
    No i natrafiamy na pierwszy poważny dylemat. Jeżeli jesteśmy pewni, że do jednej rezerwacji będzie mógł być "przypięty" tylko jeden pokój to taka konstrukcja jest jak najbardzej poprawne. Ale jeżeli dopuszczamy sytuację, że w ramach jednej rezerwacji możemy zamówić więcej niż jeden pokój to musimy to rozwiązać inaczej. W naszej bazie przyjmiemy założenie "jedna rezerwacja, jeden pokój". Dla wyjaśnienia jeżeli poszlibyśmy drugą ścieżką czyli "jedna rezerwacja, wiele pokoi" to musielibyśmy utworzyć dodatkową tabelę pośrednią i zapisywać w niej (w osobnych rekordach) numer rezerwacji (tab. reservations -> reservation_id) i numer pokoju (tab. rooms -> room_id). Wtedy też w tabeli reservations nie potrzebujemy pola reservations_room_id.
  • w dzisiejszych czasach prawie każdy na wakacje jeździ autem i oczekuje, że gdzieś zaparkuje. Dołożymy więc pole przechowujące identyfikator (numer) miejsca parkingowego – reservation_parking_id (FK)
    To pole będzie wskazywało na konkretny rekord w tabeli parking (lub w tabeli pomocniczej to się jeszcze okaże w miarę rozwoju projektu) którą dopiero stworzymy w dalszej części cyklu. Z uwagi na to, że nie każdy przyjeżdża samochodem pozostawimy tutaj możliwość wystąpienia wartości NULL.
  • dla celów statystycznych dodajmy sobie jeszcze pole mówiące nam o tym, czy dana rezerwacja była zrealizowana czy nie – reservation_realized
    Tutaj wystarczy, że odnotujemy realizację rezerwacji 0 – nie zrealizowana, 1 – zrezlizowana

Analogicznie jak przy tabelach hotels i rooms stworzymy tabelę reservations w management studio.

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 "reservations".

gotowe 🙂

lub korzystamy ze skryptu

USE HOTELS
GO

CREATE TABLE reservations(
     reservation_id bigint primary key not null
    ,reservation_client_id int not null
    ,reservation_starting_date date not null
    ,reservation_final_date date not null
    ,reservation_room_id int not null
    ,reservation_parking_id int    
    ,reservation_realized bit not null
)

Teraz musimy ustawić połączenie, jak przy tabelach hotels i rooms. Tym razem jednak łączymy pole reservation_room_id z tabeli reservations i pole room_id z tabeli rooms.

USE HOTELS
GO

ALTER TABLE reservations
ADD CONSTRAINT fk_reservations_rooms FOREIGN KEY (reservation_room_id) REFERENCES rooms(room_id)

Generujemy schemat i mamy już 3 połączone tabele.

sql database hotels baza danych hotele tutorial sql server


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