Writing Basic 

SQL SELECT Statements

Objectives

After completing this lesson, you should be able to 

do the following:

• List the capabilities of SQL SELECT statements

• Execute a basic SELECT statement

• Differentiate between SQL statements and 

iSQL*Plus commands

Lesson Aim

To extract data from the database, you need to use the structured query language (SQL) SELECT

statement. You may need to restrict the columns that are displayed. This lesson describes all the SQL 

statements needed to perform these actions.

You may want to create SELECT statements that can be used more than once. This lesson also covers 

the iSQL*Plus environment where you execute SQL statements.

Capabilities of SQL SELECT Statements

A SELECT statement retrieves information from the database. Using a SELECT statement, you can do 

the following:

             • Projection: You can use the projection capability in SQL to choose the columns in a table that 

you want returned by your query. You can choose as few or as many columns of the table as 

you require. 

             • Selection: You can use the selection capability in SQL to choose the rows in a table that you 

want returned by a query. You can use various criteria to restrict the rows that you see.

             • Joining: You can use the join capability in SQL to bring together data that is stored in different 

tables by creating a link between them. You learn more about joins in a later lesson.

Basic SELECT Statement

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

FROM table;

• SELECT identifies what columns

• FROM identifies which table

Basic SELECT Statement

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 specifies the table containing the columns 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 selected columns different headings

FROM table specifies the table containing the columns

Note: Throughout this course, 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, ... 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;

Selecting All Columns of All Rows

You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). 

In the example on the slide, the department table contains four columns: DEPARTMENT_ID, 

DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains seven 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, like the example on the slide, 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;

Selecting Specific Columns of All Rows

You can use the SELECT statement to display specific columns of the table by specifying the column 

names, separated by commas. The example on the slide 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 going from left to 

right, you use the following statement:

SELECT location_id, department_id

FROM   departments;

Instructor Note 

You can also select from pseudocolumns. A pseudocolumn behaves like a table column but is not 

actually stored in the table. You cannot insert or delete values of the pseudocolumns. Some available 

pseudocolumns are CURRVAL, NEXTVAL, LEVEL, ROWID, and ROWNUM.

Writing SQL Statements

• SQL statements are not case sensitive. 

• SQL statements can be on one or more lines.

• Keywords cannot be abbreviated or split

across lines.

• Clauses are usually placed on separate lines.

• Indents are used to enhance readability.

Writing SQL Statements

Using the following simple rules and guidelines, you can construct valid statements that are both easy 

to read and easy to 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, 

are entered in lowercase.

Executing SQL Statements

Using iSQL*Plus, click the Execute button to run the command or commands in the editing

window.

Column Heading Defaults

• iSQL*Plus:

– Default heading justification: Center

– Default heading display: Uppercase

• SQL*Plus:

– Character and Date column headings are left-

justified

– Number column headings are right-justified

– Default heading display: Uppercase

In iSQL*Plus, column headings are displayed in uppercase and centered. 

SELECT last_name, hire_date, salary

FROM   employees;

Arithmetic Expressions

Create expressions with number and date data by 

using arithmetic operators.

Operator Description   

+             Add

-              Subtract 

*             Multiply 

/              Divide 

Arithmetic Expressions

You may need to modify the way in which data is displayed, perform calculations, or look at what-if 

scenarios. These are all possible using arithmetic expressions. An arithmetic expression can contain 

column names, constant numeric values, and the arithmetic operators.

Arithmetic Operators

The slide lists the arithmetic operators available in SQL. You can use arithmetic operators in any 

clause of a SQL statement except in the FROM clause. 

Using Arithmetic Operators

The example in the slide uses the addition operator to calculate a salary increase of $300 for all 

employees and displays a new 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.

Operator Precedence

*  /  + -

• Multiplication and division take priority over 

addition and subtraction.

• Operators of the same priority are evaluated from 

left to right.

• Parentheses are used to force prioritized 

evaluation and to clarify statements.

