SQL Joins and Subqueries Tutorial
Read Now
The ability to write the SQL language - the cornerstone of all relational database operations - is essential for anyone who develops database applications.
In this SQL Training course you will learn how to retrieve large amounts of data quickly and efficiently. Using SQL, you will be able to write basic to complex queries, build reporting solutions or implement data warehouses.
What you will learn in this SQL Training course
- What is SQL?
- Working with Relational Databases
- SELECT statements
- Aggregate Functions
- SQL Built-in Functions
- Joins and Subqueries
- CASE Statements and Unions
SQL Training Prerequisistes
No Prerequisites
Duration
2 Days
Outline for Introduction to SQL Training
Chapter 1. Introduction to Relational Databases
- What is a Relational Database?
- Relational Database Management System (RDBMS)
- Popular RDBMS Products
- Relational Database Concepts
- Tables
- Rows/Records
- Columns/Fields
- Data Types
- Relationships
- Primary Keys
- Foreign Keys
- What is SQL?
- Brief History of SQL
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Different Flavors of SQL
- AdventureWorks and Northwind
- Lab 1: Exploring the Northwind Database
- Summary
Chapter 2. Introduction to SELECT Statements
- What is a SELECT Statement?
- Comments
- Multi-line Comments
- Case Insensitivity
- SELECT Statement Syntax
- Minimal SELECT Statement
- Complete SELECT Statement
- Simple SELECT Statement
- Whitespace
- Multiple Statements
- Lab 2: Selecting Records
- Selecting Individual Columns
- Lab 3: Retrieving Data
- Sorting Rows using ORDER BY Clause
- Lab 4: Sorting Data
- Selecting Unique Rows using DISTINCT Keyword
- Lab 5: Retrieving Unique Data
- Summary
Chapter 3. SELECT Statements with WHERE Clause
- Filtering Results using WHERE Clause
- Comparison Operators
- Lab 6: Comparison Operators
- NULL Values
- Testing for NULL
- ISNULL, NVL, IFNULL
- Lab 7: NULL Tests
- Combining WHERE and ORDER BY Clauses
- Lab 8: WHERE and ORDER BY Clauses
- Logical Operators - AND, OR
- Logical Operators – NOT
- Logical Operators - Order of Evaluation
- Lab 9: Multiple Conditions
- BETWEEN Operator
- IN Operator
- LIKE Operator
- Lab 10: Additional Operators
- Summary
Chapter 4. SELECT Statements with Computed Columns
- What are Computed Columns?
- Simple Calculations
- Mixing Calculations and Columns
- Arithmetic Calculations
- Calculations on Columns
- Column Aliases
- Column Aliases
- Omitting the AS keyword
- Using Column Labels in
- ORDER BY
- Clause
- Concatenation
- Concatenating Text and Column Values
- CONCAT Alternatives
- Lab 11: Computed Columns
- Summary
Chapter 5. Aggregate Functions
- What are Aggregate Functions?
- Aggregate Functions – COUNT
- Aggregate Functions – SUM and AVG
- Aggregate Functions – MIN and MAX
- Grouping Results Using GROUP BY Clause
- Grouping and
- Sorting
- Results
- Filtering Grouped Results using HAVING Clause
- Lab 12:
- Aggregate Functions
- Summary
Chapter 6. SQL Built-in Functions
- Types of SQL Built-in Functions
- String Functions
- String Function Examples
- Date Functions
- Date Function Examples
- Math
- Functions
- Math
- Function Examples
- Math
- Function Examples
- Lab 13: SQL Built-in
- Functions
- Summary
Chapter 7. Joins and Subqueries
- Primary Keys and Foreign Key
- Inner Joins
- Inner Join Examples
- Prefixing Columns with Table Names
- Using Table Aliases
- Alternate Inner Join Syntax
- Outer Joins
- Left Outer Joins
- Left Outer Join Examples
- Right Outer Joins
- Full Outer Joins
- Self Joins
- Self Join Example
- Lab 14: Inner and Outer Joins
- What is a Subquery?
- Subquery Examples
- Regular
- Subquery vs. Correlated Subquery
- Correlated Subquery Example
- Lab 15: Subqueries
- Summary
Chapter 8. CASE Statements and UNIONs
- CASE Statements
- CASE Statement Examples
- Lab 16:
- UNION Clause
- UNION Example
- UNION ALL
- INTERSECT Clause
- EXCEPT/MINUS Clause
- Lab 17:
- Summary
Lab Exercises
Lab 1. Exploring the Database
Lab 2. Selecting Records
Lab 3. Retrieving Data
Lab 4. Sorting Data
Lab 5. Retrieving Unique Data
Lab 6. Comparison Operators
Lab 7. NULL Tests
Lab 8. WHERE and ORDER BY Clauses
Lab 9. Multiple Conditions
Lab 10. Additional Operators
Lab 11. Computed Columns
Lab 12. Aggregate Functions
Lab 13. SQL Built-in Functions
Lab 14. Inner and Outer Joins
Lab 15. Subqueries
Lab 16. CASE Statements
Lab 17. UNIONs