book

SQL For Dummies®, 7th Edition

Name: SQL For Dummies®, 7th Edition
Author: Allen G. Taylor
ISBN: 9780470557419

by Allen G. Taylor

February 2010

Beginner

464 pages

9h 48m

English

For Dummies

Read now

Unlock full access

Copyright
About The Author
Author's Acknowledgments
Publisher's Acknowledgments
Introduction
About This BookWho Should Read This Book?How This Book Is OrganizedPart I: Basic ConceptsPart II: Using SQL to Build DatabasesPart III: Storing and Retrieving DataPart IV: Controlling OperationsPart V: Taking SQL to the Real WorldPart VI: Advanced TopicsPart VII: The Part of TensAppendix and glossaryIcons Used in This BookGetting Started
I. Basic Concepts
1. Relational Database Fundamentals
1.1. Keeping Track of Things1.2. What Is a Database?1.3. Database Size and Complexity1.4. What Is a Database Management System?1.5. Flat Files1.6. Database Models1.6.1. Relational model1.6.2. Why relational is better1.6.3. Components of a relational database1.6.4. Dealing with your relations1.6.5. Enjoy the view1.6.6. Schemas, domains, and constraints1.6.6.1. Schemas1.6.6.2. Domains1.6.6.3. Constraints1.6.7. The object model challenges the relational model1.6.8. The object-relational model1.7. Database Design Considerations
2. SQL Fundamentals
2.1. What SQL Is and Isn't2.2. A (Very) Little History2.3. SQL Statements2.4. Reserved Words2.5. Data Types2.5.1. Exact numerics2.5.1.1. INTEGER data type2.5.1.2. SMALLINT data type2.5.1.3. BIGINT data type2.5.1.4. NUMERIC data type2.5.1.5. DECIMAL data type2.5.2. Approximate numerics2.5.2.1. REAL data type2.5.2.2. DOUBLE PRECISION data type2.5.2.3. FLOAT data type2.5.3. Character strings2.5.3.1. CHARACTER data type2.5.3.2. CHARACTER VARYING data type2.5.3.3. CHARACTER LARGE OBJECT data type2.5.3.4. NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT data types2.5.4. Binary strings2.5.4.1. BINARY data type2.5.4.2. BINARY VARYING data type2.5.4.3. BINARY LARGE OBJECT data type2.5.5. Booleans2.5.6. Datetimes2.5.6.1. DATE data type2.5.6.2. TIME WITHOUT TIME ZONE data type2.5.6.3. TIMESTAMP WITHOUT TIME ZONE data type2.5.6.4. TIME WITH TIME ZONE data type2.5.6.5. TIMESTAMP WITH TIME ZONE data type2.5.7. Intervals2.5.8. XML type2.5.9. ROW types2.5.10. Collection types2.5.10.1. ARRAY type2.5.10.2. MULTISET type2.5.11. REF types2.5.12. User-defined types2.5.12.1. Distinct types2.5.12.2. Structured types2.5.12.2.1. Constructors2.5.12.2.2. Mutators and observers2.5.12.2.3. Subtypes and supertypes2.5.12.2.4. Example of a structured type2.5.13. Data type summary2.6. Null Values2.7. Constraints2.8. Using SQL in a Client/Server System2.8.1. The server2.8.1.1. What the server is2.8.1.2. What the server does2.8.2. The client2.8.2.1. What the client is2.8.2.2. What the client does2.9. Using SQL on the Internet or an Intranet
3. The Components of SQL
3.1. Data Definition Language3.1.1. When "Just do it!" is not good advice3.1.2. Creating tables3.1.3. A room with a view3.1.3.1. Single-table view3.1.3.2. Creating a multitable view3.1.4. Collecting tables into schemas3.1.5. Ordering by catalog3.1.6. Getting familiar with DDL statements3.1.6.1. CREATE3.1.6.2. ALTER3.1.6.3. DROP3.2. Data Manipulation Language3.2.1. Value expressions3.2.1.1. Numeric value expressions3.2.1.2. String value expressions3.2.1.3. Datetime and interval value expressions3.2.1.4. Boolean value expressions3.2.1.5. User-defined type value expressions3.2.1.6. Row value expressions3.2.1.7. Collection value expressions3.2.1.8. Reference value expressions3.2.2. Predicates3.2.3. Logical connectives3.2.4. Set functions3.2.4.1. COUNT3.2.4.2. MAX3.2.4.3. MIN3.2.4.4. SUM3.2.4.5. AVG3.2.5. Subqueries3.3. Data Control Language3.3.1. Transactions3.3.2. Users and privileges3.3.3. Referential integrity constraints can jeopardize your data3.3.4. Delegating responsibility for security
II. Using SQL to Build Databases

