groups34 PRINT


1) For each grade find number of people who earn salary in this grade.

1) select count(ename),grade from emp,salgrade where sal>=losal and

sal<=hisal group by grade;

2) Find people who earn the highest salary for their job and grade.

2) select distinct E1.empno,E1.ename,E1.sal,E1.job from EMP E1, SALGRADE S1

where E1.sal=(select max(E2.sal) from EMP E2

where E1.job=E2.job) order by E1.sal;

3) Show names, jobs and hire dates for all people employed in NEW YORK in 1984.

3) select ename, job, hiredate from emp e, dept d

where e.deptno=d.deptno and e.hiredate like('1984')

and d.loc='NEW YORK';

4) Find all employees (personal number, name, profession and salary), who

earn more than the average salary for people with salaries in the second

grade. Display results order by salaries descending.

4) select empno,ename,job,sal from emp e where e.sal>(select avg(sal) from emp a, salgrade where grade=2 and a.sal>=losal and a.sal<=hisal) order by sal desc;

5) How many people earn more than the minimal salary for the department

located in DALLAS.

5) select count(empno) from EMP where sal>(select min(sal) from EMP where

deptno=(select D.deptno from DEPT D where D.loc='DALLAS'));

6) Find the number of people who don't supervises the others.

6) select count(*) from EMP M

where not exists ( select null from EMP E where E.mgr = M.empno );

7) For each manager find the total monthly income earned by his

subordinates. Display name of manager and the sum.

7) select A.ename, sum(B.sal) from EMP A, EMP B

where B.mgr=A.empno group by A.ename;

8) Find all people working as ANALISTS and employed before FORD.

8) select ename from emp where job='ANALYST'

and hiredate < (select hiredate from emp where ename='FORD');

9) Find all jobs which appear in department 10 but not in department 20.

9) select job from emp where deptno=10 MINUS

(select job from EMP where deptno=20) order by job;

10) For each job in each grade find the minimal yearly income.

Order results by incomes descending.

10) select min(12*E.sal+NVL(comm,0)),job,grade from EMP E, SALGRADE S where E.sal between S.losal and S.hisal group by E.job, S.grade

order by min(12*E.sal+NVL(E.comm,0)) desc;

1) Find all people working as CLERK and employed after ADAMS.

Display results ordered by hire dates.

1) select ename from EMP where job='CLERK' and hiredate>(select hiredate from EMP where ename='ADAMS');

2) Find all jobs which appear both in department 10 and in department 20.

2) select job from EMP where deptno=10 INTERSECT select job from EMP where deptno=20;

3) Find people who earn the lowest salary for their job and grade.

3) select distinct E1.empno,E1.ename,E1.sal,E1.job from EMP E1, SALGRADE S1

where E1.sal=(select min(E2.sal) from EMP E2

where E1.job=E2.job) order by E1.sal;

4) How many people earn less than the maximal salary for the department

located in NEW YORK

4) select count(empno) from EMP where sal<(select max(sal) from EMP where

deptno=(select D.deptno from DEPT D where D.loc='NEW YORK'));

5) For each manager find the number of his subordinate. Display name of

the manager and the number of people working under his supervision.

5)

6) Find all employees (personal number, name, profession and salary),

who earn less than the average salary for people with salaries in the third

grade. Display results order by salaries ascending.

6) select empno,ename,job,sal from EMP where

sal<(select avg(sal) from EMP,SALGRADE where sal between losal and hisal

and grade=3) order by sal asc;

7) For each job in each grade find the average yearly incomes.

Order result by jobs.

7) select distinct job,avg(sal*12+NVL(comm,0)),grade from EMP,SALGRADE

where sal between losal and hisal

group by job,grade;

8) Display names, jobs and hire dates for all employees working in DALLAS.

Order results by names.

8) select ename,job,hiredate from EMP,DEPT where EMP.deptno=DEPT.deptno

and DEPT.loc='DALLAS' order by ename;

9) Show all values of salaries from the 3-rd grade earned by people from

Dallas (don't repeat the same values)

9) select distinct sal from EMP,DEPT,SALGRADE where sal between losal and hisal

and EMP.deptno=DEPT.deptno

and DEPT.loc='DALLAS'

and SALGRADE.grade>=3;

10) Find the number of people who don't supervise the others.

10) select count(*) from EMP M

where not exists ( select null from EMP E where E.mgr = M.empno );

1) Display all employees (personal number, name, profession and salary),

who earn more than the average salary for people with salaries in the grade 2.

1) select empno,ename,job,sal from EMP where

sal>(select avg(sal) from EMP,SALGRADE where sal between losal and hisal

and grade=2) order by sal;

