Home > Store

Sams Teach Yourself SQL in One Hour a Day, 5th Edition

Register your product to gain access to bonus material or receive a coupon.

Sams Teach Yourself SQL in One Hour a Day, 5th Edition

eBook (Watermarked)

  • 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 EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® 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.

Not for Sale

Description

  • Copyright 2009
  • Edition: 5th
  • eBook (Watermarked)
  • 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

Updates

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020