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.
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.
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.
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.
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
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.
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.
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.
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.
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
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.
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.
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.
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.
Use set difference in queries.
Use the related clauses EXCEPT and EXCEPT ALL.
Practice subqueries together with INNER JOINS.
Unit 4: Advanced Joins
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.
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.
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.
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.
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.
Submit a resume