eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
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.
Product Details
| ISBN-13: | 9780132715126 |
|---|---|
| Publisher: | Pearson Education |
| Publication date: | 05/30/2008 |
| Series: | Sams Teach Yourself |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 504 |
| File size: | 7 MB |
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.
Read an Excerpt
IntroductionIntroduction
Welcome to the world of relational databases and SQL! This book is written for those self-motivated individuals out there who would like to get an edge on relational database technology by learning the Structured Query Language—SQL. This book was written primarily for those with very little or no experience with relational database management systems using SQL. This book also applies to those who have some experience with relational databases but need to learn how to navigate within the database, issue queries against the database, build database structures, manipulate data in the database, and more. This book is not geared toward individuals with significant relational database experience who have been using SQL on a regular basis.
What This Book Intends to Accomplish
This book was written for individuals with little or no experience using SQL or those who have used a relational database, but their tasks have been very limited within the realm of SQL. Keeping this thought in mind, it should be noted up front that this book is strictly a learning mechanism, and one in which we present the material from ground zero and provide examples and exercises with which to begin to apply the material covered. This book is not a complete SQL reference and should not be relied on as a sole reference of SQL. However, this book combined with a complete SQL command reference could serve as a complete solution source to all of your SQL needs.
What We Added to This Edition
This edition contains the same content and format as the first through third editions. We have been through the entire book, searching for the little things that could be improved to produce a better edition. We have also added concepts and commands from the new SQL standard, SQL:2003, to bring this book up to date, making it more complete and applicable to today's SQL user. The most important addition was the use of MySQL for hands-on exercises. By using an open source database such as MySQL, all readers have equal opportunity for participation in hands-on exercises.
What You Need
You might be wondering, what do I need to make this book work for me? Theoretically, you should be able to pick up this book, study the material for the current hour, study the examples, and either write out the exercises or run them on a relational database server. However, it would be to your benefit to have access to a relational database system to which to apply the material in each lesson. The relational database to which you have access is not a major factor because SQL is the standard language for all relational databases. Some database systems that you can use include Oracle, Sybase, Informix, Microsoft SQL Server, Microsoft Access, MySQL, and dBase.
Conventions Used in This Book
For the most part, we have tried to keep conventions in this book as simple as possible.
Many new terms are printed in italics.
In the listings, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. Any code that is serving as a placeholder appears in italic monospace.
SQL code and keywords have been placed in uppercase for your convenience and general consistency. For example:
SELECT
• FROM PRODUCTS_TBL;PROD_ID PROD_DESC COST————— ——————————————————11235 WITCHES COSTUME 29.99222 LASTIC PUMPKIN 18 INCH 7.7513 FALSE PARAFFIN TEETH 1.190 LIGHTED LANTERNS 14.515 ASSORTED COSTUMES 109 CANDY CORN 1.356 PUMPKIN CANDY 1.4587 PLASTIC SPIDERS 1.05119 ASSORTED MASKS 4.959 rows selected.
The following special design features enhance the text:
There are syntax boxes to draw your attention to the syntax of the commands discussed during each hour.
SELECT ALL
• DISTINCT COLUMN1, COLUMN2 FROM TABLE , TABLE2 ;
Note - Notes are provided to expand on the material covered in each hour of the book.
Caution - Cautions are provided to warn the reader about "disasters" that could occur and certain precautions that should be taken.
Tip - Tips are also given to supplement the material covered during appropriate hours of study.
ANSI SQL and Vendor Implementations
One thing that is difficult about writing a book like this on standard SQL is that although there is an ANSI standard for SQL, each database vendor has its own implementation of SQL. With each implementation come variations from the actual standard, enhancements to the standard, and even missing elements from the standard.
The expected question is, "Because there is an ANSI standard for SQL, what is so difficult about teaching standard SQL?" The answer to this question begins with the statement that ANSI SQL is just that: a standard. ANSI SQL is not an actual language. To teach you SQL, we had to come up with examples and exercises that involve using one or more implementations of SQL. Because each vendor has its own implementation with its own specifications for the language of SQL, these variations, if not handled properly in this book, could actually cause confusion concerning the syntax of various SQL commands. Therefore, we have tried to stay as close to the ANSI standard as possible, foremost discussing the ANSI standard and then showing examples from different implementations that are very close, if not the same, as the exact syntax that ANSI prescribes.
We have, however, accompanied examples of variations among implementations with notes for reminders and tips on what to watch out for. Just remember this: Each implementation differs slightly from other implementations. The most important thing is that you understand the underlying concepts of SQL and its commands. Although slight variations do exist, SQL is basically the same across the board and is very portable from database to database, regardless of the particular implementation.
Understanding the Examples and Exercises
We have chosen to use MySQL for most of the examples in this book due to its high compliance to the ANSI standard; however, we have also shown examples from Oracle, Sybase, Microsoft SQL Server, and dBase.
The use of MySQL for hands-on exercises was chosen so that all readers may participate, with minimal confusion in converting SQL syntax into the proper syntax of the database each reader is using. MySQL was chosen for exercises because it is an open source database (free), it is easy to install, and its syntax is very similar to that of the ANSI Standard. Additionally, MySQL is compatible with most operating system platforms.
In Appendix B, "Using MySQL for Exercises," we show you how to obtain and install MySQL. After it is installed on your computer, MySQL can be used for most of the exercises in this book. Unfortunately, because MySQL is not fully ANSI SQL compliant, MySQL exercises are not available for every subject.
As stated, some differences in the exact syntax exist among implementations of SQL. For example, if you attempt to execute some examples in this book, you might have to make minor modifications to fit the exact syntax of the implementation that you are using. We have tried to keep all the examples compliant with the standard; however, we have intentionally shown you some examples that are not exactly compliant. The basic structure for all the commands is the same. To learn SQL, you have to start with an implementation using practical examples. For hands-on practice, we use MySQL. If you have access to another database implementation such as Oracle, we encourage its use for hands-on exercises. You should be able to emulate the database and examples used in this book without much difficulty. Any adjustments that you might have to make to the examples in this book to fit your implementation exactly will only help you to better understand the syntax and features of your implementation.
Good luck!
© Copyright Pearson Education. All rights reserved.
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
Preface
Introduction
Welcome to the world of relational databases and SQL! This book is written for those self-motivated individuals out there who would like to get an edge on relational database technology by learning the Structured Query Language—SQL. This book was written primarily for those with very little or no experience with relational database management systems using SQL. This book also applies to those who have some experience with relational databases but need to learn how to navigate within the database, issue queries against the database, build database structures, manipulate data in the database, and more. This book is not geared toward individuals with significant relational database experience who have been using SQL on a regular basis.
What This Book Intends to Accomplish
This book was written for individuals with little or no experience using SQL or those who have used a relational database, but their tasks have been very limited within the realm of SQL. Keeping this thought in mind, it should be noted up front that this book is strictly a learning mechanism, and one in which we present the material from ground zero and provide examples and exercises with which to begin to apply the material covered. This book is not a complete SQL reference and should not be relied on as a sole reference of SQL. However, this book combined with a complete SQL command reference could serve as a complete solution source to all of your SQL needs.
What We Added to This Edition
This edition contains the same content and format as the first through third editions. We have been through the entire book, searching for the little things that could be improved to produce a better edition. We have also added concepts and commands from the new SQL standard, SQL:2003, to bring this book up to date, making it more complete and applicable to today's SQL user. The most important addition was the use of MySQL for hands-on exercises. By using an open source database such as MySQL, all readers have equal opportunity for participation in hands-on exercises.
What You Need
You might be wondering, what do I need to make this book work for me? Theoretically, you should be able to pick up this book, study the material for the current hour, study the examples, and either write out the exercises or run them on a relational database server. However, it would be to your benefit to have access to a relational database system to which to apply the material in each lesson. The relational database to which you have access is not a major factor because SQL is the standard language for all relational databases. Some database systems that you can use include Oracle, Sybase, Informix, Microsoft SQL Server, Microsoft Access, MySQL, and dBase.
Conventions Used in This Book
For the most part, we have tried to keep conventions in this book as simple as possible.
Many new terms are printed in italics.
In the listings, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. Any code that is serving as a placeholder appears in italic monospace.
SQL code and keywords have been placed in uppercase for your convenience and general consistency. For example:
SELECT
• FROM PRODUCTS_TBL;PROD_ID PROD_DESC COST---------- ------------------------------------11235 WITCHES COSTUME 29.99222 LASTIC PUMPKIN 18 INCH 7.7513 FALSE PARAFFIN TEETH 1.190 LIGHTED LANTERNS 14.515 ASSORTED COSTUMES 109 CANDY CORN 1.356 PUMPKIN CANDY 1.4587 PLASTIC SPIDERS 1.05119 ASSORTED MASKS 4.959 rows selected.
The following special design features enhance the text:
There are syntax boxes to draw your attention to the syntax of the commands discussed during each hour.
SELECT ALL
• DISTINCT COLUMN1, COLUMN2 FROM TABLE , TABLE2 ;
Note - Notes are provided to expand on the material covered in each hour of the book.
Caution - Cautions are provided to warn the reader about "disasters" that could occur and certain precautions that should be taken.
Tip - Tips are also given to supplement the material covered during appropriate hours of study.
ANSI SQL and Vendor Implementations
One thing that is difficult about writing a book like this on standard SQL is that although there is an ANSI standard for SQL, each database vendor has its own implementation of SQL. With each implementation come variations from the actual standard, enhancements to the standard, and even missing elements from the standard.
The expected question is, "Because there is an ANSI standard for SQL, what is so difficult about teaching standard SQL?" The answer to this question begins with the statement that ANSI SQL is just that: a standard. ANSI SQL is not an actual language. To teach you SQL, we had to come up with examples and exercises that involve using one or more implementations of SQL. Because each vendor has its own implementation with its own specifications for the language of SQL, these variations, if not handled properly in this book, could actually cause confusion concerning the syntax of various SQL commands. Therefore, we have tried to stay as close to the ANSI standard as possible, foremost discussing the ANSI standard and then showing examples from different implementations that are very close, if not the same, as the exact syntax that ANSI prescribes.
We have, however, accompanied examples of variations among implementations with notes for reminders and tips on what to watch out for. Just remember this: Each implementation differs slightly from other implementations. The most important thing is that you understand the underlying concepts of SQL and its commands. Although slight variations do exist, SQL is basically the same across the board and is very portable from database to database, regardless of the particular implementation.
Understanding the Examples and Exercises
We have chosen to use MySQL for most of the examples in this book due to its high compliance to the ANSI standard; however, we have also shown examples from Oracle, Sybase, Microsoft SQL Server, and dBase.
The use of MySQL for hands-on exercises was chosen so that all readers may participate, with minimal confusion in converting SQL syntax into the proper syntax of the database each reader is using. MySQL was chosen for exercises because it is an open source database (free), it is easy to install, and its syntax is very similar to that of the ANSI Standard. Additionally, MySQL is compatible with most operating system platforms.
In Appendix B, "Using MySQL for Exercises," we show you how to obtain and install MySQL. After it is installed on your computer, MySQL can be used for most of the exercises in this book. Unfortunately, because MySQL is not fully ANSI SQL compliant, MySQL exercises are not available for every subject.
As stated, some differences in the exact syntax exist among implementations of SQL. For example, if you attempt to execute some examples in this book, you might have to make minor modifications to fit the exact syntax of the implementation that you are using. We have tried to keep all the examples compliant with the standard; however, we have intentionally shown you some examples that are not exactly compliant. The basic structure for all the commands is the same. To learn SQL, you have to start with an implementation using practical examples. For hands-on practice, we use MySQL. If you have access to another database implementation such as Oracle, we encourage its use for hands-on exercises. You should be able to emulate the database and examples used in this book without much difficulty. Any adjustments that you might have to make to the examples in this book to fit your implementation exactly will only help you to better understand the syntax and features of your implementation.
Good luck!
© Copyright Pearson Education. All rights reserved.