Skip to main content

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:

sql
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:

sql
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

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

  • Adds new records into the tables.

🔍 Selecting Data

sql
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

sql
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

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

  • Updates data in existing rows.

🗑️ Deleting Data

sql
DELETE FROM ftypes WHERE num = 4;

  • Removes rows from a table.

🔢 Using BETWEEN

sql
SELECT * FROM ftypes WHERE num BETWEEN 1 AND 2;

  • Filters values in an inclusive range.

🔗 Joins (Combining Data from Tables)

LEFT JOIN:

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

  • All rows from planes + matching rows from ftypes.

RIGHT JOIN:

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

  • All rows from ftypes + matching rows from planes.

INNER JOIN:

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

  • Only rows that match in both tables.


Back to top