WIXLIB

Teach Yourself SQL in 21 Days, SecondEditionTable of Contents:IntroductionWeek 1 at a GlanceDay 1 Introduction to SQLDay 2 Introduction to the Query: The SELECT StatementDay 3 Expressions, Conditions, and OperatorsDay 4 Functions: Molding the Data You RetrieveDay 5 Clauses in SQLDay 6 Joining TablesDay 7 Subqueries: The Embedded SELECT StatementWeek 1 in ReviewWeek 2 at a GlanceDay 8 Manipulating DataDay 9 Creating and Maintaining Tables

Day 10 Creating Views and IndexesDay 11 Controlling TransactionsDay 12 Database SecurityDay 13 Advanced SQL TopicsDay 14 Dynamic Uses of SQLWeek 2 in ReviewWeek 3 at a GlanceDay 15 Streamlining SQL Statements for Improved PerformanceDay 16 Using Views to Retrieve Useful Information from the Data DictionaryDay 17 Using SQL to Generate SQL StatementsDay 18 PL/SQL: An IntroductionDay 19 Transact-SQL: An IntroductionDay 20 SQL*PlusDay 21 Common SQL Mistakes/Errors and ResolutionsWeek 3 in ReviewAppendixesA Glossary of Common SQL StatementsB Source Code Listings for the C Program Used on Day 14C Source Code Listings for the Delphi Program Used on Day 14D Resources

E ASCII TableF Answers to Quizzes and Excercises Copyright, Macmillan Computer Publishing. All rights reserved.

