Sams Teach Yourself SQL in 24 Hours

Sams Teach Yourself SQL in 24 Hours

Sams Teach Yourself SQL in 24 Hours

Sams Teach Yourself SQL in 24 Hours

eBook

$24.49  $32.19 Save 24% Current price is $24.49, Original price is $32.19. You Save 24%.

Available on Compatible NOOK Devices and the free NOOK Apps.
WANT A NOOK?  Explore Now

Related collections and offers


Overview

Sams Teach Yourself SQL in 24 Hours, Third Edition presents the key features of SQL (Structured Query Language) in an easy to understand format with updated code examples, notes, diagrams, exercises, and quizzes. New material covers more information on transactions, constructs, embedded databases, and object-oriented programming. In this edition, the authors include examples based on a database like MySQL, a very popular open source database.


Product Details

ISBN-13: 9780132715126
Publisher: Pearson Education
Publication date: 05/30/2008
Series: Sams Teach Yourself
Sold by: Barnes & Noble
Format: eBook
Pages: 504
File size: 7 MB

About the Author

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.

Read an Excerpt

IntroductionIntroduction

Welcome to the world of relational databases and SQL! This book is written for those self-motivated individuals out there who would like to get an edge on relational database technology by learning the Structured Query Language—SQL. This book was written primarily for those with very little or no experience with relational database management systems using SQL. This book also applies to those who have some experience with relational databases but need to learn how to navigate within the database, issue queries against the database, build database structures, manipulate data in the database, and more. This book is not geared toward individuals with significant relational database experience who have been using SQL on a regular basis.

What This Book Intends to Accomplish

This book was written for individuals with little or no experience using SQL or those who have used a relational database, but their tasks have been very limited within the realm of SQL. Keeping this thought in mind, it should be noted up front that this book is strictly a learning mechanism, and one in which we present the material from ground zero and provide examples and exercises with which to begin to apply the material covered. This book is not a complete SQL reference and should not be relied on as a sole reference of SQL. However, this book combined with a complete SQL command reference could serve as a complete solution source to all of your SQL needs.

What We Added to This Edition

This edition contains the same content and format as the first through third editions. We have been through the entire book, searching for the little things that could be improved to produce a better edition. We have also added concepts and commands from the new SQL standard, SQL:2003, to bring this book up to date, making it more complete and applicable to today's SQL user. The most important addition was the use of MySQL for hands-on exercises. By using an open source database such as MySQL, all readers have equal opportunity for participation in hands-on exercises.

What You Need

You might be wondering, what do I need to make this book work for me? Theoretically, you should be able to pick up this book, study the material for the current hour, study the examples, and either write out the exercises or run them on a relational database server. However, it would be to your benefit to have access to a relational database system to which to apply the material in each lesson. The relational database to which you have access is not a major factor because SQL is the standard language for all relational databases. Some database systems that you can use include Oracle, Sybase, Informix, Microsoft SQL Server, Microsoft Access, MySQL, and dBase.

Conventions Used in This Book

For the most part, we have tried to keep conventions in this book as simple as possible.

Many new terms are printed in italics.

In the listings, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. Any code that is serving as a placeholder appears in italic monospace.

SQL code and keywords have been placed in uppercase for your convenience and general consistency. For example:

SELECT
• FROM PRODUCTS_TBL;
PROD_ID PROD_DESC COST————— ——————————————————11235 WITCHES COSTUME 29.99222 LASTIC PUMPKIN 18 INCH 7.7513 FALSE PARAFFIN TEETH 1.190 LIGHTED LANTERNS 14.515 ASSORTED COSTUMES 109 CANDY CORN 1.356 PUMPKIN CANDY 1.4587 PLASTIC SPIDERS 1.05119 ASSORTED MASKS 4.959 rows selected.

The following special design features enhance the text:

There are syntax boxes to draw your attention to the syntax of the commands discussed during each hour.

SELECT ALL
• DISTINCT COLUMN1, COLUMN2 FROM TABLE , TABLE2 ;

Note - Notes are provided to expand on the material covered in each hour of the book.

Caution - Cautions are provided to warn the reader about "disasters" that could occur and certain precautions that should be taken.

Tip - Tips are also given to supplement the material covered during appropriate hours of study.

ANSI SQL and Vendor Implementations

One thing that is difficult about writing a book like this on standard SQL is that although there is an ANSI standard for SQL, each database vendor has its own implementation of SQL. With each implementation come variations from the actual standard, enhancements to the standard, and even missing elements from the standard.

The expected question is, "Because there is an ANSI standard for SQL, what is so difficult about teaching standard SQL?" The answer to this question begins with the statement that ANSI SQL is just that: a standard. ANSI SQL is not an actual language. To teach you SQL, we had to come up with examples and exercises that involve using one or more implementations of SQL. Because each vendor has its own implementation with its own specifications for the language of SQL, these variations, if not handled properly in this book, could actually cause confusion concerning the syntax of various SQL commands. Therefore, we have tried to stay as close to the ANSI standard as possible, foremost discussing the ANSI standard and then showing examples from different implementations that are very close, if not the same, as the exact syntax that ANSI prescribes.

