Table of Contents

Part I: An SQL Concepts Overview

Hour 1. Welcome to the World of SQL

SQL Definition and History

SQL Sessions

Types of SQL Commands

The Database Used in This Book

Summary

Q&A

Workshop

Part II: Building Your Database

Hour 2. Defining Data Structures

What Is Data?

Basic Data Types

Summary

Q&A

Workshop

Hour 3. Managing Database Objects

What Are Database Objects?

What Is a Schema?

Tables: The Primary Storage for Data

Integrity Constraints

Summary

Q&A

Workshop

Hour 4. The Normalization Process

Normalizing a Database

Denormalizing a Database

Summary

Q&A

Workshop

Hour 5. Manipulating Data

Overview of Data Manipulation

Populating Tables with New Data

Updating Existing Data

Deleting Data from Tables

Summary

Q&A

Workshop

Hour 6. Managing Database Transactions

What Is a Transaction?

Controlling Transactions

Transactional Control and Database Performance

Summary

Q&A

Workshop

Part III: Getting Effective Results from Queries

Hour 7. Introduction to the Database Query

What Is a Query?

Introduction to the SELECT Statement

Examples of Simple Queries

Summary

Q&A

Workshop

Hour 8. Using Operators to Categorize Data

What Is an Operator in SQL?

Comparison Operators

Logical Operators

Conjunctive Operators

Negative Operators

Arithmetic Operators

Summary

Q&A

Workshop

Hour 9. Summarizing Data Results from a Query

What Are Aggregate Functions?

Summary

Q&A

Workshop

Hour 10. Sorting and Grouping Data

Why Group Data?

The GROUP BY Clause

GROUP BY Versus ORDER BY

CUBE and ROLLUP Expressions

The HAVING Clause

Summary

Q&A

Workshop

Hour 11. Restructuring the Appearance of Data

ANSI Character Functions

Common Character Functions

Miscellaneous Character Functions

Mathematical Functions

Conversion Functions

Combining Character Functions

Summary

Q&A

Workshop

Hour 12. Understanding Dates and Times

How Is a Date Stored?

Date Functions

Date Conversions

Summary

Q&A

Workshop

Part IV: Building Sophisticated Database Queries

Hour 13. Joining Tables in Queries

Selecting Data from Multiple Tables

Understanding Joins

Join Considerations

Summary

Q&A

Workshop

Hour 14. Using Subqueries to Define Unknown Data

What Is a Subquery?

Embedded Subqueries

Correlated Subqueries

Subquery Performance

Summary

Q&A

Workshop

Hour 15. Combining Multiple Queries into One

Single Queries Versus Compound Queries

Compound Query Operators

Using ORDER BY with a Compound Query

Using GROUP BY with a Compound Query

Retrieving Accurate Data

Summary

Q&A

Workshop

Part V: SQL Performance Tuning

Hour 16. Using Indexes to Improve Performance

What Is an Index?

How Do Indexes Work?

The CREATE INDEX Command

Types of Indexes

When Should Indexes Be Considered?

When Should Indexes Be Avoided?

Altering an Index

Dropping an Index

Summary

Q&A

Workshop

Hour 17. Improving Database Performance

What Is SQL Statement Tuning?

Database Tuning Versus SQL Statement Tuning

Formatting Your SQL Statement

Full Table Scans

Other Performance Considerations

Cost-Based Optimization

Performance Tools

Summary

Q&A

Workshop

Part VI: Using SQL to Manage Users and Security

Hour 18. Managing Database Users

User Management in the Database

The Management Process

Tools Utilized by Database Users

Summary

Q&A

Workshop

Hour 19. Managing Database Security

What Is Database Security?

What Are Privileges?

Controlling User Access

Controlling Privileges Through Roles

Summary

Q&A

Workshop

Part VII: Summarized Data Structures

Hour 20. Creating and Using Views and Synonyms

What Is a View?

Creating Views

WITH CHECK OPTION

Creating a Table from a View

Views and the ORDER BY Clause

Updating Data Through a View

Dropping a View

Performance Impact of Using Nested Views

What Is a Synonym?

Summary

Q&A

Workshop

Hour 21. Working with the System Catalog

What Is the System Catalog?

How Is the System Catalog Created?

What Is Contained in the System Catalog?

System Catalog Tables by Implementation

Querying the System Catalog

Updating System Catalog Objects

Summary

Q&A

Workshop

Part VIII: Applying SQL Fundamentals in Today’s World

Hour 22. Advanced SQL Topics

Cursors

Stored Procedures and Functions

Triggers

Dynamic SQL

Call-Level Interface

Using SQL to Generate SQL

Direct Versus Embedded SQL

Windowed Table Functions

Working with XML

Summary

Q&A

Workshop

Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet

SQL and the Enterprise

Accessing a Remote Database

SQL and the Internet

SQL and the Intranet

Summary

Q&A

Workshop

Hour 24. Extensions to Standard SQL

Various Implementations

Example Extensions

Interactive SQL Statements

Summary

Q&A

Workshop

Part IX: Appendixes

Appendix A: Common SQL Commands

Appendix B: Using the Databases for Exercises

Windows Installation Instructions for MySQL

Windows Installation Instructions for Oracle

Windows Installation Instructions for Microsoft SQL Server

Appendix C: Answers to Quizzes and Exercises

Appendix D: CREATE TABLE Statements for Book Examples

Appendix E: INSERT Statements for Data in Book Examples

Appendix F: Glossary

Appendix G: Bonus Exercises

Index