SQL QUERIES -Part 3
41)
Display name,salary,hra,pf,da,total salary for each employee. The
output
should be in the order of total salary,hra 15% of salary,da 10% of salary,pf 5%
salary,total
salary will be(salary+hra+da)-pf.
SQL>select
ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10 as
da,
sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;
42)
Display depart numbers and total number of employees working in each
department.
SQL>select
deptno,count(deptno)from emp group by deptno;
43)
Display the various jobs and total number of employees within each job
group.
SQL>select
job,count(job)from emp group by job;
44)
Display the depart numbers and total salary for each department.
SQL>select
deptno,sum(sal) from emp group by deptno;
45) Display
the depart numbers and max salary for each department.
SQL>select
deptno, max(sal) from emp group by deptno;
46)
Display the various jobs and total salary for each job
SQL>select
job, sum(sal) from emp group by job;
47)
Display the various jobs and total salary for each job
SQL>select
job, min (sal) from emp group by job;
48)
Display the depart numbers with more than three employees in each dept.
SQL>
select deptno, count (deptno) from EMP group by deptno having
Count (*)>3;
49) Display
the various jobs along with total salary for each of the jobs
Where total salary is greater than
40000.
SQL>
select job, sum (sal) from EMP group by job having sum (sal)>40000;
50)
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.
SQL>select job, count (empno) from EMP group by job
having count (job)>3
51)
Display the name of the employee who earns highest salary.
SQL>select
ename from EMP where sal= (select max (sal) from EMP);
52)
Display the employee number and name for employee working as clerk and
earning highest salary among clerks.
SQL>select
empno,ename from emp where job='CLERK'
and sal=(select max(sal) from
emp where job='CLERK');
53)
Display the names of salesman who earns a salary more than the highest
salary of any clerk.
SQL>select
ename,sal from emp where job='SALESMAN' and sal>(select
max(sal) from emp where job='CLERK');
54)
Display the names of clerks who earn a salary more than the lowest
salary
of any salesman.
SQL>select
ename from emp where job='CLERK' and sal>(select min(sal)
from emp where job='SALESMAN');
54)Display
the names of employees who earn a salary more than that of
Jones or that of salary grether
than that of scott.
SQL> select ename,sal from emp where sal>
(select sal from emp where
ename='JONES')and sal>
(select sal from emp where
ename='SCOTT');
55)
Display the names of the employees who earn highest salary in their
respective departments.
SQL>select ename,sal,deptno from emp
where sal in(select max(sal) from
emp group by deptno);
56)
Display the names of the employees who earn highest salaries in their
respective job groups.
SQL>select
ename,sal,job from emp where sal in(select max(sal) from emp
group by job)
57)
Display the employee names who are working in accounting department.
SQL>select
ename from emp where deptno=(select deptno from dept where
dname='ACCOUNTING')
58)
Display the employee names who are working in Chicago
SQL>select
ename from emp where deptno=(select deptno from dept where
LOC='CHICAGO
59)
Display the Job groups having total salary greater than the maximum
salary for managers.
SQL>SELECT
JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT
MAX(SAL) FROM EMP WHERE
JOB='MANAGER');
60)
Display the names of employees from department number 10 with salary
grether than that of any employee
working in other department.
SQL>select
ename from emp where deptno=10 and sal>any(select sal from
emp where deptno not in 10).
No comments:
Post a Comment