SQL Advanced

Overview

Following the Basic SQL course, this course incorporates lecture, lab and practical exercises to teach students how to enhance concurrent database operations both for performance and correctness as well as creating advanced queries for analysis. Topics covered include using indexes, transactions and subqueries and other advanced query forms.

The course may be customized by request to cover any of these topics in more depth or to substitute other topics such as writing and using stored procedures and triggers, advanced data constraints such as cascading updates and deletes, etc. The course can be adapted for use with any of the major commercial Relational Database Management Systems (RDBMS).

Benefits

Upon successful completion of this course, students should be able to use SQL to:

• create and use indexes to improve performance of queries
• use transaction control to ensure correctness of data operations
• build advanced queries including subqueries and set operations
• summarize data columns using ROLLUP and CUBE
• calculate mode, median and standard deviation on data

Intended Audience

This course is recommended for advanced database application programmers and system administrators, database programmers, analysts, and power end users interested in gaining an ability to create advanced SQL queries on a database.

Prerequisites

Students should have a good understanding of basic SQL including using SELECT statements with GROUP BY, ORDER BY and aggregate functions. Facility with using views and join queries is also desirable.

On-site System Requirements

In order for students to do the labs, they must have a computer with client software able to connect to a test server for the RDBMS they will be using.

Although a number of operating Systems can be used, the course is usually taught in a Windows or UNIX/Linux environment with access to at least one machine with the target RDBMS installed. It is preferable to have the database server installed on each student machine.

Suggested Follow-On Courses

Database Design (any RDBMS), Database Administration (Microsoft SQL Server).

Training Approach

This is an intensive, interactive course, which is approximately 70% lecture and 30% lab. Questions are highly encouraged.

Recommended Texts (if desired, as additional resource):

SQL Fundamentals, 2nd edition , John Patrick, Prentice-Hall.
Transact-SQL Desk Reference: For Microsoft SQL Server, Deac Lancaster, Prentice-Hall.

Outline

Chapters 1 – 3 are used for the Basic SQL course. This is a continuation of that course.

Day 1

Chapter 4 —Indexes, Transaction Control, and Privileges

• Indexes
• B-Tree Index Structure: Access Path to the Data Table
• Creating an Index
• Transaction Control in ANSI SQL
• Starting and Ending a Transaction
• Transactions and Backup and Recovery
• Transaction Control: Locking & Isolation Levels
• Locking - maintains data integrity while having concurrency
• Isolation Levels
• Implementation of the Isolation Levels using Locks (MSS)
• Setting Transaction Properties - Isolation Level
• Isolation Levels in Oracle
• Isolation Levels in MS SQL Server
• Security Concepts and Access Privileges
• Constraints in ANSI SQL-92
• Column and Table Constraints
• Uniqueness Constraints and Primary Key Constraints
• Foreign Key Constraints
• Referential Integrity (RI)
• Check Constraints

• Constraint Timing in SQL-92

• Views with Check Option, controlling Updates through a View

Lab Exercises - Chapter 4

Day 2

Chapter 5 — Subqueries and Other Advanced Queries

• SUBQUERIES - In INSERT, UPDATE, DELETE and SELECT
• Subqueries in the SELECT column-list
• Correlated Subquery
• BETWEEN - Range Queries
• IN, NOT IN
• EXISTS, NOT EXISTS
• NOT IN vs. NOT EXISTS
• (SOME/ANY) and ALL
• Set Operations With Tables
• UNION, UNION ALL, INTERSECT, EXCEPT

Chapter 6 — Queries for Analysis

• OUTER JOIN To Count Matches, Including Zero
• Left Outer Join, Right Outer Join, Full Outer Join, Union Join
• Using ROLLUP and CUBE to total and subtotal data values
• Statistics in SQL
• MODE, MEDIAN, and VARIANCE and STANDARD DEVIATION
• Relational Division

Lab Exercises - Chapter 6