We have, however, accompanied examples of variations among implementations with notes for reminders and tips on what to watch out for. Just remember this: Each implementation differs slightly from other implementations. The most important thing is that you understand the underlying concepts of SQL and its commands. Although slight variations do exist, SQL is basically the same across the board and is very portable from database to database, regardless of the particular implementation.

Understanding the Examples and Exercises

We have chosen to use MySQL for most of the examples in this book due to its high compliance to the ANSI standard; however, we have also shown examples from Oracle, Sybase, Microsoft SQL Server, and dBase.

The use of MySQL for hands-on exercises was chosen so that all readers may participate, with minimal confusion in converting SQL syntax into the proper syntax of the database each reader is using. MySQL was chosen for exercises because it is an open source database (free), it is easy to install, and its syntax is very similar to that of the ANSI Standard. Additionally, MySQL is compatible with most operating system platforms.

In Appendix B, "Using MySQL for Exercises," we show you how to obtain and install MySQL. After it is installed on your computer, MySQL can be used for most of the exercises in this book. Unfortunately, because MySQL is not fully ANSI SQL compliant, MySQL exercises are not available for every subject.

As stated, some differences in the exact syntax exist among implementations of SQL. For example, if you attempt to execute some examples in this book, you might have to make minor modifications to fit the exact syntax of the implementation that you are using. We have tried to keep all the examples compliant with the standard; however, we have intentionally shown you some examples that are not exactly compliant. The basic structure for all the commands is the same. To learn SQL, you have to start with an implementation using practical examples. For hands-on practice, we use MySQL. If you have access to another database implementation such as Oracle, we encourage its use for hands-on exercises. You should be able to emulate the database and examples used in this book without much difficulty. Any adjustments that you might have to make to the examples in this book to fit your implementation exactly will only help you to better understand the syntax and features of your implementation.

Good luck!

© Copyright Pearson Education. All rights reserved.

Table of Contents

(NOTE: Each chapter concludes with a Summary, Q&A and Workshop.).
Introduction.

I. A SQL CONCEPTS OVERVIEW.

Hour 1. Welcome to the World of SQL.
SQL Definition and History. SQL Sessions. Types of SQL Commands. An Introduction to the Database Used in This Book.

II. BUILDING YOUR DATABASE.


Hour 2. Defining Data Structures.
What Is Data? Basic Data Types.

Hour 3. Managing Database Objects.
What Are Database Objects? What Is a Schema? A Table: The Primary Storage for Data. Integrity Constraints.

Hour 4. The Normalization Process.
Normalizing a Database.

Hour 5. Manipulating Data.
Overview of Data Manipulation. Populating Tables with New Data. Updating Existing Data. Deleting Data from Tables.

Hour 6. Managing Database Transactions.
What Is a Transaction? What Is Transactional Control? Transactional Control and Database Performance.

III. GETTING EFFECTIVE RESULTS FROM QUERIES.


Hour 7. Introduction to the Database Query.
What Is a Query? Introduction to the SELECT Statement. Examples of Simple Queries.

Hour 8. Using Operators to Categorize Data.
What Is an Operator in SQL? Comparison Operators. Logical Operators. Conjunctive Operators. Negating Conditions with the NOT Operator. Arithmetic Operators.

Hour 9. Summarizing Data Results from a Query.
What Are Aggregate Functions?

Hour 10. Sorting and Grouping Data.
Why Group Data? The GROUP BY Clause. GROUP BY Versus ORDER BY. The HAVING Clause.

Hour 11. Restructuring the Appearance of Data.
The Concepts of ANSI Character Functions. Various Common Character Functions. Miscellaneous Character Functions. Mathematical Functions. Conversion Functions. The Concept of Combining Character Functions.

Hour 12. Understanding Dates and Times.
How Is a Date Stored? Date Functions. Date Conversions.

IV. BUILDING SOPHISTICATED DATABASE QUERIES.


Hour 13. Joining Tables in Queries.
Selecting Data from Multiple Tables. Types of Joins. Join Considerations.

Hour 14. Using Subqueries to Define Unknown Data.
What Is a Subquery? Embedding a Subquery Within a Subquery..

Hour 15. Combining Multiple Queries into One.
Single Queries Versus Compound Queries. Why Would I Ever Want to Use a Compound Query? Compound Query Operators. Using an ORDER BY with a Compound Query. Using GROUP BY with a Compound Query. Retrieving Accurate Data.

V. SQL PERFORMANCE TUNING.


