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.

Chapter 1 Notes

COUNT, DISTINCT

Debugging:

• Misspelling
• Incorrect capitalization
• Incorrect or missing punctuation
• Comma errors
• Keyword errors

Order of operations:

1. FROM
2. WHERE
3. SELECT
4. Refined results (such as LIMIT)

SQL formatting is not required but there are several "best practices"

• Capitalize keywords
• New lines between keywords
• Semicolon at the end of the line of code

Why format?

• Easier collaboration
• Clean and readable
• Looks professional
• Easier to understand and debug

Chapter 2 Notes

Filtering Numbers

• Comparison operators (>,<,>=,<=,=,<>)
• = can also be used with strings but requires '' around the string

Multiple Criteria

• OR: Only need to satisfy one condition
• AND: Need to satisfy all criteria
• AND, OR: Use () around the individual clauses
• BETWEEN: Provides answer between a specified range (this is inclusive of the end values)

Filtering Text

• LIKE (Uses % and _ to find patterns)
  1. WHERE name LIKE 'Ade%' <- This looks for any name containing Ade (can be one or more characters after)
  2. WHERE name LIKE 'Ev_' <- This looks for a three character name that starts with Ev (match a single character only)
• NOT LIKE (does not match specific criteria) <- This is case sensitive
  1. WHERE name NOT LIKE 'A.%'
• IN (able to specify multiple values in a WHERE cause without having to separate by OR keywork)
  1. WHERE release_year IN (1920, 1930, 1940)

Null Values (missing or unknown values)

• IS NULL (in the WHERE clause)
• IS NOT NULL (if using a COUNT keyword, this will be the same result)

Chapter 3 Notes

Summarizing Data - Aggregate function (Come after the SELECT function)

• AVG() - Numeric only
• SUM() - Numeric only
• MIN()
• MAX()
• COUNT()

Summarizing Subsets

• Can combine the WHERE clause along with the aggregate functions (the WHERE clause is first before the SELECT clause in order of operations)
• ROUND(number_to_round, decimal_places) - Numeric only
• SELECT ROUND(AVG(budget),2) <- The demical places is optional and if left out, will be a whole number
• ROUND() with negative parameter (will round to the left, thousandths, etc)

Aliasing and Arithmetic

• (+,-,*,/) : parantheses help in clarity and understanding
• Arithmentic is horizontal, aggregates are vertical
• Always need to have alias and must be with SELECT statement only

Chapter 4 Notes

Sorting Results

• ORDER BY (Ascending by default)
• ASC or DESC (will be after the field name) <- Example: ORDER BY duration DESC
• Will contain null data (can add a WHERE ____ IS NOT NULL in front to remove null values)
• Can ORDER BY multiple values (will be in the order stated and field 2 would be a tie-breaker)
• End of order of execution, just before the LIMIT

Grouping Data

• GROUP BY (can be single fieldname or multiple)
• Error handling <- SQL will give error if a field is selected, but not within the GROUP BY clause
• To correct this error, and aggregate function must be placed around the field <- Example: COUNT(title)
• Order of execution: FROM, GROUP BY, SELECT, ORDER BY, LIMIT

Filtering grouped data

• Can't filter aggregate functions with WHERE clause
• HAVING <- Example HAVING COUNT(title) > 500
• Written order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
• Order of execution: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT

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.