Selecting Rows
1
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć2
Schedule: Timing Topic
40 minutes Lecture
25 minutes Practice
65 minutes Total
Class Management Note:
Files required for lesson are:
Demonstration: l1prec1.sql, l1prec2.sql, l1alias.sql, l1null1.sql
Practice: None
Selecting Rows 1Ć3
Objectives
In order to extract data from the database you need to use the Structured Query
Language (SQL) SELECT command. You may need to restrict the columns that
are displayed. This lesson explains all of the commands you will use to perform
these actions.
You will want to create SELECT statements that can be used time and time
again. In this lesson you will also see how to save your statements for later use.
At the end of this lesson, you should be able to
D
Write a SELECT statement to query the database.
D
Perform arithmetic calculations using SQL arithmetic operators.
D
Handle null values.
D
Specify alternative column headings using aliases.
D
Concatenate columns.
D

Edit SQL statements in the SQL*Plus buffer and create command files.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć4
Selecting Rows 1Ć5
The Basic Query Block
A SELECT statement retrieves information from the database, implementing all
algebraic operators.
Syntax
SELECT [DISTINCT] {*,column [alias],....}
FROM table;
where: SELECT is a list of at least one column.
DISTINCT suppresses duplicates.
* selects all columns.
column selects the named column.
alias gives selected columns a different heading.
FROM table specifies the table containing the columns.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć6
Selecting Rows 1Ć7
The Basic Query Block
continued
Writing SQL Commands
By following these simple rules and guidelines, you will be able to construct valid
statements that are easy both to read and to edit.
D
SQL commands may be entered on one or many lines.
D
Clauses are usually placed on separate lines for readability and ease of editing.
D
Tabs and indents can be used to make code more readable.
D
Command words cannot be split across lines or abbreviated.

D
Keywords and commands typically are entered in uppercase; all other words,
such as table names and columns, are entered in lowercase.
D
SQL commands are not case sensitive, unless indicated.
D
An SQL command is entered at the SQL prompt, and subsequent lines are
numbered. This is called the SQL buffer.
D
Only one statement can be current at any time within the buffer, and the
statement can be executed in a number of ways:
D
Place a semicolon (;) at the end of last clause.
D
Place a semicolon or slash on the last line in the buffer.
D
Place a slash at the SQL prompt.
D
Issue a SQL*Plus RUN command at the SQL prompt.
For more information, see
Oracle Applications: Coding Standards, Release 10G.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć8
Simplest SELECT statement contains the
following two clauses:
Selecting Rows 1Ć9
The Basic Query Block
continued
In its simplest form, a SELECT statement must include the following:
D
A SELECT clause, which specifies the columns to be displayed.

D
A FROM clause, which specifies the table containing the columns listed in the
SELECT clause.
Selecting All Columns and Rows
The asterisk (*) selects all columns from the table.
Example
List all columns and all rows from the S_DEPT table.
SQL> SELECT *
2 FROM s_dept;
ID NAME REGION_ID
------- --------------- ---------
10 Finance 1
31 Sales 1
32 Sales 2
33 Sales 3
34 Sales 4
35 Sales 5
41 Operations 1
42 Operations 2
43 Operations 3
44 Operations 4
45 Operations 5
50 Administration 1
12 rows selected.
Class Management Note:
Let the students know that the details of all the tables are given in Appendix
B.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć10
Selecting Rows 1Ć11
The Basic Query Block

continued
Selecting Specific Columns
You restrict the query to display only certain columns by specifying the column
names, separated by commas, in the SELECT clause.
Example
Display all department numbers, employee last names, and manager numbers in the
S_EMP table.
SQL> SELECT dept_id, last_name, manager_id
2 FROM s_emp;
DEPT_ID LAST_NAME MANAGER_ID
------- ------------ ----------
50 Velasquez
41 Ngao 1
31 Nagayama 1
10 Quick-To-See 1
50 Ropeburn 1
41 Urguhart 2
42 Menchu 2
43 Biri 2
44 Catchpole 2
...
25 rows selected.
Specify the columns you want to see, in the order in which you want to see them, in
the SELECT clause. Do not forget to use the comma as a column name separator.
Column Heading Defaults
Character and date column headings and data are left-justified within a column and
numbers are right-justified. Character and date column headings may be truncated,
but number headings may not be truncated. The column labels appear in uppercase by
default. You can override the column label display with an alias.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć12

Selecting Rows 1Ć13
Arithmetic Expressions
You may need to modify the way data is displayed, perform calculations, or look at
what-if scenarios. This is possible using arithmetic expressions.
An arithmetic expression may contain column names, constant numeric values, and
the arithmetic operators.
Arithmetic Operators
These are the arithmetic operators available in SQL. You may use arithmetic
operators in any clause of a SQL statement except the FROM clause.
Operators
Description
+ Add
- Subtract
* Multiply
/ Divide
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć14
Selecting Rows 1Ć15
Arithmetic Expressions
continued
Example
Use the multiplication operator to display annual salary figures and their commission
percentage for all employees.
SQL> SELECT last_name, salary * 12, commission_pct
2 FROM s_emp;
LAST_NAME SALARY*12 COMMISSION_PCT
------------ ---------- --------------
...
Havel 15684
Magee 16800 10
Giljum 17880 12.5

