Read this Oracle Tutorial Point article to know how to use DISTINCT and WHERE clause and what are all conditions to follow while using these clauses in SQL in Oracle Database.
DISTINCT CLAUSE:
Until it is instructed SQL * Plus displays the results of a query while not eliminating duplicate rows. To eliminate the duplicate rows in the result the DISTINCT keyword is used. Multiple columns are declared next to the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns and describe a distinct combination of the columns.
Example:
WHERE CLAUSE:
The number of rows returned by a query can be limited/filter by using the WHERE clause. A WHERE clause contains a restriction that must be met and should directly follow the FROM clause.
Syntax:
The WHERE clause can compares,
Examples:
DISTINCT CLAUSE:
Until it is instructed SQL * Plus displays the results of a query while not eliminating duplicate rows. To eliminate the duplicate rows in the result the DISTINCT keyword is used. Multiple columns are declared next to the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns and describe a distinct combination of the columns.
Example:
SQL> SELECT DISTINCT DNO FROM EMP; SQL> SELECT DISTINCT JOB, DNO FROM EMP;
WHERE CLAUSE:
The number of rows returned by a query can be limited/filter by using the WHERE clause. A WHERE clause contains a restriction that must be met and should directly follow the FROM clause.
Syntax:
SELECT [DISTINCT] {*|Column1 [alias],Column2 [alias],....} FROM TABLE
[WHERE condition(s)];
The WHERE clause can compares,
- Value in columns
- Arithmetic Expressions
- Literal Values
- Functions
- Column Name
- Comparison Operator
- Column Name, Constants or list of values.
Examples:
SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE JOB='MANAGER'; SQL> SELECT ENAME,HIREDATE,DNO,SAL FROM EMP WHERE DNO=10; SQL> SELECT ENO,ENAME,SAL FROM EMP WHERE SAL>=5000; SQL> SELECT ENAME || 'JOINED On' || HIREDATE 'EMPLOYEES JOINING DATES' FROM EMP
WHERE HIREDATE='01-OCT-90'; SQL> SELECT ENAME || 'Works in Department' || DNO 'EMPLOYEES AND DEPARTMENTS' FROM EMP
WHERE DNO<>20; SQL> SELECT ENAME,SAL,DNO,JOB FROM EMP WHERE JOB <> 'CLERK';
Comments
Post a Comment