Compute Group Salary

---

-- DATA SETUP --

create table emp_t(empno number,
                   ename varchar2(100),
                   mgr   number,
                   sal   number);

Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7839,'KING',null,1000);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7698,'BLAKE',7839,700);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7782,'CLARK',7839,500);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7566,'JONES',7839,800);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7788,'SCOTT',7566,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7902,'FORD',7566,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7369,'SMITH',7902,75);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7499,'ALLEN',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7521,'WARD',7698,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7654,'MARTIN',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7844,'TURNER',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7876,'ADAMS',7788,50);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7900,'JAMES',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7934,'MILLER',7782,300);

commit;

---

-- Query 1 --

select sum(sal)
from EMP_T
start with ENAME = 'FORD'
connect by PRIOR empno=mgr;

select EMPNO, ENAME, MGR, SAL,(select sum(sal)
                                from EMP_T
                                start with ENAME = a.ENAME
                                connect by PRIOR empno=mgr) group_sal
from emp_t a;

---

-- Query 2 --

CREATE OR REPLACE FUNCTION get_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
/
select EMPNO, ENAME, MGR, SAL,get_group_sal(a.ename) group_sal
from emp_t a;

---

-- Query 3 --

with FUNCTION fn_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
select EMPNO, ENAME, MGR, SAL,fn_group_sal(ename) group_sal
from emp_t a;

---

Query to Extract Names from mail id

---

---

create table tab1(mail_id varchar2(50));

insert into tab1 values('siva.k.academy@gmail.com');
insert into tab1 values('jeevan.anand@yahoo.com');
insert into tab1 values('ravi@outlook.com');

commit;

select * from tab1;


with d1 as (select mail_id m, 
       substr(mail_id,1, instr(mail_id,'@')-1) n,
       substr(mail_id, instr(mail_id,'@')+1) d
from tab1),
d2 as (select m,d,n,
       instr(n,'.',1,1) f_dot ,
       instr(n,'.',1,2) s_dot 
from d1),
d3 as (select m,d,n,f_dot,s_dot, 
       substr(n,1,decode(f_dot,0,length(n),f_dot-1)) f_name,
       decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1)) m_name,
       decode(f_dot+s_dot,0,null,substr(n,decode(s_dot,0,f_dot,s_dot)+1)) l_name
from d2)
select m,f_name,m_name,l_name
from d3;

---

Two dimensional array in PLSQL using VARRAY

Example to understand "Two dimensional array in PLSQL using VARRAY"

---

SET SERVEROUTPUT ON
DECLARE
    TYPE matrix_elem_type IS  VARRAY ( 3 ) OF NUMBER;
    TYPE matrix_type IS       VARRAY ( 3 ) OF matrix_elem_type;
    lv_matrix1       matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix2       matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix3_total matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix_elem   matrix_elem_type := matrix_elem_type(NULL,NULL,NULL);
    -- Local Procedure to print the array
    procedure print_array(pin_array matrix_type,pin_desc varchar2) as
    begin
    dbms_output.put_line('Printing the '||pin_desc||' ....');
    for i in pin_array.first..pin_array.last loop
        for j in pin_array(i).first..pin_array(i).last loop
           dbms_output.put(pin_array(i)(j)||'   ');
        end loop;
        dbms_output.put_line('');
    end loop;
    end;
BEGIN
    -- Assing First Array
    lv_matrix_elem := matrix_elem_type(1,2,3);
    lv_matrix1(1) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(4,5,6);
    lv_matrix1(2) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(7,8,9);
    lv_matrix1(3) := lv_matrix_elem;
    
    -- Printing the first array
    print_array(lv_matrix1,'First Array');
    
    -- Assing Second Array
    lv_matrix_elem := matrix_elem_type(11,12,13);
    lv_matrix2(1) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(14,15,16);
    lv_matrix2(2) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(17,18,19);
    lv_matrix2(3) := lv_matrix_elem;
    
    -- Printing the Second array
    print_array(lv_matrix2,'Second Array');    
    
    -- Add Array1+Array2
    
    for i in lv_matrix1.first..lv_matrix1.last loop
        for j in lv_matrix1(i).first..lv_matrix1(i).last loop
        lv_matrix_elem(j) := lv_matrix1(i)(j) + lv_matrix2(i)(j);
        end loop;
        lv_matrix3_total(i) := lv_matrix_elem;
    end loop;
    
    --Print Total Array
    print_array(lv_matrix3_total,'Total Array');    
END;

How to get unique records without using “DISTINCT” in oracle

drop table Student_list;
create table Student_list(roll_no number, stu_name varchar2(30));

Insert into Student_list (ROLL_NO,stu_name) values (1,'KING');
Insert into Student_list (ROLL_NO,stu_name) values (2,'BLAKE');
Insert into Student_list (ROLL_NO,stu_name) values (3,'CLARK');
Insert into Student_list (ROLL_NO,stu_name) values (4,'JONES');
Insert into Student_list (ROLL_NO,stu_name) values (5,'SCOTT');
Insert into Student_list (ROLL_NO,stu_name) values (6,'RAGHU');
Insert into Student_list (ROLL_NO,stu_name) values (7,'FORD');
Insert into Student_list (ROLL_NO,stu_name) values (8,'SMITH');
Insert into Student_list (ROLL_NO,stu_name) values (9,'ALLEN');
Insert into Student_list (ROLL_NO,stu_name) values (10,'WARD');
Insert into Student_list (ROLL_NO,stu_name) values (1,'KING');
Insert into Student_list (ROLL_NO,stu_name) values (5,'SCOTT');
Insert into Student_list (ROLL_NO,stu_name) values (7,'FORD');

COMMIT;

---

select * from Student_list
order by roll_no,stu_name;

---

SELECT DISTINCT ROLL_NO,STU_NAME
FROM STUDENT_LIST
ORDER BY ROLL_NO,STU_NAME;

---

SELECT UNIQUE ROLL_NO,STU_NAME
FROM STUDENT_LIST
ORDER BY ROLL_NO,STU_NAME;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
GROUP BY ROLL_NO,STU_NAME
ORDER BY ROLL_NO,STU_NAME;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
UNION 
SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
UNION 
SELECT NULL,NULL
FROM DUAL WHERE 1=2;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
INTERSECT
SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST;

---

SELECT ROLL_NO,STU_NAME
FROM STUDENT_LIST
MINUS
SELECT NULL,NULL
FROM DUAL;

---

SELECT ROLL_NO,STU_NAME FROM (
SELECT ROLL_NO,STU_NAME,
    ROW_NUMBER() over(partition by ROLL_NO,STU_NAME 
                      ORDER BY ROLL_NO,STU_NAME) R
FROM STUDENT_LIST)
where R=1;

---

SELECT ROLL_NO,STU_NAME FROM (
SELECT ROLL_NO,STU_NAME,
    RANK() over(partition by ROLL_NO,STU_NAME 
                ORDER BY ROWNUM) R
FROM STUDENT_LIST)
where R=1;

---

SELECT * FROM STUDENT_LIST
WHERE ROWID IN (
SELECT MIN(ROWID)
FROM STUDENT_LIST
GROUP BY ROLL_NO,STU_NAME)
ORDER BY ROLL_NO,STU_NAME;

---

SELECt * FROM STUDENT_LIST A
WHERE 1 = ( SELECT COUNT(1) 
            FROM STUDENT_LIST B 
            WHERE A.ROLL_NO = B.ROLL_NO
            AND A.STU_NAME = B.STU_NAME
            AND A.ROWID >= B.ROWID);

---

"Accessible by" clause

1. "Accessible by" clause is used to declare a particular unit may be referenced only by given list of units.
2. Anonymous block cannot be listed under "Accessible by" Clause, hence the units declared as "Accessible by" cannot be called dynamically.
3. "Accessible by" declared units cannot be invoked from outside of database

CREATE OR replace PROCEDURE proc_for_sal_increment ( pin_empno NUMBER,
                                                    pin_pct    NUMBER ) 
accessible BY (update_sal_wrapper)
AS
BEGIN
  UPDATE emp
  SET    sal = ( sal ) + ( sal * pin_pct / 100 )
  WHERE  empno = pin_empno;
  
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Unable to Update sal : '
  || SQLCODE);
END;

---

BEGIN
    Proc_for_sal_increment(7698, 10);
END;
/


exec proc_for_sal_increment(7698, 10); 

CREATE OR replace PROCEDURE Update_sal_wrapper
AS
BEGIN
    Proc_for_sal_increment(7698, 10);
END;
/

exec update_sal_wrapper; 

Pragma UDF (User Defined Function)

CREATE OR REPLACE FUNCTION fn1 (
    p_input1 NUMBER
) RETURN NUMBER
    AS
BEGIN
    RETURN p_input1 * 2;
END;
/

SELECT
    SUM(fn1(ROWNUM) )
FROM
    dual
CONNECT BY
    level <= 2000000;

-----------------------------------------------------

WITH
    FUNCTION fn1 (
        p_input1 NUMBER
    ) RETURN NUMBER
        AS
    BEGIN
        RETURN p_input1 * 2;
    END;

SELECT
    SUM(fn1(ROWNUM) )
FROM
    dual
CONNECT BY
    level <= 2000000;

-----------------------------------------------------

CREATE OR REPLACE FUNCTION fn1 (
    p_input1 NUMBER
) RETURN NUMBER
    AS
PRAGMA UDF;
BEGIN
    RETURN p_input1 * 2;
END;
/

SELECT
    SUM(fn1(ROWNUM) )
FROM
    dual
CONNECT BY
    level <= 2000000;

-----------------------------------------------------

Sequence as default value for table columns

Oracle 12c allows to use sequence as default value for column value. This video shows the behavior in pre-12C & 12C version onwards.



-- Behaviour from 12c onwards ( USAGE OF SEQUENCE FOR DEFAULT VALUE )

drop table stu_details;
create sequence stu_id_seq;

CREATE TABLE stu_details (
    stu_id         NUMBER DEFAULT stu_id_seq.nextval,
    stu_name       VARCHAR2(30),
    date_of_join   DATE DEFAULT sysdate
);