4. Building and Maintaining a Simple Database Structure
4.1. Using a RAD Tool to Build a Simple Database4.1.1. Deciding what to track4.1.2. Creating a database table4.1.2.1. Building a database table in Datasheet view4.1.2.2. Building a database table in Design view4.1.3. Altering the table structure4.1.4. Creating an index4.1.5. Deleting a table4.2. Building POWER with SQL's DDL4.2.1. Using SQL with Microsoft Access4.2.2. Creating a table4.2.3. Creating an index4.2.4. Altering the table structure4.2.5. Deleting a table4.2.6. Deleting an index4.3. Portability Considerations
5. Building a Multitable Relational Database
5.1. Designing a Database5.1.1. Step 1: Defining objects5.1.2. Step 2: Identifying tables and columns5.1.3. Step 3: Defining tables5.1.4. Domains, character sets, collations, and translations5.1.5. Getting into your database fast with keys5.1.5.1. Primary keys5.1.5.2. Foreign keys5.2. Working with Indexes5.2.1. What's an index, anyway?5.2.2. Why you should want an index5.2.3. Maintaining an index5.3. Maintaining Data Integrity5.3.1. Entity integrity5.3.2. Domain integrity5.3.3. Referential integrity5.3.3.1. Trouble between parent and child tables5.3.3.2. Cascading deletions — use with care5.3.3.3. Alternative ways to control update anomalies5.3.4. Just when you thought it was safe . . .5.3.4.1. Adding a column to an existing table5.3.4.2. Deleting a column from an existing table5.3.5. Potential problem areas5.3.5.1. Bad input data5.3.5.2. Operator error5.3.5.3. Mechanical failure5.3.5.4. Malice5.3.5.5. Data redundancy5.3.5.6. Exceeding the capacity of your DBMS5.3.6. Constraints5.3.6.1. Column constraints5.3.6.2. Table constraints5.3.6.3. Assertions5.4. Normalizing the Database5.4.1. Modification anomalies and normal forms5.4.2. First normal form5.4.3. Second normal form5.4.4. Third normal form5.4.5. Domain-key normal form (DK/NF)5.4.6. Abnormal form
III. Storing and Retrieving Data
6. Manipulating Database Data
6.1. Retrieving Data6.2. Creating Views6.2.1. From tables6.2.2. With a selection condition6.2.3. With a modified attribute6.3. Updating Views6.4. Adding New Data6.4.1. Adding data one row at a time6.4.2. Adding data only to selected columns6.4.3. Adding a block of rows to a table6.4.3.1. Copying from a foreign data file6.4.3.2. Transferring all rows between tables6.4.3.3. Transferring selected columns and rows between tables6.5. Updating Existing Data6.6. Transferring Data6.7. Deleting Obsolete Data
7. Specifying Values
7.1. Values7.1.1. Row values7.1.2. Literal values7.1.3. Variables7.1.4. Special variables7.1.5. Column references7.2. Value Expressions7.2.1. String value expressions7.2.2. Numeric value expressions7.2.3. Datetime value expressions7.2.4. Interval value expressions7.2.5. Conditional value expressions7.3. Functions7.3.1. Summarizing by using set functions7.3.1.1. COUNT7.3.1.2. AVG7.3.1.3. MAX7.3.1.4. MIN7.3.1.5. SUM7.3.2. Value functions7.3.2.1. String value functions7.3.2.1.1. SUBSTRING7.3.2.1.2. SUBSTRING SIMILAR7.3.2.1.3. SUBSTRING_REGEX7.3.2.1.4. TRANSLATE_REGEX7.3.2.1.5. OVERLAY7.3.2.1.6. UPPER7.3.2.1.7. LOWER7.3.2.1.8. TRIM7.3.2.1.9. TRANSLATE and CONVERT7.3.2.2. Numeric value functions7.3.2.2.1. POSITION7.3.2.2.2. OCCURRENCES_REGEX7.3.2.2.3. POSITION_REGEX7.3.2.2.4. EXTRACT7.3.2.2.5. CHARACTER_LENGTH7.3.2.2.6. OCTET_LENGTH7.3.2.2.7. CARDINALITY7.3.2.2.8. ABS7.3.2.2.9. MOD7.3.2.2.10. LN7.3.2.2.11. EXP7.3.2.2.12. POWER7.3.2.2.13. SQRT7.3.2.2.14. FLOOR7.3.2.2.15. CEIL or CEILING7.3.2.2.16. WIDTH_BUCKET7.3.2.3. Datetime value functions7.3.2.4. Interval value functions
8. Using Advanced SQL Value Expressions
8.1. CASE Conditional Expressions8.1.1. Using CASE with search conditions8.1.1.1. Updating values based on a condition8.1.1.2. Avoiding conditions that cause errors8.1.2. Using CASE with values8.1.3. A special CASE — NULLIF8.1.4. Another special CASE — COALESCE8.2. CAST Data-Type Conversions8.2.1. Using CAST within SQL8.2.2. Using CAST between SQL and the host language8.3. Row Value Expressions
9. Zeroing In on the Data You Want
9.1. Modifying Clauses9.2. FROM Clauses9.3. WHERE Clauses9.3.1. Comparison predicates9.3.2. BETWEEN9.3.3. IN and NOT IN9.3.4. LIKE and NOT LIKE9.3.5. SIMILAR9.3.6. NULL9.3.7. ALL, SOME, ANY9.3.8. EXISTS9.3.9. UNIQUE9.3.10. DISTINCT9.3.11. OVERLAPS9.3.12. MATCH9.3.13. Referential integrity rules and the MATCH predicate9.4. Logical Connectives9.4.1. AND9.4.2. OR9.4.3. NOT9.5. GROUP BY Clauses9.6. HAVING Clauses9.7. ORDER BY Clauses
10. Using Relational Operators
10.1. UNION10.1.1. The UNION ALL operation10.1.2. The CORRESPONDING operation10.2. INTERSECT10.3. EXCEPT10.4. Various Joins10.4.1. Basic join10.4.2. Equi-join10.4.3. Cross join10.4.4. Natural join10.4.5. Condition join10.4.6. Column-name join10.4.7. Inner join10.4.8. Outer join10.4.8.1. Left outer join10.4.8.2. Right outer join10.4.8.3. Full outer join10.4.9. Union join10.5. ON versus WHERE
11. Delving Deep with Nested Queries
11.1. What Subqueries Do11.1.1. Nested queries that return sets of rows11.1.1.1. Subqueries introduced by the keyword IN11.1.1.2. Subqueries introduced by the keyword NOT IN11.1.2. Nested queries that return a single value11.1.3. The ALL, SOME, and ANY quantifiers11.1.4. Nested queries that are an existence test11.1.4.1. EXISTS11.1.4.2. NOT EXISTS11.1.5. Other correlated subqueries11.1.5.1. Correlated subqueries introduced with IN11.1.5.2. Subqueries introduced with comparison operators11.1.5.3. Subqueries in a HAVING clause11.1.6. UPDATE, DELETE, and INSERT
12. Recursive Queries
12.1. What Is Recursion?12.1.1. Houston, we have a problem12.1.2. Failure is not an option12.2. What Is a Recursive Query?12.3. Where Might You Use a Recursive Query?12.3.1. Querying the hard way12.3.2. Saving time with a recursive query12.4. Where Else Might You Use a Recursive Query?
IV. Controlling Operations
13. Providing Database Security
13.1. The SQL Data Control Language13.2. User Access Levels13.2.1. The database administrator13.2.2. Database object owners13.2.3. The public13.3. Granting Privileges to Users13.3.1. Roles13.3.2. Inserting data13.3.3. Looking at data13.3.4. Modifying table data13.3.5. Deleting obsolete rows from a table13.3.6. Referencing related tables13.3.7. Using domains, character sets, collations, and translations13.3.8. Causing SQL statements to be executed13.4. Granting Privileges across Levels13.5. Granting the Power to Grant Privileges13.6. Taking Privileges Away13.7. Using GRANT and REVOKE Together to Save Time and Effort
14. Protecting Data
14.1. Threats to Data Integrity14.1.1. Platform instability14.1.2. Equipment failure14.1.3. Concurrent access14.1.3.1. Transaction interaction trouble14.1.3.2. Serialization eliminates harmful interactions14.2. Reducing Vulnerability to Data Corruption14.2.1. Using SQL transactions14.2.2. The default transaction14.2.3. Isolation levels14.2.3.1. Getting mucked up by a dirty read14.2.3.2. Getting bamboozled by a nonrepeatable read14.2.3.3. Risking the phantom read14.2.3.4. Getting a reliable (if slower) read14.2.4. The implicit transaction-starting statement14.2.5. SET TRANSACTION14.2.6. COMMIT14.2.7. ROLLBACK14.2.8. Locking database objects14.2.9. Backing up your data14.2.10. Savepoints and subtransactions14.3. Constraints Within Transactions
15. Using SQL within Applications
15.1. SQL in an Application15.1.1. Keeping an eye out for the asterisk15.1.2. SQL strengths and weaknesses15.1.3. Procedural languages' strengths and weaknesses15.1.4. Problems in combining SQL with a procedural language15.1.4.1. Contrasting operating modes15.1.4.2. Data type incompatibilities15.2. Hooking SQL into Procedural Languages15.2.1. Embedded SQL15.2.1.1. Declaring host variables15.2.1.2. Converting data types15.2.2. Module language15.2.2.1. Module declarations15.2.2.2. Module procedures15.2.3. Object-oriented RAD tools15.2.4. Using SQL with Microsoft Access
V. Taking SQL to the Real World
16. Accessing Data with ODBC and JDBC
16.1. ODBC16.1.1. The ODBC interface16.1.2. Components of ODBC16.2. ODBC in a Client/Server Environment16.3. ODBC and the Internet16.3.1. Server extensions16.3.2. Client extensions16.3.2.1. Helper applications16.3.2.2. ActiveX controls16.3.2.3. Scripts16.4. ODBC and an Intranet16.5. JDBC
17. Operating on XML Data with SQL
17.1. How XML Relates to SQL17.2. The XML Data Type17.2.1. When to use the XML type17.2.2. When not to use the XML type17.3. Mapping SQL to XML and XML to SQL17.3.1. Mapping character sets17.3.2. Mapping identifiers17.3.3. Mapping data types17.3.4. Mapping tables17.3.5. Handling null values17.3.6. Generating the XML Schema17.4. SQL Functions That Operate on XML Data17.4.1. XMLDOCUMENT17.4.2. XMLELEMENT17.4.3. XMLFOREST17.4.4. XMLCONCAT17.4.5. XMLAGG17.4.6. XMLCOMMENT17.4.7. XMLPARSE17.4.8. XMLPI17.4.9. XMLQUERY17.4.10. XMLCAST17.5. Predicates17.5.1. DOCUMENT17.5.2. CONTENT17.5.3. XMLEXISTS17.5.4. VALID17.6. Transforming XML Data into SQL Tables17.7. Mapping Non-Predefined Data Types to XML17.7.1. Domain17.7.2. Distinct UDT17.7.3. Row17.7.4. Array17.7.5. Multiset17.8. The Marriage of SQL and XML
VI. Advanced Topics
18. Stepping through a Dataset with Cursors
18.1. Declaring a Cursor18.1.1. Query expression18.1.2. ORDER BY clause18.1.3. Updatability clause18.1.4. Sensitivity18.1.5. Scrollability18.2. Opening a Cursor18.3. Fetching Data from a Single Row18.3.1. Syntax18.3.2. Orientation of a scrollable cursor18.3.3. Positioned DELETE and UPDATE statements18.4. Closing a Cursor
19. Adding Procedural Capabilities with Persistent Stored Modules
19.1. Compound Statements19.1.1. Atomicity19.1.2. Variables19.1.3. Cursors19.1.4. Conditions19.1.5. Handling conditions19.1.5.1. Handler declarations19.1.5.2. Handler actions and handler effects19.1.6. Conditions that aren't handled19.1.7. Assignment19.2. Flow of Control Statements19.2.1. IF...THEN...ELSE...END IF19.2.2. CASE...END CASE19.2.2.1. Simple CASE statement19.2.2.2. Searched CASE statement19.2.3. LOOP...ENDLOOP19.2.4. LEAVE19.2.5. WHILE...DO...END WHILE19.2.6. REPEAT...UNTIL...END REPEAT19.2.7. FOR...DO...END FOR19.2.8. ITERATE19.3. Stored Procedures19.4. Stored Functions19.5. Privileges19.6. Stored Modules
20. Handling Errors
20.1. SQLSTATE20.2. WHENEVER Clause20.3. Diagnostics Areas20.3.1. Diagnostics header area20.3.2. Diagnostics detail area20.3.3. Constraint violation example20.3.4. Adding constraints to an existing table20.3.5. Interpreting the information returned by SQLSTATE20.4. Handling Exceptions
21. Triggers
21.1. Examining Some Applications of Triggers21.2. Creating a Trigger21.2.1. Statement and row triggers21.2.2. When a trigger fires21.2.3. The triggered SQL statement21.2.4. An example trigger definition21.3. Firing a Succession of Triggers21.4. Referencing Old Values and New Values21.5. Firing Multiple Triggers on a Single Table
VII. The Part of Tens
22. Ten Common Mistakes
22.1. Assuming That Your Clients Know What They Need22.2. Ignoring Project Scope22.3. Considering Only Technical Factors22.4. Not Asking for Client Feedback22.5. Always Using Your Favorite Development Environment22.6. Using Your Favorite System Architecture Exclusively22.7. Designing Database Tables in Isolation22.8. Neglecting Design Reviews22.9. Skipping Beta Testing22.10. Not Documenting Your Process
23. Ten Retrieval Tips
23.1. Verify the Database Structure23.2. Try Queries on a Test Database23.3. Double-Check Queries That Include Joins23.4. Triple-Check Queries with Subselects23.5. Summarize Data with GROUP BY23.6. Watch GROUP BY Clause Restrictions23.7. Use Parentheses with AND, OR, and NOT23.8. Control Retrieval Privileges23.9. Back Up Your Databases Regularly23.10. Handle Error Conditions Gracefully
A. SQL:2008 Reserved Words
Bonus Content: Glossary

