SQL Assignment 1

===========================================================================

1. Display all the information of the EMP table?

==
SQL> SELECT * FROM EMP;

EMPN ENAME JOB MGR HIREDATE Salary COMMIS DEP

O SION TNO
7839 KING PRESIDEN 17-NOV-81 5000 10
T
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR81 2975 20
7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30
7844 TURNER SALESMAN 7698 08-Sep-81 1500 0 30
7900 JAMES CLERK 7698 03-Dec-81 950 30
7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30
7902 FORD ANALYST 7566 03-Dec-81 3000 20
7369 SMITH CLERK 7902 17-Dec-80 800 20
7788 SCOTT ANALYST 7566 09-Dec-82 3000 20
7876 ADAMS CLERK 7788 12-Jan-83 1100 20
7934 MILLER CLERK 7782 23-Jan-82 1300 10

14 rows selected.
2. Display unique Jobs from EMP table?
==
SQL> SELECT DISTINCT job, DEPTNO from emp;

JOB DEPTNO
--------- ----------
PRESIDENT 10
MANAGER 20
CLERK 10
SALESMAN 30
ANALYST 20
MANAGER 30
MANAGER 10
CLERK 30
CLERK 20

9 rows selected.
3. List the employees who joined before 1981.
==
SQL> SELECT * from emp WHERE HIREDATE < '01-JAN-1981';

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
4. List the employees who are working for the Deptno 10 or20.
==
SQL> SELECT * from emp WHERE deptno = 10 OR deptno = 20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7369 SMITH CLERK 7902 17-DEC-80 800

20

7788 SCOTT ANALYST 7566 09-DEC-82 3000

20
 EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7934 MILLER CLERK 7782 23-JAN-82 1300

10

5. List all the Clerks of Deptno 20.

==
SQL> SELECT * From emp where job = 'CLERK' AND deptNO = 20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7876 ADAMS CLERK 7788 12-JAN-83 1100

20

6. Display the details of SMITH.

==
SQL> SELECT * From emp where ename = 'SMITH';

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7. Display the location of SMITH.

==
8. List the employees whose salary is more than 3000 after giving 20% increment.
==

9. List the grade, EMP name for the deptno 10 or deptno 30 but salary grade is not 4 while
they joined the company before ’31-dec-82’.
==

10. List the employees those who joined in company before 15th of the month.
==

11. List the employees who are working as Managers.

==
SQL> SELECT * from emp WHERE JOB = 'MANAGER';

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

7782 CLARK MANAGER 7839 09-JUN-81 2450

10

7566 JONES MANAGER 7839 02-APR-81 2975

20

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
75 JONES MANAGER 7839 02-APR-81 2975
20
12. List the Ename and Salary is increased by 15% and expressed as no.of Dollars.
==

13. Produce the output of EMP table ‘EMP_AND_JOB’ for Ename and Job.
==

14. Produce the following output from EMP.

LOYEE SMITH (clerk)
ALLEN (Salesman)
==

15. Display the unique department with jobs.

==

16. Display the details of the Blake.

==
SELECT * from emp WHERE ENAME = 'BLAKE';

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

17. List all the clerks.

==
SQL> SELECT * from emp WHERE JOB = 'CLERK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
 7900 JAMES CLERK 7698 03-DEC-81 950
30

7369 SMITH CLERK 7902 17-DEC-80 800

20

7876 ADAMS CLERK 7788 12-JAN-83 1100

20

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7934 MILLER CLERK 7782 23-JAN-82 1300
10

18. List the empno, salary, commission of employees.

==

19. Display the unique department of the employees.

==

20. List all the employees joined on 1st may 81.

==
SQL> SELECT * from emp WHERE HIREDATE = '01-MAY-1981';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
21. List the employees who are working as managers.
==
SQL> SELECT * from emp WHERE JOB = 'MANAGER';

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

7782 CLARK MANAGER 7839 09-JUN-81 2450

10

7566 JONES MANAGER 7839 02-APR-81 2975

20

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
75 JONES MANAGER 7839 02-APR-81 2975
20

22. List the employees who are either clerks or managers.

==
SQL> SELECT * from emp WHERE JOB = 'MANAGER' OR JOB = 'CLERK';

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

7782 CLARK MANAGER 7839 09-JUN-81 2450

10

7566 JONES MANAGER 7839 02-APR-81 2975

20
 EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7900 JAMES CLERK 7698 03-DEC-81 950
30

7369 SMITH CLERK 7902 17-DEC-80 800

20

7876 ADAMS CLERK 7788 12-JAN-83 1100

20

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7934 MILLER CLERK 7782 23-JAN-82 1300
10

75 JONES MANAGER 7839 02-APR-81 2975

20

8 rows selected.

23. List the employees who have joined on the following dates 1 may 81,17 Nov 81,30 Dec 81
==

24. List the employees who have joined in the year 1981.
==

25. List the employees whose annual salary ranging from 23000 to 40000.
==
SQL> SELECT ename, sal from emp WHERE (SAL + COMM)*12 BETWEEN 23000 AND
40000;
 ENAME SAL
---------- ----------
MARTIN 1250

26. List the employees working under the Managers 7369,7890,7654,7900.

==
SQL> SELECT * FROM EMP WHERE MGR IN(7566,7890,7654,7902);
==
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7369 SMITH CLERK 7902 17-DEC-80 800

20

7788 SCOTT ANALYST 7566 09-DEC-82 3000

20

27. List the employees who joined in the second half of 82.
==
SQL> SELECT * from emp WHERE HIREDATE > '01-JULY-1982' AND HIREDATE < '31-
DEC-1982' ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000
20
28. Find out salary of both MILLER and SMITH.
==
SQL> SELECT ename, sal from emp WHERE ename = 'MILLER' OR ename = 'SMITH';

ENAME SAL
---------- ----------
SMITH 800
MILLER 1300

29. Find out the names and salaries of all employees earning more than 1000 per One month.
==
SQL> SELECT ename, sal from emp WHERE SAL + COMM > 1000;

ENAME SAL
---------- ----------
MARTIN 1250
ALLEN 1600
TURNER 1500
WARD 1250

30. Display the names and salaries of all employees except JAMES.
==
SQL> SELECT * FROM EMP where ENAME NOT IN ('JAMES');

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10

7698 BLAKE MANAGER 7839 01-MAY-81 2850

30

7782 CLARK MANAGER 7839 09-JUN-81 2450

10
 EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

30

7902 FORD ANALYST 7566 03-DEC-81 3000

20

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

7788 SCOTT ANALYST 7566 09-DEC-82 3000

20

7876 ADAMS CLERK 7788 12-JAN-83 1100

20
 EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7934 MILLER CLERK 7782 23-JAN-82 1300
10

75 JONES MANAGER 7839 02-APR-81 2975

20

14 rows selected.