السلام عليكم و رحمة الله و بركاته

كيف الحال؟؟


ممكن تساعدوني في حل هذه الأسئلة في 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.


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


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.




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.




h. List names of all employees that have exactly two A ‘s in the name (not necessarily consecutive.)



i. List name and salary of all employees who earn less than their immediate subordinates.

j. List name of all departments that have no employees.

k. List employee name, job, salary, grade, and department Name of all employees except clerks. Sort on salary displaying highest salary first.





l. Write a SQL query that would accept a string as a parameter, and verify that it is in the format m*n, where m, and n are alphabets. Your query should display the parameter string together with a ‘Y’ if the string format is valid, ’N’ otherwise.








مع خالص الشكر و الاحترام

السلام عليكم ورحمه الله وبركاته
إليك ياخى اجابه السوال الاول رقم b وإن شاء الله اكون معك فى باقى الاسئله

select empno,e.deptno,dname,sal
from emp e,dept
where dept.deptno=e.deptno
and sal =(select max(sal) from emp where deptno=e.deptno group by deptno);

تم تعديل 15 ديسمبر 200718 سنة بواسطة amr fawzy

select empno,e.deptno,dname,salfrom emp e,deptwhere dept.deptno=e.deptno
and sal>(select max(sal) from emp
Where deptno=e.deptno group by deptno and dname='RESEARCH department':)

مساعده وحب الخيــر.. مو متآكده ميه ميه انها صح بس اذا غلط احد يصححها جزاكم الله خير

نسيت اقول ان الاجابه السابقه كانت لفقره c

السلام عليكم ورحمه الله وبركاته
مجهود طيب منك فى محاولتك للاجابه والاجابه شبه صحيحه وإليك الاحابه الصحيحه

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);

تم تعديل 16 ديسمبر 200718 سنة بواسطة amr fawzy

مشكورين اخواني على التجاوب معي في حل الأسئلة و اتمنى تجدوا حل بقية الأسئلة لأني فعلا حاولت اجاوب بس ما ظهر لي اي نتيجة و لكن،


اجبت على بعض الأسئلة و لكنني لست متأكدة من الإجابة ارجو تصحيح الإجابة إذا كانت خاطئة

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

SQL> select ename,empno,job
from emp,dept
where dept.deptno=emp.deptno
and
dept.deptno not between 20 and 30;

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

SQL> select ename,sal ||' PRESIDENT ' from emp
2 where sal in (select max(sal) from emp group by deptno);

k. List employee name, job, salary, grade, and department Name of all employees except clerks. Sort on salary displaying highest salary first.

SQL> select ename,job,sal,grade,deptno
2 from emp,salgrade
3 where job not like 'CLERK'
4 order by sal;

و شكرا على التعاون و جزاكم الله خير الجزاء

السلام عليكم ورحمه الله وبركاته
إجابه السؤال الرابع

select empno,ename
from emp e
where sal>(select avg(sal) from emp
where deptno=e.deptno
group by deptno);

نرجو المشاركه من باقى الاعضاء حتى تعم الاستفاده وجزاكم الله كل خير وإليك ياخونى شرح جيد ل sql مفيد جدا لكل المبتدئين
ومن له رغبه فى البدء فى عالم اوراكل نسالكم دعوه صالحه

SQL.DOC

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

امممم والله هنا شاكه كثيررر لأني تعاملت على ان الرئيس له جدول مستقل وربطت بينه وبين المستخدم

ياليت اخوي عمر يتكرم بالتصحيح اذا كان فيه غلط <= ومنك نستفيد يااستاذ

الحل السابق حل فقره g

شكرا جزيلا على المتابعة الجادة و المفيدة

كما اتقدم بالشكر الجزيل على الملف الكامل و المفيد ساقوم بطباعته و قرائته و التعلم منه

جزاك الله خير الجزاء

مع احترامي

select emp-name,emp-sal
from emp,dep
where dept.deptno=e.deptno and emp-sal>(select avg(emp-sal) from dep);

هذا الحل لفقره e

ونترك المجال لغيري .. وان شاء الله راح ارجع واشوف الموضوع مره ثانيه احد حلها كامل والا اساعد

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> 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

مشكورين اخواني على التجاوب مع الموضوع

سأريكم إجاباتي و محاولاتي لبقية الأسئلة و اتمنى تصحيحها

شاكرتا لكم حسن تعاونكم

و الله يعطيكم الف عافية


مع احترامي

السلام عليكم و رحمة الله و بركاته

بداية اود أن اشكر كل من اضاف اجابات في هذه الصفحة و اود ان اعرض عليكم محاولاتي

هذه إجاباتي على جميع الأسئلة الواردة

اتمنى ان يتم تصحيح الخطأ

Perform the following queries with regards to the tables available to the user SCOTT in personal Oracle 10g on your computer:

a) List names of all employees who live in DALLAS.

SQL>  select ename 
 2   from emp,dept
 3   where emp.deptno=dept.deptno
 4   and loc= 'DALLAS';

ENAME
----------
SMITH
JONES
SCOTT
ADAMS
FORD

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> 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

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

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);

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.

select empno,ename
from emp e
where sal>(select avg(sal) from emp
where deptno=e.deptno
group by deptno);

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.

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

h. List names of all employees that have exactly two A ‘s in the name (not necessarily consecutive.)

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

ENAME
----------
ADAMS

i. List name and salary of all employees who earn less than their immediate subordinates.


j. List name of all departments that have no employees.



k. List employee name, job, salary, grade, and department Name of all employees except clerks. Sort on salary displaying highest salary first.

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.

l. Write a SQL query that would accept a string as a parameter, and verify that it is in the format m*n, where m, and n are alphabets. Your query should display the parameter string together with a ‘Y’ if the string format is valid, ’N’ otherwise.

مع احترامي للجميع

الأسئلة السابقة تتضمن الكود مع ال output

عاجل جدا

غدا يوم الأثنين (31/12/2007) سيتم تسليم الأسئلة فأرجو ممن لديهم معرفة بحل الأسئلة تصحيحها و اضافة اللازم

و جزاكم الله خير الجزاء

مع احترامي للجميع