Archiwa tagu: hotele

db HOTELS 11. Wyświetl wszystkie rezerwacje (z informacją czy zrealizowane)

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 11

Treść: Wyświetl wszystkie rezerwacje (z informacją czy zrealizowane)

Polecenia/funkcje w zapytaniu: SELECT, CASE, FROM, JOIN, WHERE


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


Rozwiązanie:

Zapytanie:

SELECT
     Klient.client_last_name AS Nazwisko
    ,Klient.client_first_name AS [Imie]
    ,Kraj.country_name AS [Kraj (hotel)]
    ,Miasto.city_name AS [Miasto (hotel)]
    ,reservation_starting_date AS [Data rozpoczecia rezerwacji]
    ,reservation_final_date AS [Data zakończenia rezerwacji]
    ,CASE
        WHEN Rezerwacja.reservation_realized = 1 THEN 'Zrealizowana'
        ELSE 'NIE zrealizowana'
     END AS [Stan realizacji]
FROM
    reservations Rezerwacja
    JOIN clients Klient ON Rezerwacja.reservation_client_id = Klient.client_id
    JOIN rooms Pokoj ON Rezerwacja.reservation_room_id = Pokoj.room_id
    JOIN hotels Hotel ON Pokoj.room_hotel_id = Hotel.hotel_id
    JOIN countries Kraj ON Hotel.hotel_country_id = Kraj.country_id
    JOIN cities Miasto ON Hotel.hotel_city_id = Miasto.city_id
WHERE
    Klient.client_last_name = 'Banko'

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


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


 

db HOTELS 10. Oblicz udział procentowy hoteli w danym kraju względem wszystkich hoteli w bazie

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 10

Treść: Oblicz udział procentowy hoteli w danym kraju względem wszystkich hoteli w bazie

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


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


Rozwiązanie:

Zapytanie:

DECLARE @ile_hoteli numeric =
    (
        SELECT
            COUNT(*)
        FROM    
            hotels
    )   
SELECT
     C.country_name AS Kraj
    ,CAST(COUNT(*)*100/@ile_hoteli AS NUMERIC(6,2)) AS [%]
FROM
    hotels H
    JOIN countries C ON H.hotel_country_id = C.country_id    
GROUP BY
    C.country_name
ORDER BY
    2 DESC   

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


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


 

db HOTELS 9. Jakie było obłożenie wszystkich hoteli (%) w dniu 2016-10-10

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 9

Treść: Jakie było obłożenie wszystkich hoteli (%) w dniu 2016-10-10

Polecenia/funkcje w zapytaniu: DECLARE, SELECT, COUNT, FROM, WHERE, BETWEEN, CAST(), ROUND()


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


Rozwiązanie:

Zapytanie:

DECLARE @dataSprawdzenia date = '2016-10-10'
DECLARE @ilePokoi int =
    (
        SELECT
            COUNT(*)
        FROM
            rooms
    )
DECLARE @ilePokoiZajetych int =
    (    
        SELECT
            COUNT(*)    
        FROM    
            reservations R    
        WHERE
            @dataSprawdzenia BETWEEN R.reservation_starting_date AND R.reservation_final_date
    )    
DECLARE @oblozenie REAL = 100*@ilePokoiZajetych/@ilePokoi;
SELECT
     'Wszystkich pokoi: '+CAST(@ilePokoi AS NVARCHAR) AS Pokoi
    ,'Zajętych pokoi: '+CAST(@ilePokoiZajetych AS NVARCHAR) AS Zajętych
    ,'Obłożenie pokoi w dn. 2016-10-10 wynosi: '
        +CAST(CAST(ROUND((100*CAST(@ilePokoiZajetych AS NUMERIC)/CAST(@ilePokoi AS NUMERIC)),2) AS NUMERIC(6,2)) AS NVARCHAR)
        +'%' AS Obłożenie

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


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


 

db HOTELS 8. W jakim hotelu złożono najwięcej rezerwacji które zostały zrealizowane.

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 8

Treść:  W jakim hotelu złożono najwięcej rezerwacji które zostały zrealizowane.

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


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


Rozwiązanie:

Zapytanie:

SELECT TOP 1
     CO.country_name AS Kraj
    ,CI.city_name AS Miasto
    ,H.hotel_id AS [Nazwa hotelu]
    ,COUNT(*) AS [Ilość złożonych rezerwacji]
FROM
    reservations RE
    JOIN rooms RO ON RE.reservation_room_id = RO.room_id
    JOIN hotels H ON RO.room_hotel_id = H.hotel_id
    JOIN countries CO ON H.hotel_country_id = CO.country_id
    JOIN cities CI ON H.hotel_city_id = CI.city_id
