Unit 1: Advanced SQL
In this unit, we will look at SQL DML commands beyond the basic "select", "join", and "group by", as you will sometimes find it useful to filter a group using a having clause and/or to perform subqueries, which can be used to compare two results set in special ways. We will also study the self-join as well as several set-theoretic operators.
Completing this unit should take you approximately 13 hours.
1.1: Relational Algebra Review and Join Commands
Read slides 3 through 33 (pages 2-17). As you read this lecture be sure to think of how the necessary mathematical operations are likely to affect the outcome of your database actions.
1.2: Additional Join Operations
Read slides 3 through 48 (pages 3-24).
1.2.1: SELF Join
Read this page for a solid overview of the SELF JOINS command and how it is used to join a table. Practice utilizing the SQL commands using MySQL or another available SQL database.
1.2.2: FULL Joins
Read this page for a solid overview of the FULL JOINS command and how it is used to achieve a full join of the left and right outer joins of a table. Practice utilizing the SQL commands using MySQL or another available SQL database.
1.3: Set-Theoretic Operators
Read this page for an overview of using the Union, Union All, Minus, and Intersect operators in SQL.
1.4: The HAVING Clause
Read slides 3 through 31 (pages 3-16).
Read this page for a solid overview of the basics of using the HAVING clause command in SQL. Practice utilizing the SQL commands using MySQL or another available SQL database.
1.5: Views
Read this page for an overview of views and indexes in SQL.