Sams Teach Yourself SQL in One Hour a Day (Sams Teach Yourself -- Hours) - Softcover

Stephens, Ryan; Plew, Ron; Jones, Arie

Softcover

ISBN 10: 0672330253 ISBN 13: 9780672330254

Publisher: Sams Publishing, 2009

Synopsis

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     249

         The Field’s Data Type     249

         Table Storage and Sizing&nbs...

About this title

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.

Edition details

• Publisher: Sams Publishing
• Publication date: 2009
• ISBN 10: 0672330253
• ISBN 13: 9780672330254
• Binding: Paperback
• Language: English
• Edition number: 5
• Number of pages: 840