In just 24 lessons of one hour or less, you will learn professional techniques to design and build efficient databases and query them to extract useful information. Using a straightforward, step-by-step approach, each lesson builds on the previous one, allowing you to learn the essentials of ANSI SQL from the ground up.
Example code demonstrates the authors’ professional techniques, while exercises written for MySQL offer the reader hands-on learning with an open-source database. Included are advanced techniques for using views, managing transactions, database administration, and extending SQL.
Step-by-step instructions carefully walk you through the most common SQL tasks.
Q&As, Quizzes, and Exercises at the end of each chapter help you test your knowledge.
Notes and Tips point out shortcuts and solutions.
New terms are clearly defined and explained.
Learn how to…
- Use SQL-2003, the latest standard for the Structured Query Language
- Design and deploy efficient, secure databases
- Build advanced queries for information retrieval
- Sort, group, and summarize information for best presentation
- Tune databases and queries for maximum performance
- Understand database administration and security techniques
For more than ten years the authors have studied, applied, and documented the SQL standard and its application to critical database systems.
Ryan Stephens and Ron Plew are entrepreneurs, speakers, and cofounders of Perpetual Technologies, Inc. (PTI), a fast-growing IT management and consulting firm which specializes in database technologies. They 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 Senior SQL Server database administrator and analyst for PTI. He is a regular speaker at technical events and has authored several books and articles.
Category: Database
Covers: ANSI SQL
User Level: Beginning–Intermediate
Register your book at informit.com/title/9780672330186 for convenient access to updates and corrections
as they become available.
Table of Contents:
Introduction 1
What This Book Intends to Accomplish......................... 1
What We Added to This Edition..................................... 1
What You Need............................................................ 2
Conventions Used in This Book..................................... 2
ANSI SQL and Vendor Implementations........................ 3
Understanding the Examples and Exercises................... 3
Part I: A SQL Concepts Overview
Hour 1: Welcome to the World of SQL 7
SQL Definition and History............................................ 7
SQL Sessions............................................................. 14
Types of SQL Commands........................................... 15
The Database Used in This Book................................. 17
Summary.................................................................. 22
Q&A.......................................................................... 23
Workshop................................................................. 24
Part II: Building Your Database
Hour 2: Defining Data Structures 27
What Is Data?............................................................ 27
Basic Data Types....................................................... 28
Summary.................................................................. 36
Q&A.......................................................................... 37
Workshop................................................................. 37
Hour 3: Managing Database Objects 41
What Are Database Objects?...................................... 41
What Is a Schema?.................................................... 42
A Table: The Primary Storage for Data........................ 44
Integrity Constraints.................................................. 52
Summary.................................................................. 56
Q&A.......................................................................... 57
Workshop................................................................. 58
Hour 4: The Normalization Process 61
Normalizing a Database............................................. 61
Denormalizing a Database.......................................... 69
Summary.................................................................. 69
Q&A.......................................................................... 70
Workshop................................................................. 70
Hour 5: Manipulating Data 73
Overview of Data Manipulation................................... 73
Populating Tables with New Data................................ 74
Updating Existing Data............................................... 79
Deleting Data from Tables.......................................... 81
Summary.................................................................. 82
Q&A.......................................................................... 82
Workshop................................................................. 83
Hour 6: Managing Database Transactions 87
What Is a Transaction?............................................... 87
Controlling Transactions............................................. 88
Transactional Control and Database Performance........ 95
Summary.................................................................. 95
Q&A.......................................................................... 96
Workshop................................................................. 96
Part III: Getting Effective Results from Queries
Hour 7: Introduction to the Database Query 101
What Is a Query?..................................................... 101
Introduction to the SELECT Statement...................... 101
Examples of Simple Queries..................................... 109
Summary................................................................ 113
Q&A........................................................................ 113
Workshop............................................................... 114
Hour 8: Using Operators to Categorize Data 117
What Is an Operator in SQL?.................................... 117
Comparison Operators............................................. 118
Logical Operators..................................................... 121
Conjunctive Operators............................................. 127
Negative Operators.................................................. 130
Arithmetic Operators................................................ 134
Summary................................................................ 138
Q&A........................................................................ 138
Workshop............................................................... 138
Hour 9: Summarizing Data Results from a Query 141
What Are Aggregate Functions?................................ 141
Summary................................................................ 149
Q&A........................................................................ 149
Workshop............................................................... 149
Hour 10: Sorting and Grouping Data 151
Why Group Data?.................................................... 151
The GROUP BY Clause.............................................. 152
GROUP BY Versus ORDER BY.................................... 156
The HAVING Clause.................................................. 159
Summary................................................................ 160
Q&A........................................................................ 160
Workshop............................................................... 161
Hour 11: Restructuring the Appearance of Data 165
ANSI Character Functions......................................... 165
Various Common Character Functions...................... 166
Miscellaneous Character Functions............................ 175
Mathematical Functions............................................ 178
Conversion Functions............................................... 179
Combining Character Functions................................ 181
Summary................................................................ 182
Q&A........................................................................ 182
Workshop............................................................... 183
Hour 12: Understanding Dates and Times 185
How Is a Date Stored?............................................. 186
Date Functions........................................................ 187
Date Conversions.................................................... 192
Summary................................................................ 197
Q&A........................................................................ 197
Workshop............................................................... 198
Part IV: Building Sophisticated Database Queries
Hour 13: Joining Tables in Queries 203
Selecting Data from Multiple Tables........................... 203
Types of Joins......................................................... 204
Join Considerations.................................................. 214
Summary................................................................ 218
Q&A........................................................................ 218
Workshop............................................................... 219
Hour 14: Using Subqueries to Define Unknown Data 221
What Is a Subquery?................................................ 221
Embedded Subqueries.............................................. 227
Correlated Subqueries.............................................. 229
Summary................................................................ 230
Q&A........................................................................ 231
Workshop............................................................... 231
Hour 15: Combining Multiple Queries into One 235
Single Queries Versus Compound Queries................. 235
Compound Query Operators.................................... 236
Using ORDER BY with a Compound Query................. 242
Using GROUP BY with a Compound Query................. 244
Retrieving Accurate Data.......................................... 246
Summary................................................................ 246
Q&A........................................................................ 246
Workshop............................................................... 247
Part V: SQL Performance Tuning
Hour 16: Using Indexes to Improve Performance 253
What Is an Index?.................................................... 253
How Do Indexes Work?........................................... 254
The CREATE INDEX Command.................................. 255
Types of Indexes..................................................... 255
When Should Indexes Be Considered?....................... 258
When Should Indexes Be Avoided?........................... 259
Dropping an Index.................................................... 260
Summary................................................................ 261
Q&A........................................................................ 261
Workshop............................................................... 262
Hour 17: Improving Database Performance 265
What Is SQL Statement Tuning?............................... 265
Database Tuning Versus SQL Statement Tuning......... 266
Formatting Your SQL Statement............................... 266
Full Table Scans....................................................... 272
Other Performance Considerations........................... 273
Performance Tools................................................... 276
Summary................................................................ 276
Q&A........................................................................ 277
Workshop............................................................... 278
Part VI: Using SQL to Manage Users and Security
Hour 18: Managing Database Users 283
Users Are the Reason.............................................. 284
The Management Process........................................ 286
Tools Utilized by Database Users.............................. 293
Summary................................................................ 294
Q&A........................................................................ 294
Workshop............................................................... 295
Hour 19: Managing Database Security 297
What Is Database Security?...................................... 297
What Are Privileges?................................................. 298
Controlling User Access............................................ 302
Controlling Privileges Through Roles.......................... 305
Summary................................................................ 307
Q&A........................................................................ 308
Workshop............................................................... 309
Part VII: Summarized Data Structures
Hour 20: Creating and Using Views and Synonyms 313
What Is a View?....................................................... 313
Creating Views......................................................... 316
WITH CHECK OPTION............................................... 320
Updating Data Through a View.................................. 321
Creating a Table from a View.................................... 322
Views and the ORDER BY Clause............................... 323
Dropping a View....................................................... 323
What Is a Synonym?................................................ 324
Summary................................................................ 325
Q&A........................................................................ 326
Workshop............................................................... 326
Hour 21: Working with the System Catalog 329
What Is the System Catalog?................................... 329
How Is the System Catalog Created?....................... 331
What Is Contained in the System Catalog?................ 331
System Catalog Tables by Implementation............... 333
Querying the System Catalog................................... 335
Updating System Catalog Objects............................ 337
Summary................................................................ 337
Q&A........................................................................ 338
Workshop............................................................... 338
Part VIII: Applying SQL Fundamentals in Today’s World
Hour 22: Advanced SQL Topics 343
Cursors................................................................... 343
Stored Procedures and Functions............................. 346
Triggers................................................................... 349
Dynamic SQL........................................................... 351
Call-Level Interface.................................................. 352
Using SQL to Generate SQL...................................... 352
Direct Versus Embedded SQL................................... 353
Windowed Table Functions....................................... 354
Working with XML.................................................... 354
Summary................................................................ 355
Q&A........................................................................ 356
Workshop............................................................... 356
Hour 23: Extending SQL to the Enterprise, the Internet, and the Intranet 359
SQL and the Enterprise............................................. 359
Accessing a Remote Database.................................. 361
SQL and the Internet................................................ 364
SQL and the Intranet................................................ 365
Summary................................................................ 366
Q&A........................................................................ 367
Workshop............................................................... 367
Hour 24: Extensions to Standard SQL 369
Various Implementations.......................................... 369
Example Extensions................................................. 372
Interactive SQL Statements..................................... 375
Summary................................................................ 376
Q&A........................................................................ 377
Workshop............................................................... 377
Part IX: Appendixes
Appendix A: Common SQL Commands 381
Appendix B: Using MySQL for Exercises 387
Appendix C: Answers to Quizzes and Exercises 391
Appendix D: CREATE TABLE Statements for Book Examples 435
Appendix E: INSERT Statements for Book Examples 437
Appendix F: Bonus Exercises 441
Glossary 447
TOC, 0672330180, MF
About the Author :
For more than 10 years, the authors have studied, applied, and documented the SQL standard and its application to critical database systems in this book. Ryan Stephens and Ron Plew are entrepreneurs, speakers, and cofounders 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 high availability of critical systems.
Arie D. Jones is Senior SQL Server database administrator and analyst for Perpetual Technologies, Inc. (PTI) in Indianapolis, Indiana. Arie leads PTI’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. The most recent is SQL Functions Programmer’s Reference from Wrox Publishing.
