Cantitate/Preț
Produs

Sams Teach Yourself SQL in One Hour a Day: Sams Teach Yourself...in One Hour

Autor Ryan Stephens, Ron Plew, Arie D. Jones
en Limba Engleză Paperback – iun 2009
In just one hour a day or less, this book will help you start using the SQL query language to access the data they need to solve real-world business problems. Friendly, accessible, and complete, it covers everything you need to know to use SQL with any database, whether you have previous database experience or not.Using realistic, hands-on examples, you will learn how to write basic queries... design better databases... use SQL in database administration... optimize SQL statements to run faster... work with data dictionaries, tables, triggers, and cursors... create and debug complex SQL statements... embed SQL in other software, and more. This book also contains additional chapters on using SQL in Oracle SQL*Plus, Oracle PL/SQL, Transact-SQL, and MySQL environments. This edition has been fully revised and refined to reflect the latest SQL practices and techniques. All syntax has been updated for the ANSI SQL 2003 Standard and 2006 XML extensions, and this edition contains far more advanced coverage, including XML, stored procedures, and windowed table functions. Throughout, step-by-step instructions walk you through your most common questions, issues, and tasks...Quizzes and Exercises help you test your knowledge... "Did You Know?" tips offer insider advice and shortcuts... and "Watch Out!" alerts help you avoid problems.
Citește tot Restrânge

Preț: 24564 lei

Preț vechi: 30705 lei
-20%

Puncte Express: 368

Preț estimativ în valută:
4706 5098$ 4036£

Comandă specială

Livrare economică 18-24 aprilie

Doresc să fiu notificat când acest titlu va fi disponibil:

Preluare comenzi: 021 569.72.76

Specificații

ISBN-13: 9780672330254
ISBN-10: 0672330253
Pagini: 814
Dimensiuni: 178 x 231 x 42 mm
Greutate: 1.28 kg
Ediția:0005Nouă
Editura: Sams
Seria Sams Teach Yourself...in One Hour

Locul publicării:Indianapolis, United States

Descriere

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

Cuprins

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
 

Notă biografică

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.

Textul de pe ultima copertă

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 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 &nbs