Intermediate SQL

Intermediate SQL

Here you can access every table used in the course. To access each table, you will need to specify the cinema schema in your queries (e.g., cinema.reviews for the reviews table.

---

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Selecting Data

A) Querying a Database

COUNT() -COUNT() -Counts the number of records with a value in a field. -use an alias for clarity SELECT COUNT(birthdate) AS count_birthdates FROM people;

Count() with multiple records SELECT COUNT(name) AS count_names, COUNT(birthdate) AS count_birthdates FROM people;

-COUNT(field_name) counts values in a field

-COUNT() -counts totalrecords in a table SELECT COUNT() AS total_records FROM people;

DISTINCT -removes duplicates to return only unique values

SELECT DISTINCT language FROM films;

SELECT COUNT(DISTINCT birthdate) AS count_distinct_birthdates FROM people; -COUNT() includes duplicates -DISTINCT excludes duplicates

Query Execution A) Order of Execution -SQL is not processed in its written order.

execution order SELECT name(2) FROM people (1) LIMIT 10; (3)

-LIMIT limits how many results we return -good to know processing order or debugging and aliasing -aliases are declared in the SELECT statement

B)Debugging SQL/Common Errors: -misspelling -incorrect capitalization -incorrect or missing punctuation

• look out for comma errors!
• lookout for keyword errors
• Learn by making mistakes

SQL Style -Formatting is now required -but lack of formatting can cause issues

Best Practices: SELECT title, release_year, country FROM films LIMIT 3;

-this makes things easier to read -including a semi colon is considered best practice -easier to translate between sql flavors -indicates the end of a query

Dealing with non-standard field names -release year instead of release_year -put non-standard field names in double quotes

Why do we format? -easier collaboration -clean and readable

2. Filtering Records A) Filtering Numbers -WHERE filtering clause -WHERE color = 'green'

SELECT title FROM films WHERE release_year > 1960;

Comparison operators:

(Greater than or after) < (Less than or before) = (Equal to) = (Greater than or equal to) <= (Less than or equal to) <> (Not equal to)

Order of Execution with WHERE SELECT item(3) FROM coats(1) WHERE color = 'green'(2) LIMIT 5;(4)

B) Multiple Criteria** Keywords: OR, AND, BETWEEN

SELECT * FROM coats WHERE color = 'yellow' OR length = 'short';

OR operator -use OR when you need to satisfy at least one codition

AND -use AND if we need to satisfy all criteria

C) Filtering Text -Where can also filter text -filter a pattern rather than specific text -Like, Not Like, IN

Like -used to search for a patterin in a field -% match zero, one or many characters _ match a single character

Null -missing values -very common -use is null or is not null to: -identify missing values, select missing values, exclude missing values

Is Null select name from people where birthdate is null;

Is not null select count(*) as count_birthdates from people where birthdate is not null;

3. Aggregate Functions

A) Summarizing Data -Aggregate functions returns a single value, summarize data, and perform basic arithmetic calculations inside queries -COUNT, AVG, MIN, MAX, SUM (Aggregate functions) -they come after select

SELECT AVG(budget) FROM films;

-avg, sum are used for numerical fields only -count, min, max can be used for various data types -best to use an alias when summarizing data

B) Summarizing subsets -Using Where with aggregate functions

SELECT AVG(budget) AS avg_budget FROM films WHERE release_year >= 2010;

ROUND() -round a number to a specified decimal -can only be used with numerical fields -ROUND(number_to_round, decimal places)

SELECT ROUND(AVG(budget), 2) AS avg_budget FROM films WHERE release_year >= 2010;

SELECT ROUND(AVG(budget), -5) AS avg_budget FROM films WHERE release_year >= 2010;

C) Aliasing and arithmetic +, -, *, and /

SELECT(4 + 3); select(4 * 3); select (4 / 3); SELECT (4 - 3);

Aggregate functions vs. arithmetic -Aggregate functions: perform the operations on the field vertically -Arithmetic functions: performs the operations on the record horizontally

Aliasing with functions -keep field names clear

Order of Execution with an Alias

1. From
2. Where
3. select(aliases are defined here)
4. limit

*Aliases defined in the select clause cannot be used in the where clause due to order of executoin

4. Sorting and Grouping

A) Sorting Results

Order by -sorts results into one or more fields -comes after from -sorts in ascending order by default (smalles to biggest or A-Z)

DESCending -this keyword sorts in descending order -DESC

Order by multiple fields

• Order by field_one, field_two

Different orders -you can sort in ascending order as well descending at the same time

Explore Datasets

Use the descriptions, films, people, reviews, and roles tables to explore the data and practice your skills!

• Which titles in the reviews table have an IMDB score higher than 8.5?
• Select all titles from Germany released after 2010 from the films table.
• Calculate a count of all movies by country using the films table.