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.
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.
1. Calculated Columns
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.
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.
Here are other examples of queries that use calculated columns:
Show the value of the inventory for each product.
Show the value of the inventory for each product
Show me the result if I raised each salesperson’s quota by 3 percent of their year-to-date sales.
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 the next DB2 example.
List the name, 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 next example.
List the sales for each city.
The query results appear to consist of a separate “sentence” for each office, but they’re 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.
2. 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.
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 was 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
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.