What is a SQL Query?
Learn what a SQL Query is and the fundamentals of the SQL SELECT statement, which is used to query the database for useful information.
From the book
From the book
From the book
Learning how to retrieve information from or manipulate information in a database is commonly a perplexing exercise. However, it can be a relatively easy task as long as you understand the question you're asking or the change you're trying to make to the database. After you understand the problem, you can translate it into the language used by any database system, which in most cases is Structured Query Language (SQL). You have to translate your request into an SQL statement so that your database system knows what information you want to retrieve or change. SQL provides the means for you and your database system to communicate.
Introducing SELECT
Above all other keywords, SELECT truly lies at the heart of SQL. It is the cornerstone of the most powerful and complex statement within the language and the means by which you retrieve information from the tables in your database. You use SELECT in conjunction with other keywords and clauses to find and view information in an almost limitless number of ways. Nearly any question regarding who, what, where, when, or even what if and how many can be answered with SELECT. As long as you've designed your database properly and collected the appropriate data, you can get the answers you need to make sound decisions for your organization. As you'll discover when you get to Part V, “Modifying Sets of Data,” you'll apply many of the techniques you learn about SELECT to create UPDATE, INSERT, and DELETE statements.
The SELECT operation in SQL can be broken down into three smaller operations, which I will refer to as the SELECT statement, the SELECT expression, and the SELECT query. (Breaking down the SELECT operation in this manner will make it far easier to understand and to appreciate its complexity.) Each of these operations provides its own set of keywords and clauses, providing you with the flexibility to create a final SQL statement that is appropriate for the question you want to pose to the database. You can even combine the operations in various ways to answer very complex questions.
The SELECT Statement
The SELECT statement forms the basis of every question you pose to the database. When you create and execute a SELECT statement, you are querying the database. (I know it sounds a little obvious, but I want to make certain that everyone reading this starts from the same point of reference.) In fact, many RDBMS programs allow you to save a SELECT statement as a query, view, function, or stored procedure. Whenever someone says she is going to query the database, you know that she's going to execute some sort of SELECT statement. Depending on the RDBMS program, SELECT statements can be executed directly from a command line window, from an interactive Query by Example (QBE) grid, or from within a block of programming code. Regardless of how you choose to define and execute it, the syntax of the SELECT statement is always the same.
Note: Many database systems provide extensions to the SQL Standard to allow you to build complex programming statements (such as If...Then...Else) in functions and stored procedures, but the specific syntax is unique to each different product. It is far beyond the scope of this book to cover even one or two of these programming languages—such as Microsoft SQL Server's Transact-SQL or Oracle's PL/SQL. (I cover a basic form of If...Then...Else [CASE] defined in the SQL Standard in Chapter 19, "Condition Testing.") You'll still use the cornerstone SELECT statement when you build functions and stored procedures for your particular database system. Throughout this book, I'll use the term view to refer to a saved SQL statement even though you might embed your SQL statement in a function or procedure.
A SELECT statement is composed of several distinct keywords, known as clauses. You define a SELECT statement by using various configurations of these clauses to retrieve the information you require. Some of these clauses are required, although others are optional. Additionally, each clause has one or more keywords that represent required or optional values. These values are used by the clause to help retrieve the information requested by the SELECT statement as a whole. Figure 4-1 shows a diagram of the SELECT statement and its clauses.
Click to view full-sized image
Note: The syntax diagram in Figure 4-1 reflects a rudimentary SELECT statement. I'll continue to update and modify the diagram as I introduce and work with new keywords and clauses. So for those of you who might have some previous experience with SQL statements, just be patient and bear with me for the time being.
Here's a brief summary of the clauses in a SELECT statement.
- SELECT—This is the primary clause of the SELECT statement and is absolutely required. You use it to specify the columns you want in the result set of your query. The columns themselves are drawn from the table or view you specify in the FROM clause. You can also use in this clause aggregate functions, such as Sum(HoursWorked), or mathematical expressions, such as Quantity * Price.
- FROM—This is the second most important clause in the SELECT statement and is also required. You use the FROM clause to specify the tables or views from which to draw the columns you've listed in the SELECT clause.
- WHERE—This is an optional clause that you use to filter the rows returned by the FROM clause. The WHERE keyword is followed by an expression, technically known as a predicate, that evaluates to true, false, or unknown. You can test the expression by using standard comparison operators, Boolean operators, or special operators.
- GROUP BY—When you use aggregate functions in the SELECT clause to produce summary information, you use the GROUP BY clause to divide the information into distinct groups. Your database system uses any column or list of columns following the GROUP BY keywords as grouping columns. The GROUP BY clause is optional.
- HAVING—The HAVING clause filters the result of aggregate functions in grouped information. It is similar to the WHERE clause in that the HAVING keyword is followed by an expression that evaluates to true, false, or unknown. You can test the expression by using standard comparison operators, Boolean operators, or special operators. HAVING is also an optional clause.
Learn more about SQL Queries and how to build them in SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition.