TABLE - 1
-----------------------------------------------------------------------------------
----
CREATE TABLE employees (
empno INT PRIMARY KEY,
ename VARCHAR(255),
job VARCHAR(255),
mgr INT,
hiredate DATE,
sal DECIMAL,
comm DECIMAL,
deptno INT NOT NULL
);
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, NULL,
20);
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600,
300, 30);
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250,
500, 30);
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975,
NULL, 20);
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981-09-28', 'YYYY-MM-DD'), 1250,
1400, 30);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850,
NULL, 30);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'YYYY-MM-DD'), 2450,
NULL, 10);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1987-04-19', 'YYYY-MM-DD'), 3000,
NULL, 20);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1981-11-17', 'YYYY-MM-DD'), 5000,
NULL, 10);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'YYYY-MM-DD'), 1500,
NULL, 30);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('1987-05-23', 'YYYY-MM-DD'), 1100,
NULL, 20);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7900, 'JAMES', 'CLERK', 7698, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 950, NULL,
30);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 3000,
NULL, 20);
1 row created.
SQL> INSERT INTO employees (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
2 (7934, 'MILLER', 'CLERK', 7782, TO_DATE('1982-01-23', 'YYYY-MM-DD'), 1300,
NULL, 10);
TABLE 2
--------------------------------------------------------------------------------
SQL> CREATE TABLE DEPT(
2 DEPTNO INT PRIMARY KEY,
3 DNAME VARCHAR(10),
4 LOC VARCHAR(15)
5 );
SQL> INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'DALLAS');
1 row created.
SQL> INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'CHICAGO');
1 row created.
SQL> INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');
TABLE 3
----------------------------------------------------------------------------------
SQL> CREATE TABLE SALGRADE (
2 GRADE INT PRIMARY KEY,
3 LOSAL INT,
4 HISAL INT
5 );
SQL> INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (1, 700, 1200);
1 row created.
SQL> INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (2, 1201, 1400);
1 row created.
SQL> INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (3, 1401, 2000);
1 row created.
SQL> INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (4, 2001, 3000);
1 row created.
SQL> INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (5, 3001, 9999);
###################################################################################
##################
WEEK-5 ANSWERS
1. Unique Job
SQL> SELECT DISTINCT JOB FROM EMPLOYEES;
JOB
--------------------------------------------------------------------------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
2. List the details of all employees in deptno 10 and 20 in alphabetical order.
SQL> SELECT * FROM employees
2 WHERE deptno IN (10, 20)
3 ORDER BY ename;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------
-------------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
3. List the names of employees who have “th” or “ll” in their names.
SQL> SELECT ENAME FROM EMPLOYEES
2 WHERE ename LIKE '%TH%' OR ename LIKE '%LL%';
ENAME
--------------------------------------------------------------------------------
SMITH
ALLEN
MILLER
4. List the names, jobs and salaries of all employees who have a manager.
SQL> SELECT ENAME, JOB, SAL FROM EMPLOYEES
2 WHERE MGR IS NOT NULL;
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
SMITH
CLERK
800
ALLEN
SALESMAN
1600
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
WARD
SALESMAN
1250
JONES
MANAGER
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
2975
MARTIN
SALESMAN
1250
BLAKE
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
MANAGER
2850
CLARK
MANAGER
2450
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
SCOTT
ANALYST
3000
TURNER
SALESMAN
1500
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
ADAMS
CLERK
1100
JAMES
CLERK
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
950
FORD
ANALYST
3000
MILLER
ENAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
CLERK
1300
5. List name and salary increased by 15% of all employees.
SQL> SELECT ENAME, (SAL*0.15)+SAL FROM EMPLOYEES;
ENAME (SAL*0.15)+SAL
-----------------------------------------------------------------------------------
---------------------
SMITH 920
ALLEN 1840
WARD 1437.5
JONES 3421.25
MARTIN 1437.5
BLAKE 3277.5
CLARK 2817.5
SCOTT 3450
KING 5750
TURNER 1725
ADAMS 1265
JAMES 1092.5
FORD 3450
MILLER 1495
6. Find all the employees who were hired during 1982.
SQL> SELECT * FROM EMPLOYEES WHERE HIREDATE BETWEEN '1-JAN-82' AND '31-DEC-82';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------
-----------------------------------------------------------------------------------
----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7. Display name, annual salary, commission of all salesmen whose monthly salary is
greater than
commission.
SQL> SELECT ENAME, (SAL * 12) AS annual_salary, COMM
2 FROM EMPLOYEES
3 WHERE JOB = 'SALESMAN' AND (SAL) > COMM;
ENAME
ANNUAL_SALARY COMM
-----------------------------------------------------------------------------------
---------------------
ALLEN 19200
300
WARD 15000
500
8. Produce the output as “smith has held the position of clerk in dept. 20 since
17- dec-80”.
SQL> SELECT
2 ENAME || ' has held the position of ' || JOB || ' in dept. ' || DEPTNO ||
' since ' || TO_CHAR(HIREDATE, 'DD-MON-YYYY') AS EMP_INFO
3 FROM EMPLOYEES;
EMP_INFO
-----------------------------------------------------------------------------------
---------------------
SMITH has held the position of CLERK in dept. 20 since 17-DEC-1980
ALLEN has held the position of SALESMAN in dept. 30 since 20-FEB-1981
WARD has held the position of SALESMAN in dept. 30 since 22-FEB-1981
JONES has held the position of MANAGER in dept. 20 since 02-APR-1981
MARTIN has held the position of SALESMAN in dept. 30 since 28-SEP-1981
BLAKE has held the position of MANAGER in dept. 30 since 01-MAY-1981
CLARK has held the position of MANAGER in dept. 10 since 09-JUN-1981
SCOTT has held the position of ANALYST in dept. 20 since 19-APR-1987
KING has held the position of PRESIDENT in dept. 10 since 17-NOV-1981
TURNER has held the position of SALESMAN in dept. 30 since 08-SEP-1981
ADAMS has held the position of CLERK in dept. 20 since 23-MAY-1987
JAMES has held the position of CLERK in dept. 30 since 03-DEC-1981
FORD has held the position of ANALYST in dept. 20 since 03-DEC-1981
MILLER has held the position of CLERK in dept. 10 since 23-JAN-1982
9. Find the average salary and average total remuneration of all employees other
than salesman
SQL> SELECT AVG(SAL) AS AVERAGE_SALARY, AVG(SAL + NVL(COMM,0)) AS AVERAG
E_REMUNERATION FROM EMPLOYEES
2 WHERE JOB !='SALESMAN';
AVERAGE_SALARY AVERAGE_REMUNERATION
-------------- --------------------
2342.5 2342.5
10. Find maximum, minimum and average salaries in each department.
SQL> SELECT DEPTNO, MAX(SAL) AS MAX, MIN(SAL) AS MIN, AVG(SAL) AS AVERAGE FROM
EMPLOYEES GROUP BY DEPTNO;
DEPTNO MAX MIN AVERAGE
---------- ---------- ---------- ----------
30 2850 950 1566.66667
20 3000 800 2175
10 5000 1300 2916.66667
11. Find the departments which have more than three employees.
SQL> SELECT DEPTNO, COUNT(ENAME) FROM EMPLOYEES GROUP BY DEPTNO;
DEPTNO COUNT(ENAME)
---------- ------------
30 6
20 5
10 3
12.Give the salary grades for all the employees.
SQL> SELECT
2 e.ename,
3 e.sal,
4 s.grade
5 FROM employees e
6 JOIN salgrade s
7 ON e.sal BETWEEN s.losal AND s.hisal;
ENAME SAL GRADE
-----------------------------------------------------------------------------------
---------------------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5
13. Find the employee details whose salary is greater than blake’s salary.
SQL> SELECT * FROM EMPLOYEES
2 WHERE SAL > (SELECT SAL FROM EMPLOYEES WHERE ENAME = 'BLAKE');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------
-----------------------------------------------------------------------------------
----------7566 JONES MANAGER 7839 02-APR-81 2975
20
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000
20
14. Display the employee names who earn highest salary in each job.
SQL> SELECT
2 e.ename,
3 e.job,
4 e.sal
5 FROM employees e
6 JOIN (
7 SELECT job, MAX(sal) AS max_salary
8 FROM employees
9 GROUP BY job
10 ) maxsal
11 ON e.job = maxsal.job AND e.sal = maxsal.max_salary;
ENAME JOB SAL
-----------------------------------------------------------------------------------
---------------------
MILLER CLERK 1300
ALLEN SALESMAN 1600
KING PRESIDENT 5000
JONES MANAGER 2975
FORD ANALYST 3000
SCOTT ANALYST 3000
15. Find employee details of employees who have the same job and salary as that
scott.
SQL> SELECT * FROM EMPLOYEES E
2 JOIN(SELECT JOB, SAL
3 FROM EMPLOYEES
4 WHERE ENAME = 'SCOTT')SCOT
5 ON E.JOB = SCOT.JOB AND E.SAL = SCOT.SAL AND E.ENAME != 'SCOTT';
EMPNO ENAME
JOB
MGR HIREDATE SAL COMM DEPTNO JOB SAL
----------
-----------------------------------------------------------------------------------
----------
7902 FORD
ANALYST
7566 03-DEC-81 3000 20 ANALYST
3000
16. Display the maximum salaries in accounting and research department.
SQL> SELECT
2 d.dname,
3 MAX(e.sal) AS max_salary
4 FROM employees e
5 JOIN dept d
6 ON e.deptno = d.deptno
7 WHERE d.dname IN ('ACCOUNTING', 'RESEARCH')
8 GROUP BY d.dname;
DNAME MAX_SALARY
---------- ----------
ACCOUNTING 5000
RESEARCH 3000
17. Display salary grades of all employees except of those employees whose salary
grade is 3
and 4.
SQL> SELECT S.GRADE, E.ENAME FROM EMPLOYEES E
2 JOIN SALGRADE S
3 ON E.SAL BETWEEN S.LOSAL AND S.HISAL
4 WHERE S.GRADE != '3' AND S.GRADE != '4';
GRADE ENAME
-----------------------------------------------------------------------------------
--------------------
1 SMITH
1 JAMES
1 ADAMS
2 WARD
2 MARTIN
2 MILLER
5 KING