Hour 16. Using Indexes to Improve Performance.
What Is an Index? How Do Indexes Work? The CREATE INDEX Command. Types of Indexes. When Should Indexes Be Considered? When Should Indexes Be Avoided?

Hour 17. Improving Database Performance.
What Is SQL Statement Tuning? Database Tuning Versus SQL Tuning. Formatting Your SQL Statement. Full Table Scans. Other Performance Considerations. Performance Tools.

VI. USING SQL TO MANAGE USERS AND SECURITY.


Hour 18. Managing Database Users.
Users Are the Reason. The Management Process. Tools Utilized by Database Users.

Hour 19. Managing Database Security.
What Is Database Security? How Does Security Differ from User Management? What Are Privileges? Controlling User Access. Controlling Privileges Through Roles.

VII. SUMMARIZED DATA STRUCTURES.


Hour 20. Creating and Using Views and Synonyms.
What Is a View? Creating Views. Dropping a View. What Is a Synonym?

Hour 21. Working with the System Catalog.
What Is the System Catalog? How Is the System Catalog Created? What Is Contained in the System Catalog? Examples of System Catalog Tables by Implementation. Querying the System Catalog. Updating System Catalog Objects.

VIII. APPLYING SQL FUNDAMENTALS IN TODAY'S WORLD.


Hour 22. Advanced SQL Topics.
Advanced Topics. Cursors. Stored Procedures and Functions. Triggers. Dynamic SQL. Call-Level Interface. Using SQL to Generate SQL. Direct Versus Embedded SQL.

Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet.
SQL and the Enterprise. Accessing a Remote Database. Accessing a Remote Database Through a Web Interface. SQL and the Internet. SQL and the Intranet.

Hour 24. Extensions to Standard SQL.
Various Implementations. Examples of Extensions from Some Implementations. Interactive SQL Statements.

X. APPENDIXES.


Appendix A: Common SQL Commands.
SQL Statements. SQL Clauses.

Appendix B: Using MySQL for Exercises.
Windows Installation Instructions. Linux Installation Instructions.

Appendix C: Answers to Quizzes and Exercises.
Hour 1, “Welcome to the World of SQL” . Hour 2, “Defining Data Structures” . Hour 3, “Managing Database Objects” . Hour 4, “The Normalization Process” . Hour 5, “Manipulating Data” . Hour 6, “Managing Database Transactions” . Hour 7, “Introduction to the Database Query” . Hour 8, “Using Operators to Categorize Data” . Hour 9, “Summarizing Data Results from a Query” . Hour 10, “Sorting and Grouping Data” . Hour 11, “Restructuring the Appearance of Data” . Hour 12, “Understanding Dates and Time” . Hour 13, “Joining Tables in Queries” . Hour 14, “Using Subqueries to Define Unknown Data” . Hour 15, “Combining Multiple Queries into One” . Hour 16, “Using Indexes to Improve Performance” . Hour 17, “Improving Database Performance” . Hour 18, “Managing Database Users” . Hour 19, “Managing Database Security” . Hour 20, “Creating and Using Views and Synonyms” . Hour 21, “Working with the System Catalog” . Hour 22, “Advanced SQL Topics” . Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet” . Hour 24, “Extensions to Standard SQL” .

Appendix D: CREATE TABLE Statements for Book Examples.
EMPLOYEE_TBL. EMPLOYEE_PAY_TBL. CUSTOMER_TBL. ORDERS_TBL. PRODUCTS_TBL.

Appendix E: INSERT Statements for Data in Book Examples.
INSERT Statements.

Appendix F: Glossary.
Appendix G: Bonus Exercises.
Index.

Preface

Introduction

Introduction

Welcome to the world of relational databases and SQL! This book is written for those self-motivated individuals out there who would like to get an edge on relational database technology by learning the Structured Query Language—SQL. This book was written primarily for those with very little or no experience with relational database management systems using SQL. This book also applies to those who have some experience with relational databases but need to learn how to navigate within the database, issue queries against the database, build database structures, manipulate data in the database, and more. This book is not geared toward individuals with significant relational database experience who have been using SQL on a regular basis.

What This Book Intends to Accomplish

This book was written for individuals with little or no experience using SQL or those who have used a relational database, but their tasks have been very limited within the realm of SQL. Keeping this thought in mind, it should be noted up front that this book is strictly a learning mechanism, and one in which we present the material from ground zero and provide examples and exercises with which to begin to apply the material covered. This book is not a complete SQL reference and should not be relied on as a sole reference of SQL. However, this book combined with a complete SQL command reference could serve as a complete solution source to all of your SQL needs.

What We Added to This Edition

This edition contains the same content and format as the first through third editions. We have been through the entire book, searching for the little things that could be improved to produce a better edition. We have also added concepts and commands from the new SQL standard, SQL:2003, to bring this book up to date, making it more complete and applicable to today's SQL user. The most important addition was the use of MySQL for hands-on exercises. By using an open source database such as MySQL, all readers have equal opportunity for participation in hands-on exercises.

