1.Group by guidelines: 1)Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.
2) You cannot use the WHERE clause to restrict groups.You use the HAVING clause to restrict groups.
3) Using the GROUP BY Clause on Multiple Columns
example:
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
SQL> SELECT deptno, job, sum(sal)
2 FROM emp
3 GROUP BY deptno, job;
SQL> SELECT job, SUM(sal) PAYROLL
2 FROM emp
3 WHERE job NOT LIKE 'SALES%'
4 GROUP BY job
5 HAVING SUM(sal)>5000
6 ORDER BY SUM(sal);
2.Guidelines for Using Subqueries:
1)Enclose subqueries in parentheses.
2)Place subqueries on the right side of the comparison operator.
3)Do not add an ORDER BY clause to a subquery.
4)Use single-row operators with single-row subqueries.
5)Use multiple-row operators with multiple-row subqueries.
3. Types of Subqueries
1) Single-row subqueries: Queries that return only one row from the inner SELECT statement
Operator: = > >= < <= <>
SQL> SELECT ename, job
2 FROM emp
3 WHERE job =
4 (SELECT job
5 FROM emp
6 WHERE empno = 7369)
7 AND sal >
8 (SELECT sal
9 FROM emp
10 WHERE empno = 7876);
ENAME JOB
---------- ---------
MILLER CLERK
The
Oracle Server executes subqueries first.
The Oracle Server returns results into the main query’s HAVING clause.
SQL> SELECT deptno, MIN(sal)
2 FROM emp
3 GROUP BY deptno
4 HAVING MIN(sal) >
5 (SELECT MIN(sal)
6 FROM emp
7 WHERE deptno = 20);
2)Multiple-row subqueries: Queries that return more than one row from the inner SELECT statement
Operator: IN,ANy,ALL
SQL> SELECT empno, ename, job
2 FROM emp
3 WHERE sal < ANY
4 (SELECT sal
5 FROM emp
6 WHERE job = 'CLERK')
7 AND job <> 'CLERK';
3)Multiple-column subqueries: Queries that return more than one column from the inner SELECT statement