insert into stu_details(stu_name,date_of_join) values('RAGU',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(201,'SCOTT',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(NULL,'SMITH',sysdate);

select * 
from stu_details;

-- Behaviour from 12c onwards ( USAGE OF "DEFAULT ON NULL" )
drop table stu_details;
drop sequence stu_id_seq;
create sequence stu_id_seq;

CREATE TABLE stu_details (
    stu_id         NUMBER DEFAULT ON NULL stu_id_seq.nextval,
    stu_name       VARCHAR2(30),
    date_of_join   DATE DEFAULT sysdate
);

insert into stu_details(stu_name,date_of_join) values('RAGU',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(100,'SCOTT',sysdate);
insert into stu_details(stu_id,stu_name,date_of_join) values(NULL,'SMITH',sysdate);

select * 
from stu_details;

Memoptimized rowstore

• Oracle introduced a new feature called "Memoptimized rowstore", this feature is designed for fast query performance . Most of the times while accessing key value based data, we will be accessing the value based on the primary key value of table. These type of queries will benefit much from this feature .
• “MEMOPTIMIZE FOR READ” enabled tables are pinned in new memory pool called “MEMOPTIMIZE_POOL” with its new hash index. Queries accessing the table via primary keys will bybass the SQL execution layer, and access the data directly using the in-memory hash index. 
• Oracle introduced a new memory pool to manage the "Memoptimized rowstore" storage called MEMOPTIMIZE_POOL, and a new sizing parameter called "MEMOPTIMIZE_POOL_SIZE" is introduces.

Scripts used in demo:

CREATE TABLE key_val_pair (
    id         NUMBER   CONSTRAINT id_pk PRIMARY KEY,
    key_val    VARCHAR2(100),
    key_name   VARCHAR2(100)
)
segment creation IMMEDIATE memoptimize FOR READ;

---

INSERT INTO key_val_pair
SELECT ROWNUM ,'KEY'||ROWNUM, 'VALUE'||ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

---

EXPLAIN PLAN FOR
SELECT *
FROM key_val_pair
WHERE id =120;

---

SELECT *
FROM TABLE(dbms_xplan.display);

---

PLAN_TABLE_OUTPUT
PLAN hash value: 2264066049
 
-------------------------------------------------------------------------------------------------------
| id  | operation                              | name         | ROWS  | bytes | cost (%cpu)| TIME     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |     1 |   117 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ optim| key_val_pair |     1 |   117 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE scan READ optim         | id_pk        |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
predicate information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=120)

---

The new acess paths "INDEX UNIQUE scan READ optim" and "INDEX UNIQUE scan READ optim"  are from oracle 18c accessing using in-memory hash index value.

Rules for new access path:

1. The tables accessed using primary key alone in "where" condition (no other condition should exists), will use two new access paths "TABLE ACCESS BY INDEX ROWID READ OPTIM" and "INDEX UNIQUE SCAN READ OPTIM". This will confirm that the data are accessed via "READ OPTIM" path(ie. using the hash index value from in-memory cache).
2. Only the primary key condition with equality option will use this new access path.
3. Even if multiple primary key values are used in "IN" clause also wont use the access path

Unused Column in Oracle

CREATE TABLE cust_details (
    cust_id               NUMBER,
    cust_name             VARCHAR2(100),
    cust_address          VARCHAR2(255),
    cust_account_number   NUMBER 
);

insert into cust_details values(1,'SCOTT','500,1A,california',11111111);
insert into cust_details values(2,'KING','120,1B,california',22222222);
insert into cust_details values(3,'JAMES','400,1A,california',33333333);
insert into cust_details values(4,'JHON','300,1A,california',44444444);

commit;

desc cust_details;

select * 
from cust_details;

alter table cust_details set unused(cust_account_number);

select * 
from cust_details;

desc cust_details;

select * 
from USER_TAB_COLUMNS 
where table_name = 'CUST_DETAILS';

alter table cust_details drop unused columns;

Invisible Column from Oracle 12c

CREATE TABLE cust_details (
    cust_id               NUMBER,
    cust_name             VARCHAR2(100),
    cust_address          VARCHAR2(255),
    cust_account_number   NUMBER INVISIBLE
);

desc cust_details;

SET COLINVISIBLE ON

desc cust_details;

insert into cust_details values(1,'SCOTT','500,1A,california');
insert into cust_details(cust_id,cust_name,cust_address,cust_account_number) 
                         values(2,'JAMES','101,1A,Atlantic,08201',1234123412341234);
                         
SELECT
    *
FROM
    cust_details;

SELECT
    cust_id,
    cust_name,
    cust_address,
    cust_account_number
FROM
    cust_details;
    
update cust_details set cust_account_number = -9999
where cust_account_number is null;

alter table cust_details modify cust_account_number visible;

desc cust_details;

alter table cust_details modify cust_account_number invisible;

desc cust_details;

SELECT
    column_id,
    column_name,
    hidden_column
FROM
    user_tab_cols
WHERE
    table_name = 'CUST_DETAILS'
ORDER BY
    column_id;

Virtual Column in Oracle

Notes:
1. Indexes can be defined on virtual column., and these are equivalent to function based index.
2. virtual columns can be referred any in select stateless such as "where" clause etc, however DMLs are not allowed on virtual columns.
3. Virtual columns can be defined on "Heap organized table", and not supported for "external", "IOT", clustered or temporary tables.
4. virtual column cannot refer to other virtual column. It should refer to the columns of the same table.
5. The expression for virtual column should evaluate to scalar data type only.
6. Virtual column can be used as partition key.


CREATE TABLE loan_details (
    sno                         number,
    principal_amount  number,
    rate_of_interest     number,
    number_of_years  number
);

insert into loan_details values(1,5000,8,1);
insert into loan_details values(2,5000,10,1);
insert into loan_details values(3,5000,12,1);

commit;

SELECT
    sno,
    principal_amount,
    number_of_years,
    rate_of_interest,
    ( round(principal_amount * number_of_years * rate_of_interest  / 100) ) simple_interest,
    (round(principal_amount * power( (1 + ( (rate_of_interest / 100) / 12) ), (number_of_years * 12) )-principal_amount)) compound_interest
FROM
    loan_details;


drop table loan_details;

CREATE TABLE loan_details (
    sno                        number,
    principal_amount number,
    rate_of_interest    number,
    number_of_years  number,
simple_interest  as ( round(principal_amount * number_of_years * rate_of_interest  / 100) ),
compound_interest NUMBER
GENERATED ALWAYS AS
(round(principal_amount * power( (1 + ( (rate_of_interest / 100) / 12) ), (number_of_years * 12) )-principal_amount))
VIRTUAL
);

insert into loan_details(sno,principal_amount,rate_of_interest,number_of_years) values(1,5000,8,1);
insert into loan_details(sno,principal_amount,rate_of_interest,number_of_years) values(2,5000,10,1);
insert into loan_details(sno,principal_amount,rate_of_interest,number_of_years) values(3,5000,12,1);

select * from loan_details;

desc loan_details;

select * from USER_TAB_COLUMNS where table_name = 'LOAN_DETAILS';

UNPIVOT in SQL

---

SELECT 'welcome' col1,
       'to'      col2,
       'oracle'  col3,
       'class'   col4
FROM   dual;

---

SELECT * FROM (
            SELECT
                'welcome' col1,
                'to'      col2,
                'oracle'  col3,
                'class'   col4
            FROM
                dual)
UNPIVOT(column_val for column_name IN (col1,col2,col3,col4));

---

SELECT
    *
FROM
    (
        SELECT
            ename,
            To_char(empno) empno,
            job
        FROM
            emp
    )
UNPIVOT ( column_val for column_name  IN ( ename,empno,job ));

---

Approx_rank, Approx_sum, Approx_count ( Oracle 18C New Functions)

1. "GROUP BY" clause is mandatory to use approx_* functions.
2. Approx_count & Approx_sum functions has to be used with "HAVING" clause containing approx_rank function.
3. Both approx_* functions and non approx_* aggregate functions are not allowed in same select statement.
4. "ORDER BY" clause within approx_rank function should use only approx_* functions only (+ with "DESC" keyword only).
5. "HAVING" clause should contain "<= number" or "< number" conditions only. Follow this format only ('APPROX_RANK(PARTITION BY ... ORDER BY ... DESC) <(=) n')

SELECT   empno,          approx_rank ( order by approx_sum(sal) DESC ) FROM     scott.emp GROUP BY empno HAVING   approx_rank ( ORDER BY approx_sum(sal) DESC ) <=10 ORDER BY 2;

SELECT   deptno,          Approx_sum(sal),          approx_rank ( order by approx_sum(sal) DESC ) FROM     scott.emp GROUP BY deptno HAVING   approx_rank ( ORDER BY approx_sum(sal) DESC ) <=10;

SELECT   deptno,          Approx_count(empno),          approx_rank ( order by approx_count(empno) DESC ) FROM     scott.emp GROUP BY deptno HAVING   approx_rank ( ORDER BY approx_count(empno) DESC ) <=10;

Oracle Approximate Count Distinct Function

Oracle Approximate Count Distinct Function

CREATE TABLE tab_num_list
  (
     col1 NUMBER
  );

INSERT INTO tab_num_list
SELECT LEVEL
FROM   dual
CONNECT BY LEVEL <= 100000;

SELECT Count(*),
       Count(DISTINCT col1),
       Approx_count_distinct(col1)
FROM   tab_num_list; 

Loading Data into Oracle database

There are many situation, where the data from external sources(like text, csv, JSON, XML etc.,) needs to be loaded into database.This can be achieved by many ways. Though there are many ways, each option is suited for particular scenarios only. This is mainly due to the difference in the source data and target data structures. For eg, we might need different mechanism to read text file vs XML files. The mechanism needs to be decided depending on the nature/type of source data.

Given below the possible ways to load data into oracle database.

1. Database Client Tools (like SQL developer, TOAD etc., support loading data from files into tables)
2.  Program/Script ( Using any "Programming language" (Like Java etc.,)  or "Scripting Language" (Like shell script, python etc.,))
3. SQL Loader (Oracle provided utility )
4. External Table ( Oracle provided option to read data from external files as if they present in table)
5. PLSQL Program ( Oracle supplied UTL_FILE package to do file handling operations like read, write, copy and remove operations on operating system files)
6. ETL Tools ( Lot of ETL tools are available to map, extract and load the data from external files into oracle database)

Of all the options given above, option 3,4 & 5 are oracle provided mechanism to load data into oracle database.

Here is a course on Udemy to teach about oracle supplied options to load the data from external source into oracle database.

This course covers in detail about SQL Loader, UTL_FILE & External table concepts with example.

https://www.udemy.com/oracle-file-handling-utl_file-sql-loader-external-table/

PIVOT in SQL

SELECT     * FROM     (         SELECT job,deptno,sal         FROM emp     )         PIVOT ( sum ( sal )             FOR deptno             IN ( 10,20,30 )         );
SELECT     * FROM     (         SELECT deptno,sal         FROM emp     )         PIVOT ( sum ( sal )             FOR deptno             IN ( 10,20,30 )         );
SELECT     * FROM     (         SELECT job,sal         FROM  emp     )         PIVOT ( sum ( sal )             FOR job             IN ( 'CLERK','SALESMAN','PRESIDENT','MANAGER','ANALYST' )         );
SELECT     * FROM     (         SELECT  job,deptno,sal         FROM   emp     )         PIVOT ( sum ( sal )             FOR job             IN ( 'CLERK','SALESMAN','PRESIDENT','MANAGER','ANALYST' )         );