Sams Teach Yourself SQL in One Hour a Day
Product Description
Sams Teach Yourself SQL in One Hour a Day
- Used Book in Good Condition
<P style="MARGIN: 0px">The Fifth Edition of <I>Sams Teach Yourself SQL in 21 Days</I> </P> <P style="MARGIN: 0px">More than 48,000 sold!</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px">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:</P> <P style="MARGIN: 0px"> </P> <UL> <LI>Quickly apply essential SQL techniques in useful, real-world queries <LI>Design trustworthy, high-performance databases <LI>Manipulate your data with views and transactions <LI>Leverage powerful features including stored procedures, triggers, and cursors <LI>Work with new objects introduced with the latest SQL standards <LI>Get practical, expert tips on implementing SQL in your business environment</LI> </UL> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px">Learn on your own time, at your own pace </P> <UL> <LI>No previous SQL or database experience required <LI>Learn techniques that work with any current version of SQL <LI>Discover how to write faster, more efficient queries <LI>Secure your data using best practices from experienced database administrators <LI>Build more powerful databases with features exclusive to Oracle SQL*Plus, Oracle PL/SQL, and Microsoft Transact-SQL <LI>Write queries for the free, open source MySQL database <LI>Embed your SQL code in other applications</LI> </UL> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px">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 <I>Sams Teach Yourself SQL in 24 Hours</I>, First through Fourth Editions, <I>Sams Teach Yourself SQL in 21 Days</I>, Second through Fourth Editions, and <I>Database Design</I>. </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>Table of Contents </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px">Introduction  1</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART I: Introducing SQL </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 1: Getting Started with SQL    5</B> </P> <P style="MARGIN: 0px">    A Brief History of SQL    5</P> <P style="MARGIN: 0px">    A Brief History of Databases    6</P> <P style="MARGIN: 0px">    Today’s Database Landscape    11</P> <P style="MARGIN: 0px">    A Cross-Product Language    12</P> <P style="MARGIN: 0px">        Early Implementations    12</P> <P style="MARGIN: 0px">        SQL and Client/Server Application Development    13</P> <P style="MARGIN: 0px">   An Overview of SQL    13</P> <P style="MARGIN: 0px">   Popular SQL Implementations    14</P> <P style="MARGIN: 0px">        MySQL    14</P> <P style="MARGIN: 0px">        Oracle    14</P> <P style="MARGIN: 0px">        Microsoft SQL Server and Sybase    15</P> <P style="MARGIN: 0px">        IBM DB2    16</P> <P style="MARGIN: 0px">   Open Database Connectivity    16</P> <P style="MARGIN: 0px">   Embedding SQL in Application Programming    17</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 2: Introducing the Query    21</B> </P> <P style="MARGIN: 0px">   Exploring SQL’s Background    21</P> <P style="MARGIN: 0px">   Learning Basic Query Syntax    22</P> <P style="MARGIN: 0px">   The Building Blocks of Data Retrieval: SELECT and FROM    23</P> <P style="MARGIN: 0px">   Applying Query Concepts    25</P> <P style="MARGIN: 0px">   Writing Your First Query    26</P> <P style="MARGIN: 0px">        Terminating a SQL Statement    28</P> <P style="MARGIN: 0px">        Selecting Individual Columns    28</P> <P style="MARGIN: 0px">        Changing the Order of the Columns    29</P> <P style="MARGIN: 0px">        Selecting Different Tables    31</P> <P style="MARGIN: 0px">   Selecting Distinct Values    31</P> <P style="MARGIN: 0px">        Exercises 37</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 3: Expressions, Conditions, and Operators    39</B> </P> <P style="MARGIN: 0px">   Working with Query Expressions    40</P> <P style="MARGIN: 0px">   Placing Conditions on Queries    40</P> <P style="MARGIN: 0px">   Learning How to Use Operators    42</P> <P style="MARGIN: 0px">       Arithmetic Operators    42</P> <P style="MARGIN: 0px">       Comparison Operators    55</P> <P style="MARGIN: 0px">       Character Operators    63</P> <P style="MARGIN: 0px">       Logical Operators    70</P> <P style="MARGIN: 0px">       Set Operators    75</P> <P style="MARGIN: 0px">       Miscellaneous Operators: IN and BETWEEN    78</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 4: Clauses in SQL Queries    85</B> </P> <P style="MARGIN: 0px">   Specifying Criteria with the WHERE Clause    87</P> <P style="MARGIN: 0px">   Order from Chaos: The ORDER BY Clause    89</P> <P style="MARGIN: 0px">   The GROUP BY Clause    98</P> <P style="MARGIN: 0px">   The HAVING Clause    105</P> <P style="MARGIN: 0px">   Combining Clauses    112</P> <P style="MARGIN: 0px">       Example 4.1    112</P> <P style="MARGIN: 0px">       Example 4.2    113</P> <P style="MARGIN: 0px">       Example 4.3    113</P> <P style="MARGIN: 0px">       Example 4.4    115</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 5: Joining Tables    121</B> </P> <P style="MARGIN: 0px">   Joining Multiple Tables in a Single SELECT Statement    121</P> <P style="MARGIN: 0px">       Cross Joining Tables    123</P> <P style="MARGIN: 0px">       Finding the Correct Column     128</P> <P style="MARGIN: 0px">   Joining Tables Based on Equality    129</P> <P style="MARGIN: 0px">   Joining Tables Based on Nonequality    137</P> <P style="MARGIN: 0px">   OUTER JOINs Versus INNER JOINs    139</P> <P style="MARGIN: 0px">   Joining a Table to Itself: The Self Join    143</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 6: Embedding Subqueries into Queries    151</B> </P> <P style="MARGIN: 0px">   Building a Subquery    153</P> <P style="MARGIN: 0px">   Using Aggregate Functions with Subqueries    160</P> <P style="MARGIN: 0px">   Nesting Subqueries    162</P> <P style="MARGIN: 0px">   Referencing Outside with Correlated Subqueries    166</P> <P style="MARGIN: 0px">   Using EXISTS, ANY, and ALL    169</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 7: Molding Data with Built-in Functions    179</B> </P> <P style="MARGIN: 0px">   Using Aggregate Functions to Summarize Data    180</P> <P style="MARGIN: 0px">        COUNT    180</P> <P style="MARGIN: 0px">        SUM    181</P> <P style="MARGIN: 0px">        AVG    182</P> <P style="MARGIN: 0px">        MAX    184</P> <P style="MARGIN: 0px">        MIN    185</P> <P style="MARGIN: 0px">        VARIANCE    186</P> <P style="MARGIN: 0px">        STDDEV    186</P> <P style="MARGIN: 0px">    Using Functions to Format Date and Time Values    187</P> <P style="MARGIN: 0px">        ADD_MONTHS/ADD_DATE    188</P> <P style="MARGIN: 0px">        LAST_DAY    190</P> <P style="MARGIN: 0px">        MONTHS_BETWEEN    191</P> <P style="MARGIN: 0px">        NEXT_DAY    193</P> <P style="MARGIN: 0px">        SYSDATE    193</P> <P style="MARGIN: 0px">    Using Functions for Arithmetic Operations    195</P> <P style="MARGIN: 0px">        ABS    195 </P> <P style="MARGIN: 0px">        CEIL and FLOOR    196</P> <P style="MARGIN: 0px">        EXP 196 </P> <P style="MARGIN: 0px">        LN and LOG    197</P> <P style="MARGIN: 0px">        MOD 198 </P> <P style="MARGIN: 0px">        POWER    199</P> <P style="MARGIN: 0px">        SIGN 199 </P> <P style="MARGIN: 0px">        SQRT    200</P> <P style="MARGIN: 0px">   Using Functions to Modify the Appearance of Character Values    201</P> <P style="MARGIN: 0px">        CHR    201 </P> <P style="MARGIN: 0px">        CONCAT    202</P> <P style="MARGIN: 0px">        INITCAP    203</P> <P style="MARGIN: 0px">        LOWER and UPPER    203</P> <P style="MARGIN: 0px">        LPAD and RPAD    205</P> <P style="MARGIN: 0px">        LTRIM and RTRIM    206</P> <P style="MARGIN: 0px">        REPLACE    207</P> <P style="MARGIN: 0px">        SUBSTR    209</P> <P style="MARGIN: 0px">        TRANSLATE    213</P> <P style="MARGIN: 0px">        INSTR    214</P> <P style="MARGIN: 0px">        LENGTH    214</P> <P style="MARGIN: 0px">   Conversion Functions    215</P> <P style="MARGIN: 0px">        TO_CHAR    215</P> <P style="MARGIN: 0px">        TO_NUMBER    217</P> <P style="MARGIN: 0px">   Miscellaneous Functions    217</P> <P style="MARGIN: 0px">        GREATEST and LEAST    217</P> <P style="MARGIN: 0px">        USER    218</P> <P style="MARGIN: 0px">   Supplemental Examples of MySQL Character Functions    219</P> <P style="MARGIN: 0px">       LENGTH    219</P> <P style="MARGIN: 0px">        LOCATE    219</P> <P style="MARGIN: 0px">        INSTR    220</P> <P style="MARGIN: 0px">        LPAD    220</P> <P style="MARGIN: 0px">        RPAD    220</P> <P style="MARGIN: 0px">        LEFT    220</P> <P style="MARGIN: 0px">        RIGHT    221</P> <P style="MARGIN: 0px">        SUBSTRING    221</P> <P style="MARGIN: 0px">        LTRIM    221</P> <P style="MARGIN: 0px">        RTRIM    222</P> <P style="MARGIN: 0px">        TRIM    222</P> <P style="MARGIN: 0px">   Supplemental Examples of MySQL Date Functions    222</P> <P style="MARGIN: 0px">        DATE_FORMAT    223</P> <P style="MARGIN: 0px">        TIME_FORMAT    224</P> <P style="MARGIN: 0px">        CURDATE    224</P> <P style="MARGIN: 0px">        CURTIME    225</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART II: Database Design </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 8: Database Normalization    229</B> </P> <P style="MARGIN: 0px">   Normalizing a Database    229</P> <P style="MARGIN: 0px">        The Raw Database    229</P> <P style="MARGIN: 0px">        Logical Database Design    230</P> <P style="MARGIN: 0px">        The Needs of the End User    230</P> <P style="MARGIN: 0px">        Data Redundancy    231</P> <P style="MARGIN: 0px">   Understanding the Normal Forms    231</P> <P style="MARGIN: 0px">        The First Normal Form    232</P> <P style="MARGIN: 0px">        The Second Normal Form    233</P> <P style="MARGIN: 0px">        The Third Normal Form    234</P> <P style="MARGIN: 0px">   Making Normalization Work    235</P> <P style="MARGIN: 0px">        Referential Integrity    235</P> <P style="MARGIN: 0px">   Benefits of Normalization    236</P> <P style="MARGIN: 0px">   Drawbacks of Normalization    237</P> <P style="MARGIN: 0px">   Denormalizing a Database    237</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 9: Creating and Maintaining Tables    241</B> </P> <P style="MARGIN: 0px">   Beginning with the CREATE DATABASE Statement    242</P> <P style="MARGIN: 0px">        CREATE DATABASE Options    243</P> <P style="MARGIN: 0px">        Database Design    244</P> <P style="MARGIN: 0px">        Creating a Data Dictionary (System Catalog)     244</P> <P style="MARGIN: 0px">        Creating Key Fields    246</P> <P style="MARGIN: 0px">   Defining Tables with the CREATE TABLE Statement    247</P> <P style="MARGIN: 0px">        The Table Name    248</P> <P style="MARGIN: 0px">        The Field Name    249</P> <P style="MARGIN: 0px">        The Field’s Data Type    249</P> <P style="MARGIN: 0px">        Table Storage and Sizing    254</P> <P style="MARGIN: 0px">        Creating a Table from an Existing Table    255</P> <P style="MARGIN: 0px">  Modifying Table Structures with the ALTER TABLE Statement    257</P> <P style="MARGIN: 0px">  The DROP TABLE Statement    261</P> <P style="MARGIN: 0px">  The DROP DATABASE Statement    262</P> <P style="MARGIN: 0px">       Working with DROP TABLE and DROP DATABASE    262</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 10: Controlling Data Integrity    267</B> </P> <P style="MARGIN: 0px">   Introducing Constraints    267</P> <P style="MARGIN: 0px">       Data Integrity    267</P> <P style="MARGIN: 0px">       Why Use Constraints?     268</P> <P style="MARGIN: 0px">   Exploring Types of Constraints    269</P> <P style="MARGIN: 0px">        NOT NULL Constraints    269</P> <P style="MARGIN: 0px">        Primary Key Constraints    271</P> <P style="MARGIN: 0px">        Unique Constraints    273</P> <P style="MARGIN: 0px">        Foreign Key Constraints    274</P> <P style="MARGIN: 0px">        Check Constraints    276</P> <P style="MARGIN: 0px">  Managing Constraints    277</P> <P style="MARGIN: 0px">        Using the Right Order    278</P> <P style="MARGIN: 0px">        Different Approaches to Creating Constraints    279</P> <P style="MARGIN: 0px">        Example Oracle Referential Integrity Reports    279</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART III: Data Manipulation </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 11: Manipulating Data    285</B> </P> <P style="MARGIN: 0px">   Introducing Data-Manipulation Statements    285</P> <P style="MARGIN: 0px">   Entering Data with the INSERT Statement    286</P> <P style="MARGIN: 0px">        Entering One Record with the INSERT...VALUES Statement    286</P> <P style="MARGIN: 0px">        Inserting NULL Values    289</P> <P style="MARGIN: 0px">        Inserting Unique Values    291</P> <P style="MARGIN: 0px">        Entering Multiple Records with the INSERT...SELECT Statement    292</P> <P style="MARGIN: 0px">  Modifying Existing Data with the UPDATE Statement    295</P> <P style="MARGIN: 0px">  Removing Information with the DELETE Statement    298</P> <P style="MARGIN: 0px">  Importing and Exporting Data from Foreign Sources    303</P> <P style="MARGIN: 0px">        Microsoft Access    303</P> <P style="MARGIN: 0px">        Microsoft SQL Server    304</P> <P style="MARGIN: 0px">        Oracle    305</P> <P style="MARGIN: 0px">        MySQL    305</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 12: Dates and Time in SQL    309</B> </P> <P style="MARGIN: 0px">   How Are Date and Time Values Stored?     310</P> <P style="MARGIN: 0px">        ANSI Standard Data Types for Date and Time    310</P> <P style="MARGIN: 0px">        DATETIME Elements    311</P> <P style="MARGIN: 0px">        Implementation of Specific Data Types    311</P> <P style="MARGIN: 0px">   Applying Date Functions to the Query    312</P> <P style="MARGIN: 0px">        The Current Date    312</P> <P style="MARGIN: 0px">        Time Zones    314</P> <P style="MARGIN: 0px">        Adding Time to Dates    315</P> <P style="MARGIN: 0px">        Subtracting Dates    318</P> <P style="MARGIN: 0px">        Comparing Dates and Time Periods    320</P> <P style="MARGIN: 0px">        Other Miscellaneous Date Functions    320</P> <P style="MARGIN: 0px">  Converting Date Formats    321</P> <P style="MARGIN: 0px">        Date Pictures    322</P> <P style="MARGIN: 0px">        Converting Dates to Character Strings    324</P> <P style="MARGIN: 0px">        Converting Character Strings to Dates    325</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 13: Creating Views    331</B> </P> <P style="MARGIN: 0px">   Introducing Views    331</P> <P style="MARGIN: 0px">   Using Views    332</P> <P style="MARGIN: 0px">        Exploring a Simple View    335</P> <P style="MARGIN: 0px">        Renaming Columns    337</P> <P style="MARGIN: 0px">        Examining SQL View Processing    338</P> <P style="MARGIN: 0px">        Restrictions on Using SELECT    343</P> <P style="MARGIN: 0px">        Modifying Data in a View    343</P> <P style="MARGIN: 0px">        Problems with Modifying Data Using Views    345</P> <P style="MARGIN: 0px">        Common Applications of Views    346</P> <P style="MARGIN: 0px">        Removing Views with the DROP VIEW Statement    350</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 14: Controlling Transactions    353</B> </P> <P style="MARGIN: 0px">   Transaction Management    354</P> <P style="MARGIN: 0px">   The Banking Application    354</P> <P style="MARGIN: 0px">   Beginning a Transaction    356</P> <P style="MARGIN: 0px">   Finishing a Transaction    358</P> <P style="MARGIN: 0px">   Canceling the Transaction    361</P> <P style="MARGIN: 0px">   Using Transaction Savepoints    363</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART IV: Database Administration </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 15: Creating Indexes on Tables to Improve Performance    369</B> </P> <P style="MARGIN: 0px">   What Are Indexes?     370</P> <P style="MARGIN: 0px">         Indexing Tips    378</P> <P style="MARGIN: 0px">         Indexing on More Than One Field    379</P> <P style="MARGIN: 0px">   Using the UNIQUE Keyword with CREATE INDEX    381</P> <P style="MARGIN: 0px">   Indexes and Joins    382</P> <P style="MARGIN: 0px">   Using Clustered Indexes    384</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 16: Streamlining SQL Statements for Improved Performance    389</B> </P> <P style="MARGIN: 0px">   Making Your SQL Statements Readable    390</P> <P style="MARGIN: 0px">   Avoiding the Full-Table Scan    391</P> <P style="MARGIN: 0px">   Adding a New Index    393</P> <P style="MARGIN: 0px">   Arranging Elements in a Query    393</P> <P style="MARGIN: 0px">        Procedures    395</P> <P style="MARGIN: 0px">        Avoiding OR    396</P> <P style="MARGIN: 0px">   OLAP Versus OLTP    397</P> <P style="MARGIN: 0px">        Tuning an OLTP System    397</P> <P style="MARGIN: 0px">        Tuning an OLAP System    398</P> <P style="MARGIN: 0px">   Batch Loads Versus Transactional Processing    398</P> <P style="MARGIN: 0px">   Optimizing Data Loads by Dropping Indexes    400</P> <P style="MARGIN: 0px">   COMMIT Statement    401</P> <P style="MARGIN: 0px">   Rebuilding Tables and Indexes in a Dynamic Environment    402</P> <P style="MARGIN: 0px">   Tuning the Database    405</P> <P style="MARGIN: 0px">   Identifying Performance Obstacles    407</P> <P style="MARGIN: 0px">   Using Built-in Tuning Tools    409</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 17: Database Security    413</B> </P> <P style="MARGIN: 0px">   Security’s Role in Database Administration    413</P> <P style="MARGIN: 0px">   Popular Database Products and Security    414</P> <P style="MARGIN: 0px">   Oracle Express and MySQL Security    416</P> <P style="MARGIN: 0px">        Creating Users    416</P> <P style="MARGIN: 0px">        Creating Roles    419</P> <P style="MARGIN: 0px">        User Privileges    421</P> <P style="MARGIN: 0px">        Using Views for Security Purposes    429</P> <P style="MARGIN: 0px">        Using Synonyms in Place of Views    430</P> <P style="MARGIN: 0px">        Using Views to Solve Security Problems    431</P> <P style="MARGIN: 0px">        Using the WITH GRANT OPTION Clause    433</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 18: Exploring the Data Dictionary (System Catalog)     437</B> </P> <P style="MARGIN: 0px">   An Introduction to the Data Dictionary    437</P> <P style="MARGIN: 0px">   Identifying Data Dictionary Users    438</P> <P style="MARGIN: 0px">   Exploring the Contents of the Data Dictionary    439</P> <P style="MARGIN: 0px">        Oracle’s Data Dictionary    439</P> <P style="MARGIN: 0px">        MySQL Data Dictionary    440</P> <P style="MARGIN: 0px">   A Look Inside Oracle’s Data Dictionary    440</P> <P style="MARGIN: 0px">        User Views    440</P> <P style="MARGIN: 0px">        System DBA Views    449</P> <P style="MARGIN: 0px">        Dynamic Performance Views    458</P> <P style="MARGIN: 0px">   A Look Inside MySQL’s Data Dictionary    459</P> <P style="MARGIN: 0px">        Showing Table Commands Within MySQL    460</P> <P style="MARGIN: 0px">        Using INFORMATION_SCHEMA    461</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART V: More SQL Objects </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 19: Temporary Tables, Stored Procedures, Triggers, and Cursors    467</B> </P> <P style="MARGIN: 0px">   Creating Temporary Tables    468</P> <P style="MARGIN: 0px">   Using Cursors    472</P> <P style="MARGIN: 0px">        Creating a Cursor    473</P> <P style="MARGIN: 0px">        Opening a Cursor    473</P> <P style="MARGIN: 0px">        Scrolling a Cursor    473</P> <P style="MARGIN: 0px">        Testing a Cursor’s Status    474</P> <P style="MARGIN: 0px">        Closing a Cursor    475</P> <P style="MARGIN: 0px">        The Scope of Cursors    475</P> <P style="MARGIN: 0px">   Creating and Using Stored Procedures    476</P> <P style="MARGIN: 0px">        Removing a Stored Procedure    478</P> <P style="MARGIN: 0px">   Designing and Using Triggers    479</P> <P style="MARGIN: 0px">        Triggers and Transactions    480</P> <P style="MARGIN: 0px">        Restrictions on Using Triggers    481</P> <P style="MARGIN: 0px">        Nested Triggers    481</P> <P style="MARGIN: 0px">   Using Embedded SQL    481</P> <P style="MARGIN: 0px">        Static and Dynamic SQL    482</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 20: New Objects in the Latest Standard    487</B> </P> <P style="MARGIN: 0px">   Exploring the CREATE ROLE Statement    488</P> <P style="MARGIN: 0px">   Creating Triggers    490</P> <P style="MARGIN: 0px">   Using the CREATE TYPE Statement    492</P> <P style="MARGIN: 0px">   Regular Expressions    497</P> <P style="MARGIN: 0px">   Working with BLOB Data Types    498</P> <P style="MARGIN: 0px">   A Short XML Example    499</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART VI: Advanced SQL Programming </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 21: Using SQL to Generate SQL Statements    503</B> </P> <P style="MARGIN: 0px">   Understanding the Power of SQL Statement Generation    503</P> <P style="MARGIN: 0px">   Miscellaneous SQL*Plus Commands    505</P> <P style="MARGIN: 0px">        SET ECHO ON/OFF    505</P> <P style="MARGIN: 0px">        SET FEEDBACK ON/OFF    506</P> <P style="MARGIN: 0px">        SET HEADING ON/OFF    506</P> <P style="MARGIN: 0px">        SPOOL FILENAME/OFF    506</P> <P style="MARGIN: 0px">   Counting the Rows in All Tables    507</P> <P style="MARGIN: 0px">   Granting System Privileges to Multiple Users    511</P> <P style="MARGIN: 0px">   Granting Privileges on Your Tables to Another User    513</P> <P style="MARGIN: 0px">   Disabling Table Constraints to Load Data    516</P> <P style="MARGIN: 0px">   Creating Numerous Synonyms in a Single Bound    517</P> <P style="MARGIN: 0px">   Creating Views on Your Tables    520</P> <P style="MARGIN: 0px">   Truncating All Tables in a Schema    522</P> <P style="MARGIN: 0px">   Using SQL to Generate Shell Scripts    523</P> <P style="MARGIN: 0px">   Applying SQL Generation and Other Concepts to the Real World    524</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 22: Creating Complex SQL Queries    529</B> </P> <P style="MARGIN: 0px">   CREATE TABLE statements    529</P> <P style="MARGIN: 0px">   Examples of Complex Queries    532</P> <P style="MARGIN: 0px">        Computing Age from Date of Birth    532</P> <P style="MARGIN: 0px">        Breaking a Fraction of a Day into Hours, Minutes, and Seconds    533</P> <P style="MARGIN: 0px">        Converting Bytes to Kilobytes to Megabytes    536</P> <P style="MARGIN: 0px">        Database Fragmentation Report    536</P> <P style="MARGIN: 0px">        Subqueries in DML    537</P> <P style="MARGIN: 0px">        Formatting Your Dates    538</P> <P style="MARGIN: 0px">        Subquery Involving a Maximum Value    539</P> <P style="MARGIN: 0px">        Multiple Subqueries    540</P> <P style="MARGIN: 0px">        Using Dashes and Parentheses to Format Numeric Values    541</P> <P style="MARGIN: 0px">        Increasing a Numeric Value by a Given Percent    542</P> <P style="MARGIN: 0px">        Finding the Next Highest Numeric Value in a Column    542</P> <P style="MARGIN: 0px">        Dealing with NULL Values    544</P> <P style="MARGIN: 0px">   Tips for Building Complex Queries    546</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 23: Debugging Your SQL Statements    551</B> </P> <P style="MARGIN: 0px">   Exploring Common SQL Errors    551</P> <P style="MARGIN: 0px">        Table or View that Does Not Exist    552</P> <P style="MARGIN: 0px">        Invalid Username or Password    553</P> <P style="MARGIN: 0px">        FROM Keyword Not Specified    553</P> <P style="MARGIN: 0px">        Group Function Not Allowed    554</P> <P style="MARGIN: 0px">        Invalid Column Name    555</P> <P style="MARGIN: 0px">        Missing Keyword    556</P> <P style="MARGIN: 0px">        Missing Left Parenthesis    556</P> <P style="MARGIN: 0px">        Missing Right Parenthesis    557</P> <P style="MARGIN: 0px">        Missing Comma    558</P> <P style="MARGIN: 0px">        Column Ambiguously Defined    558</P> <P style="MARGIN: 0px">        SQL Command Not Properly Ended    559</P> <P style="MARGIN: 0px">        Missing Expression    559</P> <P style="MARGIN: 0px">        Not Enough Arguments for Function    560</P> <P style="MARGIN: 0px">        Not Enough Values    560</P> <P style="MARGIN: 0px">        Integrity Constraint Violated–Parent Key Not Found    561</P> <P style="MARGIN: 0px">        Oracle Not Available    562</P> <P style="MARGIN: 0px">        Inserted Value Too Large for Column    562</P> <P style="MARGIN: 0px">        TNS: Listener Could Not Resolve SID Given in Connect Descriptor    563</P> <P style="MARGIN: 0px">        Insufficient Privileges During Grants    563</P> <P style="MARGIN: 0px">        Escape Character in Your Statement–Invalid Character    564</P> <P style="MARGIN: 0px">        Cannot Create Operating System File    564</P> <P style="MARGIN: 0px">   Exploring Common Logical Mistakes    564</P> <P style="MARGIN: 0px">        Using Reserved Words in Your SQL Statement    564</P> <P style="MARGIN: 0px">        The Use of DISTINCT When Selecting Multiple Columns    566</P> <P style="MARGIN: 0px">        Dropping an Unqualified Table    566</P> <P style="MARGIN: 0px">        The Use of Public Synonyms in a Multischema Database    567</P> <P style="MARGIN: 0px">        The Dreaded Cartesian Product    567</P> <P style="MARGIN: 0px">        Failure to Enforce Input Standards    568</P> <P style="MARGIN: 0px">        Failure to Enforce File System Structure Conventions    568</P> <P style="MARGIN: 0px">        Allowing Large Tables to Take Default Storage Parameters    569</P> <P style="MARGIN: 0px">        Placing Objects in the System Tablespace    569</P> <P style="MARGIN: 0px">        Failure to Compress Large Backup Files    570</P> <P style="MARGIN: 0px">        Failure to Budget System Resources    570</P> <P style="MARGIN: 0px">   Preventing Problems with Your Data    571</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 24: Embedding SQL in Application Programming    575</B> </P> <P style="MARGIN: 0px">   A Quick Trip Through Some Application Development Tools    575</P> <P style="MARGIN: 0px">        ODBC    576</P> <P style="MARGIN: 0px">        Oracle Express    576</P> <P style="MARGIN: 0px">        SQL in Java with JDBC    576</P> <P style="MARGIN: 0px">        SQL in .NET with OleDB    577</P> <P style="MARGIN: 0px">        Getting Set Up for Oracle    577</P> <P style="MARGIN: 0px">   Creating the Database    577</P> <P style="MARGIN: 0px">   Using Java and SQL    581</P> <P style="MARGIN: 0px">   Using . NET and SQL    583</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART VII: SQL in Various Database Implementations </B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 25: Using Oracle SQL*Plus to Satisfy Reporting Needs    587</B> </P> <P style="MARGIN: 0px">   An Introduction to SQL*Plus    587</P> <P style="MARGIN: 0px">   The SQL*Plus Buffer    588</P> <P style="MARGIN: 0px">   Viewing Table Structure with the DESCRIBE Command    593</P> <P style="MARGIN: 0px">   Displaying Settings with the SHOW Command    594</P> <P style="MARGIN: 0px">   Manipulating Files with File Commands    595</P> <P style="MARGIN: 0px">        The SAVE, GET, and EDIT Commands    595</P> <P style="MARGIN: 0px">        Starting a File    596</P> <P style="MARGIN: 0px">        Spooling Query Output    598</P> <P style="MARGIN: 0px">   Customizing the Work Environment with SET Commands    599</P> <P style="MARGIN: 0px">   Removing Settings with the CLEAR Command    603</P> <P style="MARGIN: 0px">   Formatting Your Output    603</P> <P style="MARGIN: 0px">        TTITLE and BTITLE    604</P> <P style="MARGIN: 0px">        Formatting Columns (COLUMN, HEADING, FORMAT)     605</P> <P style="MARGIN: 0px">   Creating Report and Group Summaries    606</P> <P style="MARGIN: 0px">        BREAK ON    607</P> <P style="MARGIN: 0px">        COMPUTE    608</P> <P style="MARGIN: 0px">   Using Variables in SQL*Plus    610</P> <P style="MARGIN: 0px">        Substitution Variables (&)     611</P> <P style="MARGIN: 0px">        DEFINE    611</P> <P style="MARGIN: 0px">        ACCEPT    612</P> <P style="MARGIN: 0px">        NEW_VALUE    614</P> <P style="MARGIN: 0px">   Using the DUAL Table    615</P> <P style="MARGIN: 0px">   Exploring the DECODE Function    616</P> <P style="MARGIN: 0px">   DATE Conversions    619</P> <P style="MARGIN: 0px">   Running a Series of SQL Files    622</P> <P style="MARGIN: 0px">   Adding Comments to Your SQL Script    623</P> <P style="MARGIN: 0px">   Creating Advanced Reports    624</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 26: An Introduction to Oracle PL/SQL    629</B> </P> <P style="MARGIN: 0px">   Introducing PL/SQL    629</P> <P style="MARGIN: 0px">   The Structure of a PL/SQL Block    630</P> <P style="MARGIN: 0px">        The DECLARE Section    632</P> <P style="MARGIN: 0px">        The PROCEDURE Section    635</P> <P style="MARGIN: 0px">        The EXCEPTION Section    640</P> <P style="MARGIN: 0px">   Transactional Control in PL/SQL    644</P> <P style="MARGIN: 0px">   Putting Everything Together    644</P> <P style="MARGIN: 0px">        Sample Tables and Data    645</P> <P style="MARGIN: 0px">        A Simple PL/SQL Block    646</P> <P style="MARGIN: 0px">        A More Extended Example of a PL/SQL Block    648</P> <P style="MARGIN: 0px">   Using Stored Procedures, Packages, and Triggers    652</P> <P style="MARGIN: 0px">        Sample Procedure    653</P> <P style="MARGIN: 0px">        Sample Package    654</P> <P style="MARGIN: 0px">        Sample Trigger    654</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 27: An Introduction to Transact-SQL    661</B> </P> <P style="MARGIN: 0px">    An Overview of Transact-SQL    661</P> <P style="MARGIN: 0px">    Extensions to ANSI QL    662</P> <P style="MARGIN: 0px">        Who Uses Transact-SQL?     662</P> <P style="MARGIN: 0px">        The Basic Components of Transact-SQL    662</P> <P style="MARGIN: 0px">    Data Types    663</P> <P style="MARGIN: 0px">        Character Strings    663</P> <P style="MARGIN: 0px">        Numeric Data Types    663</P> <P style="MARGIN: 0px">        Date Data Types    664</P> <P style="MARGIN: 0px">        Money Data Types    664</P> <P style="MARGIN: 0px">        Binary Strings    664</P> <P style="MARGIN: 0px">        bit: A Logical Data Type    665</P> <P style="MARGIN: 0px">   Accessing the Database with Transact-SQL    665</P> <P style="MARGIN: 0px">        The BASEBALL Database    665</P> <P style="MARGIN: 0px">        Declaring Local Variables    668</P> <P style="MARGIN: 0px">        Declaring Global Variables    668</P> <P style="MARGIN: 0px">        Using Variables    670</P> <P style="MARGIN: 0px">        The PRINT Command    671</P> <P style="MARGIN: 0px">   Establishing Flow Control    672</P> <P style="MARGIN: 0px">        BEGIN and END Statements    672</P> <P style="MARGIN: 0px">        IF ...ELSE Statements    673</P> <P style="MARGIN: 0px">        The EXISTS Condition    675</P> <P style="MARGIN: 0px">        Testing a Query’s Result    675</P> <P style="MARGIN: 0px">        The WHILE Loop    676</P> <P style="MARGIN: 0px">        The BREAK Command    677</P> <P style="MARGIN: 0px">        The CONTINUE Command    677</P> <P style="MARGIN: 0px">        Using the WHILE Loop to Scroll Through a Table    678</P> <P style="MARGIN: 0px">   Using Transact-SQL Wildcard Operators    679</P> <P style="MARGIN: 0px">   Date Conversions    680</P> <P style="MARGIN: 0px">   SQL Server Diagnostic Tools–SET Commands    681</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>LESSON 28: Using MySQL on a UNIX-based System    685</B> </P> <P style="MARGIN: 0px">   MySQL Administration    686</P> <P style="MARGIN: 0px">        Installing MySQL    686</P> <P style="MARGIN: 0px">        Starting and Stopping MySQL    687</P> <P style="MARGIN: 0px">        Initial MySQL Privileges    688</P> <P style="MARGIN: 0px">   The MySQL Terminal Monitor    688</P> <P style="MARGIN: 0px">        Connecting to the Database    689</P> <P style="MARGIN: 0px">        Command-Line Options    689</P> <P style="MARGIN: 0px">        Entering MySQL Monitor Commands    690</P> <P style="MARGIN: 0px">        Command-Line History    692</P> <P style="MARGIN: 0px">        Batch Mode    692</P> <P style="MARGIN: 0px">        SHOW    693</P> <P style="MARGIN: 0px">   MySQL Utilities    694</P> <P style="MARGIN: 0px">        Exercises    695</P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>PART VIII: Appendices</B> </P> <P style="MARGIN: 0px"> </P> <P style="MARGIN: 0px"> <B>APPENDIX A: Answers    697</B> </P> <P style="MARGIN: 0px"> <B>APPENDIX B: Code Examples to Create Tables    731 </B> </P> <P style="MARGIN: 0px"> <B>APPENDIX C: Code Examples to Populate Tables    743 </B> </P> <P style="MARGIN: 0px"> <B>APPENDIX D: Using MySQL for Exercises    763</B> </P> <P style="MARGIN: 0px"> <B>Index    767 </B> </P> <P style="MARGIN: 0px"> <B> </B> </P> <P style="MARGIN: 0px"> <B>Online Appendixes </B> </P> <P style="MARGIN: 0px"> <B>APPENDIX E: Glossary of Common SQL Commands PDF:1 </B> </P> <P style="MARGIN: 0px"> <B>APPENDIX F: Glossary of Common SQL Functions PDF:7</B> </P> <P style="MARGIN: 0px"> </P>
