Sams Teach Yourself SQL in 24 Hours

Sams Teach Yourself SQL® in 24 Hours......Page 2
Copyright © 2003 by Sams Publishing......Page 3
Contents at a Glance......Page 4
Contents......Page 6
About the Authors......Page 22
Tell Us What You Think!......Page 24
What We Added to This Edition......Page 26
Conventions Used in This Book......Page 27
ANSI SQL and Vendor Implementations......Page 28
Understanding the Examples and Exercises......Page 29
PART I A SQL Concepts Overview......Page 30
HOUR 1 Welcome to the World of SQL......Page 32
SQL Definition and History......Page 33
SQL Sessions......Page 38
Types of SQL Commands......Page 39
An Introduction to the Database Used in This Book......Page 42
Summary......Page 47
Q&A......Page 48
Workshop......Page 49
PART II Building Your Database......Page 50
What Is Data?......Page 52
Basic Data Types......Page 53
Q&A......Page 61
Workshop......Page 62
HOUR 3 Managing Database Objects......Page 66
What Is a Schema?......Page 67
Integrity Constraints......Page 76
Summary......Page 81
Q&A......Page 82
Workshop......Page 83
HOUR 4 The Normalization Process......Page 86
Normalizing a Database......Page 87
Workshop......Page 94
Overview of Data Manipulation......Page 96
Populating Tables with New Data......Page 97
Updating Existing Data......Page 102
Deleting Data from Tables......Page 104
Q&A......Page 105
Workshop......Page 106
What Is a Transaction?......Page 110
What Is Transactional Control?......Page 111
Transactional Control and Database Performance......Page 118
Workshop......Page 119
PART III Getting Effective Results from Queries......Page 122
What Is a Query?......Page 124
Introduction to the SELECT Statement......Page 125
Examples of Simple Queries......Page 133
Q&A......Page 136
Workshop......Page 137
What Is an Operator in SQL?......Page 140
Comparison Operators......Page 141
Logical Operators......Page 144
Conjunctive Operators......Page 150
Negating Conditions with the NOT Operator......Page 153
Arithmetic Operators......Page 157
Workshop......Page 160
What Are Aggregate Functions?......Page 164
Summary......Page 171
Workshop......Page 172
HOUR 10 Sorting and Grouping Data......Page 174
The GROUP BY Clause......Page 175
GROUP BY Versus ORDER BY......Page 179
The HAVING Clause......Page 182
Q&A......Page 183
Workshop......Page 184
HOUR 11 Restructuring the Appearance of Data......Page 186
Various Common Character Functions......Page 187
Miscellaneous Character Functions......Page 196
Mathematical Functions......Page 199
Conversion Functions......Page 200
The Concept of Combining Character Functions......Page 203
Workshop......Page 204
HOUR 12 Understanding Dates and Times......Page 208
How Is a Date Stored?......Page 209
Date Functions......Page 211
Date Conversions......Page 216
Q&A......Page 220
Workshop......Page 221
PART IV Building Sophisticated Database Queries......Page 224
Selecting Data from Multiple Tables......Page 226
Types of Joins......Page 227
Join Considerations......Page 236
Workshop......Page 241
What Is a Subquery?......Page 244
Embedding a Subquery Within a Subquery......Page 251
Summary......Page 254
Workshop......Page 255
HOUR 15 Combining Multiple Queries into One......Page 260
Why Would I Ever Want to Use a Compound Query?......Page 261
Compound Query Operators......Page 262
Using an ORDER BY with a Compound Query......Page 268
Using GROUP BY with a Compound Query......Page 270
Summary......Page 271
Workshop......Page 272
PART V SQL Performance Tuning......Page 276
HOUR 16 Using Indexes to Improve Performance......Page 278
How Do Indexes Work?......Page 279
Types of Indexes......Page 280
When Should Indexes Be Considered?......Page 283
When Should Indexes Be Avoided?......Page 284
Q&A......Page 286
Workshop......Page 287
HOUR 17 Improving Database Performance......Page 288
Database Tuning Versus SQL Tuning......Page 289
Formatting Your SQL Statement......Page 290
Full Table Scans......Page 295
Other Performance Considerations......Page 296
Summary......Page 300
Workshop......Page 301
PART VI Using SQL to Manage Users and Security......Page 304
HOUR 18 Managing Database Users......Page 306
Users Are the Reason......Page 307
The Management Process......Page 309
Q&A......Page 316
Workshop......Page 317
HOUR 19 Managing Database Security......Page 318
How Does Security Differ from User Management?......Page 319
What Are Privileges?......Page 320
Controlling User Access......Page 323
Controlling Privileges Through Roles......Page 327
Summary......Page 329
Workshop......Page 330
PART VII Summarized Data Structures......Page 334
HOUR 20 Creating and Using Views and Synonyms......Page 336
What Is a View?......Page 337
Creating Views......Page 339
What Is a Synonym?......Page 347
Summary......Page 349
Workshop......Page 350
HOUR 21 Working with the System Catalog......Page 352
What Is the System Catalog?......Page 353
What Is Contained in the System Catalog?......Page 354
Examples of System Catalog Tables by Implementation......Page 356
Querying the System Catalog......Page 358
Summary......Page 360
Workshop......Page 361
PART VIII Applying SQL Fundamentals in Today\'s World......Page 364
Advanced Topics......Page 366
Cursors......Page 367
Stored Procedures and Functions......Page 370
Triggers......Page 372
Dynamic SQL......Page 374
Call-Level Interface......Page 375
Direct Versus Embedded SQL......Page 376
Summary......Page 377
Workshop......Page 378
HOUR 23 Extending SQL to the Enterprise, the Internet, and the Intranet......Page 380
SQL and the Enterprise......Page 381
Accessing a Remote Database......Page 383
Accessing a Remote Database Through a Web Interface......Page 384
SQL and the Internet......Page 385
SQL and the Intranet......Page 386
Q&A......Page 387
Workshop......Page 388
Various Implementations......Page 390
Examples of Extensions from Some Implementations......Page 393
Interactive SQL Statements......Page 396
Summary......Page 397
Workshop......Page 398
PART IX Appendixes......Page 400
SQL Statements......Page 402
SQL Clauses......Page 405
APPENDIX B Using MySQL for Exercises......Page 408
Linux Installation Instructions......Page 409
Hour 1, “Welcome to the World of SQL”......Page 412
Hour 2, “Defining Data Structures”......Page 414
Hour 3, “Managing Database Objects”......Page 416
Hour 4, “The Normalization Process”......Page 418
Hour 5, “Manipulating Data”......Page 420
Hour 6, “Managing Database Transactions”......Page 423
Hour 7, “Introduction to the Database Query”......Page 424
Hour 8, “Using Operators to Categorize Data”......Page 426
Hour 9, “Summarizing Data Results from a Query”......Page 429
Hour 10, “Sorting and Grouping Data”......Page 431
Hour 11, “Restructuring the Appearance of Data”......Page 433
Hour 12, “Understanding Dates and Time”......Page 435
Hour 13, “Joining Tables in Queries”......Page 436
Hour 14, “Using Subqueries to Define Unknown Data”......Page 438
Hour 15, “Combining Multiple Queries into One”......Page 441
Hour 16, “Using Indexes to Improve Performance”......Page 444
Hour 17, “Improving Database Performance”......Page 445
Hour 18, “Managing Database Users”......Page 448
Hour 19, “Managing Database Security”......Page 449
Hour 20, “Creating and Using Views and Synonyms”......Page 450
Hour 21, “Working with the System Catalog”......Page 451
Hour 22, “Advanced SQL Topics”......Page 452
Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet”......Page 453
Hour 24, “Extensions to Standard SQL”......Page 454
EMPLOYEE_TBL......Page 456
ORDERS_TBL......Page 457
PRODUCTS_TBL......Page 458
INSERT Statements......Page 460
APPENDIX F Glossary......Page 466
APPENDIX G Bonus Exercises......Page 470
INDEX......Page 476