SQL: The Complete Reference PDF

SQL: The Complete Reference by James R. Groff and Paul N. Weinberg ISBN: 0072118458 Osborne/McGraw-Hill © 1999, 994 pages An encyclopedic reference guide to the SQL database language for both technical and non-technical readers. Table of Contents Colleague Comments Back Cover Synopsis by Dean Andrews What is SQL and where did it come from? How do the SQL tools vary across database applications from different vendors? How will SQL change in the future? You'll find the answers to all these questions and many more in SQL: The Complete Reference. Much more than just a listing of SQL commands and their parameters, this encyclopedic reference guide explains the concepts and constructs of SQL programming such that non-technical readers will understand them and technical readers won't be bored. Table of Contents SQL - The Complete Reference - 4 Preface - 6 Part I An Overview of SQL Chapter 1 - Introduction - 8 Chapter 2 - A Quick Tour of SQL - 15 Chapter 3 - SQL In Perspective - 22 Chapter 4 - Relational Databases - 38 Part II Retrieving Data Chapter 5 - SQL Basics - 51 Chapter 6 - Simple Queries - 69 Chapter 7 - Multi-Table Queries (Joins) - 101 Chapter 8 - Summary Queries - 136 Chapter 9 - Subqueries and Query Expressions - 158 Part III Updating Data Chapter 10 - Database Updates - 196 Chapter 11 - Data Integrity - 211 Chapter 12 - Transaction Processing - 236 Part IV Database Structure Chapter 13 - Creating a Database - 256 Chapter 14 - Views - 290 Chapter 15 - SQL Security - 304 -2- Chapter 16 - The System Catalog - 321 Part V Programming with SQL Chapter 17 - Embedded SQL - 344 Chapter 18 - Dynamic SQL* - 387 Chapter 19 - SQL APIs - 430 Part VI SQL Today and Tomorrow Chapter 20 - Database Processing and Stored Procedures - 435 Chapter 21 - SQL and Data Warehousing - 535 Chapter 22 - SQL Networking and Distributed Databases - 546 Chapter 23 - SQL and Objects - 575 Chapter 24 - The Future of SQL - 602 Part VII Appendices Appendix A - The Sample Database - 612 Appendix B - Database Vendor Profiles - 616 Appendix C - Company and Product List - 629 Appendix D - SQL Syntax Reference - 634 Appendix E - SQL Call Level Interface - 635 Appendix F - SQL Information Schema Standard - 651 Appendix G - CD-ROM Installation Guide - 667 Back Cover Gain the working knowledge of SQL and relational databases essential for today's information systems professionals. Relational databases built on the SQL database language are the foundation for modern enterprise data processing and are also a force behind many of today's important technology trends. SQL: The Complete Reference provides an in-depth discussion of SQL fundamentals, modern SQL products, and SQL's role in trends such as data warehousing, "thin-client" architectures, and Internet-based e-commerce. This book is your one-stop resource for all you need to know about SQL. It will help you: Learn the key concepts and latest developments in relational database technology Master the industry-standard SQL language for managing database information Understand the differences among all the leading brands of DBMS systems Set up and manage SQL-based databases and write programs to access them Understand how to use and program SQL databases with application servers and the Web to create e-commerce applications Find out more about the proposed SQL3 standard and the key trends in object technologies, 64-bit architectures, distributed databases, 3- tier Internet applications, and more About the Authors James R. Groff and Paul N. Weinberg were the co-founders of Network Innovations Corporation, an early developer of SQL-based networking -3- software that links personal computers to corporate databases. Groff is currently CEO of TimesTen Performance Software, developer of an ultra-high performance main-memory SQL database for communications and Internet applications. Weinberg is vice president of A2i, Inc., developer of a database- driven, cross-media catalog publishing system that supports printed and electronic output from a single data source. SQL: The Complete Reference James R. Groff Paul N. Weinberg Publisher Brandon A Nordin Associate Publisher and Editor-in-Chief Scott Rogers Senior Acquisitions Editor Wendy Rinaldi Acquisitions Editor Jane K. Brownlow Project Editor Heidi Poulin Editorial Assistant Monica Faltiss Copy Editor Nancy Crumpton Proofreader Rhonda Holmes Indexer Valerie Robbins Computer Designer Jani Beckwith Michelle Galicia Illustrators Robert Hansen Brian Wells Beth Young -4- Osborne/McGraw-Hill 2600 Tenth Street Berkeley, California 94710 U.S.A. For information on translations or book distributors outside the U.S.A., or to arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please contact Osborne/McGraw-Hill at the above address. Copyright © 1999 by The McGraw-Hill Companies. All rights reserved. Printed in the United States of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher, with the exception that the program listings may be entered, stored, and executed in a computer system, but they may not be reproduced for publication. Licensed Materials - Property of IBM IBM® DB2® Universal Database Personal Edition, Version 5.2, for the Windows® Operating Environments© Copyright IBM Corp. 1993, 1998. All Rights Reserved. U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP schedule Contract with IBM Corp. © 1999 Informix Corporation. All rights reserved. Informix® is a trademark of Informix Corporation or its affiliates and is registered in the U.S. and some other jurisdictions. Microsoft® SQL Server ™ 7.0 Evaluation Edition. Copyright Microsoft Corporation, 1997- 98. All rights reserved. Oracle8 Personal Edition© 1996,1998, Oracle Corporation. All rights reserved. Copyright © 1996-1998, Sybase, Inc. All rights reserved. 1234567890 DOC DOC 90198765432109 ISBN 0-07-211845-8 Information has been obtained by Osborne/McGraw-Hill from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, Osborne/McGraw-Hill, or others, Osborne/McGraw-Hill does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained from use of such information. Acknowledgments Special thanks to Matan Arazi for doing such an exceptional job assembling the Bonus CD-ROM. He pulled off a real miracle to squeeze all five SQL, DBMS products onto a single CD, a technical feat that would not have been possible without his diligent tenacity Thanks also to everyone at Osborne for pulling it all together, including Jane Brownlow and Wendy Rinaldi for doing tag-team duty as our acquisitions editors, and to Heidi Poulin for her meticulous attention to detail. -5- Preface Overview SQL: The Complete Reference provides a comprehensive, in-depth treatment of the SQL language for both technical and non-technical users, programmers, data processing professionals, and managers who want to understand the impact of SQL in the computer market. This book offers a conceptual framework for understanding and using SQL, describes the history of SQL and SQL standards, and explains the role of SQL in the computer industry today. It will show you, step-by-step, how to use SQL features, with many illustrations and realistic examples to clarify SQL concepts. The book also compares SQL products from leading DBMS vendors  describing their advantages, benefits, and trade-offs  to help you select the right product for your application. The accompanying CD contains actual trial versions of five leading SQL databases, so you can try them for yourself and gain actual experience in using major database products from Oracle, Microsoft, Sybase, Informix, an IBM. In some of the chapters in this book, the subject matter is explored at two different levels— a fundamental description of the topic, and an advanced discussion intended for computer professionals who need to understand some of the "internals" behind SQL. The more advanced information is covered in sections marked with an asterisk (*). You do not need to read these sections to obtain an understanding of what SQL is and what it does. How this Book Is Organized The book is divided into six parts that cover various aspects of the SQL language: Part One, "An Overview of SQL," provides an introduction to SQL and a market perspective of its role as a database language. Its four chapters describe the history of SQL, the evolution of SQL standards, and how SQL relates to the relational data model and to earlier database technologies. Part One also contains a quick tour of SQL that briefly illustrates its most important features and provides you with an overview of the entire language early in the book. Part Two, "Retrieving Data," describes the features of SQL that allow you to perform database queries. The first chapter in this part describes the basic structure of the SQL language. The next four chapters start with the simplest SQL queries, and progressively build to more complex queries, including multi-table queries, summary queries, and queries that use subqueries. Part Three, "Updating Data," shows how you can use SQL to add new data to a database, delete data from a database, and modify existing database data. It also describes the database integrity issues that arise when data is updated, and how SQL addresses these issues. The last of the three chapters in this part discusses the SQL transaction concept and SQL support for multi-user transaction processing. Part Four, "Database Structure," deals with creating and administering a SQL-based database. Its four chapters tell you how to create the tables, views, and indexes that form the structure of a relational database. It also describes the SQL security scheme that prevents unauthorized access to data, and the SQL system catalog that describes the structure of a database. This part also discusses the significant differences between the database structures supported by various SQL-based DBMS products. Part Five, "Programming with SQL," describes how application programs use SQL for database access. It discusses the embedded SQL specified by the ANSI standard and used by IBM, Oracle, Ingres, Informix, and most other SQL-based DBMS products. It also describes the dynamic SQL interface that is used to build general-purpose database tools, such as report writers and database browsing programs. Finally, this -6- part describes the popular SQL APIs, including ODBC, the ISO-standard Call-Level Interface, and Oracle Call Interface, and contrasts them with the embedded SQL interface. Part Six, "SQL Today and Tomorrow," examines the state of SQL-based DBMS products today, major database trends, the "hot" new applications, and the directions that SQL will take over the next few years. It describes the intense current activity in SQL networking and distributed databases, and the evolution of special features to support SQL-based OLTP, and SQL-based data warehousing. This part also discusses the impact of object technology on SQL and relational databases, and the emergence of hybrid, object-relational database models. Conventions Used in this Book SQL: The Complete Reference describes the SQL features and functions that are available in the most popular SQL-based DBMS products and those that are described in the ANSI/ISO SQL standards. Whenever possible, the SQL statement syntax described in the book and used in the examples applies to all dialects of SQL. When the dialects differ, the differences are pointed out in the text, and the examples follow the most common practice. In these cases, you may have to modify the SQL statements in the examples slightly to suit your particular brand of DBMS. Throughout the book, technical terms appear in italics the first time that they are used and defined. SQL language elements, including SQL keywords, table and column names, and sample SQL statements appear in an uppercase monospace font. SQL API function names appear in a lowercase monospace font. Program listings also appear in monospace font, and use the normal case conventions for the particular programming language (uppercase for COBOL and FORTRAN, lowercase for C). Note that these conventions are used solely to improve readability; most SQL implementations will accept either uppercase or lowercase statements. Many of the SQL examples include query results, which appear immediately following the SQL statement as they would in an interactive SQL session. In some cases, long query results are truncated after a few rows; this is indicated by a vertical ellipsis (...) following the last row of query results. Why this Book Is for You SQL: The Complete Reference is the right book for anyone who wants to understand and learn SQL, including database users, data processing professionals, programmers, students, and managers. It describes—in simple, understandable language liberally illustrated with figures and examples—what SQL is, why it is important, and how you use it. This book is not specific to one particular brand or dialect of SQL. Rather, it describes the standard, central core of the SQL language and then goes on to describe the differences among the most popular SQL products, including Oracle, Microsoft SQL Server, IBM's DB2, Informix Universal Server, Sybase Adaptive Server, and others. It also explains the importance of SQL-based standards, such as ODBC and the ANSI/ISO SQL2 and evolving SQL3 standards. If you are a new user of SQL, this book offers comprehensive, step-by-step treatment of the language, building from simple queries to more advanced concepts. The structure of the book will allow you to quickly start using SQL, but the book will continue to be valuable as you begin to use more complex features of the language. You can use the SQL software on the companion CD to try out the examples and build your SQL skills. If you are a data processing professional or a manager, this book will give you a perspective on the impact that SQL is having in every segment of the computer market— from personal computers, to mainframes, to online transaction processing systems and data warehousing applications. The early chapters describe the history of SQL, its role in the market, and its evolution from earlier database technologies. The final chapters describe the future of SQL and the development of new database technologies such as distributed databases, business intelligence databases, and object-relational database capabilities. -7- If you are a programmer, this book offers a very complete treatment of programming with SQL. Unlike the reference manuals of many DBMS products, it offers a conceptual framework for SQL programming, explaining the why as well as the how of developing a SQL-based application. It contrasts the SQL programming interfaces offered by all of the leading SQL products, including embedded SQL, dynamic SQL, ODBC and proprietary APIs such as the Oracle Call Interface, providing a perspective not found in any other book. If you are selecting a DBMS product, this book offers a comparison of the SQL features, advantages, and benefits offered by the various DBMS vendors. The differences between the leading DBMS products are explained, not only in technical terms, but also in terms of their impact on applications and their competitive position in the marketplace. The DBMS software on the companion CD can be used to try out these features in a prototype of your own application. In short, both technical and non-technical users can benefit from this book. It is the most comprehensive source of information available about the SQL language, SQL features and benefits, popular SQL-based products, the history of SQL, and the impact of SQL on the future direction of the computer market. Part I: An Overview of SQL Chapter List Chapter Introduction 1: Chapter A Quick Tour of SQL 2: Chapter SQL In Perspective 3: Chapter Relational Databases 4: Chapter 1: Introduction Overview The SQL language and relational database systems based on it are one of the most important foundation technologies in the computer industry today. Over the last decade, the popularity of SQL has exploded, and it stands today as the standard computer database language. Literally hundreds of database products now support SQL, running on computer systems from mainframes to personal computers and even handheld devices. An official international SQL standard has been adopted and expanded twice. Virtually every major enterprise software product relies on SQL for its data management, and SQL is at the core of the database products from Microsoft and Oracle, two of the largest software companies in the world. From its obscure beginnings as an IBM research project, SQL has leaped to prominence as both an important computer technology and a powerful market force. What, exactly, is SQL? Why is it important? What can it do, and how does it work? If SQL is really a standard, why are there so many different versions and dialects? How do popular SQL products like SQL Server, Oracle, Informix, Sybase, and DB2 compare? How -8- does SQL relate to Microsoft standards, such as ODBC and COM? How does JDBC link SQL to the world of Java and object technology? Does SQL really scale from mainframes to handheld devices? Has it really delivered the performance needed for high-volume transaction processing? How will SQL impact the way you use computers, and how can you get the most out of this important data management tool? The SQL Language SQL is a tool for organizing, managing, and retrieving data stored by a computer database. The name "SQL" is an abbreviation for Structured Query Language. For historical reasons, SQL is usually pronounced "sequel," but the alternate pronunciation "S.Q.L." is also used. As the name implies, SQL is a computer language that you use to interact with a database. In fact, SQL works with one specific type of database, called a relational database. Figure 1-1 shows how SQL works. The computer system in the figure has a database that stores important information. If the computer system is in a business, the database might store inventory, production, sales, or payroll data. On a personal computer, the database might store data about the checks you have written, lists of people and their phone numbers, or data extracted from a larger computer system. The computer program that controls the database is called a database management system, or DBMS. Figure 1-1: Using SQL for database access When you need to retrieve data from a database, you use the SQL language to make the request. The DBMS processes the SQL request, retrieves the requested data, and returns it to you. This process of requesting data from a database and receiving back the results is called a database query—hence the name Structured Query Language. The name Structured Query Language is actually somewhat of a misnomer. First of all, SQL is far more than a query tool, although that was its original purpose and retrieving data is still one of its most important functions. SQL is used to control all of the functions that a DBMS provides for its users, including: Data definition. SQL lets a user define the structure and organization of the stored data and relationships among the stored data items. Data retrieval. SQL allows a user or an application program to retrieve stored data from the database and use it. Data manipulation. SQL allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data. Access control. SQL can be used to restrict a user's ability to retrieve, add, and modify data, protecting stored data against unauthorized access. Data sharing. SQL is used to coordinate data sharing by concurrent users, ensuring that they do not interfere with one another. -9- Data integrity. SQL defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures. SQL is thus a comprehensive language for controlling and interacting with a database management system. Second, SQL is not really a complete computer language like COBOL, C, C++, or Java. SQL contains no IF statement for testing conditions, and no GOTO, DO, or FOR statements for program flow control. Instead, SQL is a database sublanguage, consisting of about forty statements specialized for database management tasks. These SQL statements can be embedded into another language, such as COBOL or C, to extend that language for use in database access. Alternatively, they can be explicitly sent to a database management system for processing, via a call level interface from a language such as C, C++, or Java. Finally, SQL is not a particularly structured language, especially when compared to highly structured languages such as C, Pascal, or Java. Instead, SQL statements resemble English sentences, complete with "noise words" that don't add to the meaning of the statement but make it read more naturally. There are quite a few inconsistencies in the SQL language, and there are also some special rules to prevent you from constructing SQL statements that look perfectly legal, but don't make sense. Despite the inaccuracy of its name, SQL has emerged as the standard language for using relational databases. SQL is both a powerful language and one that is relatively easy to learn. The quick tour of SQL in the next chapter will give you a good overview of the language and its capabilities. The Role of SQL SQL is not itself a database management system, nor is it a stand-alone product. You cannot go into a computer store and "buy SQL." Instead, SQL is an integral part of a database management system, a language and a tool for communicating with the DBMS. Figure 1-2 shows some of the components of a typical DBMS, and how SQL acts as the "glue" that links them together. Figure 1-2: Components of a typical database management system The database engine is the heart of the DBMS, responsible for actually structuring, storing, and retrieving the data in the database. It accepts SQL requests from other DBMS components, such as a forms facility, report writer, or interactive query facility, from user-written application programs, and even from other computer systems. As the - 10 - figure shows, SQL plays many different roles: SQL is an interactive query language. Users type SQL commands into an interactive SQL program to retrieve data and display it on the screen, providing a convenient, easy-to-use tool for ad hoc database queries. SQL is a database programming language. Programmers embed SQL commands into their application programs to access the data in a database. Both user-written programs and database utility programs (such as report writers and data entry tools) use this technique for database access. SQL is a database administration language. The database administrator responsible for managing a minicomputer or mainframe database uses SQL to define the database structure and control access to the stored data. SQL is a client/server language. Personal computer programs use SQL to communicate over a network with database servers that store shared data. This client/server architecture has become very popular for enterprise-class applications. SQL is an Internet data access language. Internet web servers that interact with corporate data and Internet applications servers all use SQL as a standard language for accessing corporate databases. SQL is a distributed database language. Distributed database management systems use SQL to help distribute data across many connected computer systems. The DBMS software on each system uses SQL to communicate with the other systems, sending requests for data access. SQL is a database gateway language. In a computer network with a mix of different DBMS products, SQL is often used in a gateway that allows one brand of DBMS to communicate with another brand. SQL has thus emerged as a useful, powerful tool for linking people, computer programs, and computer systems to the data stored in a relational database. SQL Features and Benefits SQL is both an easy-to-understand language and a comprehensive tool for managing data. Here are some of the major features of SQL and the market forces that have made it successful: Vendor independence Portability across computer systems SQL standards IBM endorsement (DB2) Microsoft commitment (ODBC and ADO) Relational foundation High-level, English-like structure Interactive, ad hoc queries - 11 - Programmatic database access Multiple views of data Complete database language Dynamic data definition Client/server architecture Extensibility and object technology Internet database access Java integration (JDBC) These are the reasons why SQL has emerged as the standard tool for managing data on personal computers, minicomputers, and mainframes. They are described in the sections that follow. Vendor Independence SQL is offered by all of the leading DBMS vendors, and no new database product over the last decade has been highly successful without SQL support. A SQL-based database and the programs that use it can be moved from one DBMS to another vendor's DBMS with minimal conversion effort and little retraining of personnel. PC database tools, such as query tools, report writers, and application generators, work with many different brands of SQL databases. The vendor independence thus provided by SQL was one of the most important reasons for its early popularity and remains an important feature today. Portability Across Computer Systems SQL-based database products run on computer systems ranging from mainframes and midrange systems to personal computers, workstations, and even handheld devices. They operate on stand-alone computer systems, in departmental local area networks, and in enterprise-wide or Internet-wide networks. SQL-based applications that begin on single-user systems can be moved to larger server systems as they grow. Data from corporate SQL-based databases can be extracted and downloaded into departmental or personal databases. Finally, economical personal computers can be used to prototype a SQL-based database application before moving it to an expensive multi-user system. SQL Standards An official standard for SQL was initially published by the American National Standards Institute (ANSI) and the International Standards Organization (ISO) in 1986, and was expanded in 1989 and again in 1992. SQL is also a U.S. Federal Information Processing Standard (FIPS), making it a key requirement for large government computer contracts. Over the years, other international, government, and vendor groups have pioneered the standardization of new SQL capabilities, such as call-level interfaces or object-based extensions. Many of these new initiatives have been incorporated into the ANSI/ISO standard over time. The evolving standards serve as an official stamp of approval for SQL and have speeded its market acceptance. IBM Endorsement (DB2) - 12 - SQL was originally invented by IBM researchers and has since become a strategic product for IBM based on its flagship DB2 database. SQL support is available on all major IBM product families, from personal computers through midrange systems (AS/400 and RS/6000) to IBM mainframes running both the MVS and VM operating systems. IBM's initial work provided a clear signal of IBM's direction for other database and system vendors to follow early in the development of SQL and relational databases. Later, IBM's commitment and broad support speeded the market acceptance of SQL. Microsoft Commitment (ODBC and ADO) Microsoft has long considered database access a key part of its Windows personal computer software architecture. Both desktop and server versions of Windows provide standardized relational database access through Open Database Connectivity (ODBC), a SQL-based call-level API. Leading Windows software applications (spreadsheets, word processors, databases, etc.) from Microsoft and other vendors support ODBC, and all leading SQL databases provide ODBC access. Microsoft has enhanced ODBC support with higher-level, more object-oriented database access layers as part of its Object Linking and Embedding technology (OLE DB), and more recently as part of Active/X (Active/X Data Objects, or ADO). Relational Foundation SQL is a language for relational databases, and it has become popular along with the relational database model. The tabular, row/column structure of a relational database is intuitive to users, keeping the SQL language simple and easy to understand. The relational model also has a strong theoretical foundation that has guided the evolution and implementation of relational databases. Riding a wave of acceptance brought about by the success of the relational model, SQL has become the database language for relational databases. High-Level, English-Like Structure SQL statements look like simple English sentences, making SQL easy to learn and understand. This is in part because SQL statements describe the data to be retrieved, rather than specifying how to find the data. Tables and columns in a SQL database can have long, descriptive names. As a result, most SQL statements "say what they mean" and can be read as clear, natural sentences. Interactive, Ad Hoc Queries SQL is an interactive query language that gives users ad hoc access to stored data. Using SQL interactively, a user can get answers even to complex questions in minutes or seconds, in sharp contrast to the days or weeks it would take for a programmer to write a custom report program. Because of SQL's ad hoc query power, data is more accessible and can be used to help an organization make better, more informed decisions. SQL's ad hoc query capability was an important advantage over nonrelational databases early in its evolution and more recently has continued as a key advantage over pure object-based databases. Programmatic Database Access SQL is also a database language used by programmers to write applications that access a database. The same SQL statements are used for both interactive and programmatic access, so the database access parts of a program can be tested first with interactive SQL and then embedded into the program. In contrast, traditional databases provided one set of tools for programmatic access and a separate query facility for ad hoc requests, without any synergy between the two modes of access. Multiple Views of Data - 13 - Using SQL, the creator of a database can give different users of the database different views of its structure and contents. For example, the database can be constructed so that each user sees data for only their department or sales region. In addition, data from several different parts of the database can be combined and presented to the user as a simple row/column table. SQL views can thus be used to enhance the security of a database and tailor it to the particular needs of individual users. Complete Database Language SQL was first developed as an ad hoc query language, but its powers now go far beyond data retrieval. SQL provides a complete, consistent language for creating a database, managing its security, updating its contents, retrieving data, and sharing data among many concurrent users. SQL concepts that are learned in one part of the language can be applied to other SQL commands, making users more productive. Dynamic Data Definition Using SQL, the structure of a database can be changed and expanded dynamically, even while users are accessing database contents. This is a major advance over static data definition languages, which prevented access to the database while its structure was being changed. SQL thus provides maximum flexibility, allowing a database to adapt to changing requirements while on-line applications continue uninterrupted. Client/Server Architecture SQL is a natural vehicle for implementing applications using a distributed, client/server architecture. In this role, SQL serves as the link between "front-end" computer systems optimized for user interaction and "back-end" systems specialized for database management, allowing each system to do what it does best. SQL also allows personal computers to function as front-ends to network servers or to larger minicomputer and mainframe databases, providing access to corporate data from personal computer applications. Extensibility and Object Technology The major challenge to SQL's continued dominance as a database standard has come from the emergence of object-based programming, and the introduction of object-based databases as an extension of the broad market trend toward object-based technology. SQL-based database vendors have responded to this challenge by slowly expanding and enhancing SQL to include object features. These "object/relational" databases, which continue to be based on SQL, have emerged as a more popular alternative to "pure object" databases and may insure SQL's continuing dominance for the next decade. Internet Database Access With the exploding popularity of the Internet and the World Wide Web, and their standards-based foundation, SQL found a new role in the late 1990s as an Internet data access standard. Early in the development of the Web, developers needed a way to retrieve and present database information on web pages and used SQL as a common language for database gateways. More recently, the emergence of three-tiered Internet architectures with distinct thin client, application server and database server layers, have established SQL as the standard link between the application and database tiers. Java Integration (JDBC) One of the major new areas of SQL development is the integration of SQL with Java. Seeing the need to link the Java language to existing relational databases, Sun - 14 - Microsystems (the creator of Java) introduced Java Data Base Connectivity (JDBC), a standard API that allows Java programs to use SQL for database access. Many of the leading database vendors have also announced or implemented Java support within their database systems, allowing Java to be used as a language for stored procedures and business logic within the database itself. This trend toward integration between Java and SQL will insure the continued importance of SQL in the new era of Java-based programming. Chapter 2: A Quick Tour of SQL Overview Before diving into the details of SQL, it's a good idea to develop an overall perspective on the language and how it works. This chapter contains a quick tour of SQL that illustrates its major features and functions. The goal of the quick tour is not to make you proficient in writing SQL statements; that is the goal of Part II of this book. Rather, by the time you've finished this chapter, you will have a basic familiarity with the SQL language and an overview of its capabilities. A Simple Database The examples in the quick tour are based on a simple relational database for a small distribution company. The database, shown in Figure 2-1, stores the information needed to implement a small order processing application. Specifically, it stores the following information: Figure 2-1: A simple relational database the customers who buy the company's products, - 15 - the orders placed by those customers, the salespeople who sell the products to customers, and the sales offices where those salespeople work. This database, like most others, is a model of the "real world." The data stored in the database represents real entities—customers, orders, salespeople, and offices. There is a separate table of data for each different kind of entity. Database requests that you make using the SQL language parallel real-world activities, as customers place, cancel, and change orders, as you hire and fire salespeople, and so on. Let's see how you can use SQL to manipulate data. Retrieving Data First, let's list the sales offices, showing the city where each one is located and its year- to-date sales. The SQL statement that retrieves data from the database is called SELECT. This SQL statement retrieves the data you want: SELECT CITY, OFFICE, SALES FROM OFFICES CITY OFFICE SALES ------------ ------ ----------- Denver 22 $186,042.00 New York 11 $692,637.00 Chicago 12 $735,042.00 Atlanta 13 $367,911.00 Los Angeles 21 $835,915.00 The SELECT statement asks for three pieces of data—the city, the office number, and the sales—for each office. It also specifies that the data comes from the OFFICES table, which stores data about sales offices. The results of the query appear, in tabular form, immediately after the request. The SELECT statement is used for all SQL queries. For example, here is a query that lists the names and year-to-date sales for each salesperson in the database. It also shows the quota (sales target) and the office number where each person works. In this case, the data comes from SALESREPS table: SELECT NAME, REP_OFFICE, SALES, QUOTA FROM SALESREPS NAME REP_OFFICE SALES QUOTA -------------- ---------- ----------- ----------- Bill Adams 13 $367,911.00 $350,000.00 Mary Jones 11 $392,725.00 $300,000.00 Sue Smith 21 $474,050.00 $350,000.00 Sam Clark 11 $299,912.00 $275,000.00 Bob Smith 12 $142,594.00 $200,000.00 Dan Roberts 12 $305,673.00 $300,000.00 Tom Snyder NULL $75,985.00 NULL Larry Fitch 21 $361,865.00 $350,000.00 Paul Cruz 12 $286,775.00 $275,000.00 - 16 - Nancy Angelli 22 $186,042.00 $300,000.00 SQL also lets you ask for calculated results. For example, you can ask SQL to calculate the amount by which each salesperson is over or under quota: SELECT NAME, SALES, QUOTA, (SALES - QUOTA) FROM SALESREPS NAME SALES QUOTA (SALES-QUOTA) -------------- ----------- ----------- -------------- Bill Adams $367,911.00 $350,000.00 $17,911.00 Mary Jones $392,725.00 $300,000.00 $92,725.00 Sue Smith $474,050.00 $350,000.00 $124,050.00 Sam Clark $299,912.00 $275,000.00 $24,912.00 Bob Smith $142,594.00 $200,000.00 -$57,406.00 Dan Roberts $305,673.00 $300,000.00 $5,673.00 Tom Snyder $75,985.00 NULL NULL Larry Fitch $361,865.00 $350,000.00 $11,865.00 Paul Cruz $286,775.00 $275,000.00 $11,775.00 Nancy Angelli $186,042.00 $300,000.00 -$113,958.00 The requested data (including the calculated difference between sales and quota for each salesperson) once again appears in a row/column table. Perhaps you would like to focus on the salespeople whose sales are less than their quotas. SQL lets you retrieve that kind of selective information very easily, by adding a mathematical comparison to the previous request: SELECT NAME, SALES, QUOTA, (SALES - QUOTA) FROM SALESREPS WHERE SALES < QUOTA NAME SALES QUOTA (SALES-QUOTA) -------------- ----------- ----------- -------------- Bob Smith $142,594.00 $200,000.00 -$57,406.00 Nancy Angelli $186,042.00 $300,000.00 -$113,958.00 The same technique can be used to list large orders in the database and find out which customer placed the order, what product was ordered, and in what quantity. You can also ask SQL to sort the orders based on the order amount: SELECT ORDER_NUM, CUST, PRODUCT, QTY, AMOUNT FROM ORDERS WHERE AMOUNT > 25000.00 ORDER BY AMOUNT ORDER_NUM CUST PRODUCT QTY AMOUNT ---------- ------------ ---- ---------- 112987 2103 4100Y 11 $27,500.00 113069 2109 775C 22 $31,350.00 112961 2117 2A44L 7 $31,500.00 113045 2112 2A44R 10 $45,000.00 Summarizing Data - 17 - SQL not only retrieves data from the database, it can be used to summarize the database contents as well. What's the average size of an order in the database? This request asks SQL to look at all the orders and find the average amount: SELECT AVG(AMOUNT) FROM ORDERS AVG(AMOUNT) ------------ $8,256.37 You could also ask for the average amount of all the orders placed by a particular customer: SELECT AVG(AMOUNT) FROM ORDERS WHERE CUST = 2103 AVG(AMOUNT) ----------- $8,895.50 Finally, let's find out the total amount of the orders placed by each customer. To do this, you can ask SQL to group the orders together by customer number and then total the orders for each customer: SELECT CUST, SUM(AMOUNT) FROM ORDERS GROUP BY CUST CUST SUM(AMOUNT) ----- ------------ 2101 $1,458.00 2102 $3,978.00 2103 $35,582.00 2106 $4,026.00 2107 $23,132.00 2108 $7,255.00 2109 $31,350.00 2111 $6,445.00 2112 $47,925.00 2113 $22,500.00 2114 $22,100.00 2117 $31,500.00 2118 $3,608.00 2120 $3,750.00 2124 $3,082.00 Adding Data to the Database SQL is also used to add new data to the database. For example, suppose you just opened a new Western region sales office in Dallas, with target sales of $275,000. Here's the INSERT statement that adds the new office to the database, as office number 23: - 18 - INSERT INTO OFFICES (CITY, REGION, TARGET, SALES, OFFICE) VALUES ('Dallas', 'Western', 275000.00, 0.00, 23) 1 row inserted. Similarly, if Mary Jones (employee number 109) signs up a new customer, Acme Industries, this INSERT statement adds the customer to the database as customer number 2125 with a $25,000 credit limit: INSERT INTO CUSTOMERS (COMPANY, CUST_REP, CUST_NUM, CREDIT_LIMIT) VALUES ('Acme Industries', 109, 2125, 25000.00) 1 row inserted. Deleting Data Just as the SQL INSERT statement adds new data to the database, the SQL DELETE statement removes data from the database. If Acme Industries decides a few days later to switch to a competitor, you can delete them from the database with this statement: DELETE FROM CUSTOMERS WHERE COMPANY = 'Acme Industries' 1 row deleted. And if you decide to terminate all salespeople whose sales are less than their quotas, you can remove them from the database with this DELETE statement: DELETE FROM SALESREPS WHERE SALES < QT= DATEADD(DAY, 15, '05/30/1989') which, of course, is considerably different from the DB2 syntax. Oracle also supports date/time data, with a single data type called DATE. Like SQL Server's DATETIME type, an Oracle DATE is, in fact, a timestamp. Also like SQL Server, the time part of an Oracle DATE value defaults to midnight if no time is explicitly specified. The default Oracle date format is different from the DB2 and SQL Server formats, so the Oracle version of the query becomes: SELECT NAME, HIRE_DATE - 61 - FROM SALESREPS WHERE HIRE_DATE >= '14-JUN-89' Oracle also supports limited date arithmetic, so the DB2-style query can also be specified but without the DAYS keyword: SELECT NAME, HIRE_DATE FROM SALESREPS WHERE HIRE_DATE >= '30-MAY-89' + 15 Finally, the ANSI/ISO SQL2 standard added support for date/time data with a set of data types that are based on, but not identical to, the DB2 types. In addition to the DATE, TIME, and TIMESTAMP data types, the standard specifies an INTERVAL data type, which can be used to store a time interval (for example, a timespan measured in days, or a duration measured in hours, minutes, and seconds). The standard also provides a very elaborate and complex method for dealing with date/time arithmetic, specifying the precision of intervals, adjusting for time zone differences, and so on. As these examples illustrate, the subtle differences in data types among various SQL products lead to some significant differences in SQL statement syntax. They can even cause the same SQL query to produce slightly different results on different database management systems. The widely praised portability of SQL is thus true but only at a general level. An application can be moved from one SQL database to another, and it can be highly portable if it uses only the most mainstream, basic SQL capabilities. However, the subtle variations in SQL implementations mean that data types and SQL statements must almost always be adjusted somewhat if it is to be moved across DBMS brands. The more complex the application, the more likely it is to become dependent on DBMS-specific features and nuances, and the less portable it will become. Constants In some SQL statements a numeric, character, or date data value must be expressed in text form. For example, in this INSERT statement, which adds a salesperson to the database: INSERT INTO SALESREPS (EMPL_NUM, NAME, QUOTA, HIRE_DATE, SALES) VALUES (115, 'Dennis Irving', 175000.00, '21-JUN-90', 0.00) the value for each column in the newly inserted row is specified in the VALUES clause. Constant data values are also used in expressions, such as in this SELECT statement: SELECT CITY FROM OFFICES WHERE TARGET > (1.1 * SALES) + 10000.00 The ANSI/ISO SQL standard specifies the format of numeric and string constants, or literals, which represent specific data values. These conventions are followed by most SQL implementations. Numeric Constants Integer and decimal constants (also called exact numeric literals) are written as ordinary decimal numbers in SQL statements, with an optional leading plus or minus sign. 21 -375 2000.00 +497500.8778 - 62 - You must not put a comma between the digits of a numeric constant, and not all SQL dialects allow the leading plus sign, so it's best to avoid it. For money data, most SQL implementations simply use integer or decimal constants, although some allow the constant to be specified with a currency symbol: $0.75 $5000.00 $-567.89 Floating point constants (also called approximate numeric literals) are specified using the E notation commonly found in programming languages such as C and FORTRAN. Here are some valid SQL floating point constants: 1.5E3 -3.14159E1 2.5E-7 0.783926E21 The E is read "times ten to the power of," so the first constant becomes "1.5 times ten to the third power," or 1500. String Constants The ANSI/ISO standard specifies that SQL constants for character data be enclosed in single quotes ('...'), as in these examples: 'Jones, John J.' 'New York' 'Western' If a single quote is to be included in the constant text, it is written within the constant as two consecutive single quote characters. Thus this constant value: 'I can''t' becomes the seven-character string "I can't". Some SQL implementations, such as SQL Server and Informix, accept string constants enclosed in double quotes ("..."): "Jones, John J." "New York" "Western" Unfortunately, the double quotes pose portability problems with other SQL products, including some unique portability problems with SQL/DS. SQL/DS allows column names containing blanks and other special characters (in violation of the ANSI/ISO standard). When these characters appear as names in a SQL statement, they must be enclosed in double quotes. For example, if the NAME column of the SALESREPS table were called "FULL NAME" in a SQL/DS database, this SELECT statement would be valid: SELECT "FULL NAME", SALES, QUOTA FROM SALESREPS WHERE "FULL NAME" = 'Jones, John J.' The SQL2 standard provides the additional capability to specify string constants from a specific national character set (for example, French or German) or from a user-defined character set. These capabilities have not yet found their way into mainstream SQL implementations. Date and Time Constants In SQL products that support date/time data, constant values for dates, times, and time intervals are specified as string constants. The format of these constants varies from one DBMS to the next. Even more variation is introduced by the differences in the way dates and times are written in different countries. - 63 - DB2 supports several different international formats for date, time, and timestamp constants, as shown in Table 5-5. The choice of format is made when the DBMS is installed. DB2 also supports durations specified as "special" constants, as in this example: HIRE_DATE + 30 DAYS Note that a duration can't be stored in the database, however, because DB2 doesn't have an explicit DURATION data type. SQL Server also supports date/time data and accepts a variety of different formats for date and time constants. The DBMS automatically accepts all of the alternate formats, and you can intermix them if you like. Here are some examples of legal SQL Server date constants: March 15, 1990 Mar 15 1990 3/15/1990 3-15-90 1990 MAR 15 and here are some legal time constants: 15:30:25 3:30:25 PM 3:30:25 pm 3 PM Oracle dates and times are also written as string constants, using this format: 15-MAR-90 You can also use Oracle's built-in TO_DATE() function to convert date constants written in other formats, as in this example: SELECT NAME, AGE FROM SALESREPS WHERE HIRE_DATE = TO_DATE('JUN 14 1989', 'MON DD YYYY') The SQL2 standard specifies a format for date and time constants, based on the ISO format in Table 5-5, except that time constants are written with colons instead of periods separating the hours, minutes, and seconds. Table 5-5: IBM SQL Date and Time Formats Format DATE Format DATE TIME TIME Name Example Format Example American mm/dd/yyyy 5/19/1960 hh:mm 2:18 PM am/pm European dd.mm.yyyy 19.5.1960 hh.mm.ss 14.18.08 Japanese yyyy-mm-dd 1960-5-19 hh:mm:ss 14:18:08 ISO yyyy-mm-dd 1960-5-19 hh.mm.ss 14.18.08 - 64 - TIMESTAMP yyyy-mm-dd- format hh.mm.ss.nnnnnn TIMESTAMP 1960-05-19- example 14.18.08.048632 Symbolic Constants In addition to user-supplied constants, the SQL language includes special symbolic constants that return data values maintained by the DBMS itself. For example, in some DBMS brands the symbolic constant CURRENT_DATE yields the value of the current date and can be used in queries such as the following, which lists the salespeople whose hire date is still in the future. SELECT NAME, HIRE_DATE FROM SALESREPS WHERE HIRE_DATE > CURRENT_DATE The SQL1 standard specified only a single symbolic constant (the USER constant described in Chapter 15), but most SQL products provide many more. Generally, a symbolic constant can appear in a SQL statement anywhere that an ordinary constant of the same data type could appear. The SQL2 standard adopted the most useful symbolic constants from current SQL implementations and provides for CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP (note the underscores!) as well as USER, SESSION_USER, and SYSTEM_USER. Some SQL products, including SQL Server, provide access to system values through built-in functions rather than symbolic constants. The SQL Server version of the preceding query is: SELECT NAME, HIRE_DATE FROM SALESREPS WHERE HIRE_DATE > GETDATE() Built-in functions are described later in this chapter. Expressions Expressions are used in the SQL language to calculate values that are retrieved from a database and to calculate values used in searching the database. For example, this query calculates the sales of each office as a percentage of its target: SELECT CITY, TARGET, SALES, (SALES/TARGET) * 100 FROM OFFICES and this query lists the offices whose sales are more than $50,000 over target: SELECT CITY FROM OFFICES WHERE SALES > TARGET + 50000.00 The ANSI/ISO SQL standard specifies four arithmetic operations that can be used in expressions: addition (X + Y), subtraction (X – Y), multiplication (X * Y), and division (X / Y). Parentheses can also be used to form more complicated expressions, like this one: - 65 - (SALES * 1.05) - (TARGET *.95) Strictly speaking, the parentheses are not required in this query because the ANSI/ISO standard specifies that multiplication and division have a higher precedence than addition and subtraction. However, you should always use parentheses to make your expressions unambiguous because different SQL dialects may use different rules. The parentheses also increase the readability of the statement and make programmatic SQL statements easier to maintain. The ANSI/ISO standard also specifies automatic data type conversion from integers to decimal numbers, and from decimal numbers to floating point numbers, as required. You can thus mix these data types in a numeric expression. Many SQL implementations support other operators and allow operations on character and date data. DB2, for example, supports a string concatenation operator, written as two consecutive vertical bar characters (||). If two columns named FIRST_NAME and LAST_NAME contain the values "Jim" and "Jackson," then this DB2 expression: ('Mr./Mrs. ' || FIRST_NAME || ' ' || LAST_NAME) produces the string "Mr./Mrs. Jim Jackson." As already mentioned, DB2 also supports addition and subtraction of DATE, TIME, and TIMESTAMP data, for occasions when those operations make sense. This capability has been included in the SQL2 standard. Built-in Functions Although the SQL1 standard doesn't specify them, most SQL implementations include a number of useful built-in functions. These facilities often provide data type conversion facilities. For example, DB2's built-in MONTH() and YEAR() functions take a DATE or TIMESTAMP value as their input and return an integer that is the month or year portion of the value. This query lists the name and month of hire for each salesperson in the sample database: SELECT NAME, MONTH(HIRE_DATE) FROM SALESREPS and this one lists all salespeople hired in 1988: SELECT NAME, MONTH(HIRE_DATE) FROM SALESREPS WHERE YEAR(HIRE_DATE) = 1988 Built-in functions also are often used for data reformatting. Oracle's built-in TO_CHAR() function, for example, takes a DATE data type and a format specification as its arguments and returns a string containing a formatted version of the date. In the results produced by this query: SELECT NAME, TO_CHAR(HIRE_DATE,'DAY MONTH DD, YYYY') FROM SALESREPS the hire dates will all have the format "Wednesday June 14, 1989" because of the built-in function. In general, a built-in function can be specified in a SQL expression anywhere that a constant of the same data type can be specified. The built-in functions supported by popular SQL dialects are too numerous to list here. The IBM SQL dialects include about two dozen built-in functions, Oracle supports a different set of about two dozen built-in - 66 - functions, and SQL Server has several dozen. The SQL2 standard incorporated the most useful built-in functions from these implementations, in many cases with slightly different syntax. These functions are summarized in Table 5-6. Table 5-6: Built-in SQL2 Functions Function Returns BIT_LENGTH(string) Number of bits in a bit string CAST(value AS data_type) The value, converted to the specified data type (e.g., a date converted to a character string) CHAR_LENGTH(string) Length of a character string CONVERT(string USING conv) String converted as specified by a named conversion function CURRENT_DATE Current date CURRENT_TIME(precision) Current time, with the specified precision CURRENT_TIMESTAMP(precision) Current date and time, with the specified precision EXTRACT(part FROM source) Specified part (DAY, HOUR, etc.) from a DATETIME value LOWER(string) String converted to all lowercase letters OCTET_LENGTH(string) Number of 8-bit bytes in a character string POSITION(target IN source) Position where the target string appears within the source string SUBSTRING(source FROM A portion of the source string, beginning at the n-th nFOR len) character, for a length of len TRANSLATE(string USING String translated as specified by a named translation trans) function TRIM(BOTH char FROM string) String with both leading and trailing occurrences of char trimmed off TRIM(LEADING char FROM String with any leading occurrences of char trimmed string) off TRIM(TRAILING char FROM String with any trailing occurrences of char trimmed string) off UPPER(string) String converted to all uppercase letters - 67 - Missing Data (NULL Values) Because a database is usually a model of a real-world situation, certain pieces of data are inevitably missing, unknown, or don't apply. In the sample database, for example, the QUOTA column in the SALESREPS table contains the sales goal for each salesperson. However, the newest salesperson has not yet been assigned a quota; this data is missing for that row of the table. You might be tempted to put a zero in the column for this salesperson, but that would not be an accurate reflection of the situation. The salesperson does not have a zero quota; the quota is just "not yet known." Similarly, the MANAGER column in the SALESREPS table contains the employee number of each salesperson's manager. But Sam Clark, the Vice President of Sales, has no manager in the sales organization. This column does not apply to Sam. Again, you might think about entering a zero, or a 9999 in the column, but neither of these values would really be the employee number of Sam's boss. No data value is applicable to this row. SQL supports missing, unknown, or inapplicable data explicitly, through the concept of a null value. A null value is an indicator that tells SQL (and the user) that the data is missing or not applicable. As a convenience, a missing piece of data is often said to have the value NULL. But the NULL value is not a real data value like 0, 473.83, or "Sam Clark." Instead, it's a signal, or a reminder, that the data value is missing or unknown. Figure 5-3 shows the contents of the SALESREPS table. Note that the QUOTA and REP_OFFICE values for Tom Snyder's row and the MANAGER value for Sam Clark's row of the table all contain NULL values. Figure 5-3: NULL values in the SALEREPS table In many situations NULL values require special handling by the DBMS. For example, if the user requests the sum of the QUOTA column, how should the DBMS handle the missing data when computing the sum? The answer is given by a set of special rules that govern NULL value handling in various SQL statements and clauses. Because of these rules, some leading database authorities feel strongly that NULL values should not be used. Others, including Dr. Codd, have advocated the use of multiple NULL values, with distinct indicators for "unknown" and "not applicable" data. Regardless of the academic debates, NULL values are a well-entrenched part of the ANSI/ISO SQL standard and are supported in virtually all commercial SQL products. They also play an important, practical role in production SQL databases. The special rules that apply to NULL values (and the cases where NULL values are handled inconsistently by various SQL products) are pointed out in the relevant sections of this book. Summary This chapter described the basic elements of the SQL language. The basic structure of SQL can be summarized as follows: The SQL language that is in common use includes about 30 statements, each consisting of a verb and one or more clauses. Each statement performs a single, - 68 - specific function. SQL-based databases can store various types of data, including text, integers, decimal numbers, floating point numbers, and usually many more vendor-specific data types. SQL statements can include expressions that combine column names, constants, and built-in functions, using arithmetic and other vendor-specific operators. Variations in data types, constants, and built-in functions make portability of SQL statements more difficult than it may seem at first. NULL values provide a systematic way of handling missing or inapplicable data in the SQL language. Chapter 6: Simple Queries Overview In many ways, queries are the heart of the SQL language. The SELECT statement, which is used to express SQL queries, is the most powerful and complex of the SQL statements. Despite the many options afforded by the SELECT statement, it's possible to start simply and then work up to more complex queries. This chapter discusses the simplest SQL queries—those that retrieve data from a single table in the database. The SELECT Statement The SELECT statement retrieves data from a database and returns it to you in the form of query results. You have already seen many examples of the SELECT statement in the quick tour presented in Chapter 2. Here are several more sample queries that retrieve information about sales offices: List the sales offices with their targets and actual sales. SELECT CITY, TARGET, SALES FROM OFFICES CITY TARGET SALES ------------- ---------- ----------- Denver $300,000.00 $186,042.00 New York $575,000.00 $692,637.00 Chicago $800,000.00 $735,042.00 Atlanta $350,000.00 $367,911.00 Los Angeles $725,000.00 $835,915.00 List the Eastern region sales offices with their targets and sales. SELECT CITY, TARGET, SALES FROM OFFICES WHERE REGION = 'Eastern' CITY TARGET SALES ------------- ----------- ------------ New York $575,000.00 $692,637.00 Chicago $800,000.00 $735,042.00 - 69 - Atlanta $350,000.00 $367,911.00 List Eastern region sales offices whose sales exceed their targets, sorted in alphabetical order by city. SELECT CITY, TARGET, SALES FROM OFFICES WHERE REGION = 'Eastern' AND SALES > TARGET ORDER BY CITY CITY TARGET SALES ------------- ----------- ---------- Atlanta $350,000.00 $367,911.00 New York $575,000.00 $692,637.00 What are the average target and sales for Eastern region offices? SELECT AVG(TARGET), AVG(SALES) FROM OFFICES WHERE REGION = 'Eastern' AVG(TARGET) AVG(SALES) ------------- ---------- $575,000.00 $598,530.00 For simple queries, the English language request and the SQL SELECT statement are very similar. When the requests become more complex, more features of the SELECT statement must be used to specify the query precisely. Figure 6-1 shows the full form of the SELECT statement, which consists of six clauses. The SELECT and FROM clauses of the statement are required. The remaining four clauses are optional. You include them in a SELECT statement only when you want to use the functions they provide. The following list summarizes the function of each clause: Figure 6-1: SELECT statement syntax diagram The SELECT clause lists the data items to be retrieved by the SELECT statement. The - 70 - items may be columns from the database, or columns to be calculated by SQL as it performs the query. The SELECT clause is described in later sections of this chapter. The FROM clause lists the tables that contain the data to be retrieved by the query. Queries that draw their data from a single table are described in this chapter. More complex queries that combine data from two or more tables are discussed in Chapter 7. The WHERE clause tells SQL to include only certain rows of data in the query results. A search condition is used to specify the desired rows. The basic uses of the WHERE clause are described later in this chapter. Those that involve subqueries are discussed in Chapter 9. The GROUP BY clause specifies a summary query. Instead of producing one row of query results for each row of data in the database, a summary query groups together similar rows and then produces one summary row of query results for each group. Summary queries are described in Chapter 8. The HAVING clause tells SQL to include only certain groups produced by the GROUP BY clause in the query results. Like the WHERE clause, it uses a search condition to specify the desired groups. The HAVING clause is described in Chapter 8. The ORDER BY clause sorts the query results based on the data in one or more columns. If it is omitted, the query results are not sorted. The ORDER BY clause is described later in this chapter. The SELECT Clause The SELECT clause that begins each SELECT statement specifies the data items to be retrieved by the query. The items are usually specified by a select list, a list of select items separated by commas. Each select item in the list generates a single column of query results, in left-to-right order. A select item can be: a column name, identifying a column from the table(s) named in the FROM clause. When a column name appears as a select item, SQL simply takes the value of that column from each row of the database table and places it in the corresponding row of query results. a constant, specifying that the same constant value is to appear in every row of the query results. a SQL expression, indicating that SQL must calculate the value to be placed into the query results, in the style specified by the expression. Each type of select item is described later in this chapter. The FROM Clause The FROM clause consists of the keyword FROM, followed by a list of table specifications separated by commas. Each table specification identifies a table containing data to be retrieved by the query. These tables are called the source tables of the query (and of the SELECT statement) because they are the source of all of the data in the query results. All of the queries in this chapter have a single source table, and every FROM clause contains a single table name. Query Results - 71 - The result of a SQL query is always a table of data, just like the tables in the database. If you type a SELECT statement using interactive SQL, the DBMS displays the query results in tabular form on your computer screen. If a program sends a query to the DBMS using programmatic SQL, the table of query results is returned to the program. In either case, the query results always have the same tabular, row/column format as the actual tables in the database, as shown in Figure 6-2. Usually the query results will be a table with several columns and several rows. For example, this query produces a table of three columns (because it asks for three items of data) and ten rows (because there are ten salespeople): Figure 6-2: The tabular structure of SQL query results List the names, offices, and hire dates of all salespeople. SELECT NAME, REP_OFFICE, HIRE_DATE FROM SALESREPS NAME REP_OFFICE HIRE_DATE --------------- ---------- --------- Bill Adams 13 12-FEB-88 Mary Jones 11 12-OCT-89 Sue Smith 21 10-DEC-86 Sam Clark 11 14-JUN-88 Bob Smith 12 19-MAY-87 Dan Roberts 12 20-OCT-86 Tom Snyder NULL 13-JAN-90 Larry Fitch 21 12-OCT-89 Paul Cruz 12 01-MAR-87 Nancy Angelli 22 14-NOV-88 In contrast, the following query produces a single row because only one salesperson has the requested employee number. Even though this single row of query results looks less "tabular" than the multi-row results, SQL still considers it to be a table of three columns and one row. What are the name, quota, and sales of employee number 107? SELECT NAME, QUOTA, SALES FROM SALESREPS WHERE EMPL_NUM = 107 - 72 - NAME QUOTA SALES -------------- ---------- ----------- Nancy Angelli $300,000.00 $186,042.00 In some cases the query results can be a single value, as in the following example: What are the average sales of our salespeople? SELECT AVG(SALES) FROM SALESREPS AVG(SALES) ------------ $289,353.20 These query results are still a table, although it's a very small one consisting of one column and one row. Finally, it's possible for a query to produce zero rows of query results, as in this example: List the name and hire date of anyone with sales over $500,000. SELECT NAME, HIRE_DATE FROM SALESREPS WHERE SALES > 500000.00 NAME HIRE_DATE ----------- --------- Even in this situation, the query results are still a table. This one is an empty table with two columns and zero rows. Note that SQL's support for missing data extends to query results as well. If a data item in the database has a NULL value, the NULL value appears in the query results when the data item is retrieved. For example, the SALESREPS table contains NULL values in its QUOTA and MANAGER columns. The following query returns these NULL values in the second and third columns of query results: List the salespeople, their quotas, and their managers. SELECT NAME, QUOTA, MANAGER FROM SALESREPS NAME QUOTA MANAGER -------------- ------------ ---------- Bill Adams $350,000.00 104 Mary Jones $300,000.00 106 Sue Smith $350,000.00 108 Sam Clark $275,000.00 NULL Bob Smith $200,000.00 106 Dan Roberts $300,000.00 104 Tom Snyder NULL 101 Larry Fitch $350,000.00 106 - 73 - Paul Cruz $275,000.00 104 Nancy Angelli $300,000.00 108 The fact that a SQL query always produces a table of data is very important. It means that the query results can be stored back into the database as a table. It means that the results of two similar queries can be combined to form a larger table of query results. Finally, it means that the query results can themselves be the target of further queries. A relational database's tabular structure thus has a very synergistic relationship with the relational query facilities of SQL. Tables can be queried, and queries produce tables. Simple Queries The simplest SQL queries request columns of data from a single table in the database. For example, this query requests three columns from the OFFICES table: List the location, region, and sales of each sales office. SELECT CITY, REGION, SALES FROM OFFICES CITY REGION SALES -------------- ------- ------------ Denver Western $186,042.00 New York Eastern $692,637.00 Chicago Eastern $735,042.00 Atlanta Eastern $367,911.00 Los Angeles Western $835,915.00 The SELECT statement for simple queries like this one includes only the two required clauses. The SELECT clause names the requested columns; the FROM clause names the table that contains them. Conceptually, SQL processes the query by going through the table named in the FROM clause, one row at a time, as shown in Figure 6-3. For each row, SQL takes the values of the columns requested in the select list and produces a single row of query results. The query results thus contain one row of data for each row in the table. Figure 6-3: Simple query processing (no WHERE clause) Calculated Columns - 74 - In addition to columns whose values come directly from the database, a SQL query can include calculated columns whose values are calculated from the stored data values. To request a calculated column, you specify a SQL expression in the select list. As discussed in Chapter 5, SQL expressions can involve addition, subtraction, multiplication, and division. You can also use parentheses to build more complex expressions. Of course the columns referenced in an arithmetic expression must have a numeric type. If you try to add, subtract, multiply, or divide columns containing text data, SQL will report an error. This query shows a simple calculated column: List the city, region, and amount over/under target for each office. SELECT CITY, REGION, (SALES - TARGET) FROM OFFICES CITY REGION (SALES-TARGET) -------------- ------ ---------------- Denver Western -$113,958.00 New York Eastern $117,637.00 Chicago Eastern -$64,958.00 Atlanta Eastern $17,911.00 Los Angeles Western $110,915.00 To process the query, SQL goes through the offices, generating one row of query results for each row of the OFFICES table, as shown in Figure 6-4. The first two columns of query results come directly from the OFFICES table. The third column of query results is calculated, row-by-row, using the data values from the current row of the OFFICES table. Figure 6-4: Query processing with a calculated column Here are other examples of queries that use calculated columns: Show the value of the inventory for each product. SELECT MFR_ID, PRODUCT_ID, DESCRIPTION, (QTY_ON_HAND * PRICE) FROM PRODUCTS MFR_ID PRODUCT_ID DESCRIPTION (QTY_ON_HAND*PRICE) ------ ---------- ---------------- ------------------ REI 2A45C Ratchet Link $16,590.00 - 75 - ACI 4100Y Widget Remover $68,750.00 QSA XK47 Reducer $13,490.00 BIC 41672 Plate $0.00 IMM 779C 900-lb Brace $16,875.00 ACI 41003 Size 3 Widget $22,149.00 ACI 41004 Size 4 Widget $16,263.00 BIC 41003 Handle $1,956.00 Show me the result if I raised each salesperson's quota by 3 percent of their year-to-date sales. SELECT NAME, QUOTA, (QUOTA + (.03*SALES)) FROM SALESREPS NAME QUOTA (QUOTA+(.03*SALES)) --------------- ----------- ------------------- Bill Adams $350,000.00 $361,037.33 Mary Jones $300,000.00 $311,781.75 Sue Smith $350,000.00 $364,221.50 Sam Clark $275,000.00 $283,997.36 Bob Smith $200,000.00 $204,277.82 Dan Roberts $300,000.00 $309,170.19 Tom Snyder NULL NULL Larry Fitch $350,000.00 $360,855.95 Paul Cruz $275,000.00 $283,603.25 Nancy Angelli $300,000.00 $305,581.26 As mentioned in Chapter 5, many SQL products provide additional arithmetic operations, character string operations, and built-in functions that can be used in SQL expressions. These can appear in select list expressions, as in this DB2 example: List the name and month and year of hire for each salesperson. SELECT NAME, MONTH(HIRE_DATE), YEAR(HIRE_DATE) FROM SALESREPS SQL constants can also be used by themselves as items in a select list. This can be useful for producing query results that are easier to read and interpret, as in the example on the bottom of the next page. List the sales for each city. SELECT CITY, 'has sales of', SALES FROM OFFICES CITY HAS SALES OF SALES ------------ ----------- -------------- Denver has sales of $186,042.00 New York has sales of $692,637.00 Chicago has sales of $735,042.00 Atlanta has sales of $367,911.00 Los Angeles has sales of $835,915.00 The query results appear to consist of a separate "sentence" for each office, but they're - 76 - really a table of three columns. The first and third columns contain values from the OFFICES table. The second column always contains the same 12-character text string. This distinction is subtle when the query results are displayed on a screen, but it is crucial in programmatic SQL, when the results are being retrieved into a program and used for calculations. Selecting All Columns (SELECT *) Sometimes it's convenient to display the contents of all the columns of a table. This can be particularly useful when you first encounter a new database and want to get a quick understanding of its structure and the data it contains. As a convenience, SQL lets you use an asterisk (*) in place of the select list as an abbreviation for "all columns": Show me all the data in the OFFICES table. SELECT * FROM OFFICES OFFICE CITY REGION MGR TARGET SALES ------------------- -------- --- ----------- ----------- 22 Denver Western 108 $300,000.00 $186,042.00 11 New York Eastern 106 $575,000.00 $692,637.00 12 Chicago Eastern 104 $800,000.00 $735,042.00 13 Atlanta Eastern 105 $350,000.00 $367,911.00 21 Los Angeles Western 108 $725,000.00 $835,915.00 The query results contain all six columns of the OFFICES table, in the same left-to-right order as in the table itself. The ANSI/ISO SQL standard specifies that a SELECT statement can have either an all- column selection or a select list, but not both, as shown in Figure 6-1. However, many SQL implementations treat the asterisk (*) as just another element of the select list. Thus the query: SELECT *, (SALES - TARGET) FROM OFFICES is legal in most commercial SQL dialects (for example in DB2, Oracle, and SQL Server), but it is not permitted by the ANSI/ISO standard. The all-columns selection is most appropriate when you are using interactive SQL casually. It should be avoided in programmatic SQL, because changes in the database structure can cause a program to fail. For example, suppose the OFFICES table were dropped from the database and then re-created with its columns rearranged and a new seventh column added. SQL automatically takes care of the database-related details of such changes, but it cannot modify your application program for you. If your program expects a SELECT * FROM OFFICES query to return six columns of query results with certain data types, it will almost certainly stop working when the columns are rearranged and a new one is added. These difficulties can be avoided if you write the program to request the columns it needs by name. For example, the following query produces the same results as SELECT * FROM OFFICES. It is also immune to changes in the database structure, as long as the named columns continue to exist in the OFFICES table: SELECT OFFICE, CITY, REGION, MGR, TARGET, SALES FROM OFFICES - 77 - Duplicate Rows (DISTINCT) If a query includes the primary key of a table in its select list, then every row of query results will be unique (because the primary key has a different value in each row). If the primary key is not included in the query results, duplicate rows can occur. For example, suppose you made this request: List the employee numbers of all sales office managers. SELECT MGR FROM OFFICES MGR ---- 108 106 104 105 108 The query results have five rows (one for each office), but two of them are exact duplicates of one another. Why? Because Larry Fitch manages both the Los Angeles and Denver offices, and his employee number (108) appears in both rows of the OFFICES table. These query results are probably not exactly what you had in mind. If there are four different managers, you might have expected only four employee numbers in the query results. You can eliminate duplicate rows of query results by inserting the keyword DISTINCT in the SELECT statement just before the select list. Here is a version of the previous query that produces the results you want: List the employee numbers of all sales office managers. SELECT DISTINCT MGR FROM OFFICES MGR ---- 104 105 106 108 Conceptually, SQL carries out this query by first generating a full set of query results (five rows) and then eliminating rows that are exact duplicates of one another to form the final query results. The DISTINCT keyword can be specified regardless of the contents of the SELECT list (with certain restrictions for summary queries, as described in Chapter 8). If the DISTINCT keyword is omitted, SQL does not eliminate duplicate rows. You can also specify the keyword ALL to explicitly indicate that duplicate rows are to be retained, but it is unnecessary since this is the default behavior. Row Selection (WHERE Clause) - 78 - SQL queries that retrieve all rows of a table are useful for database browsing and reports, but for little else. Usually you'll want to select only some of the rows in a table and include only these rows in the query results. The WHERE clause is used to specify the rows you want to retrieve. Here are some examples of simple queries that use the WHERE clause: Show me the offices where sales exceed target. SELECT CITY, SALES, TARGET FROM OFFICES WHERE SALES > TARGET CITY SALES TARGET ------------ ----------- ----------- New York $692,637.00 $575,000.00 Atlanta $367,911.00 $350,000.00 Los Angeles $835,915.00 $725,000.00 Show me the name, sales, and quota of employee number 105. SELECT NAME, SALES, QUOTA FROM SALESREPS WHERE EMPL_NUM = 105 NAME SALES QUOTA ----------- ----------- ----------- Bill Adams $367,911.00 $350,000.00 Show me the employees managed by Bob Smith (employee 104). SELECT NAME, SALES FROM SALESREPS WHERE MANAGER = 104 NAME SALES ------------ ----------- Bill Adams $367,911.00 Dan Roberts $305,673.00 Paul Cruz $286,775.00 The WHERE clause consists of the keyword WHERE followed by a search condition that specifies the rows to be retrieved. In the previous query, for example, the search condition is MANAGER = 104. Figure 6-5 shows how the WHERE clause works. Conceptually, SQL goes through each row of the SALESREPS table, one-by-one, and applies the search condition to the row. When a column name appears in the search condition (such as the MANAGER column in this example), SQL uses the value of the column in the current row. For each row, the search condition can produce one of three results: - 79 - Figure 6-5: Row selection with the WHERE clause If the search condition is TRUE, the row is included in the query results. For example, the row for Bill Adams has the correct MANAGER value and is included. If the search condition is FALSE, the row is excluded from the query results. For example, the row for Sue Smith has the wrong MANAGER value and is excluded. If the search condition has a NULL (unknown) value, the row is excluded from the query results. For example, the row for Sam Clark has a NULL value for the MANAGER column and is excluded. Figure 6-6 shows another way to think about the role of the search condition in the WHERE clause. Basically, the search condition acts as a filter for rows of the table. Rows that satisfy the search condition pass through the filter and become part of the query results. Rows that do not satisfy the search condition are trapped by the filter and excluded from the query results. Figure 6-6: The WHERE clause as a filter Search Conditions SQL offers a rich set of search conditions that allow you to specify many different kinds of queries efficiently and naturally. Five basic search conditions (called predicates in the ANSI/ISO standard) are summarized here and are described in the sections that follow: Comparison test. Compares the value of one expression to the value of another expression. Use this test to select offices in the Eastern region, or salespeople whose sales are above their quotas. Range test. Tests whether the value of an expression falls within a specified range of values. Use this test to find salespeople whose sales are between $100,000 and $500,000. Set membership test. Checks whether the value of an expression matches one of a set of values. Use this test to select offices located in New York, Chicago, or Los Angeles. - 80 - Pattern matching test. Checks whether the value of a column containing string data matches a specified pattern. Use this test to select customers whose names start with the letter "E." Null value test. Checks whether a column has a NULL (unknown) value. Use this test to find the salespeople who have not yet been assigned to a manager. Comparison Test (=, , =) The most common search condition used in a SQL query is a comparison test. In a comparison test, SQL computes and compares the values of two SQL expressions for each row of data. The expressions can be as simple as a column name or a constant, or they can be more complex arithmetic expressions. SQL offers six different ways of comparing the two expressions, as shown in Figure 6-7. Here are some examples of typical comparison tests: Figure 6-7: Comparison test syntax diagram Find salespeople hired before 1988. SELECT NAME FROM SALESREPS WHERE HIRE_DATE < '01-JAN-88' NAME --------- Sue Smith Bob Smith Dan Roberts Paul Cruz List the offices whose sales fall below 80 percent of target. SELECT CITY, SALES, TARGET FROM OFFICES WHERE SALES < (.8 * TARGET) CITY SALES TARGET ------- ----------- ----------- Denver $186,042.00 $300,000.00 List the offices not managed by employee number 108. SELECT CITY, MGR - 81 - FROM OFFICES WHERE MGR 108 CITY MGR --------- --- New York 106 Chicago 104 Atlanta 105 As shown in Figure 6-7, the inequality comparison test is written as "A < > B" according to the ANSI/ISO SQL specification. Several SQL implementations use alternate notations, such as "A != B" (used by SQL Server) and "A¬=B" (used by DB2 and SQL/DS). In some cases, these are alternative forms; in others, they are the only acceptable form of the inequality test. When SQL compares the values of the two expressions in the comparison test, three results can occur: If the comparison is true, the test yields a TRUE result. If the comparison is false, the test yields a FALSE result. If either of the two expressions produces a NULL value, the comparison yields a NULL result. Single-Row Retrieval The most common comparison test is one that checks whether a column's value is equal to some constant. When the column is a primary key, the test isolates a single row of the table, producing a single row of query results, as in this example: Retrieve the name and credit limit of customer number 2107. SELECT COMPANY, CREDIT_LIMIT FROM CUSTOMERS WHERE CUST_NUM = 2107 COMPANY CREDIT_LIMIT ------------------ ------------ Ace International $35,000.00 This type of query is the foundation of forms-based database retrieval programs. The user enters a customer number into the form, and the program uses the number to construct and execute a query. It then displays the retrieved data in the form. Note that the SQL statements for retrieving a specific customer by number, as in this example, and retrieving all customers with a certain characteristic (such as those with credit limits over $25,000) both have exactly the same form. These two types of queries (retrieval by primary key and retrieval based on a search of the data) would be very different operations in a nonrelational database. This uniformity of approach makes SQL much simpler to learn and use than earlier query languages. NULL Value Considerations The behavior of NULL values in comparison tests can reveal some "obviously true" notions about SQL queries to be, in fact, not necessarily true. For example, it would seem - 82 - that the results of these two queries: List salespeople who are over quota. SELECT NAME FROM SALESREPS WHERE SALES > QUOTA NAME ----------- Bill Adams Mary Jones Sue Smith Sam Clark Dan Roberts Larry Fitch Paul Cruz List salespeople who are under or at quota. SELECT NAME FROM SALESREPS WHERE SALES < = QUOTA NAME ------------- Bob Smith Nancy Angelli would include every row of the SALESREPS table, but the queries produce seven and two rows, respectively, for a total of nine rows, while there are ten rows in the SALESREPS table. Tom Snyder's row has a NULL value in the QUOTA column because he has not yet been assigned a quota. This row is not listed by either query; it "vanishes" in the comparison test. As this example shows, you need to think about NULL value handling when you specify a search condition. In SQL's three-valued logic, a search condition can yield a TRUE, FALSE, or NULL result. Only rows where the search condition yields a TRUE result are included in the query results. Range Test (BETWEEN) SQL provides a different form of search condition with the range test (BETWEEN) shown in Figure 6-8. The range test checks whether a data value lies between two specified values. It involves three SQL expressions. The first expression defines the value to be tested; the second and third expressions define the low and high ends of the range to be checked. The data types of the three expressions must be comparable. Figure 6-8: Range test (BETWEEN) syntax diagram This example shows a typical range test: - 83 - Find orders placed in the last quarter of 1989. SELECT ORDER_NUM, ORDER_DATE, MFR, PRODUCT, AMOUNT FROM ORDERS WHERE ORDER_DATE BETWEEN '01-OCT-89' AND '31-DEC-89' ORDER_NUM ORDER_DATE MFR PRODUCT AMOUNT ---------- --------- ------------ ---------- 112961 17-DEC-89 REI 2A44L $31,500.00 112968 12-OCT-89 ACI 41004 $3,978.00 112963 17-DEC-89 ACI 41004 $3,276.00 112983 27-DEC-89 ACI 41004 $702.00 112979 12-OCT-89 ACI 4100Z $15,000.00 112992 04-NOV-89 ACI 41002 $760.00 112975 12-OCT-89 REI 2A44G $2,100.00 112987 31-DEC-89 ACI 4100Y $27,500.00 The BETWEEN test includes the endpoints of the range, so orders placed on October 1 or December 31 are included in the query results. Here is another example of a range test: Find the orders that fall into various amount ranges. SELECT ORDER_NUM, AMOUNT FROM ORDERS WHERE AMOUNT BETWEEN 20000.00 AND 29999.99 ORDER_NUM AMOUNT ---------- ---------- 113036 $22,500.00 112987 $27,500.00 113042 $22,500.00 SELECT ORDER_NUM, AMOUNT FROM ORDERS WHERE AMOUNT BETWEEN 30000.00 AND 39999.99 ORDER_NUM AMOUNT ---------- ---------- 112961 $31,500.00 113069 $31,350.00 SELECT ORDER_NUM, AMOUNT FROM ORDERS WHERE AMOUNT BETWEEN 40000.00 AND 49999.99 ORDER_NUM AMOUNT ---------- ---------- 113045 $45,000.00 The negated version of the range test (NOT BETWEEN) checks for values that fall outside the range, as in this example: - 84 - List salespeople whose sales are not between 80 percent and 120 percent of quota. SELECT NAME, SALES, QUOTA FROM SALESREPS WHERE SALES NOT BETWEEN (.8 * QUOTA) AND (1.2 * QUOTA) NAME SALES QUOTA -------------- ----------- ----------- Mary Jones $392,725.00 $300,000.00 Sue Smith $474,050.00 $350,000.00 Bob Smith $142,594.00 $200,000.00 Nancy Angelli $186,042.00 $300,000.00 The test expression specified in the BETWEEN test can be any valid SQL expression, but in practice it's usually just a column name, as in the previous examples. The ANSI/ISO standard defines relatively complex rules for the handling of NULL values in the BETWEEN test: If the test expression produces a NULL value, or if both expressions defining the range produce NULL values, then the BETWEEN test returns a NULL result. If the expression defining the lower end of the range produces a NULL value, then the BETWEEN test returns FALSE if the test value is greater than the upper bound, and NULL otherwise. If the expression defining the upper end of the range produces a NULL value, then the BETWEEN test returns FALSE if the test value is less than the lower bound, and NULL otherwise. Before relying on this behavior, it's a good idea to experiment with your DBMS. It's worth noting that the BETWEEN test doesn't really add to the expressive power of SQL, because it can be expressed as two comparison tests. The range test: A BETWEEN B AND C is completely equivalent to: (A >= B) AND (A < = C) However, the BETWEEN test is a simpler way to express a search condition when you're thinking of it in terms of a range of values. Set Membership Test (IN) Another common search condition is the set membership test (IN), shown in Figure 6-9. It tests whether a data value matches one of a list of target values. Here are several queries that use the set membership test: Figure 6-9: Set membership test (IN) syntax diagram - 85 - List the salespeople who work in New York, Atlanta, or Denver. SELECT NAME, QUOTA, SALES FROM SALESREPS WHERE REP_OFFICE IN (11, 13, 22) NAME QUOTA SALES -------------- ----------- ----------- Bill Adams $350,000.00 $367,911.00 Mary Jones $300,000.00 $392,725.00 Sam Clark $275,000.00 $299,912.00 Nancy Angelli $300,000.00 $186,042.00 Find all orders placed on a Thursday in January 1990. SELECT ORDER_NUM, ORDER_DATE, AMOUNT FROM ORDERS WHERE ORDER_DATE IN ('04-JAN-90', '11-JAN-90', '18-JAN-90', '25- JAN-90') ORDER_NUM ORDER_DATE AMOUNT --------------------- --------- 113012 11-JAN-90 $3,745.00 113003 25-JAN-90 $5,625.00 Find all orders placed with four specific salespeople. SELECT ORDER_NUM, REP, AMOUNT FROM ORDERS WHERE REP IN (107, 109, 101, 103) ORDER_NUM REP AMOUNT ---------- --- ---------- 112968 101 $3,978.00 113058 109 $1,480.00 112997 107 $652.00 113062 107 $2,430.00 113069 107 $31,350.00 112975 103 $2,100.00 113055 101 $150.00 113003 109 $5,625.00 113057 103 $600.00 113042 101 $22,500.00 You can c

SQL: The Complete Reference PDF

Document Details

Tags

Related

Summary

Full Transcript

Upgrade to continue