Skip to content
# Start coding here...

What is SQL theory?

Is based on relational algebra Which define operations like celection, projections, union, intersection, different and join to manipulate data

Spinner
DataFrameas
df
variable
-- Select all fields from table limited 5.
SELECT*
FROM cinema.reviews
Limit 5

All set theory for SQL like UNION UNION ALL INTERSECT EXCEPT

The tables must have be same columns and same datatype we use to combine tables

Spinner
DataFrameas
df
variable
--Take two tabels as input and return all from both tabels
SELECT id
FROM cinema.reviews
UNION
SELECT id
from cinema.roles
LIMIT 5;

_ Can use malti fields and filter by WHERE clause_

Spinner
DataFrameas
df
variable
-- Select id, film_id and filter by film_id then union with roles table after that filter by id
SELECT id,  film_id
FROM cinema.reviews
-- Where condition When film id greate than 74
WHERE film_id > 74
-- Union with other quiery
UNION
SELECT id, film_id
FROM cinema.roles
-- Other condition in second query
WHERE id <> 5
LIMIT 5;

When use UNION ALL this mean include all rows with duplicate

Spinner
DataFrameas
df
variable
-- UNION ALL include duplicates
-- Select three fields
SELECT film_id, actor_id, last_update
-- From specific table
FROM dvdrentals.film_actor
-- Union all rows
UNION ALL
-- Create query yo union
SELECT film_id, category_id, last_update
FROM dvdrentals.film_category
-- Order result by film_id
ORDER BY film_id
LIMIT 5;
Spinner
DataFrameas
df
variable
-- Choose all records from two table UNION ALL
SELECT *
FROM dvdrentals.category
UNION ALL
SELECT *
FROM dvdrentals.country
ORDER BY last_update
LIMIT 5;

Intersect is set operation that return the common records between tow result sets retrieves only the rows appear in both table, it is useful to removing duplicates.

Spinner
DataFrameas
df
variable
-- INTERSECT not fount in this two table
SELECT id, title
FROM cinema.films
INTERSECT
SELECT id, name
FROM cinema.people
ORDER by id
Spinner
DataFrameas
df
variable
--We see where this table INTERSECT
select id, film_id
from cinema.reviews 
intersect
select id, person_id
from cinema.roles

Except is a set operation that returns the rows from the first query that are not present in the second query, it remove duplicates.

Spinner
DataFrameas
df
variable
SELECT code, gross_savings
FROM world.economies2010
EXCEPT
SELECT code, gross_savings
FROM world.economies2015
ORDER By code
LIMIT 5;