Overview

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   

Full Product Details

Author:   Ryan Stephens ,  Ron Plew ,  Arie Jones
Publisher:   Pearson Education (US)
Imprint:   Sams Publishing
Edition:   5th edition
Dimensions:   Width: 18.10cm , Height: 4.20cm , Length: 23.10cm
Weight:   1.268kg
ISBN:  

9780672330254

ISBN 10:   0672330253
Pages:   840
Publication Date:   11 June 2009
Audience:   Professional and scholarly ,  Professional & Vocational
Format:   Paperback
Publisher's Status:   Active
Availability:   In Print  
This item will be ordered in for you from one of our suppliers. Upon receipt, we will promptly dispatch it out to you. For in store availability, please contact us.

Table of Contents

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     254          Creating a Table from an Existing Table     255    Modifying Table Structures with the ALTER TABLE Statement     257    The DROP TABLE Statement     261    The DROP DATABASE Statement     262         Working with DROP TABLE and DROP DATABASE     262   LESSON 10: Controlling Data Integrity     267     Introducing Constraints     267         Data Integrity     267         Why Use Constraints?      268     Exploring Types of Constraints     269          NOT NULL Constraints     269          Primary Key Constraints     271          Unique Constraints     273          Foreign Key Constraints     274          Check Constraints     276    Managing Constraints     277          Using the Right Order     278          Different Approaches to Creating Constraints     279          Example Oracle Referential Integrity Reports     279   PART III: Data Manipulation   LESSON 11: Manipulating Data     285     Introducing Data-Manipulation Statements     285     Entering Data with the INSERT Statement     286          Entering One Record with the INSERT...VALUES Statement     286          Inserting NULL Values     289          Inserting Unique Values     291          Entering Multiple Records with the INSERT...SELECT Statement     292    Modifying Existing Data with the UPDATE Statement     295    Removing Information with the DELETE Statement     298    Importing and Exporting Data from Foreign Sources     303          Microsoft Access     303          Microsoft SQL Server     304          Oracle     305          MySQL     305   LESSON 12: Dates and Time in SQL     309     How Are Date and Time Values Stored?      310          ANSI Standard Data Types for Date and Time     310          DATETIME Elements     311          Implementation of Specific Data Types     311     Applying Date Functions to the Query     312          The Current Date     312          Time Zones     314          Adding Time to Dates     315          Subtracting Dates     318          Comparing Dates and Time Periods     320          Other Miscellaneous Date Functions     320    Converting Date Formats     321          Date Pictures     322          Converting Dates to Character Strings     324          Converting Character Strings to Dates     325   LESSON 13: Creating Views     331     Introducing Views     331     Using Views     332          Exploring a Simple View     335          Renaming Columns     337          Examining SQL View Processing     338          Restrictions on Using SELECT     343          Modifying Data in a View     343          Problems with Modifying Data Using Views     345          Common Applications of Views     346          Removing Views with the DROP VIEW Statement     350   LESSON 14: Controlling Transactions     353     Transaction Management     354     The Banking Application     354     Beginning a Transaction     356     Finishing a Transaction     358     Canceling the Transaction     361     Using Transaction Savepoints     363   PART IV: Database Administration   LESSON 15: Creating Indexes on Tables to Improve Performance     369     What Are Indexes?      370           Indexing Tips     378           Indexing on More Than One Field     379     Using the UNIQUE Keyword with CREATE INDEX     381     Indexes and Joins     382     Using Clustered Indexes     384   LESSON 16: Streamlining SQL Statements for Improved Performance     389     Making Your SQL Statements Readable     390     Avoiding the Full-Table Scan     391     Adding a New Index     393     Arranging Elements in a Query     393          Procedures     395          Avoiding OR     396     OLAP Versus OLTP     397          Tuning an OLTP System     397          Tuning an OLAP System     398     Batch Loads Versus Transactional Processing     398     Optimizing Data Loads by Dropping Indexes     400     COMMIT Statement     401     Rebuilding Tables and Indexes in a Dynamic Environment     402     Tuning the Database     405     Identifying Performance Obstacles     407     Using Built-in Tuning Tools     409   LESSON 17: Database Security     413     Security’s Role in Database Administration     413     Popular Database Products and Security     414     Oracle Express and MySQL Security     416          Creating Users     416          Creating Roles     419          User Privileges     421          Using Views for Security Purposes     429          Using Synonyms in Place of Views     430          Using Views to Solve Security Problems     431          Using the WITH GRANT OPTION Clause     433   LESSON 18: Exploring the Data Dictionary (System Catalog)      437     An Introduction to the Data Dictionary     437     Identifying Data Dictionary Users     438     Exploring the Contents of the Data Dictionary     439          Oracle’s Data Dictionary     439          MySQL Data Dictionary     440     A Look Inside Oracle’s Data Dictionary     440          User Views     440          System DBA Views     449          Dynamic Performance Views     458     A Look Inside MySQL’s Data Dictionary     459          Showing Table Commands Within MySQL     460          Using INFORMATION_SCHEMA     461   PART V: More SQL Objects   LESSON 19: Temporary Tables, Stored Procedures, Triggers, and Cursors     467     Creating Temporary Tables     468     Using Cursors     472          Creating a Cursor     473          Opening a Cursor     473          Scrolling a Cursor     473          Testing a Cursor’s Status     474          Closing a Cursor     475          The Scope of Cursors     475     Creating and Using Stored Procedures     476          Removing a Stored Procedure     478     Designing and Using Triggers     479          Triggers and Transactions     480          Restrictions on Using Triggers     481          Nested Triggers     481     Using Embedded SQL     481          Static and Dynamic SQL     482   LESSON 20: New Objects in the Latest Standard     487     Exploring the CREATE ROLE Statement     488     Creating Triggers     490     Using the CREATE TYPE Statement     492     Regular Expressions     497     Working with BLOB Data Types     498     A Short XML Example     499   PART VI: Advanced SQL Programming   LESSON 21: Using SQL to Generate SQL Statements     503     Understanding the Power of SQL Statement Generation     503     Miscellaneous SQL*Plus Commands     505          SET ECHO ON/OFF     505          SET FEEDBACK ON/OFF     506          SET HEADING ON/OFF     506          SPOOL FILENAME/OFF     506     Counting the Rows in All Tables     507     Granting System Privileges to Multiple Users     511     Granting Privileges on Your Tables to Another User     513     Disabling Table Constraints to Load Data     516     Creating Numerous Synonyms in a Single Bound     517     Creating Views on Your Tables     520     Truncating All Tables in a Schema     522     Using SQL to Generate Shell Scripts     523     Applying SQL Generation and Other Concepts to the Real World     524   LESSON 22: Creating Complex SQL Queries     529     CREATE TABLE statements     529     Examples of Complex Queries     532          Computing Age from Date of Birth     532          Breaking a Fraction of a Day into Hours, Minutes, and Seconds     533          Converting Bytes to Kilobytes to Megabytes     536          Database Fragmentation Report     536          Subqueries in DML     537          Formatting Your Dates     538          Subquery Involving a Maximum Value     539          Multiple Subqueries     540          Using Dashes and Parentheses to Format Numeric Values     541          Increasing a Numeric Value by a Given Percent     542          Finding the Next Highest Numeric Value in a Column     542          Dealing with NULL Values     544     Tips for Building Complex Queries     546   LESSON 23: Debugging Your SQL Statements     551     Exploring Common SQL Errors     551          Table or View that Does Not Exist     552          Invalid Username or Password     553          FROM Keyword Not Specified     553          Group Function Not Allowed     554          Invalid Column Name     555          Missing Keyword     556          Missing Left Parenthesis     556          Missing Right Parenthesis     557          Missing Comma     558          Column Ambiguously Defined     558          SQL Command Not Properly Ended     559          Missing Expression     559          Not Enough Arguments for Function     560          Not Enough Values     560          Integrity Constraint Violated–Parent Key Not Found     561          Oracle Not Available     562          Inserted Value Too Large for Column     562          TNS: Listener Could Not Resolve SID Given in Connect Descriptor     563          Insufficient Privileges During Grants     563          Escape Character in Your Statement–Invalid Character     564          Cannot Create Operating System File     564     Exploring Common Logical Mistakes     564          Using Reserved Words in Your SQL Statement     564          The Use of DISTINCT When Selecting Multiple Columns     566          Dropping an Unqualified Table     566          The Use of Public Synonyms in a Multischema Database     567          The Dreaded Cartesian Product     567          Failure to Enforce Input Standards     568          Failure to Enforce File System Structure Conventions     568          Allowing Large Tables to Take Default Storage Parameters     569          Placing Objects in the System Tablespace     569          Failure to Compress Large Backup Files     570          Failure to Budget System Resources     570     Preventing Problems with Your Data     571   LESSON 24: Embedding SQL in Application Programming     575     A Quick Trip Through Some Application Development Tools     575          ODBC     576          Oracle Express     576          SQL in Java with JDBC     576          SQL in .NET with OleDB     577          Getting Set Up for Oracle     577     Creating the Database     577     Using Java and SQL     581     Using . NET and SQL     583   PART VII: SQL in Various Database Implementations   LESSON 25: Using Oracle SQL*Plus to Satisfy Reporting Needs     587     An Introduction to SQL*Plus     587     The SQL*Plus Buffer     588     Viewing Table Structure with the DESCRIBE Command     593     Displaying Settings with the SHOW Command     594     Manipulating Files with File Commands     595          The SAVE, GET, and EDIT Commands     595          Starting a File     596          Spooling Query Output     598     Customizing the Work Environment with SET Commands     599     Removing Settings with the CLEAR Command     603     Formatting Your Output     603          TTITLE and BTITLE     604          Formatting Columns (COLUMN, HEADING, FORMAT)      605     Creating Report and Group Summaries     606          BREAK ON     607          COMPUTE     608     Using Variables in SQL*Plus     610          Substitution Variables (&)      611          DEFINE     611          ACCEPT     612          NEW_VALUE     614     Using the DUAL Table     615     Exploring the DECODE Function     616     DATE Conversions     619     Running a Series of SQL Files     622     Adding Comments to Your SQL Script     623     Creating Advanced Reports     624   LESSON 26: An Introduction to Oracle PL/SQL     629     Introducing PL/SQL     629     The Structure of a PL/SQL Block     630          The DECLARE Section     632          The PROCEDURE Section     635          The EXCEPTION Section     640     Transactional Control in PL/SQL     644     Putting Everything Together     644          Sample Tables and Data     645          A Simple PL/SQL Block     646          A More Extended Example of a PL/SQL Block     648     Using Stored Procedures, Packages, and Triggers     652          Sample Procedure     653          Sample Package     654          Sample Trigger     654   LESSON 27: An Introduction to Transact-SQL     661      An Overview of Transact-SQL     661      Extensions to ANSI QL     662          Who Uses Transact-SQL?      662          The Basic Components of Transact-SQL     662      Data Types     663          Character Strings     663          Numeric Data Types     663          Date Data Types     664          Money Data Types     664          Binary Strings     664          bit: A Logical Data Type     665     Accessing the Database with Transact-SQL     665          The BASEBALL Database     665          Declaring Local Variables     668          Declaring Global Variables     668          Using Variables     670          The PRINT Command     671     Establishing Flow Control     672          BEGIN and END Statements     672          IF ...ELSE Statements     673          The EXISTS Condition     675          Testing a Query’s Result     675          The WHILE Loop     676          The BREAK Command     677          The CONTINUE Command     677          Using the WHILE Loop to Scroll Through a Table     678     Using Transact-SQL Wildcard Operators     679     Date Conversions     680     SQL Server Diagnostic Tools–SET Commands     681   LESSON 28: Using MySQL on a UNIX-based System     685     MySQL Administration     686          Installing MySQL     686          Starting and Stopping MySQL     687          Initial MySQL Privileges     688     The MySQL Terminal Monitor     688          Connecting to the Database     689          Command-Line Options     689          Entering MySQL Monitor Commands     690          Command-Line History     692          Batch Mode     692          SHOW     693     MySQL Utilities     694          Exercises     695   PART VIII: Appendices   APPENDIX A: Answers     697 APPENDIX B: Code Examples to Create Tables     731 APPENDIX C: Code Examples to Populate Tables     743 APPENDIX D: Using MySQL for Exercises     763 Index     767   Online Appendixes APPENDIX E: Glossary of Common SQL Commands  PDF:1 APPENDIX F: Glossary of Common SQL Functions  PDF:7  

Reviews

Author Information

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.

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions