SQL in 24 Hours, Sams Teach Yourself

For more than 20 years each, the authors have studied, applied, and documented the SQL standard and its application to critical database systems in this book. The authors are experts in data management, specializing in Oracle, Microsoft, and other leading technologies. Ryan Stephens is the co-founder and CEO of Perpetual Technologies, Inc. and Indy Data Partners in Indianapolis. Ryan has studied and consulted in the IT field for more than 20 years, specializing in data management, SQL, and Oracle. Ryan authored and taught database and SQL classes for Indiana University-Purdue University in Indianapolis for 5 years, and was a programmer analyst for the Indiana Army National Guard for 12 years. Ryan has written a variety of database and SQL books for Sams Publishing. Arie D. Jones is the Vice President for Emerging Technologies for Indy Data Partners, Inc. (IDP) in Indianapolis. Arie leads IDP’s team of experts in 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. Ronald Plew is retired as co-founder and vice president of Perpetual Technologies, Inc. Ron studied and consulted in the field of relational database technology for more than 20 years and has co-authored several books for Sams Publishing. Ron taught SQL and database classes for Indiana University-Purdue University in Indianapolis for 5 years. He is a retired programmer analyst from the Indiana Army National Guard.

PART I: AN SQL CONCEPTS OVERVIEW
Hour 1: Welcome to the World of SQL   1
SQL Definition and History   1
SQL Sessions   8
Types of SQL Commands   8
Canary Airlines: The Database Used in This Book   11
Summary   15
Q&A   16
Workshop   16

PART II: BUILDING YOUR DATABASE
Hour 2: Defining Data Structures   19
What Is Data?   19
Basic Data Types   20
Summary   28
Q&A 29
Workshop   29
Hour 3: Managing Database Objects   33
Database Objects and Schema   33
Tables: The Primary Storage for Data 35
Integrity Constraints   44
Summary 49
Q&A   50
Workshop   50
Hour 4: The Normalization Process 55
Normalizing a Database   55
Denormalizing a Database 63
Summary   64
Q&A 64
Workshop   64
Hour 5: Manipulating Data   67
Overview of Data Manipulation   67
Populating Tables with New Data   68
Updating Existing Data 73
Deleting Data from Tables   75
Summary   76
Q&A   77
Workshop   77
Hour 6: Managing Database Transactions 81
What Is a Transaction?   81
Controlling Transactions   82
Poor Transactional Control   90
Summary   90
Q&A   90
Workshop   91

PART III: GETTING EFFECTIVE RESULTS FROM QUERIES
Hour 7: Introduction to Database Queries   93
The SELECT Statement   93
Case-Sensitivity   101
Fundamentals of Query Writing 102
Summary   106
Q&A   106
Workshop   107
Hour 8: Using Operators to Categorize Data   109
What Is an Operator in SQL?   109
Comparison Operators   110
Logical Operators 113
Conjunctive Operators   120
Negative Operators   123
Arithmetic Operators   128
Summary   130
Q&A 131
Workshop   131
Hour 9: Summarizing Data Results from a Query   133
Aggregate Functions   133
Summary 141
Q&A   142
Workshop 142
Hour 10: Sorting and Grouping Data 145
Why Group Data?   145
The GROUP BY Clause   146
GROUP BY Versus ORDER BY   150
CUBE and ROLLUP Expressions 153
The HAVING Clause   155
Summary   156
Q&A   157
Workshop   157
Hour 11: Restructuring the Appearance of Data   159
ANSI Character Functions   159
Common Character Functions   160
Miscellaneous Character Functions   169
Mathematical Functions 172
Conversion Functions   173
Combining Character Functions   176
Summary 177
Q&A   177
Workshop   178
Hour 12: Understanding Dates and Times   181
How Is a Date Stored?   181
Date Functions   183
Date Conversions   188
Summary   193
Q&A   194
Workshop   194

PART IV: BUILDING SOPHISTICATED DATABASE QUERIES
Hour 13: Joining Tables in Queries   197
Selecting Data from Multiple Tables   197
Understanding Joins   198
Join Considerations   207
Summary   210
Q&A   210
Workshop 211
Hour 14: Using Subqueries to Define Unknown Data   213
What Is a Subquery?   213
Embedded Subqueries 219
Correlated Subqueries   223
Subquery Performance   225
Summary   225
Q&A   226
Workshop   226
Hour 15: Combining Multiple Queries into One   229
Single Queries Versus Compound Queries   229
Compound Query Operators   230
Using ORDER BY with a Compound Query   235
Using GROUP BY with a Compound Query   237
Retrieving Accurate Data   238
Summary   239
Q&A   239
Workshop   240

