Sams Teach Yourself SQL in One Hour a Day (5th Edition) [Paperback]

Ryan Stephens (Author), Ron D. Plew (Author), Arie D. Jones (Author)

Book Description

ISBN-10: 0672330253 | ISBN-13: 978-0672330254 | Publication Date: June 8, 2009 | Edition: 5

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

 &nb...

Editorial Reviews

About the Author

For more than 10 years, the authors have studied, applied, and documented the SQL standard and its application to the critical database systems in this book. Ryan Stephens and Ron Plew are entrepreneurs, speakers, and co-founders 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 the high availability of critical systems. Arie D. Jones is the Principal Microsoft Consultant for PTI in Indianapolis, Indiana. Arie leads PTI’s team of experts in the 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. His most recent book is SQL Functions Programmer’s Reference from Wrox Publishing.

Excerpt. © Reprinted by permission. All rights reserved.

Introduction

Over the past decade the landscape of information technology has drastically shifted to a data centric world. More than ever companies are looking for ways in which they can leverage their own data networks to make intelligent business decisions. This includes the ability to gather, store, and report effectively over possibly large sets of data in multiple formats. So the role of database administrators and developers have become strategically important in the proper implementation and care of these systems.

The cornerstone to any database project is the language that will be used in order to interact with the system. Fortunately, a consortium of entities has enacted a standard query language for database environments known as the ANSI SQL standard. This provides a commonality between all database querying languages by following this know standard and allows developers to learn the standard and then work on any given number of database systems with minor adjustments.

This book takes a focused approach on getting the reader the basics of the SQL language in order to allow them to have a solid foundation for future learning. Often in today’s business environment, there is very little time to learn new things as our day to day functions consume large amounts of our time. By focusing on smaller lesson plans and logically segmenting the sections in a stepping stone fashion, the book will allow someone to learn the SQL language at their own pace and within their own schedule.

Who Should Read This Book?

This book is for people who want to learn the fundamentals of Structured Query Language (SQL) quickly. Through the use of countless examples, this book depicts all the major components of SQL, as well as options that are available with various database implementations. You should be able to apply what you learn here to relational databases in a traditional business setting.

How Is This Book Organized?

This book is divided into seven parts which logically break down the structure of ANSI SQL into easily learnable sections:

• Part I, comprised of the first seven lessons, discusses the basic concepts behind SQL and mainly focuses on the SQL query.
• Part II includes topics on the art of database design, such as creating databases and database objects properly, which is often the foundation of RDBMS application development.
• Part III focuses on data manipulation and using SQL to perform UPDATEs, INSERTs, and DELETEs of data within your database. These will be the staple commands that you will use on a day-to-day basis.
• Part IV is dedicated to database administration, which covers such topics as security, management, and performance, enabling you to maintain the integrity and performance of your database instance.
• Part V focuses on more advanced SQL objects such as triggers and stored procedures. Using these objects will allow you to perform more sophisticated data manipulation techniques that would otherwise be difficult in standard SQL syntax.
• Part VI covers more advanced SQL programming. Advanced SQL programming will allow you to perform more advanced queries and manipulation of the data within your database.
• Part VII presents you with SQL in various database implementations. SQL extensions such as PL/SQL allow you to take advantage of unique attributes within a particular database environment, such as Oracle.
• This book also contains six appendixes, which provide you with not only the answers to the exercises in each lesson but also common SQL functions and terms that you will need to learn.

After studying this book, you should have an excellent understanding of SQL and should know how to apply SQL in the real world.

---

Note - If you are familiar with the basics and history of SQL, we suggest you skim the first lesson and begin in earnest with Lesson 2 “Introducing the Query”.

---

The syntax of SQL is explained and then brought to life in examples using MySQL, which is the closest implementation of the ANSI SQL standard syntax, as well as Oracle Express edition, which demonstrates some of the extensions to ANSI SQL.

Conventions Used in This Book

This book uses the following typeface conventions:

• Menu names are separated from menu options by a comma. For example, File, Open means select the Open option from the File menu.
• New terms are set off by the icon and appear in italic.
• In some listings, we’ve included both the input and output. For these, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. The Combination icon indicates that both input and output appear in the code.
• The Input and Output icons also identify the nature of the code.
• Many code-related terms within the text also appear in monospace.
• Placeholders in code appear in italic monospace.
• When a line of code is too long to fit on one line of this book, it is broken at a convenient place and continued to the next line. A code continuation character precedes the continuation of a line of code. (You should type a line of code that has this character as one long line without breaking it.)
• Paragraphs that begin with the Analysis icon explain the preceding code example.
• The Syntax icon identifies syntax statements.
• Special design features enhance the text material:

