You are previewing Sams Teach Yourself SQL in 21 Days, Fourth Edition.
O'Reilly logo
Sams Teach Yourself SQL in 21 Days, Fourth Edition

Book Description

Sams Teach Yourself SQL in 21 Days, Fourth Edition provides a sold foundation in understanding the fundamentals of SQL (Structured Query Language). SQL is the query language used by relational databases such a Oracle, Microsoft Access, and Microsoft SQL Server. The new edition covers object-oriented programming with SQL, ODBC, JDBC, embedded SQL, accessing remote databases, and constructs. All new examples based on an open source database such as MySQL enhance this new edition by making the examples readily useable for readers.

Table of Contents

  1. Copyright
  2. What's New in the Latest Edition
  3. About the Authors
  4. Acknowledgments
  5. We Want to Hear from You!
  6. Introduction
  7. At a Glance
    1. Getting Started with SQL
      1. A Brief History of SQL
      2. A Brief History of Databases
      3. Today's Database Landscape
      4. A Cross-Product Language
      5. An Overview of SQL
      6. Popular SQL Implementations
      7. Open Database Connectivity
      8. Embedding SQL in Application Programming
      9. Summary
      10. Q&A
      11. Workshop
    2. Introducing the Query
      1. Exploring SQL's Background
      2. Learning Basic Query Syntax
      3. The Building Blocks of Data Retrieval: SELECT and FROM
      4. Applying Query Concepts
      5. Writing Your First Query
      6. Selecting Distinct Values
      7. Summary
      8. Q&A
      9. Workshop
    3. Expressions, Conditions, and Operators
      1. Working with Query Expressions
      2. Placing Conditions on Queries
      3. Learning How to Use Operators
      4. Summary
      5. Q&A
      6. Workshop
    4. Molding Data with Built-in Functions
      1. Using Aggregate Functions to Summarize Data
      2. Using Functions to Format Date and Time Values
      3. Using Functions for Arithmetic Operations
      4. Using Functions to Modify the Appearance of Character Values
      5. Conversion Functions
      6. Miscellaneous Functions
      7. Supplemental Examples of MySQL Character Functions
      8. Supplemental Examples of MySQL Date Functions
      9. Summary
      10. Q&A
      11. Workshop
    5. Clauses in SQL Queries
      1. Specifying Criteria with the WHERE Clause
      2. Order from Chaos: The ORDER BY Clause
      3. The GROUP BY Clause
      4. The HAVING Clause
      5. Combining Clauses
      6. Summary
      7. Q&A
      8. Workshop
    6. Joining Tables
      1. Joining Multiple Tables in a Single SELECT Statement
      2. Joining Tables Based on Equality
      3. Joining Tables Based on Nonequality
      4. OUTER JOINs Versus INNER JOINs
      5. Joining a Table to Itself: The Self Join
      6. Summary
      7. Q&A
      8. Workshop
    7. Embedding Subqueries into Queries
      1. Building a Subquery
      2. Using Aggregate Functions with Subqueries
      3. Nesting Subqueries
      4. Referencing Outside with Correlated Subqueries
      5. Using EXISTS, ANY, and ALL
      6. Summary
      7. Q&A
      8. Workshop
    8. In Review
  8. At a Glance
    1. Database Normalization
      1. Normalizing a Database
      2. Understanding the Normal Forms
      3. Making Normalization Work
      4. Benefits of Normalization
      5. Drawbacks of Normalization
      6. Denormalizing a Database
      7. Summary
      8. Q&A
      9. Workshop
    2. Creating and Maintaining Tables
      1. Beginning with the CREATE DATABASE Statement
      2. Defining Tables with the CREATE TABLE Statement
      3. Modifying Table Structures with the ALTER TABLE Statement
      4. The DROP TABLE Statement
      5. The DROP DATABASE Statement
      6. Summary
      7. Q&A
      8. Workshop
    3. Controlling Data Integrity
      1. Introducing Constraints
      2. Exploring Types of Constraints
      3. Managing Constraints
      4. Summary
      5. Q&A
      6. Workshop
    4. Manipulating Data
      1. Introducing Data-Manipulation Statements
      2. Entering Data with the INSERT Statement
      3. Modifying Existing Data with the UPDATE Statement
      4. Removing Information with the DELETE Statement
      5. Importing and Exporting Data from Foreign Sources
      6. Summary
      7. Q&A
      8. Workshop
    5. Controlling Transactions
      1. Transaction Control
      2. Beginning a Transaction
      3. Finishing a Transaction
      4. Canceling the Transaction
      5. Using Transaction Savepoints
      6. Summary
      7. Q&A
      8. Workshop
    6. Dates and Time in SQL
      1. How Are Date and Time Values Stored?
      2. Applying Date Functions to the Query
      3. Converting Date Formats
      4. Summary
      5. Q&A
      6. Workshop
    7. Creating Views
      1. Introducing Views
      2. Using Views
      3. Summary
      4. Q&A
      5. Workshop
    8. In Review
  9. At a Glance
    1. Creating Indexes on Tables to Improve Performance
      1. Using Indexes
      2. Summary
      3. Q&A
      4. Workshop
    2. Streamlining SQL Statements for Improved Performance
      1. Make Your SQL Statements Readable
      2. Avoiding the Full-Table Scan
      3. Adding a New Index
      4. Arranging Elements in a Query
      5. OLAP Versus OLTP
      6. Batch Loads Versus Transactional Processing
      7. Optimizing Data Loads by Dropping Indexes
      8. A Frequent COMMIT Keeps the DBA Away
      9. Rebuilding Tables and Indexes in a Dynamic Environment
      10. Tuning the Database
      11. Identifying Performance Obstacles
      12. Using Built-in Tuning Tools
      13. Summary
      14. Q&A
      15. Workshop
    3. Database Security
      1. Wanted: Database Administrator
      2. Popular Database Products and Security
      3. How Does a Database Become Secure?
      4. Personal Oracle and Security
      5. Summary
      6. Q&A
      7. Workshop
    4. Exploring the Data Dictionary (System Catalog)
      1. An Introduction to the Data Dictionary
      2. Identifying Data Dictionary Users
      3. Exploring the Contents of the Data Dictionary
      4. A Look Inside Oracle's Data Dictionary
      5. Summary
      6. Q&A
      7. Workshop
    5. Stored Procedures, Triggers, and Embedded SQL
      1. Creating Temporary Tables
      2. Using Cursors
      3. Creating and Using Stored Procedures
      4. Designing and Using Triggers
      5. Testing SELECT Statements Before Implementation
      6. Using Embedded SQL
      7. Programming with SQL
      8. Summary
      9. Q&A
      10. Workshop
    6. New Objects in the Latest Standard: SQL3
      1. Exploring the CREATE ROLE Statement
      2. Creating Triggers
      3. Using the CREATE TYPE Statement
      4. Summary
      5. Q&A
      6. Workshop
    7. Using SQL to Generate SQL Statements
      1. Understanding the Power of SQL Statement Generation
      2. Miscellaneous SQL*Plus Commands
      3. Counting the Rows in All Tables
      4. Granting System Privileges to Multiple Users
      5. Granting Privileges on Your Tables to Another User
      6. Disabling Table Constraints to Load Data
      7. Creating Numerous Synonyms in a Single Bound
      8. Creating Views on Your Tables
      9. Truncating All Tables in a Schema
      10. Using SQL to Generate Shell Scripts
      11. Reverse Engineering Tables and Indexes
      12. Applying SQL Generation and Other Concepts to the Real World
      13. Summary
      14. Q&A
      15. Workshop
    8. In Review
  10. At a Glance
    1. Debugging Your SQL Statements
      1. Exploring Common SQL Errors
      2. Exploring Common Logical Mistakes
      3. Preventing Problems with Your Data
      4. Summary
      5. Q&A
      6. Workshop
    2. Creating Complex SQL Queries
      1. Welcome to Today's Tables
      2. Examples of Complex Queries
      3. Tips for Building Complex Queries
      4. Summary
      5. Q&A
      6. Workshop
    3. Using Oracle SQL*Plus to Satisfy Reporting Needs
      1. An Introduction to SQL*Plus
      2. The SQL*Plus Buffer
      3. Viewing Table Structure with the DESCRIBE Command
      4. Displaying Settings with the SHOW Command
      5. Manipulating Files with File Commands
      6. Customizing the Work Environment with SET Commands
      7. Using the LOGIN.SQL File
      8. Removing Settings with the CLEAR Command
      9. Formatting Your Output
      10. Creating Report and Group Summaries
      11. Using Variables in SQL*Plus
      12. Using the DUAL Table
      13. Exploring the DECODE Function
      14. DATE Conversions
      15. Running a Series of SQL Files
      16. Adding Comments to Your SQL Script
      17. Creating Advanced Reports
      18. Summary
      19. Q&A
      20. Workshop
    4. An Introduction to Oracle PL/SQL
      1. Introducing PL/SQL
      2. Exploring Data Types in PL/SQL
      3. The Structure of a PL/SQL Block
      4. The DECLARE Section
      5. The PROCEDURE Section
      6. The EXCEPTION Section
      7. Transactional Control in PL/SQL
      8. Putting Everything Together
      9. Using Stored Procedures, Packages, and Triggers
      10. Summary
      11. Q&A
      12. Workshop
    5. An Introduction to Transact-SQL
      1. An Overview of Transact-SQL
      2. Extensions to ANSI SQL
      3. Data Types
      4. Accessing the Database with Transact-SQL
      5. Establishing Flow Control
      6. Using Transact-SQL Wildcard Operators
      7. Creating Summarized Reports Using COMPUTE
      8. Date Conversions
      9. SQL Server Diagnostic Tools—SET Commands
      10. Summary
      11. Q&A
      12. Workshop
    6. Embedding SQL in Application Programming
      1. A Quick Trip Through Some Application Development Tools
      2. Getting Set Up for Oracle and InterBase
      3. Creating the Database
      4. Using Microsoft Query to Perform a Join
      5. Using Visual C++ and SQL
      6. Using Delphi and SQL
      7. Using Java and SQL
      8. Summary
      9. Q&A
      10. Workshop
    7. Using MySQL on a UNIX-based System
      1. MySQL Administration
      2. The MySQL Terminal Monitor
      3. MySQL Utilities
      4. Summary
      5. Q&A
      6. Workshop
    8. In Review
    9. Answers
      1. Day 1
      2. Day 2
      3. Day 3
      4. Day 4
      5. Day 5
      6. Day 6
      7. Day 7
      8. Day 8
      9. Day 9
      10. Day 10
      11. Day 11
      12. Day 12
      13. Day 13
      14. Day 14
      15. Day 15
      16. Day 16
      17. Day 17
      18. Day 18
      19. Day 19
      20. Day 20
      21. Day 21
      22. Bonus Day 1
      23. Bonus Day 2
      24. Bonus Day 3
      25. Bonus Day 4
      26. Bonus Day 5
    10. Code Examples to Create Tables
    11. Code Examples to Populate Tables
    12. Using MySQL for Exercises
      1. Windows Installation Instructions
      2. Linux Installation Instructions
    13. Bonus Exercises
    14. Glossary of Common SQL Commands
      1. *
      2. ALTER DATABASE
      3. ALTER USER
      4. BEGIN TRANSACTION
      5. CLOSE CURSOR
      6. COMMIT TRANSACTION
      7. CREATE DATABASE
      8. CREATE INDEX
      9. CREATE PROCEDURE
      10. CREATE TABLE
      11. CREATE TRIGGER
      12. CREATE USER
      13. CREATE VIEW
      14. DEALLOCATE CURSOR
      15. DECLARE CURSOR
      16. DROP DATABASE
      17. DROP INDEX
      18. DROP PROCEDURE
      19. DROP TABLE
      20. DROP TRIGGER
      21. DROP VIEW
      22. EXECUTE
      23. FETCH
      24. FROM
      25. GRANT
      26. GROUP BY
      27. HAVING
      28. INTERSECT
      29. ORDER BY
      30. REVOKE
      31. ROLLBACK TRANSACTION
      32. SELECT
      33. SET TRANSACTION
      34. UNION
      35. WHERE
    15. ASCII Table
    16. Resources
      1. Books
      2. Magazines
      3. Internet URLs for the Keyword SQL
      4. Newsgroups
  11. Index