SQL: A BEGINNER'S GUIDE 3/E | Beginner's Guide

SQL: A BEGINNER'S GUIDE 3/E

Authors: Andy Oppel and Robert Sheldon

Published: August 2008

eISBN: 9780071548656 0071548653 | ISBN: 9780071548649

Contents

Acknowledgments

Introduction

Part I: Relational Databases and SQL

1 Introduction to Relational Databases and SQL

Understand Relational Databases

Learn About SQL

2 Working with the SQL Environment

Understand the SQL Environment

Understand SQL Catalogs

Name Objects in an SQL Environment

Create a Schema

Create a Database

3 Creating and Altering Tables

Create SQL Tables

Specify Column Data Types

Create User-Defined Types

Specify Column Default Values

Delete SQL Tables

4 Enforcing Data Integrity

Understand Integrity Constraints

Use NOT NULL Constraints

Add UNIQUE Constraints

Add PRIMARY KEY Constraints

Add FOREIGN KEY Constraints

Define CHECK Constraints

5 Creating SQL Views

Add Views to the Database

Create Updateable Views

Drop Views from the Database

6 Managing Database Security

Understand the SQL Security Model

Create and Delete Roles

Grant and Revoke Privileges

Grant and Revoke Roles

Part II: Data Access and Modification

7 Querying SQL Data

Use a SELECT Statement to Retrieve Data

Use the WHERE Clause to Define Search Conditions

Use the GROUP BY Clause to Group Query Results

Use the HAVING Clause to Specify Group Search Conditions

Use the ORDER BY Clause to Sort Query Results

8 Modifying SQL Data

Insert SQL Data

Update SQL Data

Delete SQL Data

9 Using Predicates

Compare SQL Data

Return Null Values

Return Similar Values

Reference Additional Sources of Data

Quantify Comparison Predicates

10 Working with Functions and Value Expressions

Use Set Functions

Use Value Functions

Use Value Expressions

Use Special Values

11 Accessing Multiple Tables

Perform Basic Join Operations

Join Tables with Shared Column Names

Use the Condition Join

Perform Union Operations

12 Using Subqueries to Access and Modify Data

Create Subqueries That Return Multiple Rows

Create Subqueries That Return One Value

Work with Correlated Subqueries

Use Nested Subqueries

Use Subqueries to Modify Data

Part III: Advanced Data Access

13 Creating SQL-Invoked Routines

Understand SQL-Invoked Routines

Create SQL-Invoked Procedures

Add Input Parameters to Your Procedures

Add Local Variables to Your Procedures

Work with Control Statements

Add Output Parameters to Your Procedures

Create SQL-Invoked Functions

14 Creating SQL Triggers

Understand SQL Triggers

Create SQL Triggers

Create Insert Triggers

Create Update Triggers

Create Delete Triggers

15 Using SQL Cursors

Understand SQL Cursors

Declare a Cursor

Open and Close a Cursor

Retrieve Data from a Cursor

Use Positioned UPDATE and DELETE Statements

16 Managing SQL Transactions

Understand SQL Transactions

Set Transaction Properties

Start a Transaction

Set Constraint Deferability

Create Savepoints in a Transaction

Terminate a Transaction

17 Accessing SQL Data from Your Host Program

Invoke SQL Directly

Embed SQL Statements in Your Program

Create SQL Client Modules

Use an SQL Call-Level Interface

18 Working with XML Data

Learn the Basics of XML

Learn About SQL/XML

Part IV: Appendices

A: Answers to Self Test

B: SQL:2006 Keywords

SQL Reserved Keywords

SQL Nonreserved Keywords

C: SQL Code Used in Try This Exercises

SQL Code by Try This Exercise

The INVENTORY Database

Index