Sams Teach Yourself SQL in One Hour a Day [NOOK Book]

Overview

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 ...

See more details below
Sams Teach Yourself SQL in One Hour a Day

Available on NOOK devices and apps  
  • NOOK Devices
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK Study

Want a NOOK? Explore Now

NOOK Book (eBook)
$25.49
BN.com price
(Save 42%)$43.99 List Price

Overview

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

Read More Show Less

Product Details

  • ISBN-13: 9780768690392
  • Publisher: Pearson Education
  • Publication date: 6/12/2009
  • Series: Sams Teach Yourself Series
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 840
  • File size: 10 MB

Meet the Author

For more than 10 years, the authors have studied, applied, and documented the SQL standard and its application to the critical database systems in this book. Ryan Stephens and Ron Plew are entrepreneurs, speakers, and co-founders of Perpetual Technologies, Inc.(PTI), a fast-growing IT management and consulting firm. PTI specializes in database technologies, primarily Oracle and SQL servers running on all UNIX, Linux, and Microsoft platforms. Starting out as data analysts and database administrators, Ryan and Ron now lead a team of impressive technical subject matter experts who manage databases for clients worldwide. They authored and taught database courses for Indiana University-Purdue University in Indianapolis for five years and have authored more than a dozen books on Oracle, SQL, database design, and the high availability of critical systems. Arie D. Jones is the Principal Microsoft Consultant for PTI in Indianapolis, Indiana. Arie leads PTI’s team of experts in the planning, design, development, deployment, and management of database environments and applications to achieve the best combination of tools and services for each client. He is a regular speaker at technical events and has authored several books and articles pertaining to database-related topics. His most recent book is SQL Functions Programmer’s Reference from Wrox Publishing.
Read More Show Less

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

Read More Show Less

Preface

Introduction Introduction

Over the past decade the landscape of information technology has drastically shifted to a data centric world. More than ever companies are looking for ways in which they can leverage their own data networks to make intelligent business decisions. This includes the ability to gather, store, and report effectively over possibly large sets of data in multiple formats. So the role of database administrators and developers have become strategically important in the proper implementation and care of these systems.

The cornerstone to any database project is the language that will be used in order to interact with the system. Fortunately, a consortium of entities has enacted a standard query language for database environments known as the ANSI SQL standard. This provides a commonality between all database querying languages by following this know standard and allows developers to learn the standard and then work on any given number of database systems with minor adjustments.

This book takes a focused approach on getting the reader the basics of the SQL language in order to allow them to have a solid foundation for future learning. Often in today’s business environment, there is very little time to learn new things as our day to day functions consume large amounts of our time. By focusing on smaller lesson plans and logically segmenting the sections in a stepping stone fashion, the book will allow someone to learn the SQL language at their own pace and within their own schedule. Who Should Read This Book?

This book is for people who want to learn the fundamentals of Structured Query Language (SQL) quickly. Through the use ofcountless examples, this book depicts all the major components of SQL, as well as options that are available with various database implementations. You should be able to apply what you learn here to relational databases in a traditional business setting. How Is This Book Organized?

This book is divided into seven parts which logically break down the structure of ANSI SQL into easily learnable sections:


  • Part I, comprised of the first seven lessons, discusses the basic concepts behind SQL and mainly focuses on the SQL query.
  • Part II includes topics on the art of database design, such as creating databases and database objects properly, which is often the foundation of RDBMS application development.
  • Part III focuses on data manipulation and using SQL to perform UPDATEs, INSERTs, and DELETEs of data within your database. These will be the staple commands that you will use on a day-to-day basis.
  • Part IV is dedicated to database administration, which covers such topics as security, management, and performance, enabling you to maintain the integrity and performance of your database instance.
  • Part V focuses on more advanced SQL objects such as triggers and stored procedures. Using these objects will allow you to perform more sophisticated data manipulation techniques that would otherwise be difficult in standard SQL syntax.
  • Part VI covers more advanced SQL programming. Advanced SQL programming will allow you to perform more advanced queries and manipulation of the data within your database.
  • Part VII presents you with SQL in various database implementations. SQL extensions such as PL/SQL allow you to take advantage of unique attributes within a particular database environment, such as Oracle.
  • This book also contains six appendixes, which provide you with not only the answers to the exercises in each lesson but also common SQL functions and terms that you will need to learn.

After studying this book, you should have an excellent understanding of SQL and should know how to apply SQL in the real world.

Note - If you are familiar with the basics and history of SQL, we suggest you skim the first lesson and begin in earnest with Lesson 2 “Introducing the Query”.

The syntax of SQL is explained and then brought to life in examples using MySQL, which is the closest implementation of the ANSI SQL standard syntax, as well as Oracle Express edition, which demonstrates some of the extensions to ANSI SQL. Conventions Used in This Book

