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

Agregate function

Aggregate functions are like built-in calculators in SQL. Instead of looking at each row one by one, they help you summarize lots of data at once. You can count rows, add up numbers, find averages, and more. They're super useful when you want to answer questions like “How many?”, “What’s the total?”, or “What’s the biggest?” — especially when used with GROUP BY.

COUNT

SELECT COUNT(*) FROM table;
Returns the total number of rows.

Often used with WHERE, GROUP BY, etc.

Use COUNT(column) to count only non-null values in that column.

SUM

SELECT SUM(column) FROM table;
Returns the total sum of all numeric values in a column.

AVG

SELECT AVG(column) FROM table;
Returns the average (mean) of all values in a column.

MIN

SELECT MIN(column) FROM table;
Returns the smallest value in a column.

MAX

SELECT MAX(column_name) FROM table;
Finds the largest value in a column.

HAVING

HAVING is used after GROUP BY to filter aggregated results
SELECT country_start, COUNT(*) 
FROM planes 
GROUP BY country_start 
HAVING COUNT(*) > 1;
Only shows countries with more than 1 flight starting there.

HAVING is like WHERE, but used after aggregation.

Similar to WHERE, but WHERE filters rows before grouping, and HAVING filters groups after aggregation.

`WHERE` vs `HAVING`

Clause	Filters	Can Use Aggregates?	Example Purpose
`WHERE`	Before grouping	❌ No	Filter rows before calculation
`HAVING`	After grouping	✅ Yes	Filter based on aggregates

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"

GROUP BY

Allows us to agregate some columns per some category.

AGG a place holder for any aggregate function.

GROUP BY can only appear afrter a FROM or after a WHERE statement.

IN the SELECT statement,

columns must either have an aggregate function or be in the GROUP BY call.

Where statements should not refer to the agregate result. (this is done wiht the HAVING)
SELECT category_col, AGG(data_col)
FROM table
GROUP BY category_col
When doing an ORDER BY an agregate function the entire agregate should be usedin the ORDER BY

Joins

Use of joins combine data between tables

SELECT * FROM customer 
FULL OUTER JOIN payment
ON  customer.customer_id = payment.customer_id
Where customer.customer_id IS NULL
OR payment.payment_id IS NULL

LEFT JOIN

A left outer join results in the set of records that are in the left table ,if there is no match with the right table, the results are null
SELECT * FROM planes LEFT JOIN ftypes ON planes.num = ftypes.plane_num;
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.
Technically a left join wiht table swapped around.

UNION

Used to join select statements.

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

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

Joining JOINs

Film enthusiast wants to know every movie where the actor Nick Wahlberg appears.

Use JOIN when combining related data from normalized tables.
Use UNION when stacking rows from similar queries.

Your application allows searching for actors by name, and returning:

Their first name
Last name
And each film title they’ve appeared in.

We need to use the actor, film_actor and the film tables,

SELECT DISTINCT actor.first_name, last_name, title 
FROM film
LEFT JOIN film_actor ON film.film_id = film_actor.film_id
LEFT JOIN actor ON actor.actor_id = film_actor.actor_id
WHERE actor.first_name = 'Nick' AND actor.last_name = 'Wahlberg';

This query:

Looks at every film in the catalog
Checks for links to the film_actor table
Connects those records to the actor table
Filters to only keep results where the actor is Nick Wahlberg
Ensures there are no duplicate titles using DISTINCT

Conditional expressions and Procedures

Case

case is used to execute sql code when certain conditions are true. simialr to if then else

General case statement

a general case statement is a giant subsitute for a column call, wheree conditions logic can be defined
SELECT * FROM test

SELECT a,
  CASE WHEN a = 1 THEN 'one'
  CASE WHEN a = 2 THEN 'two'
  ELSE 'other' AS label
  END
FROM test
CASE
  WHEN condition1 then result 1
  WHEN condition2 then result 2 
  ELSE some_other_result

CASE EXPRESSION

A case expression syntax, first evaluates an expression then compares the result with each value in the when clause sequentially

Example when to use case expression.

as the owner of a dvd rental store, you want to run a promotion to your customers. you want to introduce premium, plus and general users, based on their sign up.

we will use the customer_id column. (this id is eunique and shows the order of user sign up

you want ot make the first 50 premium . the next 50 will be plus, then everyone else is general.
Select a,
  Case a WHEN 1 THEN 'one'
  when 2 then 'two'
  ELSE 'other'
END 
FROM test
SELECT * FROM test

SELECT expression,
  WHEN value1 THEN result1
   WHEN value2 THEN result2
  ELSE 'other_result' 
  END

DATETIME

functions-formatting

extracting allows you to extract a sub componenet of the date value
EXTRACT(YEAR FROM date_col)

EXTRACT(MONTH FROM date_col)
Age calculates the current age of a given time stamp
AGE(date_col)
TO_CHAR - convert datatypes to text, is used in time stamp formatting.
TO_CHAR(date_col, 'mm-dd-yyyy')]
CAST(column_name AS DATE) = '2025-07-08'

SQL

SQL - Structured query language.

Creating and Using a Database

Creating Tabels

Inserting Data

Selecting Data

SELECT

SELECT DISTINCT

Agregate function

COUNT

SUM

AVG

MIN

MAX

HAVING

WHERE vs HAVING

Filtering Results

Sorting Data- Order by

Updating Data

Using BETWEEN

Like

GROUP BY

Joins

LEFT JOIN

🡺 RIGHT JOIN

UNION

Joining JOINs

Conditional expressions and Procedures

Case

General case statement

CASE EXPRESSION

DATETIME

`WHERE` vs `HAVING`