LAB5a
LAB5a
Display name of employees , department name and job name for each employee
14 rows selected.
Q2. Display the department name along with no of employees and average salary of that
department
Q3. For each department, find out no. of jobs the employees are assigned to.
JOB COUNT(DEPTNO)
---------- -------------
salesman 4
clerk 4
president 1
manager 3
analyst 2
Q4. Check for correctness of the above queries in terms of count, if you want to bring in
all entries, how would you achieve the same?
Q5. Group by the employees based on the first character of employee first name.
Display the results in alphabetic order (descending) of first character.
14 rows selected.
Q6. Display name of those employees who get a salary more than the average salary
Q7. Display name of the all the employees who are ‘stock manager’, except the one who
gets the minimum salary. SQL> select ename from emp where job='manager' and
sal>(select min(sal)from emp where job='manager');
ENAME
----------
jones
blake
Q8. Display firstname, lastname, salary of those sales representatives who earns a
higher salary than the minimum salary a sales manager receives.
SQL> select ename, sal from emp where job='salesman' and sal>(select min(sal) from emp
where job='manager');
ENAME SAL
---------- ----------
ward 2975
Q9.Display the name of the employees/employee who gets the second highest salary.
(sub query)
SQL> select max(sal) from emp where sal<(select max(sal) from emp);
MAX(SAL)
----------
3000
Q10. Come up with the query for previous question using set operators
SQL> select ename from emp where sal in(select max(sal) from emp where sal<(select
max(sal) from emp ));
ENAME
----------
scott
ford
Q11. Display the name of the employee (manager) who has the maximum no. of
employees reporting to him.
Q12. Display the name of those employees , who are in the same department as
Timothy Gates and gets an salary more than the average salary of all the employees
.SQL> select ename from emp where deptno=(select deptno from emp where
ename='Timothy Gates') and sal>(select avg(sal) from emp);
no rows selected
Q13. If an employee have spent less than 5 years then he is considered entry level id
5 – 10 then midlevel else a senior employee. Write a query, which will label the
employees in either of the above categories
SQL> select ename, case when(sysdate - hiredate)/365<5 then 'Entry Level' when(sysdate -
hiredate)/365 between 5 and 10 then 'Mid Level' else 'senior' end as employeelevel from
emp;
ENAME EMPLOYEELEV
---------- -----------
smith senior
allen senior
ward senior
jones senior
martin senior
blake senior
clark senior
scott senior
king senior
turner senior
adams senior
ENAME EMPLOYEELEV
---------- -----------
james senior
ford senior
miller senior
14 rows selected.
Q14. Write query to find out any departments that are present in department table
but does not have employees
Q. 15. Write a query which will display job id , which are present in both job and
employee columns
Q. 16. Increase salary of each employee of all the department who draws the
minimum salary by 100$.
update emp set sal=sal+100 where sal = (select min(sal) from emp);
1 row updated.
select * from emp;
14 rows selected.