Learn from Silicon Valley: SQL

This lecture provides an overview on Data and SQL, answering some key general questions:
1. What is Data and what are the different types of data?

2. What is SQL and what can it do?

Note: The slides listed here are for the entire section

This lecture introduces the general syntax around the SELECT function:
1. Retrieving columns from a table using SELECT... FROM

2. Retrieving all columns from a table using SELECT *

3. Retrieving distinct rows of data from a table using SELECT DISTINCT

This lecture covers the practice exercises for the concepts covered in the previous lecture (SELECT, SELECT * and SELECT DISTINCT).

Please attempt the questions yourself before moving to the solutions!

This lecture introduces row filtering using WHERE:
1. Syntax for the WHERE statement

2. Various conditional operators such as =, != or <>, <, >, <=, >=

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding the WHERE statement and the various conditional operators.

Please attempt the questions yourself before moving to the solutions!

This lecture goes into further detail about logical operators that can be used when filtering rows using WHERE:
1. Introduction to Boolean Variables

2. Using AND, OR and NOT

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding the WHERE statement and the various logical operators.

Please attempt the questions yourself before moving to the solutions!

This lecture goes into additional depth with regard to the WHERE statement:

1. Conditional operators IN and BETWEEN

2. Pattern matching using LIKE

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding the WHERE statement and going further into detail into the operators IN, BETWEEN and LIKE.

Please attempt the questions yourself before moving to the solutions!

This lecture covers the functionality of the ORDER BY statement, which is used to order the output of a query

This lecture covers the functionality of the LIMIT statement, which is used to limit the number of rows returned by a query

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding Order By and Limit.

Please attempt the questions yourself before moving to the solutions!

This lecture covers the functionality of the Aliases, which is used to rename specific tables, columns etc

This lecture provides an introduction to the various datatypes that you would encounter when using SQL

Note: The slides listed here are for the entire section

This video covers the numeric datatype and associated functions

This video covers the character datatype and associated functions

This video covers the temporal (time) datatype and associated functions

This video covers the boolean datatype and associated functions

This video will show you how to type cast, or convert datatypes from one form to another

This video will put everything you've learnt about datatypes to practice!

This video provides an introduction to aggregation and why it is important:
1. What does it mean to aggregate data?

2. Why is it important to understand how to do so?

Note: The slides listed here are for the entire section

This lecture covers the key aggregation concepts:
1. GROUP BY statement, which is often used in conjunction with aggregation functions

2. Common aggregation functions like SUM(), AVG(), COUNT() and more!

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding the GROUP BY and aggregation functions, covering some realistic use cases.

Please attempt the questions yourself before moving to the solutions!

This lecture introduces the HAVING statement, which allows you to mirror the WHERE statement post-aggregation

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding the HAVING statement and how to use it.

Please attempt the questions yourself before moving to the solutions!

This section covers Joins, which are a really important (albeit challenging) concept in SQL. To understand joins, we need to first understand what an ERD is, and how we can use it to visually conceptualize the structure of the database.

Note: The slides listed here are for the entire section

This lecture covers the INNER JOIN concept:
1. What is an INNER JOIN?

2. How can I use an INNER JOIN?

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding INNER JOIN.

Please attempt the questions yourself before moving to the solutions!

This lecture covers the LEFT JOIN concept:
1. What is an LEFT JOIN? How is it different from an INNER JOIN?

2. How can I use an LEFT JOIN?

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding LEFT JOIN.

Please attempt the questions yourself before moving to the solutions!

This lecture covers the RIGHT JOIN concept:
1. What is an RIGHT JOIN? Why do we not need to use them?

This lecture covers the FULL OUTER JOIN concept:
1. What is a full outer join? When should you use this join over the others?

2. Full outer joins return all records in both tables, even when they are not being referenced by either table. Missing references are replaced with null values

This lecture covers the SELF JOIN concept:
1. Why do you need self joins to join a table to itself?

2. Self joins can be done using any of the previous joins

This lecture covers the practice exercises for the concepts covered in the previous lectures regarding the various types of joins and their applications.

Please attempt the questions yourself before moving to the solutions!

This lecture covers the UNION concept:
1. What is the difference between UNION and UNION ALL?

2. UNION returns the values (excluding duplicates) from tables that are being appended together.

3. UNION ALL returns all the values (including duplicates) from tables that are being appended together.

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding UNIOON and UNION ALL

Please attempt the questions yourself before moving to the solutions!

This video provides an overview of conditional statements, as well as introducing the CASE statement, which is used to define a simple decision tree

Note: The slides listed here are for the entire section

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding the CASE statement and general conditional expressions.

Please attempt the questions yourself before moving to the solutions!

This lecture introduces the NULLIF function, which compares 2 arguments and returns NULL if they are the same.

This lecture introduces the Coalesce function, which returns the first non-null argument in a list.

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding the Nullif and Coalesce functions

Please attempt the questions yourself before moving to the solutions!

This video provides an overview of subqueries and how to use them. This section will also include an introduction to common table expressions.

Note: The slides listed here are for the entire section

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding subqueries.

Please attempt the questions yourself before moving to the solutions!

This lecture introduces the Common Table Expressions, which are a way to temporarily store subqueries for use within a query.

This lecture covers the practice exercises for the concepts covered in the previous lecture regarding CTEs - the idea behind this exercise is to simulate a situation where you have to re-purpose another person's queries, as well as to use CTEs to organize the query.

Please attempt the questions yourself before moving to the solutions!

This video provides an overview of window functions, specifically their syntax and differences from aggregation functions.

Note: The slides listed here are for the entire section

This lecture introduces the aggregation window functions, which allows for calculations of aggregated values across the window / frame

This lecture introduces the ranking window functions, and teaches the student to discern the various types of ranking functions available

This lecture introduces the positional window functions, which allows students to pull positionally relative values and run calculations on them

This lecture covers the practice exercises for the concepts covered in this chapter on Window Functions and how they might be applied in real world scenarios.

Please attempt the questions yourself before moving to the solutions!

This final section goes through the SQL cheat sheet and covers all the concepts we've learnt in this course at a high level. Students should use this as a refresher guide and revisit any concepts they might be unfamiliar with!