This book uses the following typeface conventions:

  • Menu names are separated from menu options by a comma. For example, File, Open means select the Open option from the File menu.
  • New terms are set off by the icon and appear in italic.
  • In some listings, we’ve included both the input and output. For these, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. The Combination icon indicates that both input and output appear in the code.
  • The Input and Output icons also identify the nature of the code.
  • Many code-related terms within the text also appear in monospace.
  • Placeholders in code appear in italic monospace.
  • When a line of code is too long to fit on one line of this book, it is broken at a convenient place and continued to the next line. A code continuation character precedes the continuation of a line of code. (You should type a line of code that has this character as one long line without breaking it.)
  • Paragraphs that begin with the Analysis icon explain the preceding code example.
  • The Syntax icon identifies syntax statements.
  • Special design features enhance the text material:

Note - Notes explain interesting or important points that can help you understand SQL concepts and techniques.

Tip - Tips are little pieces of information that will help you in real-world situations. Tips often offer shortcuts to make a task easier or faster.

Caution - Cautions provide information about detrimental performance issues or dangerous errors. Pay careful attention to Cautions.

Using MySQL for Hands-on Exercises

We have chosen to use MySQL for hands-on exercises in this edition. In previous editions, we left it up to the reader to obtain access to any SQL implementation. We decided that it would be better to provide the reader with an open-source SQL database that allowed all readers to start on the same level with the same software. We chose MySQL because it is the most popular open-source database available today, and it is easy to download and use.

Unfortunately, MySQL does have its limitations. There are several features of standard SQL that are not supported by MySQL. We have attempted to distinguish between the exercises that support MySQL and those that do not. Those exercises that do not will mainly focus on using Oracle Enterprise edition, instead. The beauty of SQL is that it is a standard language, although each implementation does have its differences. After using MySQL to understand the basic fundamentals of SQL, you should be able to easily apply the concepts you have learned to any SQL implementation. About the Book's Source Code

In the appendices, you will find the source code for creating all of the objects used throughout the book. This includes all of the tables and data that is used. Additionally, the source code will be available for download from the publisher’s website. This will allow you to simply cut and paste entries into your interface instead of spending the majority of your time typing and enable you to focus more clearly on the material.

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Customer Reviews

Average Rating 2.5
( 5 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(2)

2 Star

(3)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 – 7 of 5 Customer Reviews
  • Posted July 8, 2013

    I already know SQL. I bought this only as required reading for m

    I already know SQL. I bought this only as required reading for my Advanced PHP with MySQL class. I cannot recommend it. First, they are guilty of useless filler to pad the number of pages. Why give a MySQL example, including the output, when it is exactly the same as the Oracle example given just above? Also the Input/Output and Analysis headers serve little purpose other than causing distraction when trying to browse section headers. Many examples are nonsensical, at time intentionally. (Just say you can't do that, rather than spitting out bad code.) Attempts at humor fall flat. Stop trying to be For Dummies. I just stay away from SAMS books whenever I can. 

    Was this review helpful? Yes  No   Report this review
  • Posted November 8, 2010

    MySQL, Oracle, but no MSSQL

    A good solid basic book on the fundamentals of structured query language. It is without question targeted and written for the beginner. The book has numerous examples that include showing the query statement, an analysis and the output in both MySQL and Oracle. At 695 pages, not counting the appendix and index, it might appear to be a long book, but a lot of space is taken up with the abundant examples, making the book appear longer than it really is. The 28 lessons do not take long to do. Maybe if someone is brand new to SQL they may take an hour, but normally I found the lessons taking 20 to 30 minutes including the reading the chapter.

    My only complaint is that the syntax is in MySQL or Oracle and does not include MSSQL examples.

    Was this review helpful? Yes  No   Report this review
  • Posted June 7, 2010

    more from this reviewer

    SQL

    This book is for people who want to learn the fundamentals of structured query language (sql) quickly. Through the use of countless examples, this book depicts all the major components of sql, as well as options that are available with various database implementations. You should be able to apply what you learn here to relational databases in a traditional business setting. This book has chosen to use MySQL for hands-on exercises. Over the past decade the landscape of information technology has drastically shifted to a data-centric world. More than ever companies are looking for ways in which they can leverage their own data networks to make intelligent business decisions. This includes the ability to gather, store, and report effectively over possibly large sets of data in multiple formats. So the role of a database administrators and developers has become strategically important in the proper implementation and care of these systems.

    Was this review helpful? Yes  No   Report this review
  • Posted May 13, 2010

    more from this reviewer

    SQL

    This book takes a focused approach on giving you the basics of the SQL language in order to allow them to have a solid foundation for future learning. Often in today's business environment, there is very little time to learn new things as our day-to-day functions consume large amounts of our time. By focusing on smaller lesson plans and logically segmenting the sections in a stepping stone fashion, the book allows readers to learn the SQL language at their own pace and within their own schedules. This book is for people who want to learn the fundamentals of structured query language quickly. Through the use of countless examples, this book depicts all the major components of SQL, as well as options that are available with various database implementations. You should be able to apply what you learn here to relational databases in a traditional setting. The cornerstone to any database project is the language that will be used in order to interact with the system.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted August 16, 2010

    No text was provided for this review.

  • Anonymous

    Posted January 24, 2010

    No text was provided for this review.

  • Anonymous

    Posted January 25, 2010

    No text was provided for this review.

Sort by: Showing 1 – 7 of 5 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)