Course 508:
SQL Essentials
(Available on Google BigQuery, MySQL, PostgreSQL, Oracle DB, Microsoft SQL, IBM DB2)

(2 days)

 

Course Description

In today’s data-intensive environment, Business intelligence (BI) is a key to decision making through the application of skills, processes, business practices, and software technologies. This course provides the starting point to understand and apply Structured Query Language (SQL) to a database to retrieve answers from stored business data. This course is ANSI-SQL compliant and supports all major database platforms. Attendees will explore the power of the SQL SELECT statement to write and test data queries.

Learning Objectives

  • Write SQL database queries that create answers to business questions
  • Establish a foundation in the SQL Server language
  • Design single-table and multiple-table queries
  • Manipulate row and column data using built-in functions
  • Retrieve Data using SQL Management Tools and Microsoft Excel

Who Should Attend

Analysts, programmers, and team leaders new to SQL will find it helpful to understand the language on how data is retrieved from today’s business intelligence systems, and to communicate with data managers/administrators. No prior programming knowledge or knowledge of SQL is assumed.

Prerequisites

Familiarity with data to the level of working comfortably in a tool like Microsoft Excel.

Hands-On Work

This course is hands-on, and the course can be taught using the most appropriate database used in your environment, including current versions of Microsoft SQL Server, Oracle, IBM DB2, or MySQL.

Course Outline

Unit 1: The Relational Model – Part 1 – What is SQL?

  • What Is SQL and Why Does It Matter to Me?
  • Data Storage Evolution
  • Brief History of SQL
  • Introduction to Database Management Systems
  • Building Blocks: Tables of Rows and Columns
  • What Does Select * from Table
  • Configuring Your Query Tool
  • Exercise 1.1: Asking the Database a Question
  • Unit Summary

Unit 2: Structured Query Language – SELECT Statements

  • SQL Select Command
  • Data Manipulation Language (DML)
  • A Few SQL Ground Rules
  • Getting the Answers to Business Questions
  • Read-Only Nature of SELECT
  • Retrieving Columns by Name
  • Column Correlation Names (Aliases)
  • Exercise 2.1: Basic SELECT Statement
  • Result Set
  • Retrieving Columns without Using Column Names
  • Retrieving Rows with DISTINCT
  • Sorting Rows Using ORDER BY
  • Exercise 2.2: Controlling the SELECT Statement
  • Unit Summary

Unit 3: Limiting the SELECT Statement

  • The Wisdom of Limiting Rows Retrieved
  • Queries Using Row Limiting
  • Row Limiting Using Sampling
  • Exercise 3.1: Limiting Number of Rows Returned
  • The WHERE Clause
  • Adding a Simple WHERE Clause to a Query
  • WHERE Clause Comparison Operators
  • Multiple Conditions within a Single WHERE Using AND, OR, NOT
  • Exercise 3.2: Using the WHERE Clause
  • The Challenge Ahead: Someone Else’s Data Model
  • Data Dictionary
  • Exploring a Data Model with Single Table Queries
  • Describing the Structure of a Table
  • Exercise 3.3: Exploring Tables with the SELECT Statement
  • Unit Summary

Unit 4: The Relational Model – Part 2 – Database Modeling

  • The Relational Model and Conceptual Modeling
  • Entity-Relationship Diagrams (ERDs)
  • Cardinality and Optionality Depict Business Rules
  • Logical Modeling
  • Generalization vs. Specialization
  • Physical Modeling
  • Primary Keys
  • Candidate/Surrogate/Unique Keys
  • Foreign Keys
  • Indexing
  • Exercise 4.1: Database Modeling
  • Unit Summary

Unit 5: Multiple Table Selects

  • Getting Answers to Complex Business Questions
  • Table Joins
  • Specifying Join Columns Correctly
  • Table Correlation Names (Aliases)
  • Exercise 5.1: The Multiple Table SELECT
  • Revisiting Database Keys from a Join Perspective
  • Types of Joins and Join Syntax
  • Inner Join Example
  • Left Outer Join Example
  • Right Outer Join Example
  • Full Outer Join Example
  • Cartesian Product
  • How Many Tables Can Be Joined?
  • Exercise 5.2: Exploring Multiple Table Joins
  • Database Views
  • The Role of Views from a Join Perspective
  • Querying a View
  • Creating a View
  • Exercise 5.3: Using Database Views
  • Unit Summary

Unit 6: The Relational Model – Part 3 – Normalization

  • What is Normalization?
  • Normalization Forms Most Often Practiced
  • 1st Normal Form
  • 2nd Normal Form
  • 3rd Normal Form
  • What Happens If I Don’t Normalize?
  • Anomalies Solved by Normalization
  • Exercise 6.1: Database Normalization
  • Unit Summary

Unit 7: Filtering Row Data

  • Revisiting the WHERE Clause
  • Keywords Used with the WHERE Clause
  • The BETWEEN Predicate
  • The NULL Predicate
  • The IN Predicate
  • The LIKE Predicate
  • Exercise 7.1: Filtering Row Data
  • WHERE Clause Comparison Operators Revisited
  • Understanding the WHERE Clause in Table Joins
  • Exercise 7.2: Table Joins and the WHERE Clause
  • Unit Summary

Unit 8: Column Calculations

  • Operations on Columns Using Functions
  • Data Types
  • Scalar and Aggregate Functions Defined
  • Numeric/Math Functions
  • Exercise 8.1: Using a Selection of Numeric Functions
  • String Functions
  • Date/Time Functions
  • Specialty Functions: COALESCE, NULLIF, CAST
  • Finding Out What Functions are Available in a Database
  • Exercise 8.2: String and Date Manipulation
  • Using Aggregate Functions
  • Count and Sum Functions
  • Min, Max, Least, Greatest Functions
  • Aggregates for Statistics
  • Exercise 8.3: Aggregate Functions
  • Unit Summary

Unit 9: Grouping Data

  • Overview of Subqueries
  • SOME and ANY with Subqueries
  • EXISTS with Subqueries
  • Exercise 9.1: Subqueries
  • Row Data Grouping
  • GROUP BY Clause
  • Group Data Filtering
  • Exercise 9.2: Grouping Row Data
  • Removing GROUP BY Results Based on Criteria
  • HAVING Clause
  • Exercise 9.3: Using HAVING with Grouping Row Data
  • Combining Queries into a Single Result Set
  • UNION, INTERSECT, and MINUS operators
  • Subqueries
  • Exercise 9.4: Combining Queries
  • Unit Summary

Unit 10: Beyond the SELECT Statement

  • Four Classes of SQL Statements
  • Data Manipulation Language (DML)
  • Definition Language (DDL)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)
  • Exercise 10.1: Exploring Beyond the Select Statement
  • SQL Programming
  • User-Defined Functions
  • The Answer: SQL + Host Language
  • Exercise 10.2: SQL Programming Examples
  • A Good DBMS Demonstrates Four Essential Qualities
  • Locking Strategies
  • Unit Summary

Unit 11: Course Summary

  • The Query Writing Process
  • Gather the Requirements
  • Identify the Tables Needed
  • Identify the Columns Needed
  • Choose the Join Type
  • Write the Query
  • Test the Results
  • A Baker’s Dozen of Performance Tips for Query Writers

Please Contact Your ROI Representative to Discuss Course Tailoring!