SQL: A Beginner's Guide, Third Edition, 3rd Edition

SQL: A Beginner's Guide, Third Edition, 3rd Edition

by Andy Oppel, Robert Sheldon
Released August 2008
Publisher(s): McGraw-Hill
ISBN: 9780071548656

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Essential Skills--Made Easy!

Written to the SQL:2006 ANSI/ISO standard, this easy-to-follow guide will get you started programming in SQL right away. You will learn how to retrieve, insert, update, and delete database data, and perform management and administrative functions. SQL: A Beginner's Guide, Third Edition covers new features, including SQL/XML, and is loaded with updated SQL examples along with notes on using them with the latest RDBMS software versions such as MySQL 5.0, SQL Server 2008, and Oracle Database 11g.

Designed for Easy Learning:

  • Key Skills & Concepts--Lists of specific skills covered in the chapter
  • Ask the Experts--Q&A sections filled with bonus information and helpful tips
  • Try This--Hands-on exercises that show how to apply your skills
  • Notes--Extra information related to the topic being covered
  • Self-Tests--Chapter-ending quizzes to test your knowledge
  • Annotated Syntax--Example code with commentary that describes the programming techniques being illustrated

Table of contents

  1. Cover Page
  2. SQL A Beginner's Guide
  3. About the Auther
  4. Copyright Page
  5. Contents
  6. Acknowledgments
  7. Introduction
  8. PART I Relational Databases and SQL
    1. 1 Introduction to Relational Databases and SQL
      1. Understand Relational Databases
        1. The Relational Model
      2. Learn About SQL
        1. The SQL Evolution
        2. Types of SQL Statements
        3. Types of Execution
        4. SQL Standard versus Product Implementations
    2. 2 Working with the SQL Environment
      1. Understand the SQL Environment
      2. Understand SQL Catalogs
        1. Schemas
        2. Schema Objects
        3. Then What Is a Database?
      3. Name Objects in an SQL Environment
        1. Qualified Names
      4. Create a Schema
      5. Create a Database
    3. 3 Creating and Altering Tables
      1. Create SQL Tables
      2. Specify Column Data Types
        1. String Data Types
        2. Numeric Data Types
        3. Datetime Data Types
        4. Interval Data Type
        5. Boolean Data Type
        6. Using SQL Data Types
      3. Create User-Defined Types
      4. Specify Column Default Values
      5. Delete SQL Tables
    4. 4 Enforcing Data Integrity
      1. Understand Integrity Constraints
      2. Use NOT NULL Constraints
      3. Add UNIQUE Constraints
      4. Add PRIMARY KEY Constraints
      5. Add FOREIGN KEY Constraints
        1. The MATCH Clause
        2. The <referential triggered action> Clause
      6. Define CHECK Constraints
        1. Defining Assertions
        2. Creating Domains and Domain Constraints
    5. 5 Creating SQL Views
      1. Add Views to the Database
        1. Defining SQL Views
      2. Create Updateable Views
        1. Using the WITH CHECK OPTION Clause
      3. Drop Views from the Database
    6. 6 Managing Database Security
      1. Understand the SQL Security Model
        1. SQL Sessions
        2. Accessing Database Objects
      2. Create and Delete Roles
      3. Grant and Revoke Privileges
        1. Revoking Privileges
      4. Grant and Revoke Roles
        1. Revoking Roles
  9. PART II Data Access and Modification
    1. 7 Querying SQL Data
      1. Use a SELECT Statement to Retrieve Data
        1. The SELECT Clause and FROM Clause
      2. Use the WHERE Clause to Define Search Conditions
        1. Defining the WHERE Clause
      3. Use the GROUP BY Clause to Group Query Results
      4. Use the HAVING Clause to Specify Group Search Conditions
      5. Use the ORDER BY Clause to Sort Query Results
    2. 8 Modifying SQL Data
      1. Insert SQL Data
        1. Inserting Values from a SELECT Statement
      2. Update SQL Data
        1. Updating Values from a SELECT Statement
      3. Delete SQL Data
    3. 9 Using Predicates
      1. Compare SQL Data
        1. Using the BETWEEN Predicate
      2. Return Null Values
      3. Return Similar Values
      4. Reference Additional Sources of Data
        1. Using the IN Predicate
        2. Using the EXISTS Predicate
      5. Quantify Comparison Predicates
        1. Using the SOME and ANY Predicates
        2. Using the ALL Predicate
    4. 10 Working with Functions and Value Expressions
      1. Use Set Functions
        1. Using the COUNT Function
        2. Using the MAX and MIN Functions
        3. Using the SUM Function
        4. Using the AVG Function
      2. Use Value Functions
        1. Working with String Value Functions
        2. Working with Datetime Value Functions
      3. Use Value Expressions
        1. Working with Numeric Value Expressions
        2. Using the CASE Value Expression
        3. Using the CAST Value Expression
      4. Use Special Values
    5. 11 Accessing Multiple Tables
      1. Perform Basic Join Operations
        1. Using Correlation Names
        2. Creating Joins with More than Two Tables
        3. Creating the Cross Join
        4. Creating the Self-Join
      2. Join Tables with Shared Column Names
        1. Creating the Natural Join
        2. Creating the Named Column Join
      3. Use the Condition Join
        1. Creating the Inner Join
        2. Creating the Outer Join
      4. Perform Union Operations
    6. 12 Using Subqueries to Access and Modify Data
      1. Create Subqueries That Return Multiple Rows
        1. Using the IN Predicate
        2. Using the EXISTS Predicate
        3. Using Quantified Comparison Predicates
      2. Create Subqueries That Return One Value
      3. Work with Correlated Subqueries
      4. Use Nested Subqueries
      5. Use Subqueries to Modify Data
        1. Using Subqueries to Insert Data
        2. Using Subqueries to Update Data
        3. Using Subqueries to Delete Data
  10. PART III Advanced Data Access
    1. 13 Creating SQL-Invoked Routines
      1. Understand SQL-Invoked Routines
        1. SQL-Invoked Procedures and Functions
        2. Working with the Basic Syntax
      2. Create SQL-Invoked Procedures
        1. Invoking SQL-Invoked Procedures
      3. Add Input Parameters to Your Procedures
        1. Using Procedures to Modify Data
      4. Add Local Variables to Your Procedures
      5. Work with Control Statements
        1. Create Compound Statements
        2. Create Conditional Statements
        3. Create Looping Statements
      6. Add Output Parameters to Your Procedures
      7. Create SQL-Invoked Functions
    2. 14 Creating SQL Triggers
      1. Understand SQL Triggers
        1. Trigger Execution Context
      2. Create SQL Triggers
        1. Referencing Old and New Values
        2. Dropping SQL Triggers
      3. Create Insert Triggers
      4. Create Update Triggers
      5. Create Delete Triggers
    3. 15 Using SQL Cursors
      1. Understand SQL Cursors
        1. Declaring and Opening SQL Cursors
      2. Declare a Cursor
        1. Working with Optional Syntax Elements
        2. Creating a Cursor Declaration
      3. Open and Close a Cursor
      4. Retrieve Data from a Cursor
      5. Use Positioned UPDATE and DELETE Statements
        1. Using the Positioned UPDATE Statement
        2. Using the Positioned DELETE Statement
    4. 16 Managing SQL Transactions
      1. Understand SQL Transactions
      2. Set Transaction Properties
        1. Specifying an Isolation Level
        2. Specifying a Diagnostics Size
        3. Creating a SET TRANSACTION Statement
      3. Start a Transaction
      4. Set Constraint Deferability
      5. Create Savepoints in a Transaction
        1. Releasing a Savepoint
      6. Terminate a Transaction
        1. Committing a Transaction
        2. Rolling Back a Transaction
    5. 17 Accessing SQL Data from Your Host Program
      1. Invoke SQL Directly
      2. Embed SQL Statements in Your Program
        1. Creating an Embedded SQL Statement
        2. Using Host Variables in Your SQL Statements
        3. Retrieving SQL Data
        4. Error Handling
      3. Create SQL Client Modules
        1. Defining SQL Client Modules
      4. Use an SQL Call-Level Interface
        1. Allocating Handles
        2. Executing SQL Statements
        3. Working with Host Variables
        4. Retrieving SQL Data
    6. 18 Working with XML Data
      1. Learn the Basics of XML
      2. Learn About SQL/XML
        1. The XML Data Type
        2. SQL/XML Functions
        3. SQL/XML Mapping Rule
  11. PART IV Appendices
    1. A Answers to Self Test
    2. B SQL:2006 Keywords
      1. SQL Reserved Keywords
      2. SQL Nonreserved Keywords
    3. C SQL Code Used in Try This Exercises
      1. SQL Code by Try This Exercise
      2. The INVENTORY Database
  12. Index

Product information

  • Title: SQL: A Beginner's Guide, Third Edition, 3rd Edition
  • Author(s): Andy Oppel, Robert Sheldon
  • Release date: August 2008
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071548656