# Advanced SQL

In this course students learn how to work with large, complex, realistic databases. The course covers advanced topics in SQL including conditional expressions, working with text strings and using text string operations in queries, understanding sets, relations, and bags, writing subqueries and combining them with joins, using advanced joins, and defining and using functions.

## Course Features

- Students learn at their own pace by reading tutorials, watching videos, going through examples, and solving programming challenges.
- Every short lesson is followed by self-assessment, so that students instantly know whether they have mastered the concept.
- Students obtain real-time help from the NCLab AI tutorial engine, as well as remote assistance from live course instructors as needed.

## Prerequisites

The prerequisite for this course is *SQL Fundamentals*.

## Equipment Requirements

Internet access

One of the following browsers:

- Google Chrome
- Mozilla Firefox
- Microsoft Internet Explorer (9.0 or above)
- Safari

## Course Structure and Length

The course is self-paced, and students practice each skill or concept as they go. Automatic feedback is built into the course for both practices and quizzes.

The course is divided into four Units, and each Unit is composed of five Sections. Each Section consists of 7 instructional/practice levels, a quiz, and a master (proficiency) level. Students can return to any level or quiz for review.

While learning skills in this course, students can practice queries and create portfolio artifacts with NCLab’s SQL apps. Use a project idea from NCLab or create their own. NCLab provides several practice databases that can be queried with the SQL app.

*Advanced SQL* is designed to be completed in approximately 80 to 120 hours. Since the course is self-paced, the amount of time required to complete the course will vary from person to person. Students are responsible for learning both the tutorial content and the skills acquired through practice.

## Prerequisites

This course assumes that students have completed the ** SQL Fundamentals** course where they learned:

- Basic concepts in data, databases, and SQL.
- How to extract data from, and perform calculations with SQL tables.
- How to create schemata and tables, and enter and manage their own data.
- How to combine data from two or more tables using the inner join.

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