KEMBAR78
SQL Assignment 1 | PDF | Databases
Scribd
Upload
144 views12 pages

SQL Assignment 1

1. The document contains SQL queries and results from the EMP table in the database. It displays employee information like employee number, name, job, manager, hire date, salary, commission, and department number. 2. Some queries list unique jobs and departments, employees hired before 1981, those in departments 10 or 20, clerks in department 20, and specific employees like Smith. 3. Other queries filter on job roles like managers, increase salaries by a percentage, and group or format the output in various ways like employee name and job. The document demonstrates many basic and some advanced SQL queries on the EMP table.

Uploaded by

Manisha Arsule
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
144 views12 pages

SQL Assignment 1

1. The document contains SQL queries and results from the EMP table in the database. It displays employee information like employee number, name, job, manager, hire date, salary, commission, and department number. 2. Some queries list unique jobs and departments, employees hired before 1981, those in departments 10 or 20, clerks in department 20, and specific employees like Smith. 3. Other queries filter on job roles like managers, increase salaries by a percentage, and group or format the output in various ways like employee name and job. The document demonstrates many basic and some advanced SQL queries on the EMP table.

Uploaded by

Manisha Arsule
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 12

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.

You might also like