Sams Teach Yourself SQL in One Hour a Day

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