Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • 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 Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part 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
    • Summary
    • Q&A
    • Workshop
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

Summary

Some advanced SQL concepts are discussed this hour. Although this hour does not go into a lot of detail, it does provide you with a basic understanding of how you can apply the basic concepts that you have learned up to this point. You start with cursors, which are used to pass a data set selected by a query into a location in memory. After a cursor is declared in a program, it must first be opened for accessibility. Then the contents of the cursor are fetched into a variable, at which time the data can be used for program processing. The result set for the cursor is contained in memory until the cursor is closed and the memory is deallocated.

Stored procedures and triggers are covered next. Stored procedures are basically SQL statements that are stored together in the database. These statements, along with other implementation-specific commands, are compiled in the database and are ready to be executed by a database user at any given time. A trigger is also a type of stored procedure—one that allows actions to be automatically performed based on other actions that occur within the database. Stored procedures typically provide better performance benefits than individual SQL statements.

Dynamic SQL, using SQL to generate other SQL statements, and the differences between direct SQL and embedded SQL were the last subjects discussed. Dynamic SQL is SQL code dynamically created during runtime by a user, unlike static SQL. Using SQL code to generate other SQL statements is a great time-saver. It is a way of automating the creation of numerous, tedious SQL statements using features available with your implementation, such as concatenation and the selection of literal values. Finally, the main difference between direct SQL and embedded SQL is that the user issues direct SQL statements from some terminal, whereas embedded SQL is actually embedded within a host program to help process data.

The concepts of some of the advanced topics discussed during this hour are used to illustrate the application of SQL in an enterprise, covered in Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet."

Share This