Sams Teach Yourself SQL in One Hour a Day (ePub)

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