Oracle DB for system administrators, part6

26. Next I want to see all employees, having salary over average. This will be done using subquery. The idea is to create some selection from table and use the result as source for other select
SQL> SELECT fname, lname, sal FROM hr WHERE sal > (SELECT AVG(sal) FROM hr);

FNAME           LNAME                  SAL
--------------- --------------- ----------
Michael         Hartstein            13000
Susan           Mavris                6500
Hermann         Baer                 10000
Shelley         Higgins              12008
... snip ...
Alyssa          Hutton                8800
Jonathon        Taylor                8600
Jack            Livingston            8400

50 rows selected.

As you see the subquery is placed within parentheses. You can use 255 levels ob subquery which is very big number for any kind of data.

27. I want to see the list of departments having average salary under maximum of average salary for departments
SQL> SELECT deptno, AVG(sal) FROM hr GROUP BY deptno HAVING AVG(sal) < (SELECT MAX(AVG(sal)) FROM hr GROUP BY deptno) ORDER BY deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        10       4400
        20       9500
        30       4150
        40       6500
        50 3475.55556
        60       5760
        70      10000
        80 8955.88235
       100 8601.33333
       110      10154

10 rows selected.

and to be sure let see the maximum average salary of department.

SQL>  select max(avg(sal)) from hr group by deptno;

MAX(AVG(SAL))
-------------
   19333.3333
I you can see i use HAVING clause to filter group of rows. It is used because I mix multirow select (SELECT deptno) with single row function (AVG(sal)). If you try w/o this clause you will receive error
Wrong:
 SQL>  select deptno,  avg(sal)  from hr   where avg(sal) >10000  group by deptno;
 select deptno,  avg(sal)  from hr   where avg(sal) >10000  group by deptno
                                           *
ERROR at line 1:
ORA-00934: group function is not allowed here
Correct:
SQL> select deptno,  avg(sal)  from hr  group by deptno  having avg(sal) > 10000;

    DEPTNO   AVG(SAL)
---------- ----------
        90 19333.3333
       110      10154

29. Next I will try to use wildcard to select only people having TH combination of symbols in first name
SQL> select fname, lname from hr where fname like '%th%';

FNAME           LNAME
--------------- ---------------
Matthew         Weiss
Sarath          Sewall
Elizabeth       Bates
Jonathon        Taylor
Martha          Sullivan
Anthony         Cabrio
Timothy         Gates

7 rows selected.

SQL>

30. And as last from the points of retrieve information i want to get average salary by department plus average for the company
SQL>  SELECT deptno, to_char(avg(sal), 99999.99) FROM hr GROUP BY ROLLUP(deptno) ORDER BY deptno;

    DEPTNO TO_CHAR(A
---------- ---------
        10   4400.00
        20   9500.00
        30   4150.00
        40   6500.00
        50   3475.56
        60   5760.00
        70  10000.00
        80   8955.88
        90  19333.33
       100   8601.33
       110  10154.00
             6456.75

12 rows selected.
As you see the last line have no department number and actually is average salary for entire company