Introduction to SQL

This SQL training course is designed for students new to writing SQL queries. A typical student will need to learn SQL to build applications or to generate business reports. For the most part, the SQL learned in the course is applicable to all major databases. In the few cases in which there are differences between the databases, these differences will be pointed out.

Learning Objectives

• Understand how relational databases work
• Learn to use SQL to output reports
• Learn to write queries getting data from multiple tables

Course Outline

1. Relational Database Basics
   1. Brief History of SQL
   2. Relational Databases

                                                              i.      Tables

                                                            ii.      Rows

                                                          iii.      Columns

                                                          iv.      Relationships

                                                            v.      Datatypes

                                                          vi.      Primary Keys

                                                        vii.      Foreign Keys

                                                      viii.      Relational Database Management System

3. Popular Databases

                                                              i.      Commercial Databases

                                                            ii.      Popular Open Source Databases

                                                          iii.      Valid Object References

4. SQL Statements

                                                              i.      Database Manipulation Language (DML)

                                                            ii.      Database Definition Language (DDL)

                                                          iii.      Database Control Language (DCL)

2. Simple SELECTs
   1. Introduction to the Northwind Database
   2. Some Basics

                                                              i.      Comments

                                                            ii.      Whitespace and Semi-colons

                                                          iii.      Case Sensitivity

3. SELECTing All Columns in All Rows
4. Exploring the Tables
5. SELECTing Specific Columns
6. Sorting Records

                                                              i.      Sorting By a Single Column

                                                            ii.      Sorting By Multiple Columns

                                                          iii.      Sorting By Column Position

                                                          iv.      Ascending and Descending Sorts

7. The WHERE Clause and Operator Symbols

                                                              i.      Checking for Equality

                                                            ii.      Checking for Inequality

                                                          iii.      Checking for Greater or Less Than

                                                          iv.      Checking for NULL

                                                            v.      WHERE and ORDER BY

8. Using the WHERE clause to check for equality or inequality
9. Using the WHERE clause to check for greater or less than
10. Checking for NULL
11. Using WHERE and ORDER BY Together
12. The WHERE Clause and Operator Words

                                                              i.      The BETWEEN Operator

                                                            ii.      The IN Operator

                                                          iii.      The LIKE Operator

                                                          iv.      The NOT Operator

13. More SELECTs with WHERE
14. Checking Multiple Conditions

                                                              i.      AND

                                                            ii.      OR

                                                          iii.      Order of Evaluation

15. Writing SELECTs with Multiple Conditions

3. Advanced SELECTs
   1. Calculated Fields

                                                              i.      Concatenation

                                                            ii.      Mathematical Calculations

                                                          iii.      Aliases

2. Calculating Fields
3. Aggregate Functions and Grouping

                                                              i.      Aggregate Functions

                                                            ii.      Grouping Data

                                                          iii.      Selecting Distinct Records

4. Working with Aggregate Functions
5. Built-in Data Manipulation Functions

                                                              i.      Common Math Functions

                                                            ii.      Common String Functions

                                                          iii.      Common Date Functions

6. Data Manipulation Functions

4. Subqueries, Joins and Unions
   1. Subqueries
   2. Joins

                                                              i.      Table Aliases

                                                            ii.      Multi-table Joins

3. Using Joins
4. Outer Joins
5. Unions

                                                              i.      UNION ALL

                                                            ii.      UNION Rules

6. Working with Unions

5. If time allows, one or more of the following may be covered:
   1. Conditional Processing with CASE
   2. INSERT, UPDATE, DELETE
   3. Student Challenges - Design your own reports

6. SQL Best Practices and Performance Optimization