Sams Teach Yourself SQL

Sams Teach Yourself SQL

Released May 2008
Publisher(s): Sams
ISBN: None

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

In just 24 lessons of one hour or less, you will learn professional techniques to design and build efficient databases and query them to extract useful information. Using a straightforward, step-by-step approach, each lesson builds on the previous one, allowing you to learn the essentials of ANSI SQL from the ground up.

Example code demonstrates the authors’ professional techniques, while exercises written for MySQL offer the reader hands-on learning with an open-source database. Included are advanced techniques for using views, managing transactions, database administration, and extending SQL.

Step-by-step instructions carefully walk you through the most common SQL tasks.

Q&As, Quizzes, and Exercises at the end of each chapter help you test your knowledge.

Notes and Tips point out shortcuts and solutions.

New terms are clearly defined and explained.

Learn how to…

  • Use SQL-2003, the latest standard for the Structured Query Language

  • Design and deploy efficient, secure databases

  • Build advanced queries for information retrieval

  • Sort, group, and summarize information for best presentation

  • Tune databases and queries for maximum performance

  • Understand database administration and security techniques

For more than ten years the authors have studied, applied, and documented the SQL standard and its application to critical database systems.

Ryan Stephens and Ron Plew are entrepreneurs, speakers, and cofounders of Perpetual Technologies, Inc. (PTI), a fast-growing IT management and consulting firm which specializes in database technologies. They taught database courses for Indiana University–Purdue University in Indianapolis for five years and have authored more than a dozen books on Oracle, SQL, database design, and the high availability of critical systems.

Arie D. Jones is Senior SQL Server database administrator and analyst for PTI. He is a regular speaker at technical events and has authored several books and articles.

Category: Database

Covers: ANSI SQL

User Level: Beginning–Intermediate

Register your book at informit.com/title/9780672330186 for convenient access to updates and corrections

as they become available.

