Select Statement:
The SELECT statement is used to retrieve data from one or
more:
Ø TABLES
Ø OBJECT TABLES
Ø VIEWS
Ø OBJECT VIEWS
Ø MATERIALIZED VIEWS
Prerequisites:
Ø The user must have the select
privileges on the specified object.
Ø The select ANY TABLE allows
selecting the data from any recognized object.
Basic SELECT Syntax:
SELECT [DISTINCT] [*] {column1 [Alias]…} FROM TABLE_NAME;
SELECT à Specifies a list of columns.
DISTINCT à Suppresses Duplicates.
* à Projection Operator to select all
columns from the table.
COLUMN à Selects the named column.
Alias à Gives selected columns alternate
column name.
FROM TABLE_NAME à Specifies the table containing the columns.
Retrieving Data from
all columns of a Table:
Ø For this purpose the projection
operator ‘*’ is used.
Ø The operator projects data from all
the columns existing in the table with all records.
Ex:
- SELECT * FROM EMP;
Retrieving Data from
specific columns:
Ex:
- SELECT Empno, Ename, Sal From
Emp;
- SELECT Deptno, Dname, Loc from
dept;
- SELECT Hisal, Lowsal, Grade
from Salgrade;
Note:
v The column names need not be in the
same order as table.
v The column should be separated using
comma.
v The casing of column names is not
important.
Column Heading Defaults:
Ø The default justification of the
data after it is retrieved from the table is …
o
LEFTà Date and Character data
o
RIGHTà Numeric data.
Ø The default display of the data is
always UPPER casing.
Ø The character and date column
headings can be truncated, but number columns cannot be truncated.
Suppressing Duplicate Rows in Output:
Ø Until it is instructed Sql * puls
displays the result of a query without eliminating duplicate rows.
Ø To eliminate the duplicate rows in
the result, the DISTINCT keyword is used.
Ø Multiple columns can be described
after the distinct qualifier.
Ø The DISTINCT qualifier affects all
the selected columns, and represents a DISTINCT combination of the columns.
Ex:
- SELECT DISTINCT Deptno From
Emp;
- SELECT DISTINCT MGR From Emp;
- SELECT DISTINCT Job, Deptno
From Emp;
- SELECT DISTINCT Deptno, Job From Emp;
Where Clause:
Ø Used to filtering of records.
Ø The number of rows returned by a
query can be limited using the where clause.
Ø A where clause contains a condition
that must be met and should directly follow the from clause.
Syntax:
SELECT [DISTINCT] [*] {column1
[Alias]…} FROM TABLE_NAME
[WHERE
Condition(s)];
Ø The WHERE clause can compare
o
Values
in columns
o
Literal
values
o
Arithmetic
Expressions
o
Functions
Ø The components of Where clause are
o
Column
name
o
Comparison
Operator
o
Column
Name (or) Constant (or) List of Values
Ø The Character strings and dates
should be enclosed in single quotation marks.
Ø Character values are case sensitive
and date values are format sensitive (DD-MON-YY).
Ex:
- SELECT Ename, Sal, Job From Emp
where job =’MANAGER’;
- SELECT Ename, Hiredate, Deptno,
Sal From Emp where Deptno=10;
- SELECT Empno, Ename, Sal,
Deptno From Emp where hiredate = ’01-Jan-95’;