SQL Language Course Outlines

SQL Language Introduction

Course Duration

One Day

Course Level

Introduction

Course Contents

OVERVIEW OF SQL

THE SELECT STATEMENT

  • Aliasing Column Names
  • The From Clause
  • The Select Distinct Statement
  • Applied In Line Comments
  • Applied Block Comments

SORTING RETRIEVED DATA

  • Order By Statement

CALCULATED FIELDS

  • Used concatenation
  • Used mathematical operators
  • Understood aggregate (group) functions
    • Sum function
    • Avg function
    • Min function
    • Max function
    • Count function
  • Used the group by statement
  • Used the having clause
  • Used the IsNull function
  • Used Case functions
  • Used the Convert function

FILTERING DATA

  • Using the Where Clause
  • Used and & or operators
  • Used the between and not between operators
  • to select values within an inclusive range
  • Used the in operator to specify multiple values
  • in a where clause
  • Search for data using the like operator and
  • SQL wildcards

INSERTING DATA

UPDATING RECORDS

DELETING RECORDS

UNDERSTANDING THE PRINCIPLES OF RELATIONAL DATABASES

  • Inner Joins
  • Left Join
  • Right Join
  • Full Outer Join

USING A RELATIONAL DATABASE TO RETRIEVE DATA

  • Joined two tables together
  • Extracted data from related tables
  • Joined multiple tables
  • Extracted information from multiple tables

SQL Language Advanced

Course Duration

One Day

Course Level

Advanced

Course Contents

BUILDING THE DATABASE SCHEMA

  • Creating tables and columns
  • Building tables with CREATE TABLE
  • Modifying table structure with ALTER TABLE
  • Adding columns to an existing table
  • Removing tables with DROP TABLE

WORKING WITH CONSTRAINTS

  • Guaranteeing uniqueness with primary key constraints
  • Enforcing integrity with foreign key constraint
  • Imposing business rules with check constraints

IMPROVE PERFORMANCE WITH INDEXES

  • Expediting data retrieval with indexes
  • Recommending guidelines for index creation

MANIPULATING DATA

  • Modifying table contents
  • Adding table rows with INSERT
  • Changing row content with UPDATE
  • Removing rows with DELETE

APPLYING TRANSACTIONS

  • Controlling transactions with COMMIT and ROLLBACK
  • Deploying BEGIN TRANSACTION in SQL Server

COMBINING RESULTS WITH SET OPERATORS

  • Stacking results with UNION
  • Identifying matching rows with INTERSECT
  • Utilising EXCEPT to find nonmatching rows

EMPLOYING FUNCTIONS IN DATA RETRIEVAL

  • Processing data with row functions
  • Conditional formatting with the CASE expression
  • Utilising the CASE expression to simulate IF tests
  • Dealing with NULL values

CONSTRUCTING NESTED QUERIES

  • Applying subqueries in filter conditions
  • Correlated vs. noncorrelated subqueries
  • Testing the existence of rows

INCLUDING SUBQUERIES IN EXPRESSIONS

  • Placing subqueries in the column list
  • Creating complex expressions containing subqueries
  • Handling subqueries that return no rows

DEVELOPING IN-LINE AND STORED VIEWS

  • Breaking down complex problems
  • Selecting data from a query result set
  • Subqueries in the FROM clause

CREATING VIEWS IN A DATABASE

  • Building reusable code
  • Updateable vs. non-updateable views

SQL Language Introduction

Course Duration

One Day

Course Level

Introduction

Course Contents

OVERVIEW OF SQL

THE SELECT STATEMENT

  • Aliasing Column Names
  • The From Clause
  • The Select Distinct Statement
  • Applied In Line Comments
  • Applied Block Comments

SORTING RETRIEVED DATA

  • Order By Statement

CALCULATED FIELDS

  • Used concatenation
  • Used mathematical operators
  • Understood aggregate (group) functions
    • Sum function
    • Avg function
    • Min function
    • Max function
    • Count function
  • Used the group by statement
  • Used the having clause
  • Used the IsNull function
  • Used Case functions
  • Used the Convert function

FILTERING DATA

  • Using the Where Clause
  • Used and & or operators
  • Used the between and not between operators
  • to select values within an inclusive range
  • Used the in operator to specify multiple values
  • in a where clause
  • Search for data using the like operator and
  • SQL wildcards

INSERTING DATA

UPDATING RECORDS

DELETING RECORDS

UNDERSTANDING THE PRINCIPLES OF RELATIONAL DATABASES

  • Inner Joins
  • Left Join
  • Right Join
  • Full Outer Join

USING A RELATIONAL DATABASE TO RETRIEVE DATA

  • Joined two tables together
  • Extracted data from related tables
  • Joined multiple tables
  • Extracted information from multiple tables

SQL Language Advanced

Course Duration

One Day

Course Level

Advanced

Course Contents

BUILDING THE DATABASE SCHEMA

  • Creating tables and columns
  • Building tables with CREATE TABLE
  • Modifying table structure with ALTER TABLE
  • Adding columns to an existing table
  • Removing tables with DROP TABLE

WORKING WITH CONSTRAINTS

  • Guaranteeing uniqueness with primary key constraints
  • Enforcing integrity with foreign key constraint
  • Imposing business rules with check constraints

IMPROVE PERFORMANCE WITH INDEXES

  • Expediting data retrieval with indexes
  • Recommending guidelines for index creation

MANIPULATING DATA

  • Modifying table contents
  • Adding table rows with INSERT
  • Changing row content with UPDATE
  • Removing rows with DELETE

APPLYING TRANSACTIONS

  • Controlling transactions with COMMIT and ROLLBACK
  • Deploying BEGIN TRANSACTION in SQL Server

COMBINING RESULTS WITH SET OPERATORS

  • Stacking results with UNION
  • Identifying matching rows with INTERSECT
  • Utilising EXCEPT to find nonmatching rows

EMPLOYING FUNCTIONS IN DATA RETRIEVAL

  • Processing data with row functions
  • Conditional formatting with the CASE expression
  • Utilising the CASE expression to simulate IF tests
  • Dealing with NULL values

CONSTRUCTING NESTED QUERIES

  • Applying subqueries in filter conditions
  • Correlated vs. noncorrelated subqueries
  • Testing the existence of rows

INCLUDING SUBQUERIES IN EXPRESSIONS

  • Placing subqueries in the column list
  • Creating complex expressions containing subqueries
  • Handling subqueries that return no rows

DEVELOPING IN-LINE AND STORED VIEWS

  • Breaking down complex problems
  • Selecting data from a query result set
  • Subqueries in the FROM clause

CREATING VIEWS IN A DATABASE

  • Building reusable code
  • Updateable vs. non-updateable views