LAB 4
Writing and Practice of Simple Queries.
1. Get the description of EMP table.
SQL>desc emp;
RESULT:
Name Null? Type
-------------------------------- ----------------------- -------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(3)
AGE NUMBER(3)
ESAL NUMBER(10)
2. Get the description DEPT table.
SQL>desc dept;
RESULT:
Name Null? Type
--------------------------------- --------------------- ---------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
3.List all employee details.
SQL>select * from emp;
RESULT:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL
-------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -----------------
7369 SMITH CLERK 7902 17-DEC-80 800 0 20 25 0
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 25 0
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 25 0
7566 JONES MANAGER 7839 02-APR-81 2975 500 20 25 0
7698 BLAKE MANAGER 7839 01-MAY-81 2850 1400 30 25 0
4.List all employee names and their salaries, whose salary lies between
1500/- and 3500/- both inclusive.
INPUT
SQL>select ename from emp where sal between 1500 and 3500;
RESULT
ENAME
----------
ALLEN
JONES
BLAKE
CLARK
SCOTT
TURNER
FORD
russel
greg
9 rows selected.
5. List all employee names and their and their manager whose manager is
7902 or 7566 0r 7789.
INPUT SQL>select ename from emp where mgr in(7602,7566,7789);
RESULT
ENAME
-------
SCOTT
FORD
6. List all employees which starts with either J or T.
INPUT SQL>select ename from emp where ename like‘J%’ or ename like ‘T%’;
RESULT:
ENAME
---------
JONES
TURNER
JAMES
7. List all employee names and jobs, whose job title includes M or P.
INPUT SQL>select ename,job from emp where job like‘M%’ or job like ‘P%’;
RESULT:
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
KING PRESIDENT
8. List all jobs available in employee table.
INPUT SQL>select distinct job from emp;
RESULT:
JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
assistant
clerk
7 rows selected.
9. List all employees who belongs to the department 10 or 20.
INPUT SQL>select ename from emp where deptno in (10,20);
RESULT:
ENAME
----------
SMITH
JONES
CLARK
SCOTT
KING
ADAMS
FORD
MILLER
8 rows selected.
10. List all employee names , salary and 15% rise in salary.
INPUT SQL>select ename , sal , sal+0.15* sal from emp;
RESULT:
ENAME SAL SAL+0.15*SAL
---------- ---------- ------------
SMITH 800 920
ALLEN 1600 1840
WARD 1250 1437.5
JONES 2975 3421.25
MARTIN 1250 1437.5
BLAKE 2850 3277.5
CLARK 2450 2817.5
7 rows selected.
11. List minimum , maximum , average salaries of employee.
INPUT SQL>select min(sal),max(sal),avg(sal) from emp;
RESULT:
MIN(SAL) MAX(SAL) AVG(SAL)
--------- ---------- ----------
3 5000 1936.94118
12. Find how many job titles are available in employee table.
INPUT SQL>select count (distinct job) from emp;
RESULT:
COUNT(DISTINCTJOB)
------------------
7
13. What is the difference between maximum and minimum salaries of
employees in the organization?
INPUT SQL>select max(sal)-min(sal) from emp;
RESULT:
MAX(SAL)-MIN(SAL)
-----------------
4997
14. Display all employee names and salary whose salary is greater than
minimum salary of the company and job title starts with ‘M’.
INPUT SQL>select ename,sal from emp where job like‘M%’ and sal > (select min (sal)
from emp);
RESULT
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
15. Find how much amount the company is spending towards salaries.
INPUT SQL>select sum (sal) from emp;
RESULT
SUM(SAL)
---------
32928
16. Display name of the dept. with deptno 20.
INPUT SQL>select ename from emp where deptno = 20;
RESULT
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
17. List ename whose commission is NULL.
INPUT SQL>select ename from emp where comm is null;
ENAME
RESULT ----------
CLARK
SCOTT
KING
ADAMS
JAMES
FORD
6 rows selected.
18. Find no.of dept in employee table.
INPUT SQL>select count (distinct ename) from emp;
RESULT
COUNT(DISTINCTENAME
--------------------
17
19. List ename whose manager is not NULL.
INPUT SQL>select ename from emp where mgr is not null;
RESULT
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
5 rows selected.