Suppressing Duplicate Rows In Output :

• Until it is instructed sql * plus displays the results of a query without eliminating duplicate rows.
• To eliminate the duplicate rows in the results, the DISTINCT keyword is used.
• Multiple columns can be declared after the distinct qualifier.
• The DISTINCT qualifier affects all the selected columns, and represents a DISTINCT combination of the columns.

Examples:

SELECT DISTINCT Deptno FROM Emp;

SELECT DISTINCT Mgr FROM Emp;

SELECT DISTINCT Job, Deptno FROM Emp;

SELECT DISTINCT Deptno, Job FROM Emp;

• Filtering of records.
• The Numbers 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] [*] {COLUMN 1 [ALIAS], COLUMN 2 [ALIAS],......} FROM TABLE_NAME
[WHERE CONDITION(S)];

The WHERE clause can compare

• Values in columns
• Literal values
• Arithmetic expressions
• Functions

The components of WHERE clause are

• Column name
• Comparison operator
• Column name or constant or list of values

The CHARACTER stings and DATES should be enclosed in single Quotation marks.
CHARACTER values are case sensitive and DATE values are format sensitive (DD-MM-YY).
The Comparison operators are used in shuch condition that compare one expression to another.
The different comparison operators are

• Equality operator ( = )
• Not equality operator ( <>, !=, ^= )
• Greater then operator ( > )
• Less than operator ( < )
• Greater than or equal to operator ( >= )
• Less than or equal to operator ( <= )

The format of the where clause is
WHERE Expr OPERATOR VALUE

SELECT Ename, Sal, Job
FROM Emp
WHERE Job = 'MANAGER';

SELECT Ename, Hiredate, Deptno, Sal
FROM Emp
WHERE Deptno=10;

SELECT Empno, Ename, Sal
FROM Emp
WHERE Sal >= 3000;

SELECT
Ename||' joined on '|| Hiredate "Employees joining dates"
FROM Emp
WHERE Hiredate = '01-JAN-81';

SELECT
Ename|| ' works in department' ||Deptno "employees and departements "
FROM Deptno
WHERE Deptno <> 20;

SELECT Ename, Sal, Deptno, Job
FROM Emp
WHERE Job <>'CLERK';

SELECT Ename Name, Sal Basic, Sal * 12 Annual
FROM Emp
WHERE Sal * 12 >45000;