---

Note - Notes explain interesting or important points that can help you understand SQL concepts and techniques.

---

---

Tip - Tips are little pieces of information that will help you in real-world situations. Tips often offer shortcuts to make a task easier or faster.

---

---

Caution - Cautions provide information about detrimental performance issues or dangerous errors. Pay careful attention to Cautions.

---

Using MySQL for Hands-on Exercises

We have chosen to use MySQL for hands-on exercises in this edition. In previous editions, we left it up to the reader to obtain access to any SQL implementation. We decided that it would be better to provide the reader with an open-source SQL database that allowed all readers to start on the same level with the same software. We chose MySQL because it is the most popular open-source database available today, and it is easy to download and use.

Unfortunately, MySQL does have its limitations. There are several features of standard SQL that are not supported by MySQL. We have attempted to distinguish between the exercises that support MySQL and those that do not. Those exercises that do not will mainly focus on using Oracle Enterprise edition, instead. The beauty of SQL is that it is a standard language, although each implementation does have its differences. After using MySQL to understand the basic fundamentals of SQL, you should be able to easily apply the concepts you have learned to any SQL implementation.

About the Book's Source Code

In the appendices, you will find the source code for creating all of the objects used throughout the book. This includes all of the tables and data that is used. Additionally, the source code will be available for download from the publisher’s website. This will allow you to simply cut and paste entries into your interface instead of spending the majority of your time typing and enable you to focus more clearly on the material.

© Copyright Pearson Education. All rights reserved.

Product Details

• Paperback: 840 pages
• Publisher: Sams; 5 edition (June 8, 2009)
• Language: English
• ISBN-10: 0672330253
• ISBN-13: 978-0672330254
• Product Dimensions: 9.1 x 7 x 1.7 inches
• Shipping Weight: 2.8 pounds (View shipping rates and policies)
• Average Customer Review: 3.5 out of 5 stars  See all reviews (6 customer reviews)
• Amazon Best Sellers Rank: #132,184 in Books (See Top 100 in Books)
  • #75 in Books > Computers & Technology > Databases > SQL

More About the Author

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Most Helpful Customer Reviews

2 of 2 people found the following review helpful:

4.0 out of 5 stars MySQL, Oracle, but no MSSQL, November 8, 2010

By 

George - See all my reviews

This review is from: Sams Teach Yourself SQL in One Hour a Day (5th Edition) (Paperback)

A good solid basic book on the fundamentals of structured query language. It is without question targeted and written for the beginner. The book has numerous examples that include showing the query statement, an analysis and the output in both MySQL and Oracle. At 695 pages, not counting the appendix and index, it might appear to be a long book, but a lot of space is taken up with the abundant examples, making the book appear longer than it really is. The 28 lessons do not take long to do. Maybe if someone is brand new to SQL they may take an hour, but normally I found the lessons taking 20 to 30 minutes including the reading the chapter.

My only complaint is that the syntax is in MySQL or Oracle and does not include MSSQL examples.

2 of 2 people found the following review helpful:

5.0 out of 5 stars SQL in One hour a Day, June 30, 2010

By 

DIY guy - See all my reviews

This review is from: Sams Teach Yourself SQL in One Hour a Day (5th Edition) (Paperback)

This book I recommend for every beginners in SQL.
The book is very organized and have a lot of exercises.

1 of 1 people found the following review helpful:

1.0 out of 5 stars The cover states "48,000 copies" Really?, August 28, 2011

By 

Daniel W. Derrick (Pittsboro, IN United States) - See all my reviews
(REAL NAME)   

This review is from: Sams Teach Yourself SQL in One Hour a Day (5th Edition) (Paperback)

My son bought this book as a way to being learning SQL. It was recommended by a DBA. I began skimming to see how well organized it was. This book fails in so many ways:
The copy of the book was run on a copy machine, not a press. Do we need a statement now that books are really printer or you are getting a poor quality copy?
The authors state that the material is based on Oracle with the back cover stating "features exclusive to Oracle..." then goes on to mention that MySQL is also included. It looks like the original book was for Oracle exclusively and then MySQL was added later.
On page 27 the book assumes that the databases used as examples are already installed. I could not find any instructions for installing the material except for some TXT files downloaded from the book's web site. I used the SOURCE command to run these text files. Of course they are not SQL so I had plenty of errors but had enough to work with but by the end of Lesson 2 I quit. I'm looking for something better for him to use.
I will be glad to be corrected if I have missed something here. For all these reasons and more I'm giving the book 1 star.
The most appropriate SQL command for this book is DROP.