ad

Select and Where Clause

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’;