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 CREATE TABLE planes ( num INTEGER PRIMARY KEY, country_start VARCHAR(25)VARCHAR(25), country_arrival VARCHAR(25)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)CHAR(25), 
                      food_on_plane CHAR(25)CHAR(25), plane_num INTEGER,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 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 SELECT* * FROM planes WHERE country_start LIKE '%an%'; 

SELECT * FROM ftypes WHERE num >= 2;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;2; 




Updates data in existing rows.



🗑️ Deleting Data

sql




 DELETE FROM ftypes WHERE num = 4;4; 




Removes rows from a table.



🔢 Using BETWEEN

sql




 SELECT * FROM ftypes WHERE num BETWEEN 1 AND 2;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.