New Page

📘 SQL - Structured Query Language

📂 Creating and Using a Database

Commands:

CREATE DATABASE database_name;

USE travel;

Explanation:

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

🛠️ Creating Tables

planes table:

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

Stores flight routes.
num is the primary key.
country_start and country_arrival are origin/destination.

ftypes table:

 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) );

Stores flight types.
plane_num is a foreign key referencing planes(num).

✏️ Inserting Data

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

Adds new records into the tables.

🔍 Selecting Data

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 renames columns.
ORDER BY sorts results.

🧹 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 allows pattern matching.
AND/OR combine conditions.

🔁 Updating Data

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

Updates data in existing rows.

🗑️ Deleting Data

 DELETE FROM ftypes WHERE num = 4;

Removes rows from a table.

🔢 Using BETWEEN

 SELECT * FROM ftypes WHERE num BETWEEN 1 AND 2;

Filters values in an inclusive range.

🔗 Joins (Combining Data from Tables)

LEFT JOIN:

 SELECT * FROM planes LEFT JOIN ftypes ON planes.num = ftypes.plane_num;

All rows from planes + matching rows from ftypes.

RIGHT JOIN:

 SELECT * FROM planes RIGHT JOIN ftypes ON planes.num = ftypes.plane_num;

All rows from ftypes + matching rows from planes.

INNER JOIN:

 SELECT * FROM planes JOIN ftypes ON planes.num = ftypes.plane_num;

Only rows that match in both tables.