SQL Basics - 3

Group By Clause

The Group By clause is used to group rows based on distinct values that exist for specified columns. The group by clause creates a data set containing several sets of records grouped together based on a condition

select AAA.JOB , aaa.ename, aaa.empno from EMP aaa where AAA.JOB is not null group by AAA.JOB , aaa.ename, aaa.empno;

This works both in Oracle and DB2. Its essential that the order of columns in select should have the same order in the group by clause

Having Clause

The Having clause is used in conjunction with the group by clause . It adds more filter into the group by . Having is different than the where clause as it cant be used without the group by. Also many functions like count, avg cannot be used in where clause whereas these functions can be used in the having clause

select AAA.JOB , aaa.ename, aaa.empno from EMP aaa where AAA.JOB is not null group by AAA.JOB , aaa.ename, aaa.empno having AAA.EMPNO>3000 ;

works both in Oracle and DB2. The Having clause can also work similar to the distinct clause , where the distinct eliminates duplicates but doesnot show which records where duplicated

the having clause can identify which values where unique or non unique. The following query can select customers with more than one account no

select cusno from accnts group by cusno having count(accnt_no) >1

SUBQUERIES

Subqueries are used within a query for select purposes, eg

select * from HR.EMPLOYEES where HR.EMPLOYEES.DEPARTMENT_ID in (select HR.DEPARTMENTS.DEPARTMENT_ID from HR.DEPARTMENTS where HR.DEPARTMENTS.LOCATION_ID=1700)

In the the above example all the records from the employee table is selected where the department id is those departments which have location id is 1700 in the department table

multiple column sub queries is also possible

select fname, lname from cust_mstr where (fname,lname) in (select fname,lname from emp_mstr)

Sub queries can also be used in the from clause . This represents a data source from that particular select statement.

select A.accnt_no, A.curbal, A.branch_no, B.avgbal from accnt_mstr A, (select branch_no avg(curbal) avgbal from accnt_mstr) B where A.branch_no= B.branch_no and A.curbal> B.avgBal

Sub Queries ca also be used in the order by clause

Exists/ Not Exists

The exists clause is similar to the in clause except the way it is executed