2) How many people earn more than the minimal salary for the department located

in DALLAS.

2) select count(empno) from EMP where sal>(select min(sal) from EMP where

deptno=(select D.deptno from DEPT D where D.loc='DALLAS'));

3) For each grade find the number of people with salaries in this grade

(display the grade and the adequate number of people).

3) select grade, count(empno) from SALGRADE, EMP where

EMP.sal between SALGRADE.losal and SALGRADE.hisal group by grade;

4) Find people who earn the highest salary for their job and grade.

4) select distinct E1.empno,E1.ename,E1.sal,E1.job from EMP E1, SALGRADE S1

where E1.sal=(select max(E2.sal) from EMP E2

where E1.job=E2.job) order by E1.sal;

5) For each job find the person employed as the last one.

5) select A.job, A.ename from EMP A where A.hiredate=(select max(B.hiredate) from EMP B where A.job=B.job);

6) For each manager find his subordinates earning the lowest salary.

Show name of manager, name and salary of subordinate.

6) select distinct A.ename, B.sal, B.ename from EMP A, EMP B

where B.mgr=A.empno order by A.ename, B.sal;

7) Find minimal yearly income for each job in each department.

7)

8) For each manager find the total yearly income earned by his subordinate.

Display name of manager and the sum.

8) select A.ename, sum(12*B.sal+NVL(B.comm,0)) from EMP A, EMP B

where B.mgr=A.empno group by A.ename;

9) Find all people working as ANALISTS and employed before FORD.

9) select ename from EMP where job='ANALYST' and hiredate<(select hiredate from EMP where ename='FORD');

10) Find number of people who don't supervise the others.

10) select count(*) from EMP M

where not exists ( select null from EMP E where E.mgr = M.empno );

1) Find all jobs which appear both in department 10 and in department 20.

Order results by jobs.

1) select job from EMP where deptno=10 INTERSECT

(select job from EMP where deptno=20) order by job;

2) Find people who earn the lowest salary for their job and grade.

2) select distinct E1.empno,E1.ename,E1.sal,E1.job from EMP E1, SALGRADE S1

where E1.sal=(select min(E2.sal) from EMP E2

where E1.job=E2.job) order by E1.sal;

3) How many people have been employed earlier than the one employed

as the last one in ACCOUNTING

3) select count(ename) from EMP where

hiredate<(select max(hiredate) from EMP where

deptno=(select deptno from DEPT where dname='ACCOUNTING'));

4) For each manager find the number of his subordinates. Display

name of the manager and the number of people working under his

supervision. Order by the number of subordinates descending.

4)

5) Find all people working as CLERK and employed after ADAMS. Display

results ordered by hire dates.

5) select * from EMP where job='CLERK' and hiredate>(select hiredate from EMP

where ename='ADAMS') order by hiredate;

6) Display names, jobs and hire dates for all employees working in

DALLAS. Order results by names.

6) select ename,job,hiredate from EMP

where deptno=(select deptno from DEPT where loc='DALLAS') order by ename;

7) How many people earn less than the maximal salary for the department

located in NEW YORK.

7) select count(empno) from EMP where sal<(select max(sal) from EMP where

deptno=(select deptno from DEPT where loc='NEW YORK'));

8) Find the number of people who supervise the others.

8) select count(M.empno) from EMP M where exists

(select E.empno from EMP E where M.empno=E.mgr);

9) Find all employees (personal number name, profession and salary),

who earn less than the average salary for people with salaries in the

third grade. Display results order by salaries ascending.

9) select empno,ename,job,sal from EMP where sal<=(select AVG(sal)

from EMP where sal between(select losal from SALGRADE where grade=3) and

(select hisal from SALGRADE where grade=3)) order by sal;

10) For each job in each grade find the average yearly income. Order results by jobs.

10) select avg(12*E.sal+NVL(comm,0)),job,grade from EMP E, SALGRADE S where E.sal between S.losal and S.hisal group by E.job, S.grade

order by job;



Wyszukiwarka

Podobne podstrony:
Multilayer Composite Print 2
5 Maerchen PRINT
jp5 print
Attribution of Hand Bones to Sex and Population Groups
Multilayer Composite Print (3)
pcb pdf, Top Paste Mask Print
Multilayer Composite Print
Multilayer Composite Print
l16 print
Party Games for Large Groups of Teenagers
Print your own organs[1]
tpd print screeny
Test for functional groups
jp2 print
2012 AMI wyklad print cz1
09 COMPARE GROUPS answers
6023 print at home original id Nieznany
Ch5 Layers & Layer Groups

więcej podobnych podstron