DBMS : Lab Assignment No. 2 Q&A

Q&A

Ques 1) List the name & salary of the employees whose salary is more than 1000.

select ename, sal from emp where sal > 1000

Ques 2) List the employee no. & name of managers.

select e.empno, m.ename as "Manager" from emp e inner join emp m on e.mgr = m.empno

Ques 3) List the names of the analyst & salesman.

select ename from emp where job like 'ANALYST' or job like 'SALESMAN'

Ques 4) List the names of the employees who are not managers.

select ename from emp where job not like 'MANAGER'

Ques 5) List the employee details not belonging to the department 10, 30 &40.

select * from emp where deptno not in(10, 30, 40)

Ques 6) List the employee name & salary whose salary is between 1000 & 2000.

select ename, sal from emp where sal > 1000 and sal < 2000

Ques 7) List the different jobs available in the employee table.

select distinct job from emp

Ques 8) List the employee names who are not eligible for commission.

select ename from emp where comm is NULL

Ques 9) List the name of the employee & designation of the employee who does not report to anybody.

selct ename, job from emp where mgr is NULL

Ques 10) List the employees not assigned to any department.

select * from emp where deptno is NULL

Ques 11) List the employees who are eligible for commission.

select * from emp where comm is not NULL

Ques 12) List the details of employee whose salary is greater than 2000 & commission is null.

select * from emp where sal > 2000 and comm is NULL

Ques 13) List the employee no., name & salary in ascending order of salary.

select empno, ename, sal from emp order by sal

Ques 14) List the employee name, salary, job & department no. in ascending order of department no. & then on descending order of salary.

select ename, sal, job, deptno from emp order by sal desc

Ques 15) List the no. of employees working with the company.

select count(empno) from emp

Ques 16) Display department numbers and total number of employees working in each department.

select deptno, count(empno) from emp group by deptno

Ques 17) Display the various jobs and total number of employees within each job group.

select job, count(empno) from emp group by job

Ques 18) Display the depart numbers and total salary for each department.

select deptno, sum(sal) from emp group by deptno

Ques 19) Display the department numbers with more than three employees in each dept.

select deptno, count(empno) from emp group by deptno having count(empno) > 3

Ques 20) Display the various jobs along with total salary for each of the jobs where total salary is greater than 40000.

select job, sum(sal) from emp group by job having sum(sal) > 5000

Ques 21) List the jobs & no. of employees in each job. The result should be in descending order of the no. of employees.

select job, count(empno) from emp group by job order by count(empno) desc

Ques 22) Display the various jobs along with total number of employees in each job. The output should contain only those jobs with more than three employees.

select job, count(empno) from emp group by job having count(empno) > 3

Ques 23) Display. the list of employees working in each department. Display the department information even if no employee belongs to that department.

select empno, ename, dept.deptno, dname, loc from emp right outer join dept on emp.deptno = dept.deptno

Ques 24) Display employee name, job, department name , manager name and make out an under department wise?

select e.name, e.job, dname as "DEPARTMENT" m.ename as "MANAGER" from ((emp e inner join emp m on e.mgr = m.empno) inner join dept on e.deptno = dept.deptno) order by dname

Ques 25) List out all employees name, job, salary and depart name for everyone in the company except 'CLERK'. Sort on salary display the highest salary?

select ename, job, sal, dname from emp inner join dept on emp.deptno = dept.deptno where job not like 'CLERK' order by sal desc

EMP TABLE
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
7566	JONES	MANAGER	7839	02-APR-81	2975	-	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	-	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	-	20
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
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
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
7934	MILLER	CLERK	7782	23-JAN-82	1300	-	10

DEPT TABLE
DeptNo	DName	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON