يمكن تفيدوني في تصحيح الإجابات في لغة Sql*plus

[sql]

SQL> select ename

2 from emp,dept

3 where emp.deptno=dept.deptno

4 and loc= 'DALLAS';

ENAME

----------

SMITH

JONES

SCOTT

ADAMS

FORD

[/sql]

b. List NAME,DEPARTMENT NUMBER, and SALARY for all employees with the comment HIGHEST PAID next to the salary of those employees whose in highest in their department.

[sql]

SQL> SELECT ENAME,DEPTNO,SAL "HIGHEST PAID"

FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

ENAME DEPTNO HIGHEST PAID

---------- ---------- ------------

BLAKE 30 2850

KING 10 5000

FORD 20 3000

[/sql]

c. List NAME,DEPARTMENT NO, AND SALARY of all employees who earn more than the maximum salary of an employee in the RESEARCH department.

[sql]

select empno,e.deptno,dname,sal

from emp,dept

where dept.deptno=e.deptno

and

sal>(select max(sal) from emp e,dept d

where d.deptno=e.deptno

and dname ='research'

group by deptno);

[/sql]

d. List NAME,JOB of all employees, and : The Employee’s salary if employee earns more than 1500; the message MET THE TARGET if employee earns exactly 1500; The message BELOW 1500 IF EMPLOYEE EARNS LESS THAN 1500.

e. List employee name,employees who earn more than the average salary of an employee in their department.

[sql]

select empno,ename

from emp e

where sal>(select avg(sal) from emp

where deptno=e.deptno

group by deptno);

[/sql]

f. List name and salary of all employees who earn more than the average salary of an employee in their department.

g. List the name and salary of the highest paid employee beside the PRESIDENT.

[sql]

select dep-name,dep-salary,PRESIDENT-name

From employee,PRESIDENT

Where dep-sla>(select max(sal) from employee)

and dep-no=PRESIDENT.no

group by dep-name

[/sql]

[sql]

SQL> select ename from emp where ename like '%A%A%';

ENAME

----------

ADAMS

[/sql]

[sql]

SQL> select ename,job,sal,grade,deptno

2 from emp,salgrade

3 where job not like 'CLERK'

4 order by sal;

ENAME JOB SAL GRADE DEPTNO

---------- --------- ---------- ---------- ----------

WARD SALESMAN 1250 2 30

WARD SALESMAN 1250 1 30

WARD SALESMAN 1250 5 30

MARTIN SALESMAN 1250 1 30

MARTIN SALESMAN 1250 2 30

WARD SALESMAN 1250 4 30

MARTIN SALESMAN 1250 3 30

WARD SALESMAN 1250 3 30

MARTIN SALESMAN 1250 5 30

MARTIN SALESMAN 1250 4 30

TURNER SALESMAN 1500 1 30

ENAME JOB SAL GRADE DEPTNO

---------- --------- ---------- ---------- ----------

TURNER SALESMAN 1500 5 30

TURNER SALESMAN 1500 4 30

TURNER SALESMAN 1500 3 30

TURNER SALESMAN 1500 2 30

ALLEN SALESMAN 1600 3 30

ALLEN SALESMAN 1600 2 30

ALLEN SALESMAN 1600 4 30

ALLEN SALESMAN 1600 5 30

ALLEN SALESMAN 1600 1 30

CLARK MANAGER 2450 5 10

CLARK MANAGER 2450 2 10

ENAME JOB SAL GRADE DEPTNO

---------- --------- ---------- ---------- ----------

CLARK MANAGER 2450 3 10

CLARK MANAGER 2450 4 10

CLARK MANAGER 2450 1 10

BLAKE MANAGER 2850 2 30

BLAKE MANAGER 2850 3 30

BLAKE MANAGER 2850 1 30

BLAKE MANAGER 2850 4 30

BLAKE MANAGER 2850 5 30

JONES MANAGER 2975 3 20

JONES MANAGER 2975 4 20

JONES MANAGER 2975 2 20

ENAME JOB SAL GRADE DEPTNO

---------- --------- ---------- ---------- ----------

JONES MANAGER 2975 5 20

JONES MANAGER 2975 1 20

SCOTT ANALYST 3000 1 20

FORD ANALYST 3000 3 20

SCOTT ANALYST 3000 4 20

SCOTT ANALYST 3000 3 20

SCOTT ANALYST 3000 5 20

FORD ANALYST 3000 2 20

FORD ANALYST 3000 1 20

FORD ANALYST 3000 5 20

FORD ANALYST 3000 4 20

ENAME JOB SAL GRADE DEPTNO

---------- --------- ---------- ---------- ----------

SCOTT ANALYST 3000 2 20

KING PRESIDENT 5000 3 10

KING PRESIDENT 5000 1 10

KING PRESIDENT 5000 5 10

KING PRESIDENT 5000 2 10

KING PRESIDENT 5000 4 10

50 rows selected.

[/sql]