Unit 1:  Conditional Expressions

Section 1

  • Understand the difference between a statement and an expression.
  • Insert “intelligent” conditional expressions into queries using the keyword CASE.
  • Use both the “searched version” of the CASE expression which is more versatile, and the “simple (switch) version”.
  • Combine a CASE expression with an inner join.

Section 2

  • Learn about additional important applications of conditional expressions including:
  • How to use them to split values into different columns.
  • How to count non-NULL values in the columns of a SELECT statement.
  • How to use conditional expressions in aggregate functions COUNT, SUM, MIN, MAX, AVG.
  • Understand the difference between using conditional expressions and the WHERE clause.

Section 3

  • Simplify filtering values in aggregate functions using the FILTER clause.
  • Learn how integer division works in SQL, and how to prevent potential problems which it may cause.

Section 4

  • Learn about various situations which can cause an error in SQL, and how to prevent them using suitable CASE expressions. In particular, learn how to prevent division-by-zero errors.
  • Learn how SQL responds when NULL values are combined with numbers and text strings.
  • Use CASE expressions in the ORDER BY clause.

Section 5

  • Replace NULL with selected values using the function COALESCE
  • Replace selected values with NULL using the function NULLIF.
  • Learn about the relation between these functions and the conditional expression CASE.
  • Replace values in tables by combining the COALESCE and NULLIF functions.

Unit 2:  Text Strings

Section 6

  • Using the Museum of Modern Art (MoMA) database, review pattern matching and text string operations.
  • Understand the advantages of performing case-insensitive searches.
  • Use the functions LOWER or UPPER to make a text search case-insensitive.
  • Refine searches using the SQL clause ILIKE, and the shortcuts ~~, ~~*, !~~, and !~~*.
  • Search for the special characters % and _, and for the escape character \.
  • Become familiar with the official SQL documentation.

Section 7

  • Search for single quotes ‘ and use the clause SIMILAR TO with entry-level regular expressions.
  • Understand that SIMILAR TO is case-sensitive but can be made case-insensitive in combination with LOWER or UPPER (same as LIKE).
  • Use regular expressions with groups of characters or text strings enclosed in parentheses and separated by the | character.
  • Escape characters when using SIMILAR TO to search for parentheses
  • Use the character class wildcard […] in regular expressions.

Section 8

  • Use regular expressions with a pattern which is repeated (exactly) N times.
  • Use regular expressions with a pattern which is repeated at least N times.
  • Understand that repeating patterns may contain wildcards and other repeating patterns.
  • Use the shortcuts *, + and ? when working with repeating patterns.

Section 9

  • Use the ASCII table and its Unicode extension, as well as the functions ASCII and CHR.
  • Understand that both the ASCII and CHR functions work with Unicode.
  • Use Unicode and the web when searching for text strings with special characters.
  • Use the concatenation operator || and the cast operator :: .
  • Use the functions SUBSTRING, POSITION, LEFT, RIGHT, REVERSE and INITCAP.
  • Use at least two different methods to search for substrings.

Section 10

  • Modification of text strings via search-and-replace. There are many applications for this, related to cleaning, adjusting, extracting, combining and presenting data.
  • The entry-level functions OVERLAY and REPLACE.
  • Search-and-replace with POSIX regular expressions.
  • Use POSIX regular expressions in the SELECT statement, in the functions
  • REGEXP_REPLACE and SUBSTRING, and in the WHERE clause.

Unit 3:  Sets and Subqueries

Section 11

  • Distinguish sets, relations and bags in SQL
  • Understand that tables are relations and not ordered.
  • Understand why ORDER BY should be used before the clauses LIMIT and OFFSET.
  • Find and count duplicate values in columns and duplicate rows in tables.

Section 12

  • Use the UNION and UNION ALL clauses in queries.
  • Understand that the UNION clause produces a set with no duplicates, while the UNION ALL clause produces a bag which may have duplicates.
  • Understand table compatibility with UNION and UNION ALL.
  • Combine rows with UNION and UNION ALL.
  • Write queries using UNION for qualitative analysis and UNION ALL for calculations.

Section 13

  • Write subqueries to access data in multiple tables at the same time.
  • Use subqueries in combination with set operations.
  • Design subqueries to be simple and not reach into the same table as the main query.
  • Use subqueries with the FROM and WHERE … IN … clauses.
  • Understand how to alias results.
  • Use subqueries with aggregate functions.

Section 14

  • Use the set theory clauses INTERSECT and INTERSECT ALL.
  • That the intersection operation is associated with the keyword AND.
  • Understand how set theory clauses in SQL affect computing complexity.
  • Use the INTERSECT clause in subqueries.
  • Understand the similarities and differences between set theory clauses, subqueries and joins.

Section 15

  • Use set difference in queries.
  • Use the related clauses EXCEPT and EXCEPT ALL.
  • Practice subqueries together with INNER JOINS.

Unit 4:  Advanced Joins

Section 16

  • Review the INNER JOIN.
  • Use the LEFT (OUTER) JOIN in queries.
  • Understand how LEFT (OUTER) JOIN relates to the INNER JOIN.
  • Use the LEFT JOIN to find orphaned or missing data.
  • Use subqueries to work with orphaned or missing data.

Section 17

  • Use the RIGHT (OUTER) JOIN and the FULL (OUTER) JOIN in queries.
  • Understand when to use the INNER, LEFT, RIGHT, and FULL JOIN.
  • Rewrite a RIGHT JOIN as a LEFT JOIN.

Section 18

  • Understand the CROSS JOIN as the Cartesian product of the rows of the two tables.
  • Write queries using the CROSS JOIN and compare with the INNER JOIN, which is a special case of the CROSS JOIN.
  • Write queries with self joins (CROSS JOIN of a table with itself).
  • Use self-joins to establish a hierarchy among items in a table, such as among employees.
  • Create complex queries.

Section 19

  • Work with semi-joins between two tables.
  • Understand how matching values work in left and right semi-joins.
  • Work with anti-joins, and understand how they return values that do not match.
  • Practice INNER JOIN, CROSS JOIN, and subqueries.

Section 20

  • Create functions using the CREATE FUNCTION and CREATE OR REPLACE FUNCTION clauses.
  • Specify the returned data type using RETURNS.
  • Use names or numbers ($1,$2etc.) for function parameters.
  • Use the keywords IN and OUT to specify input and output parameters.
  • Enclose the body of the function in double-dollars $$…$$ or quotes ‘…’.
  • Use and understand the flags IMMUTABLE,STABLE and VOLATILE.
  • Use the flags RETURNS NULL ON NULL INPUT (STRICT), and CALLED ON NULL INPUT.
  • Use the keyword SETOF to return a column of values, or a table.
  • Create composite data types, and use them in functions.