SQL: A Beginner's Guide, Fourth Edition, 4th Edition

SQL: A Beginner's Guide, Fourth Edition, 4th Edition

by Andy Oppel
Released November 2015
Publisher(s): McGraw-Hill
ISBN: 9780071842570

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

This fully revised, self-paced learning tool lays out all the necessary steps to quickly and easily start writing SQL programs

Thoroughly updated to reflect the most recent ANSI/ISO standard, SQL: A Beginner's Guide, Fourth Edition will get you up-and-running with SQL programming right away. Clear tutorials, annotated code, and proven instructional tools guide you to easily performing queries and modifications, building databases, creating and reviewing embedded statements, troubleshooting system- and data-related problems, and much more.

You will learn how to retrieve, insert, update, and delete database data, and perform management and administrative functions. The book also covers new features, including SQL/XML and the long-awaited temporal support. Code examples are provided throughout along with notes on using them with the latest RDBMS software versions such as MySQL 5.7, SQL Server 2014, and Oracle Database 12c.

  • Platform-neutral coverage; all skills can be applied to any database product, and any SQL version
  • Features hands-on exercises and self-tests that reinforce basic knowledge
  • ”Ask the Expert” sections throughout are filled with bonus information and useful tips

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. Introduction
  9. PART I Relational Databases and SQL
    1. 1 Introduction to Relational Databases and SQL
      1. Understand Relational Databases
        1. The Relational Model
      2. Try This 1-1: Normalizing Data and Identifying Relationships
      3. Learn About SQL
        1. The SQL Evolution
        2. Types of SQL Statements
        3. Types of Execution
        4. SQL Standard versus Product Implementations
      4. Try This 1-2: Connecting to a Database
      5. Chapter 1 Self Test
    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
      6. Try This 2-1: Creating a Database and a Schema
      7. Chapter 2 Self Test
    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. Specify Identity Column and Generation Options
        1. Maintaining Independent Sequence Generators
      6. Try This 3-1: Creating SQL Tables
      7. Alter SQL Tables
      8. Drop SQL Tables
      9. Truncate SQL Tables
      10. Try This 3-2: Altering and Deleting SQL Tables
      11. Chapter 3 Self Test
    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. Try This 4-1: Adding NOT NULL, Unique, and Referential Constraints
      7. Define CHECK Constraints
        1. Defining Assertions
        2. Creating Domains and Domain Constraints
      8. Try This 4-2: Adding a CHECK Constraint
      9. Chapter 4 Self Test
    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
      4. Try This 5-1: Adding Views to Your Database
      5. Chapter 5 Self Test
    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
      5. Try This 6-1: Managing Roles and Privileges
      6. Chapter 6 Self Test
  10. 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
        2. Use the GROUP BY Clause to Group Query Results
      3. Use the HAVING Clause to Specify Group Search Conditions
      4. Use the ORDER BY Clause to Sort Query Results
      5. Try This 7-1: Querying the Inventory Database
      6. Chapter 7 Self Test
    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
      4. Merge SQL Data
      5. Try This 8-1: Modifying SQL Data
      6. Chapter 8 Self Test
    3. 9 Using Predicates
      1. Compare SQL Data
        1. Using the BETWEEN Predicate
      2. Return Null Values
      3. Return Similar Values
      4. Try This 9-1: Using Predicates in SQL Statements
      5. Reference Additional Sources of Data
        1. Using the IN Predicate
        2. Using the EXISTS Predicate
      6. Quantify Comparison Predicates
        1. Using the SOME and ANY Predicates
        2. Using the ALL Predicate
      7. Try This 9-2: Using Subqueries in Predicates
      8. Chapter 9 Self Test
    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. Try This 10-1: Using Functions and Value Expressions
      6. Chapter 10 Self Test
    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
      5. Try This 11-1: Querying Multiple Tables
      6. Chapter 11 Self Test
    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
      6. Try This 12-1: Working with Subqueries
      7. Chapter 12 Self Test
    7. 13 Working with Temporal Data
      1. Create and Use System-Versioned Tables
        1. Changing Data in a System-Versioned Table
        2. Querying a System-Versioned Table
      2. Create and Use Application-Time Period Tables
        1. Inserting Data into an Application-Time Period Table
        2. Updating Data in an Application-Time Period Table
        3. Deleting Data from an Application-Time Period Table
      3. Try This 13-1: Working with Application-Time Period Tables
      4. Create and Use System-Versioned Application-Time Period Tables
        1. Inserting Data into a System-Versioned Application-Time Period Table
        2. Updating Data in a System-Versioned Application-Time Period Table
        3. Deleting Data from a System-Versioned Application-Time Period Table
      5. Chapter 13 Self Test
  11. PART III Advanced Data Access
    1. 14 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. Try This 14-1: Creating SQL-Invoked Procedures
      7. Add Output Parameters to Your Procedures
      8. Create SQL-Invoked Functions
      9. Try This 14-2: Creating SQL-Invoked Functions
      10. Chapter 14 Self Test
    2. 15 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
      6. Create Instead Of Triggers
      7. Try This 15-1: Creating SQL Triggers
      8. Chapter 15 Self Test
    3. 16 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
      6. Try This 16-1: Working with SQL Cursors
      7. Chapter 16 Self Test
    4. 17 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
      7. Try This 17-1: Working with Transactions
      8. Chapter 17 Self Test
    5. 18 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. Try This 18-1: Embedding SQL Statements
      4. Create SQL Client Modules
        1. Defining SQL Client Modules
      5. Use an SQL Call-Level Interface
        1. Allocating Handles
        2. Executing SQL Statements
        3. Working with Host Variables
        4. Retrieving SQL Data
      6. Try This 18-2: Using the SQL Call-Level Interface
      7. Chapter 18 Self Test
    6. 19 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
      3. Try This 19-1: Using SQL/XML Functions
      4. Chapter 19 Self Test
  12. PART IV Appendices
    1. A SQL Keywords
      1. SQL Reserved Keywords
      2. SQL Nonreserved Keywords
    2. B Answers to Self Tests
    3. C SQL Code Used in Try This Exercises
  13. Index

Product information

  • Title: SQL: A Beginner's Guide, Fourth Edition, 4th Edition
  • Author(s): Andy Oppel
  • Release date: November 2015
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071842570