CLAUSES
- WHERE
- CONNECT BY
- START WITH
- GROUP BY
- HAVING
- ORDER BY
WHERE: We use where clause to filter the
table records. This
phenomena is called selection.
EG: SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO=10;
SELECT * FROM EMP WHERE
ENAME=’KING’;
SELECT * FROM EMP WHERE 1=1;
SELECT * FROM EMP WHERE LENGTH
(ENAME)=4;
SELECT * FROM EMP WHERE
10=DEPTNO;
SELECT * FROM EMP WHERE DEPTNO=DEPTNO;
SELECT * FROM EMP WHERE ENAME
LIKE ‘A%’;
- In where clause we can provide the conditions
- In where clause we can provide the ‘n’ no of conditions
- Separating with logical operator (and, or)
- Where clause won’t allow column aliases and group functions and lob columns.
----------------------------------------------------------------------------------------------------------------------
GROUP BY CLAUSE:
ü
It groups the same kind of data into segments
ü
All the select list normal columns, single row functions must be
in group by clause but reverse is not so.
EG:
SELECT C1,C2 FROM EMP GROUP BY C1,C2;
SELECT C1 FROM EMP GROUP BY C1,C2;
SELECT C1 FROM EMP GROUP BY
C1,C2(WONT ALLOWS);
ü
In
the presence of group by clause group functions are forced to execute for each
of the grouping sets.
EG: SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO,COUNT(*)
,SUM(SAL),MAX(SAL),MIN(SAL) FROM EMP
GROUP BY DEPTNO;
SELECT TO_CHAR(HIREDATE,’YY’),COUNT(*)
FROM EMP
GROUP BY TO_CHAR (HIREDATE,’YY’);
SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB;
SELECT DEPTNO,JOB,COUNT(*) FROM EMP
GROUP BY DEPTNO,JOB;
SELECT 1,JOB FROM EMP GROUP BY JOB;
SELECT 1,JOB FROM EMP GROUP BY JOB;
SELECT HIREDATE,COUNT(*) FROM EMP GROUP
BY HIREDATE;
SELECT DEPTNO||SAL FROM EMP GROUP BY
DEPTNO,SAL;
SELECT DEPTNO||SAL FROM EMP GROUP BY
DEPTNO||SAL;
NOTE: Without group by clause we can’t nest group
functions.
Maximum
we can nest two group functions in one another.
SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO;
SELECT MAX(MIN(SAL)) FROM EMP GROUP BY
DEPTNO;
---------------------------------------------------------------------------------------
HAVING CLAUSE:
It
filters the group by data.
Generally
we user having clause to provide group function condition.
Normally
we user having clause along with group by clause so to
get
meaning full data.
We
can also use the having clause very individually but it is not preferable.
It
won’t allow column aliases and analytical functions.
We
can also provide normal columns conditions in having clause but we have to see
that all the normal columns in having clause must be in group by clause and
select list.
EX: SELECT
SAL FROM EMP GROUP BY SAL HAVING
SAL>1000;
SELECT SAL FROM EMP GROUP BY SAL HAVING
DEPTNO=10;(*);
SELECT SAL FROM EMP GROUP BY SAL,DEPTNO
HAVING DPETNO=10;
SELECT COUNT(*) FROM EMP GROUP BY
DEPTNO HAVING
MAX(COUNT(*))>1;
We
can user the having clause very individually as shown in following examples
SELECT COUNT(*) FROM EMP HAVING
COUNT(*)>10;
----------------------------------------------------------------------------------------
ORDER BY CLAUSE:
It
displays the table data in one proper order that is either ascending or descending.
Order
by clause comes last in the list of clauses.
Order
by clause allows column aliases and analytical functions unlike other clauses.
SYN: SELECT ….FROM … ORDER BY COLUMNS|EXP|FUNCTIONS|COLUMN
POSITION [ASC|DESC] [NULLS
FIRST|NULLS LAST];
EG: SELECT * FROM EMP ORDER BY DEPTNO;
SELECT * FROM EMP ORDER BY DEPTNO DESC;
SELECT * FROM EMP ORDER BY DEPTNO
ASC;(DEFAULT);
SELECT * FROM EMP ORDER BY ENAME(ASCII);
SELECT * FROM EMP ORDER BY HIREDATE;
SELECT * FROM EMP ORDER BY DEPTNO,SAL;
SELECT * FROM EMP ORDER BY DEPTNO ASC,
SAL DESC;
SELECT * FROM EMP ORDER BY 6
(COLUMN
POSITION IN SELECT LIST);
SELECT * FROM EMP ORDER BY 6,2;
SELECT * FROM EMP ORDER BY COMM;
NOTE: NULLS ARE CONSIDERD AS HIGHER
VALUES
SELECT * FROM EMP ORDER BY COMM NULLS
FIRST;
SELECT * FROM EMP ORDER BY COMM DESC
NULLS LAST;
SELECT SAL, JOB FROM EMP ORDER BY 2;
SELECT SAL SALARY FROM EMP ORDER BY SALARY;
(ALIASE);
No comments:
Post a Comment