DBMS : Lab Assignment No. 4 Q&A

Q&A.

Ques 1) List the no. of jobs available in the EMPLOYEE table.

select distinct job from employee

Ques 2) List the total salaries payable to the employees.

select sum(sal) from employee

Ques 3) List the maximum salary of employees working as a salesman.

select max(sal) from employee where jab like 'SALESMAN'

Ques 4) List the minimum salary of EMPLOYEE table.

select min(sal) from employee

Ques 5) List the total salary, maximum, minimum & average salary of employee job wise.

select job, sum(sal), max(sal), min(sal), avg(sal) from employee group by job

Ques 6) List the average salary from each job excluding managers.

select job, avg(sal) from employee where job not like 'MANAGER' group by job

Ques 7) List department number, average salary for all departments employing more than 5 people.

select deptid, avg(sal) from employee having count(empid) > 5

Ques 8) List jobs of all the employees where maximum salary >= 5000.

select job from employee having max(sal) >= 5000

Ques 9) List the employee’s no.s, names, deptno. & department names.

select empno, ename, deptno, dname from employee inner join dept on employee,deptno = dept.deptno

Ques 10) List the worker names & their corresponding managers from Employee table.

select e.ename, m.ename as "MANAGER" from employee e inner join employee m on e.mgr = m.empno

Ques 11) List all employees to join the company before their managers.

select e.ename, e.hiredate, m.ename, m.hiredate from employee e inner join employee m on e.mgr = m.empno where m.hiredate > e.hiredate

Ques 12) Display the total salary beginning paid to all employees.

select sum(sal) from employee

Ques 13) Display the average salary from employee table.

select avg(sal) from employee

Ques 14) Display the maximum salary being paid to CLERK.

select max(sal) from employee where job like 'CLERK'

Ques 15) Display the maximum salary being paid to depart number 20.

select max(sal) from employee where deptno = 20

Ques 16) Display the minimum salary being paid to any SALESMAN.

select min(sal) from employee where job like 'SALESMAN'

Ques 17) Display the average salary drawn by MANAGERS.

select avg(sal) from employee where job like 'MANAGER'

Ques 18) Display the total salary drawn by ANALYST working in dept number 40.

select sum(sal) from employee where job like 'ANALYST' and deptno = 40

Ques 19) Display the names of the employees in Uppercase.

select upper(ename) from employee

Ques 20) Display the names of the employees in Lowercase.

select lower(ename) from employee

Ques 21) Display the names of the employees in Proper case.

select initcap(ename) from employee

Ques 22) Display the length of your name using appropriate function.

select length(ename) from employee

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

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

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

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

Ques 25) Display the depart numbers and max salary for each department.

select deptno, max(sal) from employee group by deptno

Ques 26) Display the various jobs and total salary for each job.

select job, sum(sal) from employee group by job

Ques 27) Display the various jobs and total salary for each job.

select job, sum(sal) from employee group by job

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

select deptno from employee group by deptno having count(empid) > 3

Ques 29) Display total no. of employees in each department. Result should be in descending order of no. of employees.

select deptno, count(empno) from employee group by deptno order by count(empno) desc

Ques 30) Display the average salary of employees in each department. Display result in ascending order of salary.

select deptno, avg(sal) from employee group by deptno order by avg(sal)