Operator Precedence
If an arithmetic expression contains more than one operator, multiplication and division are evaluated
first. If operators within an expression are of same priority, then evaluation is done from left to right.
You can use parentheses to force the expression within parentheses to be evaluated first.

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

Operator Precedence (continued)
The example on the slide displays the last name, salary, and annual compensation of employees. It
calculates the annual compensation as 12 multiplied by the monthly salary, plus a one-time bonus of
$100. Notice that multiplication is performed before addition.

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

Using Parentheses
You can override the rules of precedence by using parentheses to specify the order in which operators
are executed.
The example on the slide displays the last name, salary, and annual compensation of employees. It
calculates the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12.
Because of the parentheses, addition takes priority over multiplication.


Defining a Null Value
• A null is a value that is unavailable, unassigned,
unknown, or inapplicable.
• A null is not the same as zero or a blank space.

SELECT last_name, job_id, salary, commission_pct
FROM employees;


                            


Null Values
If a row lacks the data value for a particular column, that value is said to be null, or to contain a null.
A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as
zero or a space. Zero is a number, and a 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

Arithmetic expressions containing a null value
evaluate to null.

SELECT last_name, 12*salary*commission_pct
FROM employees;


Null Values (continued)
If any column value in an arithmetic expression is null, the result is null. For example, if you attempt
to perform division with zero, you get an error. However, if you divide a number by null, the result is
a null or unknown.
In the example on the slide, employee King does not get any commission. Because the

COMMISSION_PCT column in the arithmetic expression is null, the result is null.

Defining a Column Alias

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 is case sensitive


Column Aliases
When displaying the result of a query, iSQL*Plus normally uses the name of the selected column as
the column heading. This heading may not be descriptive and hence may be difficult to understand.
You can change a column heading by using a column alias.
Specify the alias after the column in the SELECT list using a space as a separator. By default, alias
headings appear in uppercase. If the alias contains spaces or special characters (such as # or $), or is
case sensitive, enclose the alias in double quotation marks (" ").

Using Column Aliases

SELECT last_name AS name, commission_pct comm
FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary"

FROM employees;

Column Aliases (continued)

The first example displays the names and the commission percentages of all the employees. Notice

that the optional AS keyword has been used before the column alias name. The result of the query is

the same whether the AS keyword is used or not. Also notice that the SQL statement has the column

aliases, name and comm, in lowercase, whereas the result of the query displays the column headings

in uppercase. As mentioned in a previous slide, column headings appear in uppercase by default.

The second example displays the last names and annual salaries of all the employees. Because

Annual Salary contain a space, it has been enclosed in double quotation marks. Notice that the

column heading in the output is exactly the same as the column alias.

Concatenation Operator

A concatenation operator:
• Concatenates columns or character strings to
other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character

expression


Concatenation Operator
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.

Using the Concatenation Operator

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

Concatenation Operator (continued)
In the example, LAST_NAME and JOB_ID are concatenated, and they are given the alias
Employees. Notice that the employee last name and job code are combined to make a single output
column.
The AS keyword before the alias name makes the SELECT clause easier to read.


Literal Character Strings
• A literal is a character, a number, or a date
included in the SELECT list.
• Date and character literal values must be enclosed
within single quotation marks.
• Each character string is output once for each
row returned.


Literal Character Strings
A literal is a character, a number, or a date that is included in the SELECT list and that 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.

Using Literal Character Strings

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

Literal Character Strings (continued)
The example on the slide displays last names and job codes of all employees. The column has the
heading Employee Details. Notice 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

The default display of queries is all rows, including

duplicate rows.

SELECT department_id
FROM employees;

Eliminating Duplicate Rows

Eliminate duplicate rows by using the DISTINCT

keyword in the SELECT clause.

SELECT DISTINCT department_id

FROM employees;

In this lesson, you should have learned how to:

• Write a SELECT statement that:

– Returns all rows and columns from a table

– Returns specified columns from a table

– Uses column aliases to give descriptive column

headings