ASSIGNMENT - 3

 Create the following EMP table and DEPT table , then insert the
values and perform following operations :-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

EMP table
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
DEPT table

Query for create EMP table Student: -

CREATE TABLE DEPT ( DEPTNO NUMBER(2) NOT NULL PRIMARY KEY, DNAME
VARCHAR2(14), LOC VARCHAR2(13));
Query for create EMP table Student: -
CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL PRIMARY KEY, ENAME
VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2),
COMM NUMBER(7, 2), DEPTNO NUMBER(2), CONSTRAINT FK_DEPT FOREIGN KEY (DEPTNO)
REFERENCES DEPT(DEPTNO));
Query for insert table DEPT: -
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');
Query for insert table EMP: -
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES

1
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-80', 'DD-MON-YY'), 800, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-81', 'DD-MON-YY'), 1600, 300, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-81', 'DD-MON-YY'), 1250, 500, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-APR-81', 'DD-MON-YY'), 2975, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-81', 'DD-MON-YY'), 1250, 1400, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-MAY-81', 'DD-MON-YY'), 2850, NULL, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('09-JUN-81', 'DD-MON-YY'), 2450, NULL, 10);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('19-APR-87', 'DD-MON-YY'), 3000, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-NOV-81', 'DD-MON-YY'), 5000, NULL, 10);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('08-SEP-81', 'DD-MON-YY'), 1500, 0, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7876, 'ADAMS', 'CLERK', 7788, TO_DATE('23-MAY-87', 'DD-MON-YY'), 1100, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7900, 'JAMES', 'CLERK', 7698, TO_DATE('03-DEC-81', 'DD-MON-YY'), 950, NULL, 30);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7902, 'FORD', 'ANALYST', 7566, TO_DATE('03-DEC-81', 'DD-MON-YY'), 3000, NULL, 20);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-82', 'DD-MON-YY'), 1300, NULL, 10);

2
Query for fetch table EMP: -
SELECT * FROM EMP;
Output: -
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

Query for fetch table DEPT: -

SELECT * FROM DEPT;
Output: -
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Questions:-
1. List the names of analyst and salesmen.
Query:- SELECT ENAME FROM EMP WHERE JOB IN ('ANALYST', 'SALESMAN');
Output:-
ENAME
----------
ALLEN
WARD
MARTIN
SCOTT
TURNER
FORD
2. List details of employees who have joined before 30 Sep 81.
Query:- SELECT * FROM EMP WHERE HIREDATE < TO_DATE('30-SEP-81', 'DD-MON-YY');
Output:-

3
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
3. List names of employees who are not managers.
Query:- SELECT ENAME FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT MGR FROM EMP
WHERE MGR IS NOT NULL);
Output:-
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
4. List the names of employees whose employee numbers are 7369, 7521, 7839, 7934, 7788.
Query:- SELECT ENAME FROM EMP WHERE EMPNO IN (7369, 7521, 7839, 7934, 7788);
Output:-
ENAME
----------
SMITH
WARD
SCOTT
KING
MILLER
5. List employees not belonging to department 30, 40, or 10.
Query:- SELECT ENAME FROM EMP WHERE DEPTNO NOT IN (30, 40, 10);
Output:-
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD
6. List employee names for those who have joined between 30 June and 31 Dec. ‘81.
Query:- SELECT ENAME FROM EMP WHERE HIREDATE BETWEEN TO_DATE('30-JUN-81', 'DD-
MON-YY') AND TO_DATE('31-DEC-81', 'DD-MON-YY');

4
Output:-
ENAME
----------
MARTIN
KING
TURNER
JAMES
FORD
7. List the different designations in the company.
Query:- SELECT DISTINCT JOB FROM EMP;
Output:-
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
8. List the names of employees who are not eligible for commission.
Query:- SELECT ENAME FROM EMP WHERE COMM IS NULL OR COMM = 0;
Output:-
ENAME
----------
SMITH
JONES
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
9. List the employees not assigned to any department.
Query:- SELECT ENAME FROM EMP WHERE DEPTNO IS NULL;
Output:-

no rows selected
10. List the employees who are eligible for commission.
Query:- SELECT ENAME FROM EMP WHERE COMM IS NOT NULL AND COMM > 0;
Output:-
ENAME
----------
ALLEN
WARD
MARTIN

5
11. List employees whose names either start or end with “S”.
Query:- SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%' OR ENAME LIKE '%S';
Output:-
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
JAMES
12. List names of employees whose names have “i” as the second character.
Query:- SELECT ENAME FROM EMP WHERE ENAME LIKE '_I%';
Output:-
ENAME
----------
KING
MILLER
13. List the number of employees working with the company.
Query:- SELECT COUNT(*) AS Total_Employees FROM EMP;
Output:-
TOTAL_EMPLOYEES
---------------------------
14
14. List the number of designations available in the EMP table.
Query:- SELECT COUNT(DISTINCT JOB) AS Number_of_Designations FROM EMP;
Output:-
NUMBER_OF_DESIGNATIONS
----------------------------------------
5
15.List the total salaries paid to the employees.
Query:- SELECT SUM(SAL) AS Total_Salaries FROM EMP;
Output:-
TOTAL_SALARIES
-----------------------
29025
16. List the maximum, minimum and average salary in the company.
Query:- SELECT MAX(SAL) AS Maximum_Salary, MIN(SAL) AS Minimum_Salary, AVG(SAL) AS
Average_Salary FROM EMP;
Output:-
MAXIMUM_SALARY MINIMUM_SALARY AVERAGE_SALARY
------------------------- ------------------------- ------------------------
5000 800 2073.21429
17. List the maximum salary paid to a salesman.
Query:- SELECT MAX(SAL) AS Maximum_Salary_To_Salesman FROM EMP WHERE JOB =
'SALESMAN';
Output:- MAXIMUM_SALARY_TO_SALESMAN
-------------------------------------------------
1600
6