Sedeghi 18180 10
Nguyen 18300 15
Dumas 17400 17.5
Maduro 16800
...
Notice that the resultant calculated column SALARY*12 is not a new column in the
originating table, but is for display only.
Class Management Note:
Let the students know that SQL*Plus ignores blank spaces before and after
the arithmetic operator. Formatting column values will be covered in later
lessons.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć16
Selecting Rows 1Ć17
Arithmetic Expressions
continued
Operator Precedence
If an arithmetic expression contains more than one operator, multiplication and
division are evaluated first. If operators within an expression are of the same priority,
then evaluation is from left to right.
Example
Display the last name, salary, and annual compensation of employees. Calculate the
annual compensation as 12 multiplied by the monthly salary, plus a one-time bonus of
$100.
SQL> SELECT last_name, salary, 12 * salary + 100
2 FROM s_emp;
LAST_NAME SALARY 12*SALARY+100
------------ ---------- -------------
Velasquez 2500 30100
Ngao 1450 17500
Nagayama 1400 16900

Quick-To-See 1450 17500
Ropeburn 1550 18700
Urguhart 1200 14500
...
Note: Use parentheses to reinforce the standard order of precedence and to improve
clarity. For example, the expression above can be written as (12 * salary) +
100 with no change in the result.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć18
Class Management Note:
DEMO: l1prec1.sql, l1prec2.sql
PURPOSE: l1prec1.sql demonstrates the previous page’s example with no
parentheses. Note the first couple rows of totals.
Then, execute demo file l1prec2.sql, which demonstrates using the
parentheses to override the rules of precedence. Note that the totals are
larger.
Selecting Rows 1Ć19
Arithmetic Expressions
continued
Override the rules of precedence with parentheses to specify the order in which
operators are executed.
Example
Display the last name, salary, and annual compensation of employees. Calculate the
annual compensation as monthly salary plus a monthly bonus of $100, multiplied by
12.
SQL> SELECT last_name, salary, 12 * (salary + 100)
2 FROM s_emp;
LAST_NAME SALARY 12*(SALARY+100)
------------ ---------- ---------------
Velasquez 2500 31200
Ngao 1450 18600

Nagayama 1400 18000
Quick-To-See 1450 18600
Ropeburn 1550 19800
Urguhart 1200 15600
...
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć20
Class Management Note:
DEMO: l1alias.sql.
PURPOSE: The column headings for each of the examples on the next
page. Demonstrate how to modify the file to demonstrate the other example.
Selecting Rows 1Ć21
Column Aliases
When displaying the result of a query, SQL*Plus normally uses the selected column’s
name as the heading. In many cases, that heading may be difficult to understand or
even meaningless. You can change a column’s 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 will be forced to uppercase and cannot contain blank spaces,
unless the alias is enclosed in double quotation marks (“ ”).
Example
Display the last name, salary, and annual compensation of employees. Calculate the
annual compensation as monthly salary plus a monthly bonus of $100, multiplied by
12. Name the column ANNUAL_SALARY.
SQL> SELECT last_name, salary,
2 12 * (salary + 100) AS ANNUAL_SALARY
3 FROM s_emp;
Note: You can include the AS keyword before the alias name to comply with ANSI
SQL 92 standards.
Column Aliases with Double Quotation Marks
If the alias contains spaces, special characters (such as # or $), or is case-sensitive,
enclose the alias in double quotation marks (“ ”).

SQL> SELECT last_name, salary,
2 12 * (salary + 100) ”Annual Salary”
3 FROM s_emp;
Class Management Note:
Within a SQL statement, a column alias can be used in both the SELECT
and the ORDER BY clauses. You cannot use column aliases in the WHERE
clause. Both alias features comply with the ANSI SQL 92 standard. You
can use this method of achieving mixed case column headings for now. In a
later section, you can format headings using the SQL*Plus COLUMN
command section.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć22
Selecting Rows 1Ć23
The 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 one single output column.
Example
Display the full names of the employees with the heading Employees.
SQL> SELECT first_name||last_name AS ”Employees”
2 FROM s_emp;
Employees
--------------------------------------------------
CarmenVelasquez
LaDorisNgao
MidoriNagayama
MarkQuick-To-See
AudryRopeburn
MollyUrguhart
...
The AS keyword before the alias name makes the SELECT clause easier to read.

Class Management Note:
The resulting column is VARCHAR2 datatype.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder1Ć24
Selecting Rows 1Ć25
Literal Character String
A literal is any character, expression, or number included in the SELECT list 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 like a
column in the SELECT list.
Date and character literals must be enclosed within single quotation marks (‘ ’);
number literals must not.
Example
Display the full names of the employees and their titles with the heading Employees.
Be sure to add punctuation.
SQL> SELECT first_name || ’ ’ || last_name
2 || ’, ’|| title ”Employees”
3 FROM s_emp;
Employees
---------------------------------
Carmen Velasquez, President
LaDoris Ngao, VP, Operations
Midori Nagayama, VP, Sales
Mark Quick-To-See, VP, Finance
Audry Ropeburn, VP, Administration
Molly Urguhart, Warehouse Manager
...
Class Management Note:
Point out to students that there is a blank space between single quotes in the
middle of first_name and last_name, in the select statement.