SQL in a Nutshell applies the eminently useful "Nutshell" format to Structured Query Language (SQL), the elegantbut complexdescriptive language that is used to create and manipulate large stores of data. For SQL programmers, analysts, and database administrators, the new second edition of SQL in a Nutshell is the essential date language reference for the world's top SQL database products. SQL in a Nutshell is a lean, focused, and thoroughly comprehensive reference for those who live in a deadline-driven world.This invaluable desktop quick reference drills down and documents every SQL command and how to use it in both commercial (Oracle, DB2, and Microsoft SQL Server) and open source implementations (PostgreSQL, and MySQL). It describes every command and reference and includes the command syntax (by vendor, if the syntax differs across implementations), a clear description, and practical examples that illustrate important concepts and uses. And it also explains how the leading commercial and open sources database product implement SQL. This wealth of information is packed into a succinct, comprehensive, and extraordinarily easy-to-use format that covers the SQL syntax of no less than 4 different databases.When you need fast, accurate, detailed, and up-to-date SQL information, SQL in a Nutshell, Second Edition will be the quick reference you'll reach for every time. SQL in a Nutshell is small enough to keep by your keyboard, and concise (as well as clearly organized) enough that you can look up the syntax you need quickly without having to wade through a lot of useless fluff. You won't want to work on a project involving SQL without it.
About the Author
Kevin Kline is the Technical Strategy Manager for SQL Server Solutions at Quest Software, a leading provider of award winning tools for database management and application monitoring tools. Kevin is also a founding board member and former President of the international Professional Association for SQL Server (PASS) and frequently contributes to database technology magazines, web sites, and discussion forums. Kevin's most popular book is SQL in a Nutshell published by O'Reilly Media. Kevin is also the author of monthly magazine columns for SQL Server Magazine and Database Trends & Applications. Kevin is a top rated speaker, appearing at international conferences like Microsoft TechEd, DevTeach, PASS, Microsoft IT Forum, and SQL Connections.
Daniel Kline is an Assistant Professor of English at the University of Alaska, Anchorage, where he specializes in medieval literature, literary and cultural theory, and computer-assisted pedagogy. He completed his Ph.D. at Indiana University, Bloomington, and in addition to numerous scholarly presentations, Dan recently has published academic essays in Literary and Linguistic Computing, Philological Quarterly, Chaucer Review, and Essays in Medieval Studies. When he's not spending time with his wife and two boys, Dan frets over his pet project, the Electronic Canterbury Tales. Dan can be reached at email@example.com.
Brand Hunt is a Project Manager and Software Developer at Systems Research and Development (http://srdsoftware.com) . The team at SRD is a world leader in systems for privacy-friendly identity recognition and relationship awareness. Prior to SRD, Brand worked at Rogue Wave software. Beyond work, Brand enjoys playing board games, pinochle, and snowboarding with his family and friends.
Read an Excerpt
Chapter 4: SQL Functions
A function is a special type of command word in the SQL99 command set. In effect, functions are one-word commands that return a single value. The value of a function can be determined by input parameters, as with a function that averages a list of database values. But many functions do not use any type of input parameter, such as the function that returns the current system time, CURRENT_TIME.
The SQL99 standard supports a number of useful functions. This chapter covers those functions, providing detailed descriptions and examples. In addition, each database vendor maintains a long list of their own internal functions that are outside of the scope of the SQL standard. Lists and descriptions are provided for each database implementation's internal functions.
In addition, most database vendors support the ability to create user-defined functions (UDF). For more information on UDFs, refer to the CREATE FUNCTION command in Chapter 3, SQL Statements Command Reference.
Deterministic and Nondeterministic Functions
Functions can be either deterministic or nondeterministic. A deterministic function always returns the same results if given the same input values. A nondeterministic function returns different results every time it is called, even when the same input values are provided.
Why is this important? It is important because of how functions may be used within views, user-defined functions, and stored procedures. The restrictions vary across implementations, but these objects sometimes allow only deterministic functions within their defining code. For example, Microsoft SQL Server allows the creation of an index on a column expression--as long as the expression does not contain nondeterministic functions. Rules and restrictions vary between the vendors, so check their documentation when using functions.
Types of Functions
There are several basic types and categories of functions in SQL99 and vendor implementations of SQL. The basic types of functions are:
- Aggregate functions
- Operate against a collection of values, but return a single, summarizing value.
- Scalar functions
- Operate against a single value, and return a single value based on the input value. Some scalar functions, CURRENT_TIME for example, do not require any arguments.
Aggregate functions return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement. The supported aggregate functions and their syntax are listed in Table 4-1.
Computes the average value of a column by the expression
Counts the rows defined by the expression
Counts all rows in the specified table or view
Finds the minimum value in a column by the expression
Finds the maximum value in a column by the expression
Computes the sum of column values by the expression
Technically speaking, ANY, EVERY, and SOME are considered aggregate functions. However, they have been discussed as range search criteria since they are most often used that way. Refer to the SELECT . . . WHERE topic in the previous chapter for more information on these functions.
The number of values processed by an aggregate varies depending on the number of rows queried from the table. This behavior makes aggregate functions different from scalar functions, which require a fixed number and fixed type of parameters.
The general syntax of an aggregate function is:
aggregate_function_name ( [ALL | DISTINCT] expression )
The aggregate function name may be AVG, COUNT, MAX, MIN, or SUM. The ALL clause, which is the default behavior and does not actually need to be specified, evaluates all rows when aggregating the value of the function. The DISTINCT clause uses only distinct values when evaluating the function.
AVG and SUM
The AVG function computes the average of values in a column or an expression. SUM computes the sum. Both functions work with numeric values and ignore NULL values. They also can be used to compute the average or sum of all distinct values of a column or expression.
AVG and SUM are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.
The following query computes average year-to-date sales for each type of book:
SELECT type, AVG( ytd_sales ) AS "average_ytd_sales"
GROUP BY type;
This query returns the sum of year-to-date sales for each type of book:
SELECT type, SUM( ytd_sales )
GROUP BY type;
The COUNT function has three variations. COUNT(*) counts all the rows in the target table whether they include nulls or not. COUNT(expression) computes the number of rows with non-NULL values in a specific column or expression. COUNT(DISTINCT expression) computes the number of distinct non-NULL values in a column or expression.
This query counts all rows in a table:
SELECT COUNT(*) FROM publishers;
The following query finds the number of different countries where publishers are located:
SELECT COUNT(DISTINCT country) "Count of Countries"
MIN and MAX
MIN(expression) and MAX(expression) find the minimum and maximum value (string, datetime, or numeric) in a set of rows. DISTINCT or ALL may be used with these functions, but they do not affect the result.
MIN and MAX are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.
MySQL also supports the functions LEAST( ) and GREATEST( ), providing the same capabilities.
The following query finds the best and worst sales for any title on record:
SELECT 'MIN' = MIN(ytd_sales), 'MAX' = MAX(ytd_sales)
Aggregate functions are used often in the HAVING clause of queries with GROUP BY. The following query selects all categories (types) of books that have an average price for all books in the category higher than $15.00:
SELECT type 'Category', AVG( price ) 'Average Price'
GROUP BY type
HAVING AVG(price) > 15
Scalar functions fall into the categories listed in Table 4-2....
Table of Contents
Why This Book?;
Who Should Read This Book?;
How This Book Is Organized;
Conventions Used in This Book;
How to Use This Book;
How to Contact Us;
Changes in the Second Edition;
Chapter 1: SQL History and Implementations;
1.1 The Relational Model and ANSI SQL;
1.2 History of the SQL Standard;
1.3 SQL Dialects;
Chapter 2: Foundational Concepts;
2.1 Database Platforms Described in This Book;
2.2 Categories of Syntax;
2.3 SQL2003 and Platform-Specific Datatypes;
Chapter 3: SQL Statement Command Reference;
3.1 How to Use This Chapter;
3.2 SQL Platform Support;
3.3 SQL Command Reference;
Chapter 4: SQL Functions;
4.1 Types of Functions;
4.2 ANSI SQL Aggregate Functions;
4.3 ANSI SQL Window Functions;
4.4 ANSI SQL Scalar Functions;
4.5 Platform-Specific Extensions;
Chapter 5: Database Programming;
5.1 Database Programming Overview;
5.2 Opening a Database Connection;
5.3 Closing a Database Connection;
5.4 Managing Transactions;
5.5 Executing Statements;
5.6 Retrieving Data;
5.7 Bound Parameters;
5.8 Error Handling;
Appendix A: Sybase Adaptive Server;
A.1 Sybase Adaptive Server Datatypes;
A.2 Sybase Adaptive Server SQL Statements;
A.3 Sybase Adaptive Server SQL Functions;
A.4 Sybase Adaptive Server Keywords;
Appendix B: Shared and Platform-Specific Keywords;
Most Helpful Customer Reviews
[A review of the 2nd edition, 2004.] Perhaps the best virtue of this book is that it spans all the major variants of SQL - db2, Oracle, MySQL, PostgreSQL and Microsoft's SQL Server. The authors are not beholden to any particular vendor. Thus the book describes the common ground. That is, the commands and usage that are most likely to be the same or similar across these implementations. Because a major use of this book might be in migrating. To this end, the more code you can have in this common area, the less painful the migration. Another possible usage is if you are a DBA in charge of running 2 [or more] of these implementations. Perhaps due to some legacy issues, you have to support them. If you cannot merge SQL code into one common version, you can get problems. Being able to use this book to find quickly common commands and options to those commands might greatly help you maximise a common body of code.
Contrary to several reviewers, this book is *not* well-suited to its purpose, especially compared to O'Reilly's usual high standards. You can't find what you need quickly, basic information is often missing, and there are enough glaring typos to make everything in the book suspect. This is particularly true of the book's main claim to fame, the myriad of small differences between database products. Some examples: (1) The book explains that some databases use '!=' instead of the standard SQL not-equal operator, ''. Which databases? Do they support both operators, or just '!='? The book doesn't say. (2a) The book doesn't tell you that MySQL doesn't support subqueries in SELECT statements -- even though there's a subsection on 'MySQL Syntax and Variations' for SELECT statements. (2b) The index has no entries for subqueries under any conceivable heading: queries, nested queries, subqueries, or SELECT. So you can spend quite a long time *trying* to find out which databases support subqueries. (Most do.) (3) The table of contents has a glaring typo, that makes you wonder what else got past the proofreaders and fact-checkers. Chapter 3, 'SQL Statements Command Reference' doesn't have sub-headings for individual commands or groups of commands ... except for one: 'DROP Statements', which are supposedly discussed for 70 pages, a third of the book. If O'Reilly was in such a rush to get the book out that they didn't notice this, how can you have confidence in the book's accuracy?
If you are someone like me, someone who takes a broad brush approach to developing and thinks that there is an appropriate database vendor or open source solution for every scenario, you will really love having this book on your reference shelf. The cross-platform references are excellent and show every option and attribute in thorough and accurate detail for several major database solutions. On first glance, some may scoff at 'how easy SQL is' until they move to a new database. If you pointed out the cross platform references during their first mockery, they will be apologizing and begging you to borrow it because it has the value of five books in one thin, easy to read an navigate title.
SQL in a Nutshell is designed as a cross- platform reference guide for people like me who are not experts and have to move between RDMS implementations (including desktop apps). In fact, SQL in a Nutshell's great virtue is that it finally levels the playing field by putting PostgreSQL and MySQL, the popular open source RDMS, on the same level with MS SQL Server and Oracle, each of which easily has its own market for high-end manuals and guides, while open source apps are freely available online. So, if you need full documentation on your favorite RDMS, go ahead and spend a couple of hundred dollars somewhere else. If you want a handy reference, simple explanations and comparisons, and an easy to read introduction to the four most important RDMSs currently available, pick up SQL in a Nutshell. You won't be disappointed.