Content preview from SQL For Dummies®, 7th Edition

Chapter 19. Adding Procedural Capabilities with Persistent Stored Modules

Some of the leading practitioners of database technology have been working on the standards process for years. Even after a standard has been issued and accepted by the worldwide database community, progress toward the next standard doesn't slow down. A seven-year gap separated the issuance of SQL-92 and the release of the first component of SQL:1999. During the intervening years, ANSI and ISO issued an addendum to SQL-92, called SQL-92/PSM (Persistent Stored Modules). This addendum formed the basis for a part of SQL:1999 with the same name. SQL/PSM defines a number of statements that give SQL flow of control structures comparable to the flow of control structures available in full-featured programming languages. It enables you to use SQL to perform tasks that programmers previously were forced to use other tools for. Can you imagine what your life would have been like in the caveman times of 1992, when you'd have to repeatedly swap between SQL and its procedural host language just to do your work?

Compound Statements

Throughout this book, SQL is represented as a nonprocedural language that deals with data a set at a time rather than a record at a time. With the addition of the facilities covered in this chapter, however, this statement is not as true as it used to be. Although SQL still deals with data a set at a time, it is becoming more procedural.

Archaic SQL (defined by SQL-92) doesn't follow the procedural ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470557419Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL For Dummies®, 7th Edition

by Allen G. Taylor

Chapter 19. Adding Procedural Capabilities with Persistent Stored Modules

Compound Statements

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.