What You Need

You might be wondering, what do I need to make this book work for me? Theoretically, you should be able to pick up this book, study the material for the current hour, study the examples, and either write out the exercises or run them on a relational database server. However, it would be to your benefit to have access to a relational database system to which to apply the material in each lesson. The relational database to which you have access is not a major factor because SQL is the standard language for all relational databases. Some database systems that you can use include Oracle, Sybase, Informix, Microsoft SQL Server, Microsoft Access, MySQL, and dBase.

Conventions Used in This Book

For the most part, we have tried to keep conventions in this book as simple as possible.

Many new terms are printed in italics.

In the listings, all code that you type in (input) appears in boldface monospace. Output appears in standard monospace. Any code that is serving as a placeholder appears in italic monospace.

SQL code and keywords have been placed in uppercase for your convenience and general consistency. For example:

SELECT
• FROM PRODUCTS_TBL;
PROD_ID PROD_DESC COST---------- ------------------------------------11235 WITCHES COSTUME 29.99222 LASTIC PUMPKIN 18 INCH 7.7513 FALSE PARAFFIN TEETH 1.190 LIGHTED LANTERNS 14.515 ASSORTED COSTUMES 109 CANDY CORN 1.356 PUMPKIN CANDY 1.4587 PLASTIC SPIDERS 1.05119 ASSORTED MASKS 4.959 rows selected.

The following special design features enhance the text:

There are syntax boxes to draw your attention to the syntax of the commands discussed during each hour.

SELECT ALL
• DISTINCT COLUMN1, COLUMN2 FROM TABLE , TABLE2 ;


Note - Notes are provided to expand on the material covered in each hour of the book.



Caution - Cautions are provided to warn the reader about "disasters" that could occur and certain precautions that should be taken.



Tip - Tips are also given to supplement the material covered during appropriate hours of study.


ANSI SQL and Vendor Implementations

One thing that is difficult about writing a book like this on standard SQL is that although there is an ANSI standard for SQL, each database vendor has its own implementation of SQL. With each implementation come variations from the actual standard, enhancements to the standard, and even missing elements from the standard.

The expected question is, "Because there is an ANSI standard for SQL, what is so difficult about teaching standard SQL?" The answer to this question begins with the statement that ANSI SQL is just that: a standard. ANSI SQL is not an actual language. To teach you SQL, we had to come up with examples and exercises that involve using one or more implementations of SQL. Because each vendor has its own implementation with its own specifications for the language of SQL, these variations, if not handled properly in this book, could actually cause confusion concerning the syntax of various SQL commands. Therefore, we have tried to stay as close to the ANSI standard as possible, foremost discussing the ANSI standard and then showing examples from different implementations that are very close, if not the same, as the exact syntax that ANSI prescribes.

We have, however, accompanied examples of variations among implementations with notes for reminders and tips on what to watch out for. Just remember this: Each implementation differs slightly from other implementations. The most important thing is that you understand the underlying concepts of SQL and its commands. Although slight variations do exist, SQL is basically the same across the board and is very portable from database to database, regardless of the particular implementation.

Understanding the Examples and Exercises

We have chosen to use MySQL for most of the examples in this book due to its high compliance to the ANSI standard; however, we have also shown examples from Oracle, Sybase, Microsoft SQL Server, and dBase.

The use of MySQL for hands-on exercises was chosen so that all readers may participate, with minimal confusion in converting SQL syntax into the proper syntax of the database each reader is using. MySQL was chosen for exercises because it is an open source database (free), it is easy to install, and its syntax is very similar to that of the ANSI Standard. Additionally, MySQL is compatible with most operating system platforms.

In Appendix B, "Using MySQL for Exercises," we show you how to obtain and install MySQL. After it is installed on your computer, MySQL can be used for most of the exercises in this book. Unfortunately, because MySQL is not fully ANSI SQL compliant, MySQL exercises are not available for every subject.

As stated, some differences in the exact syntax exist among implementations of SQL. For example, if you attempt to execute some examples in this book, you might have to make minor modifications to fit the exact syntax of the implementation that you are using. We have tried to keep all the examples compliant with the standard; however, we have intentionally shown you some examples that are not exactly compliant. The basic structure for all the commands is the same. To learn SQL, you have to start with an implementation using practical examples. For hands-on practice, we use MySQL. If you have access to another database implementation such as Oracle, we encourage its use for hands-on exercises. You should be able to emulate the database and examples used in this book without much difficulty. Any adjustments that you might have to make to the examples in this book to fit your implementation exactly will only help you to better understand the syntax and features of your implementation.

Good luck!

© Copyright Pearson Education. All rights reserved.

From the B&N Reads Blog

Customer Reviews