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 ...
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.
