## 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.