SQL

SQL - Structured query language.

Creating and Using a Database

CREATE DATABASE database_name;
USE travel;

• CREATE DATABASE: Creates a new database.
• USE: Selects the database to work with.

Creating Tabels

CREATE TABLE planes (
  num INTEGER PRIMARY KEY,
  country_start VARCHAR(25), 
  country_arrival VARCHAR(25)
);

• planes table stores flight routes.
• num is the unique identifier (Primary Key).
• country_start and country_arrival represent the departure and arrival countries.

CREATE TABLE ftypes (
  num INTEGER PRIMARY KEY,
  flight_type CHAR(25),
  food_on_plane CHAR(25),
  plane_num INTEGER,
  FOREIGN KEY (plane_num) REFERENCES planes(num)
);

• ftypes table stores flight types (e.g. Direct, 1 stop).
• plane_num is a foreign key linking to planes.num.

Inserting Data

INSERT INTO planes VALUES (5 , 'France', 'USA');
INSERT INTO ftypes VALUES (1, 'Direct', 'yes', 5);

• Adds records (rows) into tables.

Selecting Data

SELECT

SELECT * FROM planes;
SELECT country_start AS 'start' FROM planes;
SELECT num, country_start FROM planes;
SELECT country_start AS 'flying from', country_arrival AS 'flying to' FROM planes ORDER BY country_start;

• SELECT retrieves data.
• AS gives columns an alias (friendly name).
• ORDER BY sorts results.

SELECT DISTINCT

SELECT DISTINCT country_start FROM planes;
SELECT DISTINCT country_start, country_arrival FROM planes;
SELECT DISTINCT removes duplicate rows from the result.

• Use it to return only unique values based on selected columns.
• When using multiple columns, only unique combinations are returned

Count

SELECT COUNT(*) FROM table;

• Returns the total number of rows.
• Often used with WHERE, GROUP BY, etc.

Filtering Results

SELECT * FROM planes WHERE country_start LIKE '%an%';
SELECT * FROM ftypes WHERE num >= 2;
SELECT * FROM ftypes WHERE food_on_plane LIKE '%y%' AND flight_type LIKE '%di%';
SELECT * FROM planes WHERE country_start LIKE '%ca%' OR country_arrival LIKE '%ja%';

• WHERE filters rows.

• LIKE is used for pattern matching (e.g., %an% means “contains 'an'”).

• AND / OR combine conditions.

Sorting Data- Order by 

SELECT * FROM planes ORDER BY country_start; 

SELECT * FROM planes ORDER BY country_start ASC; 

SELECT * FROM planes ORDER BY country_start DESC, country_arrival ASC;

• ORDER BY sorts the result set based on one or more columns.

• Use ASC for ascending (default) and DESC for descending order.

• You can sort by multiple columns for more precise ordering.

Updating Data

UPDATE ftypes SET food_on_plane = 'yes' WHERE num = 2;

• UPDATE modifies existing records.
• Only rows where num = 2 are updated.

DELETE FROM ftypes WHERE num = 4;
DELETE removes rows.

Using BETWEEN

SELECT * FROM ftypes WHERE num BETWEEN 1 AND 2;
BETWEEN filters values in a range (inclusive).

Like

SELECT * FROM table WHERE column LIKE '%pattern%';

• Performs pattern matching in strings.
• % means any number of characters.
• 'di%' → starts with "di"
• '%y' → ends with "y"
• '%an%' → contains "an"

 

Joins 

Use of joins combine data between tables

LEFT JOIN

• Returns all rows from planes, with matching rows from ftypes.

• If no match, NULL values appear for ftypes.

🡺 RIGHT JOIN

• Returns all rows from ftypes, with matching rows from planes.

🪢 INNER JOIN

• Returns only rows where there's a match in both tables.