Unknown

Oracle 11g -(Class 3)-Oracle 11g -SQL Statement



*****************Class 3************

sqlplus scott/*****



SQL> set pages 2000 lines 200



Group Function:

avg, sum,min, max, count,distinct,null

Group rows:

group by having

Nesting group functions:

max(sal) - group function

Combination of Colum and group by column I must use GROUP BY claus - all the single comumn(s), not group by column

select * ==gives all column. select * with WHERE == gives only the columns not all rows, subset.

Below Same as restrict data:

Where claus only pertains to single column

Having claus only pertains group function

**Group by for combination on above.



Order by accepts both Where & Having.

In Group By we can not use alias:

SQL > Select ename.Shaikat, max(sal) from emp;





group by ename(it has to be actual name,not alias);

select deptno, max(sal) from emp where deptno <> 20 group by deptno;

SQL> select deptno, max(sal) from emp

where deptno <> 20

group by deptno;

DEPTNO Max(Sal) 30 30000 10 5000

SQL> Select department_id, job_id, sum(salary)

From employees

Where department_id > 40

Group By department_id, job_id

Order By job_id;

DEPARTMENT_ID Job_ID Sum(Salary) 110 AC_ACCOUNT 8300 110 AC_MGR 12008 90 AD_PRES 38000 90 AD_VP 34000 100 FI_ACCOUNT 39600 100 FI_MGR 12008 60 IT_PROG 28800 70 PR_REP 10000 80 SA_MAN 61000 80 SA_REP 243500

Having from group function (NOT where claus):

select job_id, SUM(salary) PAYROLL From employees

Where job_id NOT LIKE '%REP%'

Group By job_id

Having SUM(salary) > 1300

Order By SUM(salary);

SQL> select job, SUM(sal) PAYROLL

FROM emp

where job NOT LIKE '%MAN%'

group by job

having SUM(sal) > 1300

order by SUM(sal);

Job Payroll CLERK 4150 PRESIDENT 5000 ANALYST 6000 IT 30000

SUBQUERY (When there's an unknown) :





Give all employee make more than SMITH





Ename Sal ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100

**Make more than smith and belongs to scott's department





SQL> select ename, deptno, sal from emp

where sal > (select sal from emp where ename = 'SMITH')

and deptno = (select deptno from emp where ename = 'SCOTT');

Ename Deptno Sal JONES 20 2975 SCOTT 20 3000 ADAMS 20 1100 FORD 20 3000

****************Thank You********************