Teach Yourself SQL in 21 Days, SecondEditionAcknowledgmentsA special thanks to the following individuals: foremost to my loving wife, Tina, for hertolerance and endless support, to Dan Wilson for his contributions, and to ThomasMcCarthy at IUPUI. Also, thank you Jordan for your encouragement over the past fewyears.-- Ryan K. StephensSpecial thanks to my wife for putting up with me through this busiest of times. Iapologize to my mom for not seeing her as often as I should (I'll make it up to you). Also,thanks to my loyal dog, Toby. He was with me every night and wouldn't leave my side.-- Ronald PlewSpecial thanks to the following people: Jeff Perkins, David Blankenbeckler, ShannonLittle, Jr., Clint and Linda Morgan, and Shannon and Kaye Little.This book is dedicated to my beautiful wife, Becky. I am truly appreciative to you foryour support, encouragement, and love. Thanks for staying up with me during all thoselate-night sessions. You are absolutely the best.-- Bryan MorganThanks to my family, Leslie, Laura, Kelly, Valerie, Jeff, Mom, and Dad. Their supportmade working on this book possible.

-- Jeff PerkinsAbout the AuthorsRyan K. StephensRyan K. Stephens started using SQL as a programmer/analyst while serving on activeduty in the Indiana Army National Guard. Hundreds of programs later, Ryan became adatabase administrator. He currently works for Unisys Federal Systems, where he isresponsible for government-owned databases throughout the United States. In additionto his full-time job, Ryan teaches SQL and various database classes at IndianaUniversity-Purdue University Indianapolis. He also serves part-time as a programmer forthe Indiana Army National Guard. Along with Ron Plew and two others, Ryan owns aU.S. patent on a modified chess game. Some of his interests include active sports, chess,nature, and writing. Ryan lives in Indianapolis with his wife, Tina, and their three dogs,Bailey, Onyx, and Sugar.Ronald R. PlewRonald R. Plew is a database administrator for Unisys Federal Systems. He holds abachelor of science degree in business administration/management from the IndianaInstitute of Technology. He is an instructor for Indiana University-Purdue UniversityIndianapolis where he teaches SQL and various database classes. Ron also serves as aprogrammer for the Indiana Army National Guard. His hobbies include collecting Indy500 racing memorabilia. He also owns and operates Plew's Indy 500 Museum. He lives inIndianapolis with his wife, Linda. They have four grown children (Leslie, Nancy, Angela,and Wendy) and eight grandchildren (Andy, Ryan, Holly, Morgan, Schyler, Heather,Gavin, and Regan).Bryan MorganBryan Morgan is a software developer with TASC, Inc., in Fort Walton Beach, Florida.In addition to writing code and chasing the golf balls he hits, Bryan has authoredseveral books for Sams Publishing including Visual J Unleashed, Java Developer'sReference, and Teach Yourself ODBC Programming in 21 Days. He lives in Navarre, Florida,with his wife, Becky, and their daughter, Emma.Jeff PerkinsJeff Perkins is a senior software engineer with TYBRIN Corporation. He has been aprogram manager, team leader, project lead, technical lead, and analyst. A graduate ofthe United States Air Force Academy, he is a veteran with more than 2,500 hours offlying time as a navigator and bombardier in the B-52. He has co-authored three otherbooks, Teach Yourself NT Workstation in 24 Hours, Teach Yourself ODBC Programming in 21 Days,

and Teach Yourself ActiveX in 21 Days.Tell Us What You Think!As a reader, you are the most important critic and commentator of our books. We valueyour opinion and want to know what we're doing right, what we could do better, whatareas you'd like to see us publish in, and any other words of wisdom you're willing topass our way. You can help us make strong books that meet your needs and give you thecomputer guidance you require.Do you have access to CompuServe or the World Wide Web? Then check out ourCompuServe forum by typing GO SAMS at any prompt. If you prefer the World Wide Web,check out our site at http://www.mcp.com.NOTE: If you have a technical question about this book, call the technicalsupport line at 317-581-3833 or send e-mail to support@mcp.com.As the team leader of the group that created this book, I welcome your comments. Youcan fax, e-mail, or write me directly to let me know what you did or didn't like aboutthis book--as well as what we can do to make our books stronger. Here's the information:FAX: 317-581-4669E-mail: enterprise mgr@sams.mcp.comMail: Rosemarie GrahamComments DepartmentSams Publishing201 W. 103rd StreetIndianapolis, IN 46290IntroductionWho Should Read This Book?Late one Friday afternoon your boss comes into your undersized cubicle and drops a newproject on your desk. This project looks just like the others you have been working onexcept it includes ties to several databases. Recently your company decided to moveaway from homegrown, flat-file data and is now using a relational database. You haveseen terms like SQL, tables, records, queries, and RDBMS, but you don't remember

exactly what they all mean. You notice the due date on the program is three, no, makethat two, weeks away. (Apparently it had been on your boss's desk for a week!) As youbegin looking for definitions and sample code to put those definitions into context, youdiscover this book.This book is for people who want to learn the fundamentals of Structured QueryLanguage (SQL)--quickly. Through the use of countless examples, this book depicts allthe major components of SQL as well as options that are available with variousdatabase implementations. You should be able to apply what you learn here torelational databases in a business setting.OverviewThe first 14 days of this book show you how to use SQL to incorporate the power ofmodern relational databases into your code. By the end of Week 1, you will be able touse basic SQL commands to retrieve selected data.NOTE: If you are familiar with the basics and history of SQL, we suggestyou skim the first week's chapters and begin in earnest with Day 8,"Manipulating Data."At the end of Week 2, you will be able to use the more advanced features of SQL, suchas stored procedures and triggers, to make your programs more powerful. Week 3 teachesyou how to streamline SQL code; use the data dictionary; use SQL to generate more SQLcode; work with PL/SQL, Transact-SQL, and SQL*Plus; and handle common SQL mistakesand errors.The syntax of SQL is explained and then brought to life in examples using PersonalOracle7, Microsoft Query, and other database tools. You don't need access to any ofthese products to use this book--it can stand alone as an SQL syntax reference.However, using one of these platforms and walking though the examples will help youunderstand the nuances.Conventions Used in This BookThis book uses the following typeface conventions: Menu names are separated from menu options by a vertical bar ( ). For example,File Open means "select the Open option from the File menu."New terms appear in italic.

All code in the listings that you type in (input) appears in boldface monospace.Output appears in standard monospace. The input label and output label also identify the nature of the code. Many code-related terms within the text also appear in monospace. Paragraphs that begin with the analysis label explain the preceding code sample. The syntax label identifies syntax statements.The following special design features enhance the text:NOTE: Notes explain interesting or important points that can help youunderstand SQL concepts and techniques.TIP: Tips are little pieces of information to begin to help you in real-worldsituations. Tips often offer shortcuts or information to make a task easieror faster.WARNING: Warnings provide information about detrimental performanceissues or dangerous errors. Pay careful attention to Warnings. Copyright, Macmillan Computer Publishing. All rights reserved.

Teach Yourself SQL in 21 Days, SecondEditionWeek 1 At A GlanceLet's Get StartedWeek 1 introduces SQL from a historical and theoretical perspective. The firststatement you learn about is the SELECT statement, which enables you to retrieve datafrom the database based on various user-specified options. Also during Week 1 you studySQL functions, query joins, and SQL subqueries (a query within a query). Many exampleshelp you understand these important topics. These examples use Oracle7, Sybase SQLServer, Microsoft Access, and Microsoft Query and highlight some of the similaritiesand differences among the products. The content of the examples should be useful andinteresting to a broad group of readers. Copyright, Macmillan Computer Publishing. All rights reserved.

Teach Yourself SQL in 21 Days, SecondEdition- Day 1 Introduction to SQLA Brief History of SQLThe history of SQL begins in an IBM laboratory in San Jose, California, where SQL wasdeveloped in the late 1970s. The initials stand for Structured Query Language, and thelanguage itself is often referred to as "sequel." It was originally developed for IBM'sDB2 product (a relational database management system, or RDBMS, that can still bebought today for various platforms and environments). In fact, SQL makes an RDBMSpossible. SQL is a nonprocedural language, in contrast to the procedural or thirdgeneration languages (3GLs) such as COBOL and C that had been created up to thattime.NOTE: Nonprocedural means what rather than how. For example, SQL describeswhat data to retrieve, delete, or insert, rather than how to perform theoperation.The characteristic that differentiates a DBMS from an RDBMS is that the RDBMSprovides a set-oriented database language. For most RDBMSs, this set-oriented databaselanguage is SQL. Set oriented means that SQL processes sets of data in groups.Two standards organizations, the American National Standards Institute (ANSI) andthe International Standards Organization (ISO), currently promote SQL standards toindustry. The ANSI-92 standard is the standard for the SQL used throughout this book.Although these standard-making bodies prepare standards for database system designers