1
Writing Basic
SQL SELECT Statements
Copyright © Oracle Corporation, 2001. All rights
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
1-2 Copyright © Oracle Corporation, 2001. All rights
Capabilities of SQL SELECT Statements
Projection Selection
Table 1 Table 1
Join
Table 1 Table 2
1-3 Copyright © Oracle Corporation, 2001. All rights
Basic SELECT Statement
SELECT
SELECT *|{[DISTINCT]
*|{[DISTINCT] column|expression
column|expression [alias],...}
[alias],...}
FROM
FROM table;
table;
• SELECT identifies what columns
• FROM identifies which table
1-4 Copyright © Oracle Corporation, 2001. All rights
Selecting All Columns
SELECT *
FROM departments;
1-5 Copyright © Oracle Corporation, 2001. All rights
Selecting Specific Columns
SELECT department_id, location_id
FROM departments;
1-6 Copyright © Oracle Corporation, 2001. All rights
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.
1-7 Copyright © Oracle Corporation, 2001. All rights
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
1-8 Copyright © Oracle Corporation, 2001. All rights
Arithmetic Expressions
Create expressions with number and date data by
using arithmetic operators.
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
1-9 Copyright © Oracle Corporation, 2001. All rights
Using Arithmetic Operators
SELECT last_name, salary, salary + 300
FROM employees;
1-10 Copyright © Oracle Corporation, 2001. All rights
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.
1-11 Copyright © Oracle Corporation, 2001. All rights
Operator Precedence
SELECT last_name, salary, 12*salary+100
FROM employees;
1-12 Copyright © Oracle Corporation, 2001. All rights
Using Parentheses
SELECT last_name, salary, 12*(salary+100)
FROM employees;
1-13 Copyright © Oracle Corporation, 2001. All rights
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;
1-14 Copyright © Oracle Corporation, 2001. All rights
Null Values
in Arithmetic Expressions
Arithmetic expressions containing a null value
evaluate to null.
SELECT last_name, 12*salary*commission_pct
FROM employees;
1-15 Copyright © Oracle Corporation, 2001. All rights
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
1-16 Copyright © Oracle Corporation, 2001. All rights
Using Column Aliases
SELECT last_name AS name, commission_pct comm
FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;
1-17 Copyright © Oracle Corporation, 2001. All rights
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
1-18 Copyright © Oracle Corporation, 2001. All rights
Using the Concatenation Operator
SELECT last_name||job_id AS "Employees"
FROM employees;
1-19 Copyright © Oracle Corporation, 2001. All rights
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.
1-20 Copyright © Oracle Corporation, 2001. All rights
Using Literal Character Strings
SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;
1-21 Copyright © Oracle Corporation, 2001. All rights
Duplicate Rows
The default display of queries is all rows, including
duplicate rows.
SELECT
SELECT department_id
department_id
FROM
FROM employees;
employees;
1-22 Copyright © Oracle Corporation, 2001. All rights
Eliminating Duplicate Rows
Eliminate duplicate rows by using the DISTINCT
keyword in the SELECT clause.
SELECT DISTINCT department_id
FROM employees;
1-23 Copyright © Oracle Corporation, 2001. All rights
SQL and iSQL*Plus Interaction
SQL statements
iSQL*Plus Oracle
Internet server
Browser
iSQL*Plus Query results
commands
Formatted report
Client
1-24 Copyright © Oracle Corporation, 2001. All rights
SQL Statements Versus
iSQL*Plus Commands
SQL iSQL*Plus
• A language • An environment
• ANSI standard • Oracle proprietary
• Keyword cannot be • Keywords can be
abbreviated abbreviated
• Statements manipulate • Commands do not allow
data and table definitions manipulation of values in
in the database the database
• Runs on a browser
• Centrally loaded, does not
have to be implemented
on each machine
SQL iSQL*Plus
statements commands
1-25 Copyright © Oracle Corporation, 2001. All rights
Overview of iSQL*Plus
After you log into iSQL*Plus, you can:
• Describe the table structure
• Edit your SQL statement
• Execute SQL from iSQL*Plus
• Save SQL statements to files and append SQL
statements to files
• Execute statements stored in saved files
• Load commands from a text file into the iSQL*Plus
Edit window
1-26 Copyright © Oracle Corporation, 2001. All rights
Logging In to iSQL*Plus
From your Windows browser environment:
1-27 Copyright © Oracle Corporation, 2001. All rights
The iSQL*Plus Environment
10 8 9
1 7
2 3 4 5
1-28 Copyright © Oracle Corporation, 2001. All rights
Displaying Table Structure
Use the iSQL*Plus DESCRIBE command to display
the structure of a table.
DESC[RIBE]
DESC[RIBE] tablename
tablename
1-29 Copyright © Oracle Corporation, 2001. All rights
Displaying Table Structure
DESCRIBE
DESCRIBE employees
employees
1-30 Copyright © Oracle Corporation, 2001. All rights
Interacting with Script Files
SELECT last_name, hire_date, salary
FROM employees; 1
1-31 Copyright © Oracle Corporation, 2001. All rights
Interacting with Script Files
D:\temp\emp_sql.htm
SELECT last_name, hire_date, salary 2
FROM employees;
1-32 Copyright © Oracle Corporation, 2001. All rights
Interacting with Script Files
DESCRIBE employees
SELECT first_name, last_name, job_id 1
FROM employees;
3 2
1-33 Copyright © Oracle Corporation, 2001. All rights
Summary
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
• Use the iSQL*Plus environment to write, save, and
execute SQL statements and iSQL*Plus commands.
SELECT
SELECT *|{[DISTINCT]
*|{[DISTINCT] column|expression
column|expression [alias],...}
[alias],...}
FROM
FROM table;
table;
1-34 Copyright © Oracle Corporation, 2001. All rights
Practice 1 Overview
This practice covers the following topics:
• Selecting all data from different tables
• Describing the structure of tables
• Performing arithmetic calculations and specifying
column names
• Using iSQL*Plus
1-35 Copyright © Oracle Corporation, 2001. All rights
1-40 Copyright © Oracle Corporation, 2001. All rights
