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

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

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 

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

General case statement 

CASE
  WHEN condition1 then result 1
  WHEN condition2 then result 2 
  ELSE some_other_result

EG Simple table below

a
1
2

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 EXPRESSION - checks quality by using the expession 

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 * FROM test

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

Select a,
  Case a WHEN 1 THEN 'one'
  when 2 then 'two'
  ELSE 'other'
END 
FROM test