Databases – Advanced SQL
Duration
4 hours
Overview
This course provides you with more in-depth uses of SQL. It begins by examining how data from multiple tables can be joined together, and how different types of JOIN statements can be used to achieve your goal. You will also see how specific data can be selected from tables and how conditional operations can be invoked based on data values. The merging of results from multiple SELECT statements is also presented. Finally, you will see how a SELECT query can exist within another SELECT statement. These are called subqueries.
Audience
This course is suitable for IT professionals who will be working with, or retrieving data from, an enterprise database system.
Prerequisites
Successful completion of the Interskill Databases – Introduction to SQL course, or equivalent knowledge.
Objectives
After completing this course, the student should be able to:
- Describe different types of joins
- Code SQL outer joins, using WHERE and ON statements
- Code DISTINCT and CASE SQL statements
- Use Unions and Views to obtain and view data
- Describe how SQL Subqueries are used
- Identify situations where Subqueries are useful
Course Content
Joining Tables Together
Using the SELECT Statement to Join Tables
CROSS JOIN Statement
Inner and Outer Joins
Using Correlation Names
COALESCE Function
Using ON and WHERE Clauses for Joins
Joins and NULL Values
Features and Functions for Selecting Data
Identifying Unique Values Using the SELECT DISTINCT Statement
Using DISTINCT with Aggregate Functions
CASE Statement
Merging SQL Queries Using the UNION Operator
UNION Rules
Creating and Using Views
Subqueries
Purpose of Subqueries
Multiple Row and Column Subqueries
EXISTS and NOT EXIST Clauses
ANY and ALL Operators
Nested Tables
Rules when Using Subqueries
Correlated Subqueries