Register your product to gain access to bonus material or receive a coupon.
Sams Teach Yourself SQL in One Hour a Day, 5th Edition
- By Ryan Stephens, Ron Plew, Arie D. Jones
- Published May 29, 2009 by Sams. Part of the Sams Teach Yourself series.
eBook
- Sorry, this book is no longer in print.
- Includes EPUB and PDF
- About eBook Formats
This eBook includes the following formats, accessible from your Account page after purchase:
EPUB
The open industry format known for its reflowable content and usability on supported mobile devices.
PDF
The popular standard, used most often with the free Acrobat® Reader® software.
This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.
Description
- Copyright 2009
- Edition: 5th
- eBook
- ISBN-10: 0-13-264930-6
- ISBN-13: 978-0-13-264930-8
The Fifth Edition of Sams Teach Yourself SQL in 21 Days
More than 48,000 sold!
In just one hour a day, you’ll have all the skills you need to begin creating effective SQL queries, reports, and database applications. With this complete tutorial, you’ll quickly master the basics and then move on to more advanced features and concepts:
- Quickly apply essential SQL techniques in useful, real-world queries
- Design trustworthy, high-performance databases
- Manipulate your data with views and transactions
- Leverage powerful features including stored procedures, triggers, and cursors
- Work with new objects introduced with the latest SQL standards
- Get practical, expert tips on implementing SQL in your business environment
Learn on your own time, at your own pace
- No previous SQL or database experience required
- Learn techniques that work with any current version of SQL
- Discover how to write faster, more efficient queries
- Secure your data using best practices from experienced database administrators
- Build more powerful databases with features exclusive to Oracle SQL*Plus, Oracle PL/SQL, and Microsoft Transact-SQL
- Write queries for the free, open source MySQL database
- Embed your SQL code in other applications
Ryan Stephens and Ron Plew are President and VP of Perpetual Technologies, Inc. (PTI) in Indianapolis, IN, providing managed services and consulting for top database implementations running Oracle, SQL Server, and other leading technologies. They taught for 5+ years as adjunct professors at Indiana University-Purdue University. Their books include Sams Teach Yourself SQL in 24 Hours, First through Fourth Editions, Sams Teach Yourself SQL in 21 Days, Second through Fourth Editions, and Database Design.
Table of Contents
Introduction 1
PART I: Introducing SQL
LESSON 1: Getting Started with SQL 5
A Brief History of SQL 5
A Brief History of Databases 6
Today’s Database Landscape 11
A Cross-Product Language 12
Early Implementations 12
SQL and Client/Server Application Development 13
An Overview of SQL 13
Popular SQL Implementations 14
MySQL 14
Oracle 14
Microsoft SQL Server and Sybase 15
IBM DB2 16
Open Database Connectivity 16
Embedding SQL in Application Programming 17
LESSON 2: Introducing the Query 21
Exploring SQL’s Background 21
Learning Basic Query Syntax 22
The Building Blocks of Data Retrieval: SELECT and FROM 23
Applying Query Concepts 25
Writing Your First Query 26
Terminating a SQL Statement 28
Selecting Individual Columns 28
Changing the Order of the Columns 29
Selecting Different Tables 31
Selecting Distinct Values 31
Exercises 37
LESSON 3: Expressions, Conditions, and Operators 39
Working with Query Expressions 40
Placing Conditions on Queries 40
Learning How to Use Operators 42
Arithmetic Operators 42
Comparison Operators 55
Character Operators 63
Logical Operators 70
Set Operators 75
Miscellaneous Operators: IN and BETWEEN 78
LESSON 4: Clauses in SQL Queries 85
Specifying Criteria with the WHERE Clause 87
Order from Chaos: The ORDER BY Clause 89
The GROUP BY Clause 98
The HAVING Clause 105
Combining Clauses 112
Example 4.1 112
Example 4.2 113
Example 4.3 113
Example 4.4 115
LESSON 5: Joining Tables 121
Joining Multiple Tables in a Single SELECT Statement 121
Cross Joining Tables 123
Finding the Correct Column 128
Joining Tables Based on Equality 129
Joining Tables Based on Nonequality 137
OUTER JOINs Versus INNER JOINs 139
Joining a Table to Itself: The Self Join 143
LESSON 6: Embedding Subqueries into Queries 151
Building a Subquery 153
Using Aggregate Functions with Subqueries 160
Nesting Subqueries 162
Referencing Outside with Correlated Subqueries 166
Using EXISTS, ANY, and ALL 169
LESSON 7: Molding Data with Built-in Functions 179
Using Aggregate Functions to Summarize Data 180
COUNT 180
SUM 181
AVG 182
MAX 184
MIN 185
VARIANCE 186
STDDEV 186
Using Functions to Format Date and Time Values 187
ADD_MONTHS/ADD_DATE 188
LAST_DAY 190
MONTHS_BETWEEN 191
NEXT_DAY 193
SYSDATE 193
Using Functions for Arithmetic Operations 195
ABS 195
CEIL and FLOOR 196
EXP 196
LN and LOG 197
MOD 198
POWER 199
SIGN 199
SQRT 200
Using Functions to Modify the Appearance of Character Values 201
CHR 201
CONCAT 202
INITCAP 203
LOWER and UPPER 203
LPAD and RPAD 205
LTRIM and RTRIM 206
REPLACE 207
SUBSTR 209
TRANSLATE 213
INSTR 214
LENGTH 214
Conversion Functions 215
TO_CHAR 215
TO_NUMBER 217
Miscellaneous Functions 217
GREATEST and LEAST 217
USER 218
Supplemental Examples of MySQL Character Functions 219
LENGTH 219
LOCATE 219
INSTR 220
LPAD 220
RPAD 220
LEFT 220
RIGHT 221
SUBSTRING 221
LTRIM 221
RTRIM 222
TRIM 222
Supplemental Examples of MySQL Date Functions 222
DATE_FORMAT 223
TIME_FORMAT 224
CURDATE 224
CURTIME 225
PART II: Database Design
LESSON 8: Database Normalization 229
Normalizing a Database 229
The Raw Database 229
Logical Database Design 230
The Needs of the End User 230
Data Redundancy 231
Understanding the Normal Forms 231
The First Normal Form 232
The Second Normal Form 233
The Third Normal Form 234
Making Normalization Work 235
Referential Integrity 235
Benefits of Normalization 236
Drawbacks of Normalization 237
Denormalizing a Database 237
LESSON 9: Creating and Maintaining Tables 241
Beginning with the CREATE DATABASE Statement 242
CREATE DATABASE Options 243
Database Design 244
Creating a Data Dictionary (System Catalog) 244
Creating Key Fields 246
Defining Tables with the CREATE TABLE Statement 247
The Table Name 248
The Field Name 249
The Field’s Data Type 249
Table Storage and Sizing 254
Creating a Table from an Existing Table 255
Modifying Table Structures with the ALTER TABLE Statement 257
The DROP TABLE Statement 261
The DROP DATABASE Statement 262
Working with DROP TABLE and DROP DATABASE 262
LESSON 10: Controlling Data Integrity 267
Introducing Constraints 267
Data Integrity 267
Why Use Constraints? 268
Exploring Types of Constraints 269
NOT NULL Constraints 269
Primary Key Constraints 271
Unique Constraints 273
Foreign Key Constraints 274
Check Constraints 276
Managing Constraints 277
Using the Right Order 278
Different Approaches to Creating Constraints 279
Example Oracle Referential Integrity Reports 279
PART III: Data Manipulation
LESSON 11: Manipulating Data 285
Introducing Data-Manipulation Statements 285
Entering Data with the INSERT Statement 286
Entering One Record with the INSERT...VALUES Statement 286
Inserting NULL Values 289
Inserting Unique Values 291
Entering Multiple Records with the INSERT...SELECT Statement 292
Modifying Existing Data with the UPDATE Statement 295
Removing Information with the DELETE Statement 298
Importing and Exporting Data from Foreign Sources 303
Microsoft Access 303
Microsoft SQL Server 304
Oracle 305
MySQL 305
LESSON 12: Dates and Time in SQL 309
How Are Date and Time Values Stored? 310
ANSI Standard Data Types for Date and Time 310
DATETIME Elements 311
Implementation of Specific Data Types 311
Applying Date Functions to the Query 312
The Current Date 312
Time Zones 314
Adding Time to Dates 315
Subtracting Dates 318
Comparing Dates and Time Periods 320
Other Miscellaneous Date Functions 320
Converting Date Formats 321
Date Pictures 322
Converting Dates to Character Strings 324
Converting Character Strings to Dates 325
LESSON 13: Creating Views 331
Introducing Views 331
Using Views 332
Exploring a Simple View 335
Renaming Columns 337
Examining SQL View Processing 338
Restrictions on Using SELECT 343
Modifying Data in a View 343
Problems with Modifying Data Using Views 345
Common Applications of Views 346
Removing Views with the DROP VIEW Statement 350
LESSON 14: Controlling Transactions 353
Transaction Management 354
The Banking Application 354
Beginning a Transaction 356
Finishing a Transaction 358
Canceling the Transaction 361
Using Transaction Savepoints 363
PART IV: Database Administration
LESSON 15: Creating Indexes on Tables to Improve Performance 369
What Are Indexes? 370
Indexing Tips 378
Indexing on More Than One Field 379
Using the UNIQUE Keyword with CREATE INDEX 381
Indexes and Joins 382
Using Clustered Indexes 384
LESSON 16: Streamlining SQL Statements for Improved Performance 389
Making Your SQL Statements Readable 390
Avoiding the Full-Table Scan 391
Adding a New Index 393
Arranging Elements in a Query 393
Procedures 395
Avoiding OR 396
OLAP Versus OLTP 397
Tuning an OLTP System 397
Tuning an OLAP System 398
Batch Loads Versus Transactional Processing 398
Optimizing Data Loads by Dropping Indexes 400
COMMIT Statement 401
Rebuilding Tables and Indexes in a Dynamic Environment 402
Tuning the Database 405
Identifying Performance Obstacles 407
Using Built-in Tuning Tools 409
LESSON 17: Database Security 413
Security’s Role in Database Administration 413
Popular Database Products and Security 414
Oracle Express and MySQL Security 416
Creating Users 416
Creating Roles 419
User Privileges 421
Using Views for Security Purposes 429
Using Synonyms in Place of Views 430
Using Views to Solve Security Problems 431
Using the WITH GRANT OPTION Clause 433
LESSON 18: Exploring the Data Dictionary (System Catalog) 437
An Introduction to the Data Dictionary 437
Identifying Data Dictionary Users 438
Exploring the Contents of the Data Dictionary 439
Oracle’s Data Dictionary 439
MySQL Data Dictionary 440
A Look Inside Oracle’s Data Dictionary 440
User Views 440
System DBA Views 449
Dynamic Performance Views 458
A Look Inside MySQL’s Data Dictionary 459
Showing Table Commands Within MySQL 460
Using INFORMATION_SCHEMA 461
PART V: More SQL Objects
LESSON 19: Temporary Tables, Stored Procedures, Triggers, and Cursors 467
Creating Temporary Tables 468
Using Cursors 472
Creating a Cursor 473
Opening a Cursor 473
Scrolling a Cursor 473
Testing a Cursor’s Status 474
Closing a Cursor 475
The Scope of Cursors 475
Creating and Using Stored Procedures 476
Removing a Stored Procedure 478
Designing and Using Triggers 479
Triggers and Transactions 480
Restrictions on Using Triggers 481
Nested Triggers 481
Using Embedded SQL 481
Static and Dynamic SQL 482
LESSON 20: New Objects in the Latest Standard 487
Exploring the CREATE ROLE Statement 488
Creating Triggers 490
Using the CREATE TYPE Statement 492
Regular Expressions 497
Working with BLOB Data Types 498
A Short XML Example 499
PART VI: Advanced SQL Programming
LESSON 21: Using SQL to Generate SQL Statements 503
Understanding the Power of SQL Statement Generation 503
Miscellaneous SQL*Plus Commands 505
SET ECHO ON/OFF 505
SET FEEDBACK ON/OFF 506
SET HEADING ON/OFF 506
SPOOL FILENAME/OFF 506
Counting the Rows in All Tables 507
Granting System Privileges to Multiple Users 511
Granting Privileges on Your Tables to Another User 513
Disabling Table Constraints to Load Data 516
Creating Numerous Synonyms in a Single Bound 517
Creating Views on Your Tables 520
Truncating All Tables in a Schema 522
Using SQL to Generate Shell Scripts 523
Applying SQL Generation and Other Concepts to the Real World 524
LESSON 22: Creating Complex SQL Queries 529
CREATE TABLE statements 529
Examples of Complex Queries 532
Computing Age from Date of Birth 532
Breaking a Fraction of a Day into Hours, Minutes, and Seconds 533
Converting Bytes to Kilobytes to Megabytes 536
Database Fragmentation Report 536
Subqueries in DML 537
Formatting Your Dates 538
Subquery Involving a Maximum Value 539
Multiple Subqueries 540
Using Dashes and Parentheses to Format Numeric Values 541
Increasing a Numeric Value by a Given Percent 542
Finding the Next Highest Numeric Value in a Column 542
Dealing with NULL Values 544
Tips for Building Complex Queries 546
LESSON 23: Debugging Your SQL Statements 551
Exploring Common SQL Errors 551
Table or View that Does Not Exist 552
Invalid Username or Password 553
FROM Keyword Not Specified 553
Group Function Not Allowed 554
Invalid Column Name 555
Missing Keyword 556
Missing Left Parenthesis 556
Missing Right Parenthesis 557
Missing Comma 558
Column Ambiguously Defined 558
SQL Command Not Properly Ended 559
Missing Expression 559
Not Enough Arguments for Function 560
Not Enough Values 560
Integrity Constraint Violated—Parent Key Not Found 561
Oracle Not Available 562
Inserted Value Too Large for Column 562
TNS: Listener Could Not Resolve SID Given in Connect Descriptor 563
Insufficient Privileges During Grants 563
Escape Character in Your Statement—Invalid Character 564
Cannot Create Operating System File 564
Exploring Common Logical Mistakes 564
Using Reserved Words in Your SQL Statement 564
The Use of DISTINCT When Selecting Multiple Columns 566
Dropping an Unqualified Table 566
The Use of Public Synonyms in a Multischema Database 567
The Dreaded Cartesian Product 567
Failure to Enforce Input Standards 568
Failure to Enforce File System Structure Conventions 568
Allowing Large Tables to Take Default Storage Parameters 569
Placing Objects in the System Tablespace 569
Failure to Compress Large Backup Files 570
Failure to Budget System Resources 570
Preventing Problems with Your Data 571
LESSON 24: Embedding SQL in Application Programming 575
A Quick Trip Through Some Application Development Tools 575
ODBC 576
Oracle Express 576
SQL in Java with JDBC 576
SQL in .NET with OleDB 577
Getting Set Up for Oracle 577
Creating the Database 577
Using Java and SQL 581
Using . NET and SQL 583
PART VII: SQL in Various Database Implementations
LESSON 25: Using Oracle SQL*Plus to Satisfy Reporting Needs 587
An Introduction to SQL*Plus 587
The SQL*Plus Buffer 588
Viewing Table Structure with the DESCRIBE Command 593
Displaying Settings with the SHOW Command 594
Manipulating Files with File Commands 595
The SAVE, GET, and EDIT Commands 595
Starting a File 596
Spooling Query Output 598
Customizing the Work Environment with SET Commands 599
Removing Settings with the CLEAR Command 603
Formatting Your Output 603
TTITLE and BTITLE 604
Formatting Columns (COLUMN, HEADING, FORMAT) 605
Creating Report and Group Summaries 606
BREAK ON 607
COMPUTE 608
Using Variables in SQL*Plus 610
Substitution Variables (&) 611
DEFINE 611
ACCEPT 612
NEW_VALUE 614
Using the DUAL Table 615
Exploring the DECODE Function 616
DATE Conversions 619
Running a Series of SQL Files 622
Adding Comments to Your SQL Script 623
Creating Advanced Reports 624
LESSON 26: An Introduction to Oracle PL/SQL 629
Introducing PL/SQL 629
The Structure of a PL/SQL Block 630
The DECLARE Section 632
The PROCEDURE Section 635
The EXCEPTION Section 640
Transactional Control in PL/SQL 644
Putting Everything Together 644
Sample Tables and Data 645
A Simple PL/SQL Block 646
A More Extended Example of a PL/SQL Block 648
Using Stored Procedures, Packages, and Triggers 652
Sample Procedure 653
Sample Package 654
Sample Trigger 654
LESSON 27: An Introduction to Transact-SQL 661
An Overview of Transact-SQL 661
Extensions to ANSI QL 662
Who Uses Transact-SQL? 662
The Basic Components of Transact-SQL 662
Data Types 663
Character Strings 663
Numeric Data Types 663
Date Data Types 664
Money Data Types 664
Binary Strings 664
bit: A Logical Data Type 665
Accessing the Database with Transact-SQL 665
The BASEBALL Database 665
Declaring Local Variables 668
Declaring Global Variables 668
Using Variables 670
The PRINT Command 671
Establishing Flow Control 672
BEGIN and END Statements 672
IF ...ELSE Statements 673
The EXISTS Condition 675
Testing a Query’s Result 675
The WHILE Loop 676
The BREAK Command 677
The CONTINUE Command 677
Using the WHILE Loop to Scroll Through a Table 678
Using Transact-SQL Wildcard Operators 679
Date Conversions 680
SQL Server Diagnostic Tools—SET Commands 681
LESSON 28: Using MySQL on a UNIX-based System 685
MySQL Administration 686
Installing MySQL 686
Starting and Stopping MySQL 687
Initial MySQL Privileges 688
The MySQL Terminal Monitor 688
Connecting to the Database 689
Command-Line Options 689
Entering MySQL Monitor Commands 690
Command-Line History 692
Batch Mode 692
SHOW 693
MySQL Utilities 694
Exercises 695
PART VIII: Appendices
APPENDIX A: Answers 697
APPENDIX B: Code Examples to Create Tables 731
APPENDIX C: Code Examples to Populate Tables 743
APPENDIX D: Using MySQL for Exercises 763
Index 767
Online Appendixes
APPENDIX E: Glossary of Common SQL Commands PDF:1
APPENDIX F: Glossary of Common SQL Functions PDF:7
Sample Content
Table of Contents
Table of Contents
Introduction 1
PART I: Introducing SQL
LESSON 1: Getting Started with SQL 5
A Brief History of SQL 5
A Brief History of Databases 6
Today’s Database Landscape 11
A Cross-Product Language 12
Early Implementations 12
SQL and Client/Server Application Development 13
An Overview of SQL 13
Popular SQL Implementations 14
MySQL 14
Oracle 14
Microsoft SQL Server and Sybase 15
IBM DB2 16
Open Database Connectivity 16
Embedding SQL in Application Programming 17
LESSON 2: Introducing the Query 21
Exploring SQL’s Background 21
Learning Basic Query Syntax 22
The Building Blocks of Data Retrieval: SELECT and FROM 23
Applying Query Concepts 25
Writing Your First Query 26
Terminating a SQL Statement 28
Selecting Individual Columns 28
Changing the Order of the Columns 29
Selecting Different Tables 31
Selecting Distinct Values 31
Exercises 37
LESSON 3: Expressions, Conditions, and Operators 39
Working with Query Expressions 40
Placing Conditions on Queries 40
Learning How to Use Operators 42
Arithmetic Operators 42
Comparison Operators 55
Character Operators 63
Logical Operators 70
Set Operators 75
Miscellaneous Operators: IN and BETWEEN 78
LESSON 4: Clauses in SQL Queries 85
Specifying Criteria with the WHERE Clause 87
Order from Chaos: The ORDER BY Clause 89
The GROUP BY Clause 98
The HAVING Clause 105
Combining Clauses 112
Example 4.1 112
Example 4.2 113
Example 4.3 113
Example 4.4 115
LESSON 5: Joining Tables 121
Joining Multiple Tables in a Single SELECT Statement 121
Cross Joining Tables 123
Finding the Correct Column 128
Joining Tables Based on Equality 129
Joining Tables Based on Nonequality 137
OUTER JOINs Versus INNER JOINs 139
Joining a Table to Itself: The Self Join 143
LESSON 6: Embedding Subqueries into Queries 151
Building a Subquery 153
Using Aggregate Functions with Subqueries 160
Nesting Subqueries 162
Referencing Outside with Correlated Subqueries 166
Using EXISTS, ANY, and ALL 169
LESSON 7: Molding Data with Built-in Functions 179
Using Aggregate Functions to Summarize Data 180
COUNT 180
SUM 181
AVG 182
MAX 184
MIN 185
VARIANCE 186
STDDEV 186
Using Functions to Format Date and Time Values 187
ADD_MONTHS/ADD_DATE 188
LAST_DAY 190
MONTHS_BETWEEN 191
NEXT_DAY 193
SYSDATE 193
Using Functions for Arithmetic Operations 195
ABS 195
CEIL and FLOOR 196
EXP 196
LN and LOG 197
MOD 198
POWER 199
SIGN 199
SQRT 200
Using Functions to Modify the Appearance of Character Values 201
CHR 201
CONCAT 202
INITCAP 203
LOWER and UPPER 203
LPAD and RPAD 205
LTRIM and RTRIM 206
REPLACE 207
SUBSTR 209
TRANSLATE 213
INSTR 214
LENGTH 214
Conversion Functions 215
TO_CHAR 215
TO_NUMBER 217
Miscellaneous Functions 217
GREATEST and LEAST 217
USER 218
Supplemental Examples of MySQL Character Functions 219
LENGTH 219
LOCATE 219
INSTR 220
LPAD 220
RPAD 220
LEFT 220
RIGHT 221
SUBSTRING 221
LTRIM 221
RTRIM 222
TRIM 222
Supplemental Examples of MySQL Date Functions 222
DATE_FORMAT 223
TIME_FORMAT 224
CURDATE 224
CURTIME 225
PART II: Database Design
LESSON 8: Database Normalization 229
Normalizing a Database 229
The Raw Database 229
Logical Database Design 230
The Needs of the End User 230
Data Redundancy 231
Understanding the Normal Forms 231
The First Normal Form 232
The Second Normal Form 233
The Third Normal Form 234
Making Normalization Work 235
Referential Integrity 235
Benefits of Normalization 236
Drawbacks of Normalization 237
Denormalizing a Database 237
LESSON 9: Creating and Maintaining Tables 241
Beginning with the CREATE DATABASE Statement 242
CREATE DATABASE Options 243
Database Design 244
Creating a Data Dictionary (System Catalog) 244
Creating Key Fields 246
Defining Tables with the CREATE TABLE Statement 247
The Table Name 248
The Field Name 249
The Field’s Data Type 249
Table Storage and Sizing 254
Creating a Table from an Existing Table 255
Modifying Table Structures with the ALTER TABLE Statement 257
The DROP TABLE Statement 261
The DROP DATABASE Statement 262
Working with DROP TABLE and DROP DATABASE 262
LESSON 10: Controlling Data Integrity 267
Introducing Constraints 267
Data Integrity 267
Why Use Constraints? 268
Exploring Types of Constraints 269
NOT NULL Constraints 269
Primary Key Constraints 271
Unique Constraints 273
Foreign Key Constraints 274
Check Constraints 276
Managing Constraints 277
Using the Right Order 278
Different Approaches to Creating Constraints 279
Example Oracle Referential Integrity Reports 279
PART III: Data Manipulation
LESSON 11: Manipulating Data 285
Introducing Data-Manipulation Statements 285
Entering Data with the INSERT Statement 286
Entering One Record with the INSERT...VALUES Statement 286
Inserting NULL Values 289
Inserting Unique Values 291
Entering Multiple Records with the INSERT...SELECT Statement 292
Modifying Existing Data with the UPDATE Statement 295
Removing Information with the DELETE Statement 298
Importing and Exporting Data from Foreign Sources 303
Microsoft Access 303
Microsoft SQL Server 304
Oracle 305
MySQL 305
LESSON 12: Dates and Time in SQL 309
How Are Date and Time Values Stored? 310
ANSI Standard Data Types for Date and Time 310
DATETIME Elements 311
Implementation of Specific Data Types 311
Applying Date Functions to the Query 312
The Current Date 312
Time Zones 314
Adding Time to Dates 315
Subtracting Dates 318
Comparing Dates and Time Periods 320
Other Miscellaneous Date Functions 320
Converting Date Formats 321
Date Pictures 322
Converting Dates to Character Strings 324
Converting Character Strings to Dates 325
LESSON 13: Creating Views 331
Introducing Views 331
Using Views 332
Exploring a Simple View 335
Renaming Columns 337
Examining SQL View Processing 338
Restrictions on Using SELECT 343
Modifying Data in a View 343
Problems with Modifying Data Using Views 345
Common Applications of Views 346
Removing Views with the DROP VIEW Statement 350
LESSON 14: Controlling Transactions 353
Transaction Management 354
The Banking Application 354
Beginning a Transaction 356
Finishing a Transaction 358
Canceling the Transaction 361
Using Transaction Savepoints 363
PART IV: Database Administration
LESSON 15: Creating Indexes on Tables to Improve Performance 369
What Are Indexes? 370
Indexing Tips 378
Indexing on More Than One Field 379
Using the UNIQUE Keyword with CREATE INDEX 381
Indexes and Joins 382
Using Clustered Indexes 384
LESSON 16: Streamlining SQL Statements for Improved Performance 389
Making Your SQL Statements Readable 390
Avoiding the Full-Table Scan 391
Adding a New Index 393
Arranging Elements in a Query 393
Procedures 395
Avoiding OR 396
OLAP Versus OLTP 397
Tuning an OLTP System 397
Tuning an OLAP System 398
Batch Loads Versus Transactional Processing 398
Optimizing Data Loads by Dropping Indexes 400
COMMIT Statement 401
Rebuilding Tables and Indexes in a Dynamic Environment 402
Tuning the Database 405
Identifying Performance Obstacles 407
Using Built-in Tuning Tools 409
LESSON 17: Database Security 413
Security’s Role in Database Administration 413
Popular Database Products and Security 414
Oracle Express and MySQL Security 416
Creating Users 416
Creating Roles 419
User Privileges 421
Using Views for Security Purposes 429
Using Synonyms in Place of Views 430
Using Views to Solve Security Problems 431
Using the WITH GRANT OPTION Clause 433
LESSON 18: Exploring the Data Dictionary (System Catalog) 437
An Introduction to the Data Dictionary 437
Identifying Data Dictionary Users 438
Exploring the Contents of the Data Dictionary 439
Oracle’s Data Dictionary 439
MySQL Data Dictionary 440
A Look Inside Oracle’s Data Dictionary 440
User Views 440
System DBA Views 449
Dynamic Performance Views 458
A Look Inside MySQL’s Data Dictionary 459
Showing Table Commands Within MySQL 460
Using INFORMATION_SCHEMA 461
PART V: More SQL Objects
LESSON 19: Temporary Tables, Stored Procedures, Triggers, and Cursors 467
Creating Temporary Tables 468
Using Cursors 472
Creating a Cursor 473
Opening a Cursor 473
Scrolling a Cursor 473
Testing a Cursor’s Status 474
Closing a Cursor 475
The Scope of Cursors 475
Creating and Using Stored Procedures 476
Removing a Stored Procedure 478
Designing and Using Triggers 479
Triggers and Transactions 480
Restrictions on Using Triggers 481
Nested Triggers 481
Using Embedded SQL 481
Static and Dynamic SQL 482
LESSON 20: New Objects in the Latest Standard 487
Exploring the CREATE ROLE Statement 488
Creating Triggers 490
Using the CREATE TYPE Statement 492
Regular Expressions 497
Working with BLOB Data Types 498
A Short XML Example 499
PART VI: Advanced SQL Programming
LESSON 21: Using SQL to Generate SQL Statements 503
Understanding the Power of SQL Statement Generation 503
Miscellaneous SQL*Plus Commands 505
SET ECHO ON/OFF 505
SET FEEDBACK ON/OFF 506
SET HEADING ON/OFF 506
SPOOL FILENAME/OFF 506
Counting the Rows in All Tables 507
Granting System Privileges to Multiple Users 511
Granting Privileges on Your Tables to Another User 513
Disabling Table Constraints to Load Data 516
Creating Numerous Synonyms in a Single Bound 517
Creating Views on Your Tables 520
Truncating All Tables in a Schema 522
Using SQL to Generate Shell Scripts 523
Applying SQL Generation and Other Concepts to the Real World 524
LESSON 22: Creating Complex SQL Queries 529
CREATE TABLE statements 529
Examples of Complex Queries 532
Computing Age from Date of Birth 532
Breaking a Fraction of a Day into Hours, Minutes, and Seconds 533
Converting Bytes to Kilobytes to Megabytes 536
Database Fragmentation Report 536
Subqueries in DML 537
Formatting Your Dates 538
Subquery Involving a Maximum Value 539
Multiple Subqueries 540
Using Dashes and Parentheses to Format Numeric Values 541
Increasing a Numeric Value by a Given Percent 542
Finding the Next Highest Numeric Value in a Column 542
Dealing with NULL Values 544
Tips for Building Complex Queries 546
LESSON 23: Debugging Your SQL Statements 551
Exploring Common SQL Errors 551
Table or View that Does Not Exist 552
Invalid Username or Password 553
FROM Keyword Not Specified 553
Group Function Not Allowed 554
Invalid Column Name 555
Missing Keyword 556
Missing Left Parenthesis 556
Missing Right Parenthesis 557
Missing Comma 558
Column Ambiguously Defined 558
SQL Command Not Properly Ended 559
Missing Expression 559
Not Enough Arguments for Function 560
Not Enough Values 560
Integrity Constraint Violated–Parent Key Not Found 561
Oracle Not Available 562
Inserted Value Too Large for Column 562
TNS: Listener Could Not Resolve SID Given in Connect Descriptor 563
Insufficient Privileges During Grants 563
Escape Character in Your Statement–Invalid Character 564
Cannot Create Operating System File 564
Exploring Common Logical Mistakes 564
Using Reserved Words in Your SQL Statement 564
The Use of DISTINCT When Selecting Multiple Columns 566
Dropping an Unqualified Table 566
The Use of Public Synonyms in a Multischema Database 567
The Dreaded Cartesian Product 567
Failure to Enforce Input Standards 568
Failure to Enforce File System Structure Conventions 568
Allowing Large Tables to Take Default Storage Parameters 569
Placing Objects in the System Tablespace 569
Failure to Compress Large Backup Files 570
Failure to Budget System Resources 570
Preventing Problems with Your Data 571
LESSON 24: Embedding SQL in Application Programming 575
A Quick Trip Through Some Application Development Tools 575
ODBC 576
Oracle Express 576
SQL in Java with JDBC 576
SQL in .NET with OleDB 577
Getting Set Up for Oracle 577
Creating the Database 577
Using Java and SQL 581
Using . NET and SQL 583
PART VII: SQL in Various Database Implementations
LESSON 25: Using Oracle SQL*Plus to Satisfy Reporting Needs 587
An Introduction to SQL*Plus 587
The SQL*Plus Buffer 588
Viewing Table Structure with the DESCRIBE Command 593
Displaying Settings with the SHOW Command 594
Manipulating Files with File Commands 595
The SAVE, GET, and EDIT Commands 595
Starting a File 596
Spooling Query Output 598
Customizing the Work Environment with SET Commands 599
Removing Settings with the CLEAR Command 603
Formatting Your Output 603
TTITLE and BTITLE 604
Formatting Columns (COLUMN, HEADING, FORMAT) 605
Creating Report and Group Summaries 606
BREAK ON 607
COMPUTE 608
Using Variables in SQL*Plus 610
Substitution Variables (&) 611
DEFINE 611
ACCEPT 612
NEW_VALUE 614
Using the DUAL Table 615
Exploring the DECODE Function 616
DATE Conversions 619
Running a Series of SQL Files 622
Adding Comments to Your SQL Script 623
Creating Advanced Reports 624
LESSON 26: An Introduction to Oracle PL/SQL 629
Introducing PL/SQL 629
The Structure of a PL/SQL Block 630
The DECLARE Section 632
The PROCEDURE Section 635
The EXCEPTION Section 640
Transactional Control in PL/SQL 644
Putting Everything Together 644
Sample Tables and Data 645
A Simple PL/SQL Block 646
A More Extended Example of a PL/SQL Block 648
Using Stored Procedures, Packages, and Triggers 652
Sample Procedure 653
Sample Package 654
Sample Trigger 654
LESSON 27: An Introduction to Transact-SQL 661
An Overview of Transact-SQL 661
Extensions to ANSI QL 662
Who Uses Transact-SQL? 662
The Basic Components of Transact-SQL 662
Data Types 663
Character Strings 663
Numeric Data Types 663
Date Data Types 664
Money Data Types 664
Binary Strings 664
bit: A Logical Data Type 665
Accessing the Database with Transact-SQL 665
The BASEBALL Database 665
Declaring Local Variables 668
Declaring Global Variables 668
Using Variables 670
The PRINT Command 671
Establishing Flow Control 672
BEGIN and END Statements 672
IF ...ELSE Statements 673
The EXISTS Condition 675
Testing a Query’s Result 675
The WHILE Loop 676
The BREAK Command 677
The CONTINUE Command 677
Using the WHILE Loop to Scroll Through a Table 678
Using Transact-SQL Wildcard Operators 679
Date Conversions 680
SQL Server Diagnostic Tools–SET Commands 681
LESSON 28: Using MySQL on a UNIX-based System 685
MySQL Administration 686
Installing MySQL 686
Starting and Stopping MySQL 687
Initial MySQL Privileges 688
The MySQL Terminal Monitor 688
Connecting to the Database 689
Command-Line Options 689
Entering MySQL Monitor Commands 690
Command-Line History 692
Batch Mode 692
SHOW 693
MySQL Utilities 694
Exercises 695
PART VIII: Appendices
APPENDIX A: Answers 697
APPENDIX B: Code Examples to Create Tables 731
APPENDIX C: Code Examples to Populate Tables 743
APPENDIX D: Using MySQL for Exercises 763
Index 767
Online Appendixes
APPENDIX E: Glossary of Common SQL Commands PDF:1
APPENDIX F: Glossary of Common SQL Functions PDF:7