Sams Teach Yourself SQL in 24 Hours
5th Edition
Published on 9. June 2011
Book
Paperback/Softback
504 pages
978-0-672-33541-9 (ISBN)
Description
In just 24 sessions of one hour or less, you'll learn how to use SQL to build effective databases, efficiently retrieve your data, and manage everything from performance to security! Using this book's straightforward, step-by-step approach, you'll learn hands-on through practical examples. Each lesson builds on what you've already learned, giving you a strong real-world foundation for success. The authors guide you from the absolute basics to advanced techniques-including views, transactions, Web data publishing, and even powerful SQL extensions for Oracle and Microsoft SQL Server!
Step-by-step instructions carefully walk you through the most common SQL tasks.
Quizzes and Exercises at the end of each chapter help you test your knowledge.
By the Way notes present interesting information related to the discussion.
Did You Know? tips offer advice or show you easier ways to perform tasks.
Watch Out! cautions alert you to possible problems and give you advice on how to avoid them.
Learn how to...
Understand what SQL is, how it works, and what it does
Define efficient database structures and objects
"Normalize" raw databases into logically organized tables
Edit relational data and tables with DML
Manage database 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
Implement effective database security
Use views, synonyms, and the system catalog
Extend SQL to the enterprise and Internet
Master important Oracle and Microsoft SQL Server extensions to ANSI SQL
Step-by-step instructions carefully walk you through the most common SQL tasks.
Quizzes and Exercises at the end of each chapter help you test your knowledge.
By the Way notes present interesting information related to the discussion.
Did You Know? tips offer advice or show you easier ways to perform tasks.
Watch Out! cautions alert you to possible problems and give you advice on how to avoid them.
Learn how to...
Understand what SQL is, how it works, and what it does
Define efficient database structures and objects
"Normalize" raw databases into logically organized tables
Edit relational data and tables with DML
Manage database 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
Implement effective database security
Use views, synonyms, and the system catalog
Extend SQL to the enterprise and Internet
Master important Oracle and Microsoft SQL Server extensions to ANSI SQL
More details
Edition
5th edition
Language
English
Place of publication
Indianapolis
United States
Publishing group
Pearson Education (US)
Target group
Professional and scholarly
Dimensions
Height: 231 mm
Width: 179 mm
Thickness: 26 mm
Weight
770 gr
ISBN-13
978-0-672-33541-9 (9780672335419)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Other editions
New editions
Ryan Stephens | Arie D. Jones | Ron Plew
SQL in 24 Hours, Sams Teach Yourself
Book
12/2015
6th Edition
Sams Publishing
€36.50
Article exhausted; check for reprint
Previous edition
Ryan Stephens | Ron Plew | Arie D. Jones
Sams Teach Yourself SQL in 24 Hours
Book
06/2008
4th Edition
Sams Publishing
€30.94
Article exhausted; check for reprint
Persons
For more than 10 years, the authors have studied, applied, and documented the SQL standard and its application to critical database systems in this book.
Ryan Stephens and Ron Plew are entrepreneurs, speakers, and cofounders 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 high availability of critical systems.
Arie D. Jones is the principal technology manager for Perpetual Technologies, Inc. (PTI) in Indianapolis, Indiana. Arie leads PTI'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.
Ryan Stephens and Ron Plew are entrepreneurs, speakers, and cofounders 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 high availability of critical systems.
Arie D. Jones is the principal technology manager for Perpetual Technologies, Inc. (PTI) in Indianapolis, Indiana. Arie leads PTI'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.
Content
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 9
The Database Used in This Book 12
Summary 17
Q&A 17
Workshop 18
Part II: Building Your Database
HOUR 2: Defining Data Structures 21
What Is Data? 21
Basic Data Types 22
Summary 31
Q&A 31
Workshop 32
HOUR 3: Managing Database Objects 37
What Are Database Objects? 37
What Is a Schema? 37
Tables: The Primary Storage for Data 39
Integrity Constraints 49
Summary 54
Q&A 55
Workshop 55
HOUR 4: The Normalization Process 61
Normalizing a Database 61
Denormalizing a Database 69
Summary 70
Q&A 70
Workshop 71
HOUR 5: Manipulating Data 73
Overview of Data Manipulation 73
Populating Tables with New Data 74
Updating Existing Data 80
Deleting Data from Tables 82
Summary 83
Q&A 83
Workshop 84
HOUR 6: Managing Database Transactions 87
What Is a Transaction? 87
Controlling Transactions 88
Transactional Control and Database Performance 95
Summary 96
Q&A 96
Workshop 97
Part III: Getting Effective Results from Queries
HOUR 7: Introduction to the Database Query 99
What Is a Query? 99
Introduction to the SELECT Statement 99
Examples of Simple Queries 108
Summary 112
Q&A 112
Workshop 113
HOUR 8: Using Operators to Categorize Data 115
What Is an Operator in SQL? 115
Comparison Operators 116
Logical Operators 119
Conjunctive Operators 126
Negative Operators 129
Arithmetic Operators 133
Summary 136
Q&A 137
Workshop 137
HOUR 9: Summarizing Data Results from a Query 141
What Are Aggregate Functions? 141
Summary 150
Q&A 150
Workshop 150
HOUR 10: Sorting and Grouping Data 153
Why Group Data? 153
The GROUP BY Clause 154
GROUP BY Versus ORDER BY 159
CUBE and ROLLUP Expressions 161
The HAVING Clause 164
Summary 165
Q&A 166
Workshop 166
HOUR 11: Restructuring the Appearance of Data 169
ANSI Character Functions 169
Common Character Functions 170
Miscellaneous Character Functions 179
Mathematical Functions 183
Conversion Functions 183
Combining Character Functions 186
Summary 187
Q&A 188
Workshop 188
HOUR 12: Understanding Dates and Times 191
How Is a Date Stored? 191
Date Functions 193
Date Conversions 198
Summary 204
Q&A 204
Workshop 205
Part IV: Building Sophisticated Database Queries
HOUR 13: Joining Tables in Queries 207
Selecting Data from Multiple Tables 207
Understanding Joins 208
Join Considerations 217
Summary 221
Q&A 222
Workshop 222
HOUR 14: Using Subqueries to Define Unknown Data 225
What Is a Subquery? 225
Embedded Subqueries 231
Correlated Subqueries 233
Subquery Performance 234
Summary 235
Q&A 235
Workshop 236
HOUR 15: Combining Multiple Queries into One 239
Single Queries Versus Compound Queries 239
Compound Query Operators 240
Using ORDER BY with a Compound Query 246
Using GROUP BY with a Compound Query 248
Retrieving Accurate Data 250
Summary 250
Q&A 250
Workshop 251
Part V: SQL Performance Tuning
HOUR 16: Using Indexes to Improve Performance 255
What Is an Index? 255
How Do Indexes Work? 256
The CREATE INDEX Command 257
Types of Indexes 258
When Should Indexes Be Considered? 260
When Should Indexes Be Avoided? 261
Altering an Index 263
Dropping an Index 263
Summary 264
Q&A 264
Workshop 265
HOUR 17: Improving Database Performance 267
What Is SQL Statement Tuning? 267
Database Tuning Versus SQL Statement Tuning 268
Formatting Your SQL Statement 268
Full Table Scans 274
Other Performance Considerations 275
Cost-Based Optimization 279
Performance Tools 280
Summary 280
Q&A 281
Workshop 281
Part VI: Using SQL to Manage Users and Security
HOUR 18: Managing Database Users 285
User Management in the Database 285
The Management Process 288
Tools Utilized by Database Users 296
Summary 296
Q&A 297
Workshop 297
HOUR 19: Managing Database Security 299
What Is Database Security? 299
What Are Privileges? 301
Controlling User Access 304
Controlling Privileges Through Roles 308
Summary 310
Q&A 310
Workshop 311
Part VII: Summarized Data Structures
HOUR 20: Creating and Using Views and Synonyms 313
What Is a View? 313
Creating Views 316
WITH CHECK OPTION 320
Creating a Table from a View 321
Views and the ORDER BY Clause 322
Updating Data Through a View 322
Dropping a View 323
Performance Impact of Using Nested Views 323
What Is a Synonym? 324
Summary 325
Q&A 326
Workshop 326
HOUR 21: Working with the System Catalog 329
What Is the System Catalog? 329
How Is the System Catalog Created? 331
What Is Contained in the System Catalog? 331
System Catalog Tables by Implementation 333
Querying the System Catalog 334
Updating System Catalog Objects 336
Summary 337
Q&A 337
Workshop 338
Part VIII: Applying SQL Fundamentals in Today's World
HOUR 22: Advanced SQL Topics 339
Cursors 339
Stored Procedures and Functions 343
Triggers 346
Dynamic SQL 348
Call-Level Interface 349
Using SQL to Generate SQL 350
Direct Versus Embedded SQL 351
Windowed Table Functions 351
Working with XML 352
Summary 353
Q&A 353
Workshop 354
HOUR 23: Extending SQL to the Enterprise, the Internet, and the Intranet 355
SQL and the Enterprise 355
Accessing a Remote Database 357
SQL and the Internet 360
SQL and the Intranet 361
Summary 362
Q&A 363
Workshop 363
HOUR 24: Extensions to Standard SQL 367
Various Implementations 367
Example Extensions 370
Interactive SQL Statements 373
Summary 374
Q&A 375
Workshop 375
Part IX: Appendixes
APPENDIX A: Common SQL Commands 377
SQL Statements 377
SQL Clauses 381
APPENDIX B: Using the Databases for Exercises 383
Windows Installation Instructions for MySQL 383
Windows Installation Instructions for Oracle 386
Windows Installation Instructions for Microsoft SQL Server 388
APPENDIX C: Answers to Quizzes and Exercises 391
APPENDIX D: CREATE TABLE Statements for Book Examples 439
APPENDIX E: INSERT Statements for Data in Book Examples 443
APPENDIX F: Glossary 451
APPENDIX G: Bonus Exercises 455
INDEX 461
HOUR 1: Welcome to the World of SQL 1
SQL Definition and History 1
SQL Sessions 8
Types of SQL Commands 9
The Database Used in This Book 12
Summary 17
Q&A 17
Workshop 18
Part II: Building Your Database
HOUR 2: Defining Data Structures 21
What Is Data? 21
Basic Data Types 22
Summary 31
Q&A 31
Workshop 32
HOUR 3: Managing Database Objects 37
What Are Database Objects? 37
What Is a Schema? 37
Tables: The Primary Storage for Data 39
Integrity Constraints 49
Summary 54
Q&A 55
Workshop 55
HOUR 4: The Normalization Process 61
Normalizing a Database 61
Denormalizing a Database 69
Summary 70
Q&A 70
Workshop 71
HOUR 5: Manipulating Data 73
Overview of Data Manipulation 73
Populating Tables with New Data 74
Updating Existing Data 80
Deleting Data from Tables 82
Summary 83
Q&A 83
Workshop 84
HOUR 6: Managing Database Transactions 87
What Is a Transaction? 87
Controlling Transactions 88
Transactional Control and Database Performance 95
Summary 96
Q&A 96
Workshop 97
Part III: Getting Effective Results from Queries
HOUR 7: Introduction to the Database Query 99
What Is a Query? 99
Introduction to the SELECT Statement 99
Examples of Simple Queries 108
Summary 112
Q&A 112
Workshop 113
HOUR 8: Using Operators to Categorize Data 115
What Is an Operator in SQL? 115
Comparison Operators 116
Logical Operators 119
Conjunctive Operators 126
Negative Operators 129
Arithmetic Operators 133
Summary 136
Q&A 137
Workshop 137
HOUR 9: Summarizing Data Results from a Query 141
What Are Aggregate Functions? 141
Summary 150
Q&A 150
Workshop 150
HOUR 10: Sorting and Grouping Data 153
Why Group Data? 153
The GROUP BY Clause 154
GROUP BY Versus ORDER BY 159
CUBE and ROLLUP Expressions 161
The HAVING Clause 164
Summary 165
Q&A 166
Workshop 166
HOUR 11: Restructuring the Appearance of Data 169
ANSI Character Functions 169
Common Character Functions 170
Miscellaneous Character Functions 179
Mathematical Functions 183
Conversion Functions 183
Combining Character Functions 186
Summary 187
Q&A 188
Workshop 188
HOUR 12: Understanding Dates and Times 191
How Is a Date Stored? 191
Date Functions 193
Date Conversions 198
Summary 204
Q&A 204
Workshop 205
Part IV: Building Sophisticated Database Queries
HOUR 13: Joining Tables in Queries 207
Selecting Data from Multiple Tables 207
Understanding Joins 208
Join Considerations 217
Summary 221
Q&A 222
Workshop 222
HOUR 14: Using Subqueries to Define Unknown Data 225
What Is a Subquery? 225
Embedded Subqueries 231
Correlated Subqueries 233
Subquery Performance 234
Summary 235
Q&A 235
Workshop 236
HOUR 15: Combining Multiple Queries into One 239
Single Queries Versus Compound Queries 239
Compound Query Operators 240
Using ORDER BY with a Compound Query 246
Using GROUP BY with a Compound Query 248
Retrieving Accurate Data 250
Summary 250
Q&A 250
Workshop 251
Part V: SQL Performance Tuning
HOUR 16: Using Indexes to Improve Performance 255
What Is an Index? 255
How Do Indexes Work? 256
The CREATE INDEX Command 257
Types of Indexes 258
When Should Indexes Be Considered? 260
When Should Indexes Be Avoided? 261
Altering an Index 263
Dropping an Index 263
Summary 264
Q&A 264
Workshop 265
HOUR 17: Improving Database Performance 267
What Is SQL Statement Tuning? 267
Database Tuning Versus SQL Statement Tuning 268
Formatting Your SQL Statement 268
Full Table Scans 274
Other Performance Considerations 275
Cost-Based Optimization 279
Performance Tools 280
Summary 280
Q&A 281
Workshop 281
Part VI: Using SQL to Manage Users and Security
HOUR 18: Managing Database Users 285
User Management in the Database 285
The Management Process 288
Tools Utilized by Database Users 296
Summary 296
Q&A 297
Workshop 297
HOUR 19: Managing Database Security 299
What Is Database Security? 299
What Are Privileges? 301
Controlling User Access 304
Controlling Privileges Through Roles 308
Summary 310
Q&A 310
Workshop 311
Part VII: Summarized Data Structures
HOUR 20: Creating and Using Views and Synonyms 313
What Is a View? 313
Creating Views 316
WITH CHECK OPTION 320
Creating a Table from a View 321
Views and the ORDER BY Clause 322
Updating Data Through a View 322
Dropping a View 323
Performance Impact of Using Nested Views 323
What Is a Synonym? 324
Summary 325
Q&A 326
Workshop 326
HOUR 21: Working with the System Catalog 329
What Is the System Catalog? 329
How Is the System Catalog Created? 331
What Is Contained in the System Catalog? 331
System Catalog Tables by Implementation 333
Querying the System Catalog 334
Updating System Catalog Objects 336
Summary 337
Q&A 337
Workshop 338
Part VIII: Applying SQL Fundamentals in Today's World
HOUR 22: Advanced SQL Topics 339
Cursors 339
Stored Procedures and Functions 343
Triggers 346
Dynamic SQL 348
Call-Level Interface 349
Using SQL to Generate SQL 350
Direct Versus Embedded SQL 351
Windowed Table Functions 351
Working with XML 352
Summary 353
Q&A 353
Workshop 354
HOUR 23: Extending SQL to the Enterprise, the Internet, and the Intranet 355
SQL and the Enterprise 355
Accessing a Remote Database 357
SQL and the Internet 360
SQL and the Intranet 361
Summary 362
Q&A 363
Workshop 363
HOUR 24: Extensions to Standard SQL 367
Various Implementations 367
Example Extensions 370
Interactive SQL Statements 373
Summary 374
Q&A 375
Workshop 375
Part IX: Appendixes
APPENDIX A: Common SQL Commands 377
SQL Statements 377
SQL Clauses 381
APPENDIX B: Using the Databases for Exercises 383
Windows Installation Instructions for MySQL 383
Windows Installation Instructions for Oracle 386
Windows Installation Instructions for Microsoft SQL Server 388
APPENDIX C: Answers to Quizzes and Exercises 391
APPENDIX D: CREATE TABLE Statements for Book Examples 439
APPENDIX E: INSERT Statements for Data in Book Examples 443
APPENDIX F: Glossary 451
APPENDIX G: Bonus Exercises 455
INDEX 461