SELECT Statement
A select statement is a combination of two or
more clauses.
Syntax:
SELECT *|{[DISTINCT] column
[alias],...}
FROM table;
·
SELECT tells the columns to be displayed .
·
FROM clause tells from which table the columns
to be selected.
·
DISTINCT - eliminates duplicates
·
alias - A different name to a column
Asterisk (*)
You can display all columns of data in a
table by following the SELECT keyword with an
asterisk (*).
Example:
The below example displays all the rows and columns in a table called
departements.
SELECT * FROM departments;
Selecting
Specific Columns
Specific columns can be selected by using SQL
select statement.
Example:
select department_id, department_name from
departments;
In the above example , out of all the columns
in a table departments only department_id,department_name are selected.
Columns are show in the order they are
selected.
SQL :
Rules and Guidelines
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
Add : +
Subtract : -
Multiply : *
Divide : /
Arithmetic
Operators
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.
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.
Examples :
SELECT last_name, salary, salary + 300 FROM
employees;
In the above example , 300 is added to the
salary of each employee in employees table.
SELECT last_name, salary, 12*(salary + 300) FROM
employees;
In the above example , salary of each
employee is added with 300 and finally multiplied by 12 to the annual salary of employee. Here parentheses () is used to override
default precedence.
SQL : Null
Value
Defining
a Null Value
A null value is undefined , unavailable,
unassigned, unknown. It is not equal to zero
or blank space . It is simply nothing.
In sql statement if any row dosent have data, then it is said to
be null.
Any arithmetic expression using NULL values
results into NULL.
Any column can have null data, but the
constraints NOT NULL and Primary key does not allow null values in its columns.
Column
Alias
SELECT last_name AS "Name" , salary*12 "Annual
Salary" FROM employees;
In the above example, Name and Annual salary
are alias to the columns last_name , salary*12 respectively.
AS keyword can be used as optional before the
alias name.
Concatenation
Operator
A concatenation operator:
• Links columns or character strings to other
columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a
character expression
Below is an example of the concatenation.
SELECT last_name||job_id AS
"Employees" FROM employees;
Literal Character
Strings
• A literal is a character, a number, or a date that is included
in the SELECT statement.
• Date and character literal values must be enclosed within
single quotation marks.
• Each character string is output once for
each row returned.
Example : In the below example 'is a' literal string used.
SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;
Alternative Quote (q)
Operator
• Specify your own quotation mark delimiter.
• Select any delimiter.
• Increase readability and usability.
You can choose any convenient delimiter, single-byte or multibyte, or any
of the following
character pairs: [ ], { }, ( ), or < >.
Example :
SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
DISTINCT
Keyword
To eliminate duplicate rows in the result,
include the DISTINCT keyword in the SELECT
clause immediately after the SELECT keyword.
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.
Example :
SELECT DISTINCT department_id, job_id FROM
employees;
DESCRIBE command
Use the DESCRIBE command to display the
structure of a
table.
DESC[RIBE] tablename
Example :
DESC employees;
DESCRIBE
departments;
