List the employees whose salary is higher than the average salary with self join : HAVING « Query Select « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;

Table created.

SQL>
SQL>
SQL> insert into employees values(1,'Jason',  'N',  'TRAINER', 2,   date '1965-12-18',  800 , NULL,  10);

1 row created.

SQL> insert into employees values(2,'Jerry',  'J',  'SALESREP',3,   date '1966-11-19',  1600, 300,   10);

1 row created.

SQL> insert into employees values(3,'Jord',   'T' , 'SALESREP',4,   date '1967-10-21',  1700, 500,   20);

1 row created.

SQL> insert into employees values(4,'Mary',   'J',  'MANAGER', 5,   date '1968-09-22',  1800, NULL,  20);

1 row created.

SQL> insert into employees values(5,'Joe',    'P',  'SALESREP',6,   date '1969-08-23',  1900, 1400,  30);

1 row created.

SQL> insert into employees values(6,'Black',  'R',  'MANAGER', 7,   date '1970-07-24',  2000, NULL,  30);

1 row created.

SQL> insert into employees values(7,'Red',    'A',  'MANAGER', 8,   date '1971-06-25',  2100, NULL,  40);

1 row created.

SQL> insert into employees values(8,'White',  'S',  'TRAINER', 9,   date '1972-05-26',  2200, NULL,  40);

1 row created.

SQL> insert into employees values(9,'Yellow', 'C',  'DIRECTOR',10,  date '1973-04-27',  2300, NULL,  20);

1 row created.

SQL> insert into employees values(10,'Pink',  'J',  'SALESREP',null,date '1974-03-28',  2400, 0,     30);

1 row created.

SQL>
SQL> select   e1.empno
  2  from     employees e1
  3  ,        employees e2
  4  group by e1.empno
  5  ,        e1.msal
  6  having   e1.msal > avg(e2.msal);

     EMPNO
----------
         7
         8
         9
        10
         6
         5

6 rows selected.

SQL>
SQL>
SQL> drop table employees;

Table dropped.

SQL>








2.6.HAVING
2.6.1.Using the HAVING Clause to Filter Groups of Rows
2.6.2.Use Having
2.6.3.Using the WHERE, GROUP BY, and HAVING Clauses Together
2.6.4.Adds an ORDER BY clause
2.6.5.In what city do we have the most employees?
2.6.6.Display department number and its employee count if it has more than 4 employees
2.6.7.List department who has more than 4 employee born after 1960
2.6.8.List the employees whose salary is higher than the average salary with self join