01-Intermediate.md

Intermediate

Aliases, Joins, Functions and Operators

By the end of this section, you will be able to:

1. Rename a column and table using an alias
2. Join tables to retrieve data across a database
3. Display data using the arithmetic operators +, -, * / and %
4. Display data using the aggregate functions COUNT, MIN, MAX and AVG
5. Filter aggregated data using HAVING and GROUP BY
6. Change the display of data using NULL and CASE

Prerequisites

You can complete the 3 assessment items for the Beginning Workshop.

Lesson

Objective 1: Rename a column and table using an alias

• SQL AS
  • Complete at least one exercise to rename a column, and another to rename a table.

Objective 2: Join tables to retrieve data across a database

• Multi-table queries with JOINs
  • Complete the 3 tasks at the bottom of the page.
• OUTER JOINs
  • Complete the 3 tasks at the bottom of the page.

Objective 3: Display data using the arithmetic operators +, -, * / and %

• Queries with expressions
  • Complete the 3 tasks at the bottom of the page.

Objective 4: Display data using the aggregate functions COUNT, MIN, MAX, AVG, SUM, GROUP BY

Objective 5: Filter aggregated data using HAVING and GROUP BY

• Queries with aggregates (Pt. 1)
  • Complete the 3 tasks at the bottom of the page.
• Queries with aggregates (Pt. 2)
  • Complete the 3 tasks at the bottom of the page.

Objective 6: Change the display of data using NULL and CASE

• SELECT Queries with nulls
  • Complete the 2 tasks at the bottom of the page.
• SQL CASE
  • Complete the first 3 practice problems.

Assessment

• Daily Challenge 5
• Daily Challenge 7
• Daily Challenge 9
• Daily Challenge 10

Stretch Activities

• SUM and COUNT Quiz
• Using NULL Quiz