About the Book
Includes Coverage of Oracle and Microsoft SQL Implementations
In just 24 lessons of one hour or less, Sams Teach Yourself SQL in 24 Hours, Sixth Edition, helps you use SQL to build effective databases, efficiently retrieve data, and manage everything from performance to security.
This book’s straightforward, step-by-step approach shows you how to work with database structures, objects, queries, tables, and more. In just hours, you will be applying advanced techniques, including views, transactions, web connections, and powerful Oracle and SQL Server extensions. Every lesson builds on what you’ve already learned, giving you a rock-solid foundation for real-world success.
Step-by-step instructions carefully walk you through the most common SQL tasks.
Practical, hands-on examples show you how to apply what you learn.
Quizzes and exercises help you test your knowledge and stretch your skills.
Notes and tips point out shortcuts and solutions.
Learn how to…
• Define efficient database structures and objects
• “Normalize” raw databases into logically organized tables
• Edit relational data and tables with DML
• Manage transactions
• Write effective, well-performing queries
• Categorize, summarize, sort, group, and restructure data
• Work with dates and times
• Join tables in queries, use subqueries, and combine multiple queries
• Master powerful query optimization techniques
• Administer databases and manage users
• Secure databases and protect data
• Use views, synonyms, and the system catalog
• Extend SQL to the enterprise and Internet
• Master important Oracle and Microsoft extensions to ANSI SQL
Register your product at informit.com/register for convenient access to downloads, updates, and corrections as they become available.
Table of Contents:
PART I: AN SQL CONCEPTS OVERVIEW
Hour 1: Welcome to the World of SQL 1
SQL Definition and History 1
SQL Sessions 8
Types of SQL Commands 8
Canary Airlines: The Database Used in This Book 11
Summary 15
Q&A 16
Workshop 16
PART II: BUILDING YOUR DATABASE
Hour 2: Defining Data Structures 19
What Is Data? 19
Basic Data Types 20
Summary 28
Q&A 29
Workshop 29
Hour 3: Managing Database Objects 33
Database Objects and Schema 33
Tables: The Primary Storage for Data 35
Integrity Constraints 44
Summary 49
Q&A 50
Workshop 50
Hour 4: The Normalization Process 55
Normalizing a Database 55
Denormalizing a Database 63
Summary 64
Q&A 64
Workshop 64
Hour 5: Manipulating Data 67
Overview of Data Manipulation 67
Populating Tables with New Data 68
Updating Existing Data 73
Deleting Data from Tables 75
Summary 76
Q&A 77
Workshop 77
Hour 6: Managing Database Transactions 81
What Is a Transaction? 81
Controlling Transactions 82
Poor Transactional Control 90
Summary 90
Q&A 90
Workshop 91
PART III: GETTING EFFECTIVE RESULTS FROM QUERIES
Hour 7: Introduction to Database Queries 93
The SELECT Statement 93
Case-Sensitivity 101
Fundamentals of Query Writing 102
Summary 106
Q&A 106
Workshop 107
Hour 8: Using Operators to Categorize Data 109
What Is an Operator in SQL? 109
Comparison Operators 110
Logical Operators 113
Conjunctive Operators 120
Negative Operators 123
Arithmetic Operators 128
Summary 130
Q&A 131
Workshop 131
Hour 9: Summarizing Data Results from a Query 133
Aggregate Functions 133
Summary 141
Q&A 142
Workshop 142
Hour 10: Sorting and Grouping Data 145
Why Group Data? 145
The GROUP BY Clause 146
GROUP BY Versus ORDER BY 150
CUBE and ROLLUP Expressions 153
The HAVING Clause 155
Summary 156
Q&A 157
Workshop 157
Hour 11: Restructuring the Appearance of Data 159
ANSI Character Functions 159
Common Character Functions 160
Miscellaneous Character Functions 169
Mathematical Functions 172
Conversion Functions 173
Combining Character Functions 176
Summary 177
Q&A 177
Workshop 178
Hour 12: Understanding Dates and Times 181
How Is a Date Stored? 181
Date Functions 183
Date Conversions 188
Summary 193
Q&A 194
Workshop 194
PART IV: BUILDING SOPHISTICATED DATABASE QUERIES
Hour 13: Joining Tables in Queries 197
Selecting Data from Multiple Tables 197
Understanding Joins 198
Join Considerations 207
Summary 210
Q&A 210
Workshop 211
Hour 14: Using Subqueries to Define Unknown Data 213
What Is a Subquery? 213
Embedded Subqueries 219
Correlated Subqueries 223
Subquery Performance 225
Summary 225
Q&A 226
Workshop 226
Hour 15: Combining Multiple Queries into One 229
Single Queries Versus Compound Queries 229
Compound Query Operators 230
Using ORDER BY with a Compound Query 235
Using GROUP BY with a Compound Query 237
Retrieving Accurate Data 238
Summary 239
Q&A 239
Workshop 240
PART V: SQL PERFORMANCE TUNING
Hour 16: Using Indexes to Improve Performance 243
What Is an Index? 243
How Do Indexes Work? 244
The CREATE INDEX Command 245
Types of Indexes 245
When Should Indexes Be Considered? 248
When Should Indexes Be Avoided? 248
Altering an Index 250
Dropping an Index 250
Summary 251
Q&A 251
Workshop 252
Hour 17: Improving Database Performance 253
What Is SQL Statement Tuning? 253
Database Tuning Versus SQL Statement Tuning 254
Formatting Your SQL Statement 254
Full Table Scans 260
Other Performance Considerations 261
Cost-Based Optimization 264
Summary 266
Q&A 266
Workshop 266
PART VI: USING SQL TO MANAGE USERS AND SECURITY
Hour 18: Managing Database Users 269
User Management in the Database 269
The Management Process 272
Tools Utilized by Database Users 279
Summary 279
Q&A 280
Workshop 280
Hour 19: Managing Database Security 283
What Is Database Security? 283
What Are Privileges? 284
Controlling User Access 287
Controlling Privileges Through Roles 291
Summary 293
Q&A 293
Workshop 294
PART VII: SUMMARIZED DATA STRUCTURES
Hour 20: Creating and Using Views and Synonyms 297
What Is a View? 297
Creating Views 300
Updating Data Through a View 307
Dropping a View 307
Performance Impact of Nested Views 308
What Is a Synonym? 308
Summary 310
Q&A 310
Workshop 311
Hour 21: Working with the System Catalog 313
What Is the System Catalog? 313
How Is the System Catalog Created? 314
What Is Contained in the System Catalog? 315
System Catalog Tables by Implementation 316
Querying the System Catalog 317
Updating System Catalog Objects 320
Summary 320
Q&A 321
Workshop 321
PART VIII: APPLYING SQL FUNDAMENTALS IN TODAY’S WORLD
Hour 22: Advanced SQL Topics 323
Cursors 323
Stored Procedures and Functions 326
Triggers 329
Dynamic SQL 331
Call-Level Interface 331
Using SQL to Generate SQL 332
Direct Versus Embedded SQL 333
Windowed Table Functions 333
Working with XML 334
Summary 335
Q&A 335
Workshop 336
Hour 23: Extending SQL to the Enterprise, the Internet, and the Intranet 337
SQL and the Enterprise 337
Accessing a Remote Database 339
SQL and the Internet 342
SQL and the Intranet 343
Summary 344
Q&A 344
Workshop 344
Hour 24: Extensions to Standard SQL 347
Various Implementations 347
Example Extensions 350
Interactive SQL Statements 353
Summary 354
Q&A 354
Workshop 355
PART IX: APPENDIXES
Appendix A: Common SQL Commands 357
SQL Statements 357
SQL Clauses 360
Appendix B: Installing Oracle and Microsoft SQL 363
Windows Installation Instructions for Oracle 363
Windows Installation Instructions for Microsoft SQL Server 365
Appendix C: Answers to Quizzes and Exercises 369
Hour 1, “Welcome to the World of SQL” 369
Hour 2, “Defining Data Structures” 371
Hour 3, “Managing Database Objects” 373
Hour 4, “The Normalization Process” 374
Hour 5, “Manipulating Data” 376
Hour 6, “Managing Database Transactions” 379
Hour 7, “Introduction to the Database Queries” 380
Hour 8, “Using Operators to Categorize Data” 383
Hour 9, “Summarizing Data Results from a Query” 386
Hour 10, “Sorting and Grouping Data” 388
Hour 11, “Restructuring the Appearance of Data” 390
Hour 12, “Understanding Dates and Times” 391
Hour 13, “Joining Tables in Queries” 392
Hour 14, “Using Subqueries to Define Unknown Data” 394
Hour 15, “Combining Multiple Queries into One” 396
Hour 16, “Using Indexes to Improve Performance” 397
Hour 17, “Improving Database Performance” 399
Hour 18, “Managing Database Users” 403
Hour 19, “Managing Database Security” 404
Hour 20, “Creating and Using Views and Synonyms” 404
Hour 21, “Working with the System Catalog” 406
Hour 22, “Advanced SQL Topics” 407
Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet” 408
Hour 24, “Extensions to Standard SQL” 409
Appendix D: Bonus Exercises 411
Appendix E: Glossary 425
Index 429
About the Author :
For more than 20 years each, the authors have studied, applied, and documented the SQL standard and its application to critical database systems in this book. The authors are experts in data management, specializing in Oracle, Microsoft, and other leading technologies.
Ryan Stephens is the co-founder and CEO of Perpetual Technologies, Inc. and Indy Data Partners in Indianapolis. Ryan has studied and consulted in the IT field for more than 20 years, specializing in data management, SQL, and Oracle. Ryan authored and taught database and SQL classes for Indiana University-Purdue University in Indianapolis for 5 years, and was a programmer analyst for the Indiana Army National Guard for 12 years. Ryan has written a variety of database and SQL books for Sams Publishing.
Arie D. Jones is the Vice President for Emerging Technologies for Indy Data Partners, Inc. (IDP) in Indianapolis. Arie leads IDP’s team of experts in 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.
Ronald Plew is retired as co-founder and vice president of Perpetual Technologies, Inc. Ron studied and consulted in the field of relational database technology for more than 20 years and has co-authored several books for Sams Publishing. Ron taught SQL and database classes for Indiana University-Purdue University in Indianapolis for 5 years. He is a retired programmer analyst from the Indiana Army National Guard.