PART V: SQL PERFORMANCE TUNING
Hour 16: Using Indexes to Improve Performance 243
What Is an Index?   243
How Do Indexes Work?   244
The CREATE INDEX Command   245
Types of Indexes   245
When Should Indexes Be Considered?   248
When Should Indexes Be Avoided?   248
Altering an Index   250
Dropping an Index   250
Summary   251
Q&A   251
Workshop 252
Hour 17: Improving Database Performance   253
What Is SQL Statement Tuning?   253
Database Tuning Versus SQL Statement Tuning   254
Formatting Your SQL Statement   254
Full Table Scans   260
Other Performance Considerations 261
Cost-Based Optimization   264
Summary   266
Q&A   266
Workshop 266

PART VI: USING SQL TO MANAGE USERS AND SECURITY
Hour 18: Managing Database Users   269
User Management in the Database   269
The Management Process   272
Tools Utilized by Database Users   279
Summary   279
Q&A 280
Workshop   280
Hour 19: Managing Database Security   283
What Is Database Security?   283
What Are Privileges?   284
Controlling User Access 287
Controlling Privileges Through Roles   291
Summary   293
Q&A   293
Workshop   294

PART VII: SUMMARIZED DATA STRUCTURES
Hour 20: Creating and Using Views and Synonyms   297
What Is a View?   297
Creating Views   300
Updating Data Through a View   307
Dropping a View   307
Performance Impact of Nested Views   308
What Is a Synonym?   308
Summary   310
Q&A   310
Workshop   311
Hour 21: Working with the System Catalog   313
What Is the System Catalog?   313
How Is the System Catalog Created?   314
What Is Contained in the System Catalog?   315
System Catalog Tables by Implementation   316
Querying the System Catalog   317
Updating System Catalog Objects   320
Summary 320
Q&A   321
Workshop   321

PART VIII: APPLYING SQL FUNDAMENTALS IN TODAY’S WORLD
Hour 22: Advanced SQL Topics   323
Cursors 323
Stored Procedures and Functions   326
Triggers   329
Dynamic SQL 331
Call-Level Interface 331
Using SQL to Generate SQL   332
Direct Versus Embedded SQL 333
Windowed Table Functions 333
Working with XML 334
Summary   335
Q&A 335
Workshop   336
Hour 23: Extending SQL to the Enterprise, the Internet, and the Intranet   337
SQL and the Enterprise 337
Accessing a Remote Database 339
SQL and the Internet   342
SQL and the Intranet   343
Summary   344
Q&A   344
Workshop 344
Hour 24: Extensions to Standard SQL   347
Various Implementations   347
Example Extensions   350
Interactive SQL Statements   353
Summary   354
Q&A   354
Workshop   355

PART IX: APPENDIXES
Appendix A: Common SQL Commands   357
SQL Statements   357
SQL Clauses   360
Appendix B: Installing Oracle and Microsoft SQL 363
Windows Installation Instructions for Oracle   363
Windows Installation Instructions for Microsoft SQL Server   365
Appendix C: Answers to Quizzes and Exercises   369
Hour 1, “Welcome to the World of SQL”   369
Hour 2, “Defining Data Structures”   371
Hour 3, “Managing Database Objects”   373
Hour 4, “The Normalization Process”   374
Hour 5, “Manipulating Data”   376
Hour 6, “Managing Database Transactions”   379
Hour 7, “Introduction to the Database Queries”   380
Hour 8, “Using Operators to Categorize Data”   383
Hour 9, “Summarizing Data Results from a Query”   386
Hour 10, “Sorting and Grouping Data”   388
Hour 11, “Restructuring the Appearance of Data”   390
Hour 12, “Understanding Dates and Times”   391
Hour 13, “Joining Tables in Queries”   392
Hour 14, “Using Subqueries to Define Unknown Data”   394
Hour 15, “Combining Multiple Queries into One”   396
Hour 16, “Using Indexes to Improve Performance”   397
Hour 17, “Improving Database Performance”   399
Hour 18, “Managing Database Users”   403
Hour 19, “Managing Database Security”   404
Hour 20, “Creating and Using Views and Synonyms”   404
Hour 21, “Working with the System Catalog”   406
Hour 22, “Advanced SQL Topics”   407
Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet”   408
Hour 24, “Extensions to Standard SQL”   409
Appendix D: Bonus Exercises   411
Appendix E: Glossary 425
Index   429