Sub Queries, GROUP BY Queries, CUBE, ROLLUP,
WITH Clause, CASE Expression in Select Statement

SUBQUERIES

A query nested within a query is known as subquery.

For example, you want to see all the employees whose salary is above average salary. For this you have to first compute the average salary using AVG function and then compare employees salaries with this computed salary. This is possible using subquery. Here the sub query will first compute the average salary and then main query will execute.

Select * from emp where sal > (select avg(sal) from emp);

Similarly we want to see the name and empno of that employee whose salary is maximum.

Select * from emp where sal = (select max(sal) from emp);

To see second maximum salary

Select max(sal) from emp where

       sal < (select max(sal) from emp);

Similarly to see the Third highest salary.

Select max(sal) from emp where

        sal < (select max(sal) from emp Where

               sal < (select max(sal) from emp));

We want to see how many employees are there whose salary is above average.

Select count(*) from emp where

      sal > (select max(sal) from emp);

We want to see those employees who are working in Hyderabad. Remember emp and dept are joined on deptno and city column is in the dept table. Assuming that wherever the department is located the employee is working in that city.

Select * from emp where deptno

      in (select deptno from dept where city=’HYD’);

You can also use subquery in FROM clause of SELECT statement.

For example the following query returns the top 5 salaries from employees table.

Select sal from (select sal from emp order sal desc)

             where rownum <= 5;

To see the sum salary deptwise you can give the following query.

Select sum(sal) from emp group by deptno;

Now to see the average total salary deptwise you can give a sub query in FROM clause.

select  avg(depttotal) from (select sum(sal) as depttotal from emp group by deptno);

WITH

The above average total salary department wise can also be achieved in 9i using WITH clause given below

WITH

  DEPTOT AS (select sum(sal) as dsal from emp

                group by deptno)

  select avg(dsal) from deptot;

GROUP BY QUERIES

You can group query results on some column values. When you give a SELECT statement without group by clause then all the resultant rows are treated as a single group.

For Example, we want to see the sum salary of all employees dept wise. Then the following query will achieved the result

Select deptno,sum(sal) from emp group by deptno;

Similarly we want to see the average salary dept wise

Select deptno,avg(sal) from emp group by deptno;

Similarly we want to see the maximum salary in each department.

Select deptno,max(sal) from emp group by deptno;

Similarly the minimum salary.

Select deptno,min(sal) from emp group by deptno;

Now we want to see the number of employees working in each department.

Select deptno,count(*) from emp group by deptno;

Now we want to see total salary department wise where the dept wise total salary is above 5000.

For this you have to use HAVING clause. Remember HAVING clause is used to filter groups and WHERE clause is used to filter rows. You cannot use WHERE clause to filter groups.

select deptno,sum(sal) from emp group by deptno

                  having sum(sal) >= 5000;

We want to see those departments  and the number of employees working in them where the number of employees is more than 2.

Select deptno, count(*) from emp group by deptno

                        Having count(*) >=2;

Instead of displaying deptno you can also display deptnames  by using join conditions.

For example we want to see deptname and average salary of them.

Select dname,avg(sal) from emp,dept

           where emp.deptno=dept.deptno group by dname;

Similarly to see sum of sal.

Select dname,sum(sal) from emp,dept

   where emp.deptno=dept.deptno group by dname;

Now we want to see the cities name and the no of employees working in each city. Remember emp and dept are joined on deptno and city column is in the dept table. Assuming that wherever the department is located the employee is working in that city.

Select dept.city,count(empno) from emp,dept

       where emp.deptno=dept.deptno

             Group by dept.city;

ROLLUP

The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM, ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates.

For example, given three expressions (n=3) in the ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.

Rows grouped on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.

The following query uses rollup operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales

     group by rollup(prod,year);

CUBE

The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.

For example, given three expressions (n=3) in the CUBE clause of the simple_grouping_clause, the operation results in 2ⁿ = 2³ = 8 groupings. Rows grouped on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows.

The following query uses CUBE operation to show sales amount product wise and year wise. To see the structure of the sales table refer to appendices.

Select prod,year,sum(amt) from sales

     group by CUBE(prod,year);

CASE EXPRESSION

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.

For example the following query uses CASE expression to display Department Names based on deptno.

Select empno,ename,sal,CASE deptno when 10 then ‘Accounts’

                    When 20 then ‘Sales’

                    When 30 then ‘R&D’

                    Else “Unknown’ end

            From emp;

The following statement finds the average salary of the employees in the employees table using $2000 as the lowest salary possible:

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal

   ELSE 2000 END) "Average Salary" from emp e;