SQL For Dummies®

SQL For Dummies®

by Allen G. Taylor
Released August 2006
Publisher(s): Wiley
ISBN: 9780470046524

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

See how SQL interfaces with today's environments


Start building and using relational databases with SQL's newest features

The database may be the twenty-first century filing cabinet, but building one is a little more complex than sliding drawers into a metal box. With this book to guide you through all the newest features of SQL, you'll soon be whipping up relational databases, using SQL with XML to power data-driven Web sites, and more!

Discover how to
* Use SQL in a client/server system
* Build a multitable relational database
* Construct nested and recursive queries
* Set up database security
* Use SQL within applications
* Map SQL to XML

Table of contents

  1. Copyright
  2. About the Author
  3. Dedication
  4. Introduction
    1. About This Book
    2. Who Should Read This Book?
    3. How This Book Is Organized
      1. Part I: Basic Concepts
      2. Part II: Using SQL to Build Databases
      3. Part III: Storing and Retrieving Data
      4. Part IV: Controlling Operations
      5. Part V: Taking SQL to the Real World
      6. Part VI: Advanced Topics
      7. Part VII: The Part of Tens
      8. Part VIII: Appendixes
    4. Icons Used in This Book
    5. Getting Started
  5. I. Basic Concepts
    1. In this part . . .
    2. 1. Relational Database Fundamentals
      1. Keeping Track of Things
      2. What Is a Database?
      3. Database Size and Complexity
      4. What Is a Database Management System?
      5. Flat Files
      6. Database Models
        1. Relational model
        2. Why relational is better
        3. Components of a relational database
        4. Holidays bring families together
        5. Enjoy the view
        6. Schemas, domains, and constraints
          1. Schemas
          2. Domains
          3. Constraints
        7. The object model challenges the relational model
        8. The object‐relational model
      7. Database Design Considerations
    3. 2. SQL Fundamentals
      1. What SQL Is and Isn't
      2. A (Very) Little History
      3. SQL Commands
      4. Reserved Words
      5. Data Types
        1. Exact numerics
          1. INTEGER data type
          2. SMALLINT data type
          3. BIGINT data type
          4. NUMERIC data type
          5. DECIMAL data type
        2. Approximate numerics
          1. REAL data type
          2. DOUBLE PRECISION data type
          3. FLOAT data type
        3. Character strings
          1. CHARACTER data type
          2. CHARACTER VARYING data type
          3. CHARACTER LARGE OBJECT data type
          4. NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT data types
        4. Booleans
        5. Datetimes
          1. DATE data type
          2. TIME WITHOUT TIME ZONE data type
          3. TIMESTAMP WITHOUT TIME ZONE data type
          4. TIME WITH TIME ZONE data type
          5. TIMESTAMP WITH TIME ZONE data type
        6. Intervals
        7. XML type
        8. ROW types
        9. Collection types
          1. ARRAY type
          2. Multiset type
        10. REF types
        11. User‐defined types
          1. Distinct types
          2. Structured types
            1. Constructors
            2. Mutators and observers
            3. Subtypes and supertypes
            4. Example of a structured type
        12. Data type summary
      6. Null Values
      7. Constraints
      8. Using SQL in a Client/Server System
        1. The server
          1. What the server is
          2. What the server does
        2. The client
          1. What the client is
          2. What the client does
      9. Using SQL on the Internet/Intranet
    4. 3. The Components of SQL
      1. Data Definition Language
        1. When “Just do it!” is not good advice
        2. Creating tables
        3. A room with a view
          1. Single‐table view
          2. Creating a multitable view
        4. Collecting tables into schemas
        5. Ordering by catalog
        6. Getting familiar with DDL commands
          1. CREATE
          2. ALTER
          3. DROP
      2. Data Manipulation Language
        1. Value expressions
          1. Numeric value expressions
          2. String value expressions
          3. Datetime and interval value expressions
          4. Boolean value expressions
          5. User‐defined type value expressions
          6. Row value expressions
          7. Collection value expressions
          8. Reference value expressions
        2. Predicates
        3. Logical connectives
        4. Set functions
          1. COUNT
          2. MAX
          3. MIN
          4. SUM
          5. AVG
        5. Subqueries
      3. Data Control Language
        1. Transactions
        2. Users and privileges
        3. Referential integrity constraints can jeopardize your data
        4. Delegating responsibility for security
  6. II. Using SQL to Build Databases
    1. In this part . . .
    2. 4. Building and Maintaining a Simple Database Structure
      1. Building a Simple Database Using a RAD Tool
        1. Deciding what to track
        2. Creating a table with Design View
        3. Altering the table structure
        4. Identifying a primary key
        5. Creating an index
        6. Deleting a table
      2. Building PowerDesign with SQL's DDL
        1. Using SQL with Microsoft Access
        2. Creating a table
        3. Creating an index
        4. Altering the table structure
        5. Deleting a table
        6. Deleting an index
      3. Portability Considerations
    3. 5. Building a Multitable Relational Database
      1. Designing a Database
        1. Step 1: Defining objects
        2. Step 2: Identifying tables and columns
        3. Step 3: Defining tables
        4. Domains, character sets, collations, and translations
        5. Getting into your database fast with keys
          1. Primary keys
          2. Foreign keys
      2. Working with Indexes
        1. What's an index, anyway?
        2. Why you should want an index
        3. Maintaining an index
      3. Maintaining Integrity
        1. Entity integrity
        2. Domain integrity
        3. Referential integrity
        4. Just when you thought it was safe
          1. Adding a column to an existing table
          2. Deleting a column from an existing table
        5. Potential problem areas
          1. Bad input data
          2. Operator error
          3. Mechanical failure
          4. Malice
          5. Data redundancy
          6. Exceeding the capacity of your DBMS
        6. Constraints
          1. Column constraints
          2. Table constraints
          3. Assertions
      4. Normalizing the Database
        1. First normal form
        2. Second normal form
        3. Third normal form
        4. Domain‐key normal form (DK/NF)
        5. Abnormal form
  7. III. Storing and Retrieving Data
    1. In this part . . .
    2. 6. Manipulating Database Data
      1. Retrieving Data
      2. Creating Views
        1. From tables
        2. With a selection condition
        3. With a modified attribute
      3. Updating Views
      4. Adding New Data
        1. Adding data one row at a time
        2. Adding data only to selected columns
        3. Adding a block of rows to a table
          1. Copying from a foreign data file
          2. Transferring all rows between tables
          3. Transferring selected columns and rows between tables
      5. Updating Existing Data
      6. Transferring Data
      7. Deleting Obsolete Data
    3. 7. Specifying Values
      1. Values
        1. Row values
        2. Literal values
        3. Variables
        4. Special variables
        5. Column references
      2. Value Expressions
        1. String value expressions
        2. Numeric value expressions
        3. Datetime value expressions
        4. Interval value expressions
        5. Conditional value expressions
      3. Functions
        1. Summarizing by using set functions
          1. COUNT
          2. AVG
          3. MAX
          4. MIN
          5. SUM
        2. Value functions
          1. String value functions
            1. SUBSTRING
            2. UPPER
            3. LOWER
            4. TRIM
            5. TRANSLATE and CONVERT
          2. Numeric value functions
            1. POSITION
            2. EXTRACT
            3. CHARACTER_LENGTH
            4. OCTET_LENGTH
            5. CARDINALITY
            6. ABS
            7. MOD
            8. LN
            9. EXP
            10. POWER
            11. SQRT
            12. FLOOR
            13. CEIL or CEILING
            14. WIDTH_BUCKET
          3. Datetime value functions
    4. 8. Using Advanced SQL Value Expressions
      1. CASE Conditional Expressions
        1. Using CASE with search conditions
          1. Updating values based on a condition
          2. Avoiding conditions that cause errors
        2. Using CASE with values
        3. A special CASE — NULLIF
        4. Another special CASE — COALESCE
      2. CAST Data‐Type Conversions
        1. Using CAST within SQL
        2. Using CAST between SQL and the host language
      3. Row Value Expressions
    5. 9. Zeroing In on the Data You Want
      1. Modifying Clauses
      2. FROM Clauses
      3. WHERE Clauses
        1. Comparison predicates
        2. BETWEEN
        3. IN and NOT IN
        4. LIKE and NOT LIKE
        5. SIMILAR
        6. NULL
        7. ALL, SOME, ANY
        8. EXISTS
        9. UNIQUE
        10. DISTINCT
        11. OVERLAPS
        12. MATCH
        13. Referential integrity rules and the MATCH predicate
      4. Logical Connectives
        1. AND
        2. OR
        3. NOT
      5. GROUP BY Clauses
      6. HAVING Clauses
      7. ORDER BY Clauses
    6. 10. Using Relational Operators
      1. UNION
        1. The UNION ALL operation
        2. The CORRESPONDING operation
      2. INTERSECT
      3. EXCEPT
      4. Various Joins
        1. Basic join
        2. Equi‐join
        3. Cross join
        4. Natural join
        5. Condition join
        6. Column‐name join
        7. Inner join
        8. Outer join
          1. Left outer join
          2. Right outer join
          3. Full outer join
        9. Union join
      5. ON versus WHERE
    7. 11. Delving Deep with Nested Queries
      1. What Subqueries Do
        1. Nested queries that return sets of rows
          1. Subqueries introduced by the keyword IN
          2. Subqueries introduced by the keyword NOT IN
        2. Nested queries that return a single value
        3. The ALL, SOME, and ANY quantifiers
        4. Nested queries that are an existence test
          1. EXISTS
          2. NOT EXISTS
        5. Other correlated subqueries
          1. Correlated subqueries introduced with IN
          2. Subqueries introduced with comparison operators
          3. Subqueries in a HAVING clause
        6. UPDATE, DELETE, and INSERT statements
    8. 12. Recursive Queries
      1. What Is Recursion?
        1. Houston, we have a problem
        2. Failure is not an option
      2. What Is a Recursive Query?
      3. Where Might You Use a Recursive Query?
        1. Querying the hard way
        2. Saving time with a recursive query
      4. Where Else Might You Use a Recursive Query?
  8. IV. Controlling Operations
    1. In this part . . .
    2. 13. Providing Database Security
      1. The SQL Data Control Language
      2. User Access Levels
        1. The database administrator
        2. Database object owners
        3. The public
      3. Granting Privileges to Users
        1. Roles
        2. Inserting data
        3. Looking at data
        4. Modifying table data
        5. Deleting obsolete rows from a table
        6. Referencing related tables
        7. Using domains, character sets, collations, and translations
        8. Causing SQL statements to be executed
      4. Granting the Power to Grant Privileges
      5. Taking Privileges Away
      6. Using GRANT and REVOKE Together to Save Time and Effort
    3. 14. Protecting Data
      1. Threats to Data Integrity
        1. Platform instability
        2. Equipment failure
        3. Concurrent access
          1. Transaction interaction trouble
          2. Serialization eliminates harmful interactions
      2. Reducing Vulnerability to Data Corruption
        1. Using SQL transactions
        2. The default transaction
        3. Isolation levels
          1. Getting mucked up by a dirty read
          2. Getting bamboozled by a nonrepeatable read
          3. Risking the phantom read
          4. Getting a reliable (if slower) read
        4. The implicit transaction‐starting statement
        5. SET TRANSACTION
        6. COMMIT
        7. ROLLBACK
        8. Locking database objects
        9. Backing up your data
        10. Savepoints and subtransactions
      3. Constraints Within Transactions
    4. 15. Using SQL within Applications
      1. SQL in an Application
        1. Keeping an eye out for the asterisk
        2. SQL strengths and weaknesses
        3. Procedural language strengths and weaknesses
        4. Problems in combining SQL with a procedural language
          1. Contrasting operating modes
          2. Data type incompatibilities
      2. Hooking SQL into Procedural Languages
        1. Embedded SQL
          1. Declaring host variables
          2. Converting data types
        2. Module language
          1. Module declarations
          2. Module procedures
        3. Object‐oriented RAD tools
        4. Using SQL with Microsoft Access
  9. V. Taking SQL to the Real World
    1. In this part . . .
    2. 16. Accessing Data with ODBC and JDBC
      1. ODBC
        1. The ODBC interface
        2. Components of ODBC
      2. ODBC in a Client/Server Environment
      3. ODBC and the Internet
        1. Server extensions
        2. Client extensions
          1. Helper applications
          2. ActiveX controls
          3. Scripts
      4. ODBC and an Intranet
      5. JDBC
    3. 17. Operating on XML Data with SQL
      1. How XML Relates to SQL
      2. The XML Data Type
        1. When to use the XML type
        2. When not to use the XML type
      3. Mapping SQL to XML and XML to SQL
        1. Mapping character sets
        2. Mapping identifiers
        3. Mapping data types
        4. Mapping tables
        5. Handling null values
        6. Generating the XML Schema
      4. SQL Functions that Operate on XML Data
        1. XMLELEMENT
        2. XMLFOREST
        3. XMLCONCAT
        4. XMLAGG
        5. XMLCOMMENT
        6. XMLPARSE
        7. XMLPI
        8. XMLQUERY
        9. XMLCAST
      5. Predicates
        1. DOCUMENT
        2. CONTENT
        3. XMLEXISTS
        4. VALID
      6. Transforming XML Data into SQL Tables
      7. Mapping Non‐Predefined Data Types to XML
        1. Domain
        2. Distinct UDT
        3. Row
        4. Array
        5. Multiset
      8. The Marriage of SQL and XML
  10. VI. Advanced Topics
    1. In this part . . .
    2. 18. Stepping through a Dataset with Cursors
      1. Declaring a Cursor
        1. The query expression
        2. The ORDER BY clause
        3. The updatability clause
        4. Sensitivity
        5. Scrollability
      2. Opening a Cursor
      3. Fetching Data from a Single Row
        1. Syntax
        2. Orientation of a scrollable cursor
        3. Positioned DELETE and UPDATE statements
      4. Closing a Cursor
    3. 19. Adding Procedural Capabilities with Persistent Stored Modules
      1. Compound Statements
        1. Atomicity
        2. Variables
        3. Cursors
        4. Conditions
        5. Handling conditions
          1. Handler declarations
          2. Handler actions and handler effects
        6. Conditions that aren't handled
        7. Assignment
      2. Flow of Control Statements
        1. IF...THEN...ELSE...END IF
        2. CASE...END CASE
          1. Simple CASE statement
          2. Searched CASE statement
        3. LOOP...ENDLOOP
        4. LEAVE
        5. WHILE...DO...END WHILE
        6. REPEAT...UNTIL...END REPEAT
        7. FOR...DO...END FOR
        8. ITERATE
      3. Stored Procedures
      4. Stored Functions
      5. Privileges
      6. Stored Modules
    4. 20. Handling Errors
      1. SQLSTATE
      2. WHENEVER Clause
      3. Diagnostics Areas
        1. The diagnostics header area
        2. The diagnostics detail area
        3. Constraint violation example
        4. Adding constraints to an existing table
        5. Interpreting the information returned by SQLSTATE
      4. Handling Exceptions
  11. VII. The Part of Tens
    1. In this part . . .
    2. 21. Ten Common Mistakes
      1. Assuming That Your Clients Know What They Need
      2. Ignoring Project Scope
      3. Considering Only Technical Factors
      4. Not Asking for Client Feedback
      5. Always Using Your Favorite Development Environment
      6. Using Your Favorite System Architecture Exclusively
      7. Designing Database Tables in Isolation
      8. Neglecting Design Reviews
      9. Skipping Beta Testing
      10. Not Documenting Your Process
    3. 22. Ten Retrieval Tips
      1. Verify the Database Structure
      2. Try Queries on a Test Database
      3. Double‐Check Queries That Include Joins
      4. Triple‐Check Queries with Subselects
      5. Summarize Data with GROUP BY
      6. Watch GROUP BY Clause Restrictions
      7. Use Parentheses with AND, OR, and NOT
      8. Control Retrieval Privileges
      9. Back Up Your Databases Regularly
      10. Handle Error Conditions Gracefully
  12. VIII. Appendixes
    1. In this part . . .
    2. A. SQL:2003 Reserved Words
    3. B. Glossary

Product information

  • Title: SQL For Dummies®
  • Author(s): Allen G. Taylor
  • Release date: August 2006
  • Publisher(s): Wiley
  • ISBN: 9780470046524