Introduction to SQL

Outline

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