Table of contents

  1. Copyright
    1. Dedications
  2. About the Authors
  3. Acknowledgments
  4. We Want to Hear from You
  5. Reader Services
  6. Introduction
    1. What This Book Intends to Accomplish
    2. What We Added to This Edition
    3. What You Need
    4. Conventions Used in This Book
    5. ANSI SQL and Vendor Implementations
    6. Understanding the Examples and Exercises
  7. I. A SQL Concepts Overview
    1. 1. Welcome to the World of SQL
      1. SQL Definition and History
        1. What Is SQL?
        2. What Is ANSI SQL?
        3. The New Standard: SQL-2003
        4. What Is a Database?
        5. The Relational Database
        6. Client/Server Technology
        7. Web-Based Database Systems
        8. Popular Database Vendors
      2. SQL Sessions
        1. CONNECT
        2. DISCONNECT and EXIT
      3. Types of SQL Commands
        1. Defining Database Structures
        2. Manipulating Data
        3. Selecting Data
        4. Data Control Language
        5. Data Administration Commands
        6. Transactional Control Commands
      4. The Database Used in This Book
        1. Table-Naming Standards
        2. A Look at the Data
        3. A Closer Look at What Comprises a Table
          1. A Field
          2. A Record, or Row, of Data
          3. A Column
          4. The Primary Key
          5. A NULL Value
        4. MySQL Examples and Exercises
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
  8. II. Building Your Database
    1. 2. Defining Data Structures
      1. What Is Data?
      2. Basic Data Types
        1. Fixed-Length Strings
        2. Varying-Length Strings
        3. Large Object Types
        4. Numeric Types
        5. Decimal Types
        6. Integers
        7. Floating-Point Decimals
        8. Date and Time Types
        9. Literal Strings
        10. NULL Data Types
        11. BOOLEAN Values
        12. User-Defined Types
        13. Domains
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Exercises
    2. 3. Managing Database Objects
      1. What Are Database Objects?
      2. What Is a Schema?
      3. A Table: The Primary Storage for Data
        1. Columns
        2. Rows
        3. The CREATE TABLE Statement
        4. Naming Conventions
        5. The ALTER TABLE Command
          1. Modifying Elements of a Table
          2. Adding Mandatory Columns to a Table
          3. Adding Auto-Incrementing Columns to a Table
          4. Modifying Columns
        6. Creating a Table from an Existing Table
        7. Dropping Tables
      4. Integrity Constraints
        1. Primary Key Constraints
        2. Unique Constraints
        3. Foreign Key Constraints
        4. NOT NULL Constraints
        5. Check Constraints
        6. Dropping Constraints
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    3. 4. The Normalization Process
      1. Normalizing a Database
        1. The Raw Database
        2. Logical Database Design
          1. What Are the End User’s Needs?
          2. Data Redundancy
        3. The Normal Forms
          1. The First Normal Form
          2. The Second Normal Form
          3. The Third Normal Form
        4. Naming Conventions
        5. Benefits of Normalization
        6. Drawbacks of Normalization
      2. Denormalizing a Database
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Exercises
    4. 5. Manipulating Data
      1. Overview of Data Manipulation
      2. Populating Tables with New Data
        1. Inserting Data into a Table
        2. Inserting Data into Limited Columns of a Table
        3. Inserting Data from Another Table
        4. Inserting NULL Values
      3. Updating Existing Data
        1. Updating the Value of a Single Column
        2. Updating Multiple Columns in One or More Records
      4. Deleting Data from Tables
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    5. 6. Managing Database Transactions
      1. What Is a Transaction?
      2. Controlling Transactions
        1. The COMMIT Command
        2. The ROLLBACK Command
        3. The SAVEPOINT Command
        4. The ROLLBACK TO SAVEPOINT Command
        5. The RELEASE SAVEPOINT Command
        6. The SET TRANSACTION Command
      3. Transactional Control and Database Performance
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
  9. III. Getting Effective Results from Queries
    1. 7. Introduction to the Database Query
      1. What Is a Query?
      2. Introduction to the SELECT Statement
        1. The SELECT Statement
        2. The FROM Clause
        3. The WHERE Clause
        4. The ORDER BY Clause
        5. Case Sensitivity
      3. Examples of Simple Queries
        1. Counting the Records in a Table
        2. Selecting Data from Another User’s Table
        3. Using Column Aliases
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    2. 8. Using Operators to Categorize Data
      1. What Is an Operator in SQL?
      2. Comparison Operators
        1. Equality
        2. Nonequality
        3. Less Than, Greater Than
        4. Combinations of Comparison Operators
      3. Logical Operators
        1. IS NULL
        2. BETWEEN
        3. IN
        4. LIKE
        5. EXISTS
        6. ALL, SOME, and ANY Operators
      4. Conjunctive Operators
        1. AND
        2. OR
      5. Negative Operators
        1. Not Equal
        2. NOT BETWEEN
        3. NOT IN
        4. NOT LIKE
        5. IS NOT NULL
        6. NOT EXISTS
      6. Arithmetic Operators
        1. Addition
        2. Subtraction
        3. Multiplication
        4. Division
        5. Arithmetic Operator Combinations
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    3. 9. Summarizing Data Results from a Query
      1. What Are Aggregate Functions?
        1. The COUNT Function
        2. The SUM Function
        3. The AVG Function
        4. The MAX Function
        5. The MIN Function
      2. Summary
      3. Q&A
      4. Workshop
        1. Quiz
        2. Exercises
    4. 10. Sorting and Grouping Data
      1. Why Group Data?
      2. The GROUP BY Clause
        1. Group Functions
        2. Grouping Selected Data
        3. Creating Groups and Using Aggregate Functions
        4. Representing Column Names with Numbers
      3. GROUP BY Versus ORDER BY
      4. The HAVING Clause
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    5. 11. Restructuring the Appearance of Data
      1. ANSI Character Functions
        1. Concatenation
        2. Substring
        3. TRANSLATE
      2. Various Common Character Functions
        1. Concatenation
        2. TRANSLATE
        3. REPLACE
        4. UPPER
        5. LOWER
        6. SUBSTR
        7. INSTR
        8. LTRIM
        9. RTRIM
        10. DECODE
      3. Miscellaneous Character Functions
        1. LENGTH
        2. IFNULL (NULL Value Checker)
        3. COALESCE
        4. LPAD
        5. RPAD
        6. ASCII
      4. Mathematical Functions
      5. Conversion Functions
        1. Converting Character Strings to Numbers
        2. Converting Numbers to Character Strings
      6. Combining Character Functions
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
    6. 12. Understanding Dates and Times
      1. How Is a Date Stored?
        1. Standard Data Types for Date and Time
        2. DATETIME Elements
        3. Implementation-Specific Data Types
      2. Date Functions
        1. The Current Date
        2. Time Zones
        3. Adding Time to Dates
        4. Comparing Date and Time Periods
        5. Miscellaneous Date Functions
      3. Date Conversions
        1. Date Pictures
        2. Converting Dates to Character Strings
        3. Converting Character Strings to Dates
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
  10. IV. Building Sophisticated Database Queries
    1. 13. Joining Tables in Queries
      1. Selecting Data from Multiple Tables
      2. Types of Joins
        1. Component Locations of a Join Condition
        2. Joins of Equality
        3. Natural Joins
        4. Using Table Aliases
        5. Joins of Nonequality
        6. Outer Joins
        7. Self Joins
        8. Joining on Multiple Keys
      3. Join Considerations
        1. Using a Base Table
        2. The Cartesian Product
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    2. 14. Using Subqueries to Define Unknown Data
      1. What Is a Subquery?
        1. Subqueries with the SELECT Statement
        2. Subqueries with the INSERT Statement
        3. Subqueries with the UPDATE Statement
        4. Subqueries with the DELETE Statement
      2. Embedded Subqueries
      3. Correlated Subqueries
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    3. 15. Combining Multiple Queries into One
      1. Single Queries Versus Compound Queries
      2. Compound Query Operators
        1. The UNION Operator
        2. The UNION ALL Operator
        3. The INTERSECT Operator
        4. The EXCEPT Operator
      3. Using ORDER BY with a Compound Query
      4. Using GROUP BY with a Compound Query
      5. Retrieving Accurate Data
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercises
  11. V. SQL Performance Tuning
    1. 16. Using Indexes to Improve Performance
      1. What Is an Index?
      2. How Do Indexes Work?
      3. The CREATE INDEX Command
      4. Types of Indexes
        1. Single-Column Indexes
        2. Unique Indexes
        3. Composite Indexes
        4. Implicit Indexes
      5. When Should Indexes Be Considered?
      6. When Should Indexes Be Avoided?
      7. Dropping an Index
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    2. 17. Improving Database Performance
      1. What Is SQL Statement Tuning?
      2. Database Tuning Versus SQL Statement Tuning
      3. Formatting Your SQL Statement
        1. Formatting a Statement for Readability
        2. Arrangement of Tables in the FROM Clause
        3. Order of Join Conditions
        4. The Most Restrictive Condition
      4. Full Table Scans
      5. Other Performance Considerations
        1. Using the LIKE Operator and Wildcards
        2. Avoiding the OR Operator
        3. Avoiding the HAVING Clause
        4. Avoiding Large Sort Operations
        5. Using Stored Procedures
        6. Disabling Indexes During Batch Loads
      6. Performance Tools
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
  12. VI. Using SQL to Manage Users and Security
    1. 18. Managing Database Users
      1. Users Are the Reason
        1. Types of Users
        2. Who Manages Users?
        3. The User’s Place in the Database
        4. How Does a User Differ from a Schema?
      2. The Management Process
        1. Creating Users
          1. Creating Users in Oracle
          2. Creating Users in Sybase and Microsoft SQL Server
          3. Creating Users in MySQL
        2. Creating Schemas
        3. Dropping a Schema
        4. Altering Users
        5. User Sessions
        6. Removing User Access
      3. Tools Utilized by Database Users
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
    2. 19. Managing Database Security
      1. What Is Database Security?
      2. What Are Privileges?
        1. System Privileges
        2. Object Privileges
        3. Who Grants and Revokes Privileges?
      3. Controlling User Access
        1. The GRANT Command
          1. GRANT OPTION
          2. ADMIN OPTION
        2. The REVOKE Command
        3. Controlling Access on Individual Columns
        4. The PUBLIC Database Account
        5. Groups of Privileges
      4. Controlling Privileges Through Roles
        1. The CREATE ROLE Statement
        2. The DROP ROLE Statement
        3. The SET ROLE Statement
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
  13. VII. Summarized Data Structures
    1. 20. Creating and Using Views and Synonyms
      1. What Is a View?
        1. Utilizing Views to Simplify Data Access
        2. Utilizing Views as a Form of Security
        3. Utilizing Views to Maintain Summarized Data
      2. Creating Views
        1. Creating a View from a Single Table
        2. Creating a View from Multiple Tables
        3. Creating a View from a View
      3. WITH CHECK OPTION
      4. Updating Data Through a View
        1. Inserting Rows into a View
        2. Deleting Rows from a View
      5. Creating a Table from a View
      6. Views and the ORDER BY Clause
      7. Dropping a View
      8. What Is a Synonym?
        1. Creating Synonyms
        2. Dropping Synonyms
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercises
    2. 21. Working with the System Catalog
      1. What Is the System Catalog?
      2. How Is the System Catalog Created?
      3. What Is Contained in the System Catalog?
        1. User Data
        2. Security Information
        3. Database Design Information
        4. Performance Statistics
      4. System Catalog Tables by Implementation
      5. Querying the System Catalog
      6. Updating System Catalog Objects
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercises
  14. VIII. Applying SQL Fundamentals in Today’s World
    1. 22. Advanced SQL Topics
      1. Cursors
        1. Opening a Cursor
        2. Fetching Data from a Cursor
        3. Closing a Cursor
      2. Stored Procedures and Functions
      3. Triggers
        1. The CREATE TRIGGER Statement
        2. The DROP TRIGGER Statement
        3. The FOR EACH ROW Statement
      4. Dynamic SQL
      5. Call-Level Interface
      6. Using SQL to Generate SQL
      7. Direct Versus Embedded SQL
      8. Windowed Table Functions
      9. Working with XML
      10. Summary
      11. Q&A
      12. Workshop
        1. Quiz
        2. Exercises
    2. 23. Extending SQL to the Enterprise, the Internet, and the Intranet
      1. SQL and the Enterprise
        1. The Back-End Application
        2. The Front-End Application
      2. Accessing a Remote Database
        1. ODBC
        2. JDBC
        3. Vendor Connectivity Products
        4. Accessing a Remote Database Through a Web Interface
      3. SQL and the Internet
        1. Making Data Available to Customers Worldwide
        2. Making Data Available to Employees and Privileged Customers
        3. Front-End Web Tools Using SQL
      4. SQL and the Intranet
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercises
    3. 24. Extensions to Standard SQL
      1. Various Implementations
        1. Differences Between Implementations
        2. Compliance with ANSI SQL
        3. Extensions to SQL
      2. Example Extensions
        1. Transact-SQL
        2. PL/SQL
        3. MySQL
      3. Interactive SQL Statements
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercises
  15. IX. Appendixes
    1. A. Common SQL Commands
      1. SQL Statements
        1. ALTER TABLE
        2. COMMIT
        3. CREATE DOMAIN
        4. CREATE INDEX
        5. CREATE ROLE
        6. CREATE TABLE
        7. CREATE TABLE AS
        8. CREATE TYPE
        9. CREATE VIEW
        10. DELETE
        11. DROP INDEX
        12. DROP TABLE
        13. DROP VIEW
        14. GRANT
        15. INSERT
        16. INSERT...SELECT
        17. REVOKE
        18. ROLLBACK
        19. SAVEPOINT
        20. SELECT
        21. UPDATE
      2. SQL Clauses
        1. SELECT
        2. FROM
        3. WHERE
        4. GROUP BY
        5. HAVING
        6. ORDER BY
    2. B. Using MySQL for Exercises
      1. Windows Installation Instructions
      2. Linux Installation Instructions
    3. C. Answers to Quizzes and Exercises
      1. Hour 1, “Welcome to the World of SQL”
        1. Quiz Answers
        2. Exercise Answers
      2. Hour 2, “Defining Data Structures”
        1. Quiz Answers
        2. Exercise Answers
      3. Hour 3, “Managing Database Objects”
        1. Quiz Answers
        2. Exercise Answers
      4. Hour 4, “The Normalization Process”
        1. Quiz Answers
        2. Exercise Answers
      5. Hour 5, “Manipulating Data”
        1. Quiz Answers
        2. Exercise Answers
      6. Hour 6, “Managing Database Transactions”
        1. Quiz Answers
        2. Exercise Answers
      7. Hour 7, “Introduction to the Database Query”
        1. Quiz Answers
        2. Exercise Answers
      8. Hour 8, “Using Operators to Categorize Data”
        1. Quiz Answers
        2. Exercise Answers
      9. Hour 9, “Summarizing Data Results from a Query”
        1. Quiz Answers
        2. Exercise Answers
      10. Hour 10, “Sorting and Grouping Data”
        1. Quiz Answers
        2. Exercise Answers
      11. Hour 11, “Restructuring the Appearance of Data”
        1. Quiz Answers
        2. Exercise Answers
      12. Hour 12, “Understanding Dates and Time”
        1. Quiz Answers
        2. Exercise Answers
      13. Hour 13, “Joining Tables in Queries”
        1. Quiz Answers
        2. Exercise Answers
      14. Hour 14, “Using Subqueries to Define Unknown Data”
        1. Quiz Answers
        2. Exercise Answers
      15. Hour 15, “Combining Multiple Queries into One”
        1. Quiz Answers
        2. Exercise Answers
      16. Hour 16, “Using Indexes to Improve Performance”
        1. Quiz Answers
        2. Exercise Answers
      17. Hour 17, “Improving Database Performance”
        1. Quiz Answers
        2. Exercise Answers
      18. Hour 18, “Managing Database Users”
        1. Quiz Answers
        2. Exercise Answers
      19. Hour 19, “Managing Database Security”
        1. Quiz Answers
        2. Exercise Answers
      20. Hour 20, “Creating and Using Views and Synonyms”
        1. Quiz Answers
        2. Exercise Answers
      21. Hour 21, “Working with the System Catalog”
        1. Quiz Answers
        2. Exercise Answers
      22. Hour 22, “Advanced SQL Topics”
        1. Quiz Answers
        2. Exercise Answers
      23. Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet”
        1. Quiz Answers
        2. Exercise Answers
      24. Hour 24, “Extensions to Standard SQL”
        1. Quiz Answers
        2. Exercise Answers
    4. D. CREATE TABLE Statements for Book Examples
      1. EMPLOYEE_TBL
      2. EMPLOYEE_PAY_TBL
      3. CUSTOMER_TBL
      4. ORDERS_TBL
      5. PRODUCTS_TBL
    5. E. INSERT Statements for Book Examples
      1. EMPLOYEE_TBL
      2. EMPLOYEE_PAY_TBL
      3. CUSTOMER_TBL
      4. ORDERS_TBL
      5. PRODUCTS_TBL
    6. F. Bonus Exercises
  16. Glossary

Product information

  • Title: Sams Teach Yourself SQL
  • Author(s):
  • Release date: May 2008
  • Publisher(s): Sams
  • ISBN: None