SQL Tutorial: Hotel Reservation Project 🎯

Welcome to the SQL Tutorial for the Hotel Reservation Project! In this lesson, we'll be diving deep into SQL, a powerful language used for managing and manipulating databases. By the end of this tutorial, you'll be able to create, modify, and query a hotel reservation database like a pro! 💡

What is SQL?

SQL (Structured Query Language) is a language designed for managing data held in a relational database management system (RDBMS). SQL allows you to insert, update, delete, and query data in databases.

Setting Up Your Development Environment 📝

To follow along with this tutorial, you'll need a SQL client installed on your computer. We recommend using SQLite Studio, which is a user-friendly, open-source SQL client for SQLite databases.

Creating the Hotel Reservation Database ✅

Let's start by creating a database for our hotel reservation system.

sql

CREATE DATABASE hotel_reservation;

Now that we have a database, let's create tables for Rooms, Customers, and Reservations.

sql

USE hotel_reservation;

CREATE TABLE Rooms (
    id INTEGER PRIMARY KEY,
    room_number TEXT NOT NULL,
    room_type TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE Customers (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE Reservations (
    id INTEGER PRIMARY KEY,
    room_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    FOREIGN KEY (room_id) REFERENCES Rooms(id),
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

Inserting Data into the Tables 📝

Now that we have our tables, let's insert some sample data into them.

sql

-- Inserting some sample rooms
INSERT INTO Rooms (room_number, room_type, price) VALUES
    (101, 'Single', 80.00),
    (102, 'Double', 100.00),
    (103, 'Suite', 150.00);

-- Inserting some sample customers
INSERT INTO Customers (first_name, last_name, email) VALUES
    ('John', 'Doe', 'johndoe@example.com'),
    ('Jane', 'Smith', 'janesmith@example.com'),
    ('Mike', 'Johnson', 'mikejohnson@example.com');

Queries and Manipulating Data 💡

Now that we have data in our tables, let's learn how to query and manipulate that data using SQL.

Finding all available rooms 📝

sql

SELECT room_number, room_type, price FROM Rooms WHERE id NOT IN (
    SELECT room_id FROM Reservations
);

Finding the total income from room 101 📝

sql

SELECT SUM(price) FROM Reservations
JOIN Rooms ON Reservations.room_id = Rooms.id
WHERE Rooms.id = 101;

Quiz 🎯

Conclusion 🎯

Congratulations on completing the Hotel Reservation SQL tutorial! You've learned the basics of SQL, including creating databases, tables, and querying data. Keep practicing, and soon you'll be able to create sophisticated hotel reservation systems of your own!

💡 Pro Tip: Don't forget to experiment with your SQL queries and try to solve real-world problems using SQL! This will help you solidify your understanding of SQL and become a more confident developer. 🚀

Happy coding! 💻