Basic SQL SELECT Statements.

SELECT Statement

Basic SELECT Statement

Selecting All Columns

Selecting Specific Columns

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.

Column Heading Defaults

• iSQL*Plus:
– Default heading justification: Center
– Default heading display: Uppercase
• SQL*Plus:
– Character and Date column headings are leftjustified
– Number column headings are right-justified
– Default heading display: Uppercase

Arithmetic Expressions

Create expressions with number and date data by using arithmetic operators.

Using Arithmetic Operators

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.

Using Parentheses

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.

Null Values in Arithmetic Expressions

Arithmetic expressions containing a null value evaluate to 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.

Using Column Aliases

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.

Using the Concatenation Operator

Literal Character Strings

• A literal value 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.

Using Literal Character Strings

Duplicate Rows

The default display of queries is all rows, including duplicate rows.

Eliminating Duplicate Rows

Eliminate duplicate rows by using the "DISTINCT" keyword in the "SELECT" clause.

SQL and iSQL*Plus Interaction

SQL Statements versus iSQL*Plus Commands

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 columnheadings
• Use the iSQL*Plus environment to write, save, and execute SQL statements and iSQL*Plus commands.