SQL Queries Explanation

Database Schema

The database consists of four tables: tickets, users, planes, and airlines.

Creating Tables

CREATE TABLE tickets (
      user_id TEXT NOT NULL,
      ticket_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(7)))),
      origin VARCHAR(255) NOT NULL,
      destination VARCHAR(255) NOT NULL,
      departure_date VARCHAR(255) NOT NULL,
      price VARCHAR(255) NOT NULL,
      plane_id INT NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users(id),
      FOREIGN KEY (plane_id) REFERENCES planes(id)
  );

  CREATE TABLE users (
      id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) NOT NULL UNIQUE
  );

  CREATE TABLE airlines (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name VARCHAR(255) NOT NULL
  );

  CREATE TABLE planes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      airline_id INT NOT NULL,
      model VARCHAR(255) NOT NULL,
      FOREIGN KEY (airline_id) REFERENCES airlines(id)
  );

These queries create the necessary tables with appropriate columns and relationships.

Inserting Initial Data

INSERT INTO airlines (name) VALUES ('Delta');
  INSERT INTO planes (airline_id, model) VALUES (1, 'Boeing 737');

These queries insert initial data for airlines and planes.

Booking a Ticket

Check if User Exists

SELECT id FROM users WHERE email = ?

This query checks if a user with the given email already exists in the database.

Insert New User

INSERT INTO users (name, email) VALUES (?, ?)

If the user doesn't exist, this query inserts a new user into the database.

Insert Ticket

INSERT INTO tickets (user_id, origin, destination, departure_date, price, plane_id) VALUES (?, ?, ?, ?, ?, ?)

This query inserts a new ticket into the database with the provided information.

Fetching Ticket Information

SELECT tickets.*, planes.model AS plane_model, airlines.name AS airline_name
  FROM tickets
  JOIN planes ON tickets.plane_id = planes.id
  JOIN airlines ON planes.airline_id = airlines.id
  WHERE tickets.user_id = (SELECT id FROM users WHERE email = ?)

This query retrieves ticket information along with the associated plane model and airline name for a given user email. It uses JOINs to combine data from multiple tables.

These SQL queries work together to create a simple flight booking system, allowing for user management, ticket booking, and retrieval of ticket information.