My Basic Knowledge: SQL SELECT Statements

A SELECT statement retrieves information from the database. With a SELECT statement, you can use the following capabilities:

• Projection: Select the columns in a table that are returned by a query. Select as few or as many of the columns as required.

• Selection: Select the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved.

Joining: Bring together data that is stored in different tables by specifying the link between them.

Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

In its simplest form, a SELECT statement must include the following:

• A SELECT clause, which specifies the columns to be displayed

• A FROM clause, which identifies the table containing the columns that are listed in the SELECT clause

In the syntax:

SELECT is a list of one or more columns

* selects all columns

DISTINCT suppresses duplicates

column|expression selects the named column or the expression

alias gives the selected columns different headings

FROM table specifies the table containing the columns

Note: The words keyword, clause, and statement are used as follows:

• A keyword refers to an individual SQL element.
For example, SELECT and FROM are keywords.

• A clause is a part of a SQL statement.
For example, SELECT employee_id, last_name, and so on is a clause.

• A statement is a combination of two or more clauses.
For example, SELECT * FROM employees is a SQL statement.

Selecting All Columns

SELECT * FROM departments;

• You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). In the example, the department table contains four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains eight rows, one for each department.

• You can also display all columns in the table by listing all the columns after the SELECT keyword. For example, the following SQL statement displays all columns and all rows of the DEPARTMENTS table:

SELECT department_id, department_name, manager_id, location_id
FROM departments;

Selecting Specific Columns

SELECT department_id, location_id FROM departments;

You can use the SELECT statement to display specific columns of the table by specifying the column names, separated by commas. The example displays all the department numbers and location numbers from the DEPARTMENTS table.

In the SELECT clause, specify the columns that you want in the order in which you want them to appear in the output. For example, to display location before department number (from left to right), you use the following statement:

SELECT location_id, department_id FROM departments;

Writing SQL Statements

By using the following simple rules and guidelines, you can construct valid statements that are both easy to read and edit:

• SQL statements are not case-sensitive (unless indicated).

• SQL statements can be entered on one or many lines.

• Keywords cannot be split across lines or abbreviated.

• Clauses are usually placed on separate lines for readability and ease of editing.

• Indents should be used to make code more readable.

• Keywords typically are entered in uppercase; all other words, such as table names and columns names are entered in lowercase.

Arithmetic Expressions

You may need to modify the way in which data is displayed, or you may want to perform calculations, or look at what-if scenarios. All these are possible using arithmetic expressions. An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.

Arithmetic Operators

Below lists the arithmetic operators that are available in SQL. You can use arithmetic operators in any clause of a SQL statement (except the FROM clause).

Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction operators only.

Using Arithmetic Operators

SELECT last_name, salary, salary + 300 FROM employees;

Below example uses the addition operator to calculate a salary increase of $300 for all employees. It also displays a SALARY+300 column in the output.

Note that the resultant calculated column, SALARY+300, is not a new column in the EMPLOYEES table; it is for display only. By default, the name of a new column comes from the calculation that generated it—in this case, salary+300.

Note: The Oracle server ignores blank spaces before and after the arithmetic operator.

Operator Precedence

If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators in an expression are of the same priority, then evaluation is done from left to right.

You can use parentheses to force the expression that is enclosed by the parentheses to be evaluated first.

Rules of Precedence:

• Multiplication and division occur before addition and subtraction.

• Operators of the same priority are evaluated from left to right.

• Parentheses are used to override the default precedence or to clarify the statement.

Example 1:

SELECT last_name, salary, 12*salary+100 FROM employees;

Example 2:

SELECT last_name, salary, 12*(salary+100) FROM employees;

The first example displays the last name, salary, and annual compensation of employees. It calculates the annual compensation by multiplying the monthly salary with 12, plus a one-time bonus of $100. Note that multiplication is performed before addition.

Note: Use parentheses to reinforce the standard order of precedence and to improve clarity. For example, the expression in the example can be written as (12*salary)+100 with no change in the result.

Using Parentheses

You can override the rules of precedence by using parentheses to specify the desired order in which the operators are to be executed.

The second example displays the last name, salary, and annual compensation of employees. It calculates the annual compensation as follows: adding a monthly bonus of $100 to the monthly salary, and then multiplying that subtotal with 12. Because of the parentheses, addition takes priority over multiplication.

Defining a Null Value

SELECT last_name, job_id, salary, commission_pct FROM employees;

• If a row lacks a data value for a particular column, that value is said to be null or to contain a null.

• Null is a value that is unavailable, unassigned, unknown, or inapplicable. Null is not the same as zero or a blank space. Zero is a number and blank space is a character.

• Columns of any data type can contain nulls. However, some constraints (NOT NULL and PRIMARY KEY) prevent nulls from being used in the column.

• In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a sales manager or sales representative can earn a commission. Other employees are not entitled to earn commissions. A null represents that fact.

Null Values in Arithmetic Expressions

If any column value in an arithmetic expression is null, the result is null. For example, if you attempt to perform division by zero, you get an error. However, if you divide a number by null, the result is a null or unknown.

Defining a Column Alias

• Renames a column heading

• Is useful with calculations

• Immediately follows the column name (There can also be the optional AS keyword between the column name and alias.)

• Requires double quotation marks if it contains spaces or special characters, or if it is case-sensitive

Concatenation Operator

SELECT last_name||job_id AS "Employees" FROM employees;

• You can link columns to other columns, arithmetic expressions, or constant values to create a character expression by using the concatenation operator (||). Columns on either side of the operator are combined to make a single output column.

• In the example, LAST_NAME and JOB_ID are concatenated, and given the alias Employees. Note that the last name of the employee and the job code are combined to make a single output column.

• The AS keyword before the alias name makes the SELECT clause easier to read.

Null Values with the Concatenation Operator

If you concatenate a null value with a character string, the result is a character string. LAST_NAME || NULL results in LAST_NAME.

Note: You can also concatenate date expressions with other expressions or columns.

Literal Character Strings

A literal is a character, a number, or a date that is included in the SELECT list. It is not a column name or a column alias. It is printed for each row returned. Literal strings of free-format text can be included in the query result and are treated the same as a column in the SELECT list.

Date and character literals must be enclosed within single quotation marks (' '); number literals need not be enclosed in a similar manner.

Using Literal Character Strings

SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

The above example displays the last names and job codes of all employees. The column has the heading Employee Details. Note the spaces between the single quotation marks in the SELECT statement. The spaces improve the readability of the output.

In the following example, the last name and salary for each employee are concatenated with a literal, to give the returned rows more meaning:

SELECT last_name ||': 1 Month salary = '||salary Monthly FROM employees;

Duplicate Rows

SELECT department_id

FROM employees;

Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows. The first example displays all the department numbers from the EMPLOYEES table. Note that the department numbers are repeated.

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. In the below example, the EMPLOYEES table actually contains 20 rows, but there are only seven unique department numbers in the table.

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.

SELECT DISTINCT department_id, job_id FROM employees;

Displaying the Table Structure

You can display the structure of a table by using the DESCRIBE command. The command displays the column names and the data types, and it shows you whether a column must contain data (that is, whether the column has a NOT NULL constraint).

In the syntax, table name is the name of any existing table, view, or synonym that is accessible to the user.

DESCRIBE EMPLOYEE