WHERE
    RE.reservation_realized = 1
GROUP BY
    CO.country_name , CI.city_name , H.hotel_id        
ORDER BY
    COUNT(*) DESC

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


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


 

db HOTELS 7. W jakim hotelu złożono najwięcej rezerwacji.

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 7

Treść:  W jakim hotelu złożono najwięcej rezerwacji.

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


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


Rozwiązanie:

Zapytanie:

SELECT TOP 1
     CO.country_name AS Kraj
    ,CI.city_name AS Miasto
    ,H.hotel_id AS [Nazwa hotelu]
    ,COUNT(*) AS [Ilość złożonych rezerwacji]
FROM
    reservations RE
    JOIN rooms RO ON RE.reservation_room_id = RO.room_id
    JOIN hotels H ON RO.room_hotel_id = H.hotel_id
    JOIN countries CO ON H.hotel_country_id = CO.country_id
    JOIN cities CI ON H.hotel_city_id = CI.city_id
GROUP BY
    H.hotel_id , CO.country_name , CI.city_name
ORDER BY
    COUNT(*) DESC

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


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


 

db HOTELS 6. Który hotel ma najwięcej miejsc parkingowych

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 6

Treść:  Który hotel ma najwięcej miejsc parkingowych

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


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


Rozwiązanie:

Zapytanie:

DECLARE @max_parking_places int = (
SELECT TOP 1
    COUNT(P.parking_hotel_id)
FROM
    parking P    
GROUP BY
    P.parking_hotel_id
ORDER BY
    1 DESC
);
SELECT
     H.hotel_name AS [Nazwa hotelu]
    ,COUNT(P.parking_hotel_id) AS [Ilość miejsc parkingowych]
FROM
    parking P    
    JOIN hotels H ON P.parking_hotel_id = H.hotel_id
GROUP BY
    P.parking_hotel_id, H.hotel_name
HAVING    
    COUNT(P.parking_hotel_id) = @max_parking_places   

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


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


 

db HOTELS 5. Znajdź i wyświetl informacje o najdłuższej rezerwacji

SQL tutorial. db Hotels exercises. SQL SERVER.

Baza: Hotele/Hotels

Zadanie nr: 5

Treść:  Znajdź i wyświetl informacje o najdłuższej rezerwacji

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


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


Rozwiązanie:

Zapytanie:

DECLARE @ilosc_dni int =
    (
        SELECT TOP 1
            DATEDIFF(day,reservation_starting_date,reservation_final_date) AS Dni
        FROM
            reservations R    
        ORDER BY
            1 DESC            
    )    
    
SELECT
     *
FROM
    reservations R        
WHERE
    DATEDIFF(day,reservation_starting_date,reservation_final_date) = @ilosc_dni

Wynik:

SQL tutorial. db Hotels exercises. SQL SERVER.

Pobierz skrypt sql


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


 

baza HOTELS cz.6 wypełniamy bazę danymi [SQL SERVER]

baza HOTELS cz.6 wypełniamy bazę danymi [SQL SERVER]

Doszliśmy do miejsca gdzie nasza baza jest już gotowa do wypełnienia danymi. Oczywiście można by było jeszcze ją lekko rozbudować o kilka tabel ale narazie poprzestaniemy na takiej strukturze. Wypełnimy tabele danymi i w kolejnych wpisach zapiszemy "kilka" zapytań.

W tym momencie nasz schemat bazy wygląda jak poniżej.

sql database hotels baza danych hotele tutorial sql server

Skrypt którym wypełnisz tabele danymi znajdziesz tutaj

Dla początkujących poniżej opis jak utworzyć bazę danych Hotele w SQL Serverze za pomocą Management Studio.

  1. Uruchamiamy Management Studio (Start -> Wszystkie programy -> Microsoft SQL Server 2008 R2 (ja korzystam z tej wersji) -> SQL Server Management Studio).
  2. Teraz z poziomu Management Studio otwieramy plik skryptu (Menu File -> Open -> File lub wciskamy kombinację klawiszy Ctrl+O) lub otwieramy nowe okno przyciskiem "New Query".
  3. Teraz uruchamiamy skrypt przyciskiem "Execute" lub wciskając F5.
  4. Jeżeli skrypt wykona się prawidłowo powinniśmy uzyskać na dole okna komunikat "Query executed successfully".
  5. Teraz w oknie "Object explorer" w węźle "Databases" powinniśmy zobaczyć gałąź "HOTELS". Jeżeli jej nie widzimy musimy odświeżyć węzeł "Databases" klikając na nim prawym przyciskiem myszy i z menu podręcznego wybieramy opcję "Refresh".

gotowe 🙂


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

 

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