مثبت دورة تشمل اهم موضوعات Sql في 19 درس

[programmer_85]

حل التمرين السابق :

select deptno,avg(sal ) from emp where sal>2000 group by deptno
having avg(sal)=(select max(avg(sal)) from emp where sal>2000 group by deptno);

[programmer_85]

الدرس الثالث عشر

في هذه الجلسة ان شاء الله سوف نتناول كيفية عمل subquery داخل جملة select اي بين جملة ال select

وبين الكلمة المحجوزة form او مايسمى Expression .


ماهي الصيغة العامة للــ Expression


select exp1,exp2,...,(select exp from table_name
...)[col_alias] .... from table_name

سوف ناخذ امثلة على ذلك :


المثال الاول:

قم بعرض كلا من ename,job,sal,deptno,dname حيث ان الــ dname يكون في جملة expression .

select ename,job,sal,deptno,(select dname from dept[/size][/b]
[b][size=5]where deptno=emp.deptno) ds from emp;[/size][/b]
[b][size=5]

سوف نقوم بتحليل هذا الكود :

اولا قمنا بجلب كلا من ename,job,sal,deptno معا ومن المعلوم انهم كلهم في جدول emp .

لكن العمود dname في جدول dept فكيف يمكن ان نجلبه مستخدمين في هذا الكيوري expression

فاولا نقوم فقط بوضع كل عمود كما هو معتاد بعد جملة select ومن ثم نظر اذا كان يوجد عمود ضمن المطلوب

وهذا العمود هو في جدول اخر فلابد ان نتسخدم هنا جملة select في expression .

اذا عرفنا كيف نضع ونفرق بين كلا من الاعمدة اذا كانت مطلوبة لاظهار وقد يكون بينها في جدول اخر .

نقوم الان بتحليل جملة select التي داخل expression وكيف تم عملها.

هذه هي الجملة ..

(select dname from dept where deptno=emp.deptno)

حيث يتم العمل في هذه الجملة بانه يقوم بجلب اسماء الدوائر معتمدا على ذلك في المقارنة

بين رقم الدائرة في جدول الاقسام وبين رقم الدائرة في جدول الموظفين.

عندما ننفذ هذا الكود لحالة سوف يعطينا خطأ لانه لايوجد هنا شي اسمه emp

ولكن استدعيناه لانه داخل كيوري اخر وهو في الاصل موجود .. لذا لايمكن ان نفصل بينهما

لاننا عملنا كيوري داخل كيوري اخر .. وهذا شي مرئي امامنا .. وانما فصلنا هنا لكي نوضح

كيف تتم عملية select داخل expression فقط للتوضيح ..

بعد ان تجلب كل البيانات سوف تضعها الان في عمود اسمه ds وهو col_alias فقط اعطينه اسم

حتى لايطول الاسم في المخرجات وان لم تعطيه الاسم ds فهو صحيح .

بعد ذلك سوف يبداء بتنفيذ الكيوري الاساسي حيث ان كلا الاعمدة رجعت بعض القيم ومن ثم يتم العرض وفقا لذلك الكيوري

سوف تكون المخرجات بهذا الشكل :

ENAME JOB SAL DEPTNO DS
---------- --------- ---------- ---------- --------------
SMITH CLERK 800 20 RESEARCH
ALLEN SALESMAN 1600 30 SALES
WARD SALESMAN 1250 30 SALES
JONES MANAGER 2975 20 RESEARCH
MARTIN SALESMAN 1250 30 SALES
BLAKE MANAGER 2850 30 SALES
CLARK MANAGER 2450 10 ACCOUNTING
SCOTT ANALYST 3000 20 RESEARCH
KING PRESIDENT 5000 10 ACCOUNTING
TURNER SALESMAN 1500 30 SALES
ADAMS CLERK 1100 20 RESEARCH
JAMES CLERK 950 30 SALES
FORD ANALYST 3000 20 RESEARCH
MILLER CLERK 1300 10 ACCOUNTING

المثال الثاني:

قم بعرض deptno ,max(sal),sal ومن ثم قم بطرح راتب كل موظف من اكبر راتب للموظفين .

select[code] deptno

,(select max(sal) from emp) maxsal,
sal,((select max(sal) from emp) -sal) difference from emp;
[/code]

نحلل الكود معا:
العمود الاول :

deptno

وهو رقم القسم ولا اشكال فيه .

العمود الثاني :

(select max(sal) from emp) maxsal

يقوم بجلب اكبر راتب للموظفين ويعطيه اسم maxsal.

العمود الثالث:

sal

يقوم فقط بجلب الراتب لكل موظف .

العمود الرابع:

((select max(sal) from emp) -sal) difference from emp;

يقوم بجلب اكبر راتب من جدول الموظفين مرة ثانية كما كان في العمود الثاني .

لكن وجود قليل في الاختلاف بينهما ان هنا عندما يقوم بجلب اكبر راتب يقوم بطرحه وتنقيصه

من كل راتب موظف ويضعه في col_alias اسمه difference

سوف تكون المخرجات كالاتي:

DEPTNO MAXSAL SAL DIFFERENCE
---------- ---------- ---------- ----------
20 5000 800 4200
30 5000 1600 3400
30 5000 1250 3750
20 5000 2975 2025
30 5000 1250 3750
30 5000 2850 2150
10 5000 2450 2550
20 5000 3000 2000
10 5000 5000 0
30 5000 1500 3500
20 5000 1100 3900
30 5000 950 4050
20 5000 3000 2000
10 5000 1300 3700

هناك العديد من الامثلة لكن اقتصرنا على اثنين فقط لكن الفكرة موصله وبسيطة .

ارجو ان يكون الشرح وافي ومفهوم .

تمرين: قم بعرض كلا من deptno,dname ,max(sal) حيث ان max(sal) لايتربط الا فقط مع ارقام الاقسام deptno التي لديها موظفين

يعني رقم القسم deptno =40 لايظهر في المخرجات لانه غير مرتبط باي موظف .

[المفيد]

انا معاكم

[programmer_85]

حل التمربن السابق :


select dname,deptno,(select max(sal)
from emp e where deptno=e.deptno) maxsal from dept
where deptno in(select deptno from emp m
where m.deptno=dept.deptno) ;


حيث ان المخرجات سوف تكون كالاتي :

DNAME DEPTNO MAXSAL
-------------- ---------- ----------
ACCOUNTING 10 5000
RESEARCH 20 5000
SALES 30 5000

[programmer_85]

الدرس الرابع عشر

اكملنا في جسلتنا السابقة كيفية عمل subquery كتابته في expression وراينا ذلك من خلال الامثلة .

اليوم ان شاء الله سوف تناول كيفية كتابة subquery بعد جملة form .



قبل ان ندخل في صلب الموضوع يجب علينا ان نعرف شئيين مهمين وهما :

ROWID-1
2- ROWNUM.

ماهو ROWID؟؟

ان كل صف في الجدول لديه رقم فريد يمسى ROWID .

حيث ان ROWID يحتوي من ثلاثة اشياء وهما :

الاول: USER NAME

الثاني: TABLE_NAME

الثالث: POSITION OF ROW.

لنرى ذلك من خلال الامثلة :

SELECT ROWID ,EMPNO,ENAME FROM EMP;[/b][/size]
[size=5][b]

حيث ان المخرجات سوف تكون الاتي :

][/b]

ROWID EMPNO ENAME
------------------ ---------- ----------
AAAMgzAAEAAAAAgAAA 7369 SMITH
AAAMgzAAEAAAAAgAAB 7499 ALLEN
AAAMgzAAEAAAAAgAAC 7521 WARD
AAAMgzAAEAAAAAgAAD 7566 JONES
AAAMgzAAEAAAAAgAAE 7654 MARTIN
AAAMgzAAEAAAAAgAAF 7698 BLAKE
AAAMgzAAEAAAAAgAAG 7782 CLARK
AAAMgzAAEAAAAAgAAH 7788 SCOTT
AAAMgzAAEAAAAAgAAI 7839 KING
AAAMgzAAEAAAAAgAAJ 7844 TURNER
AAAMgzAAEAAAAAgAAK 7876 ADAMS
AAAMgzAAEAAAAAgAAL 7900 JAMES
AAAMgzAAEAAAAAgAAM 7902 FORD
AAAMgzAAEAAAAAgAAN 7934 MILLER

حيث ان ROWID يبدا بالتسلسل منحروف معينة ومن ثم يختلف في الاخير


AAAMgzAAEAAAAAgAAA


حيث ان الحرف الاخير هو A

في المقابل ناخذ ROWID الثاني وسوف نرى الاختلاف في الحرف الاخير وهو B



AAAMgzAAEAAAAAgAAB


وهكذا في بقية الصفوف .


الثاني: ROWNUM

ان ROWNUM هو نفس ROWID اي انه يكون فريد لكن نرى ان ROWNUM يرجع ارفام مثل 1 ..2..3..4... اي ارقام تسلسلية .

مثال على ذلك :

SELECT ROWNUM ,ENAME FROM EMP;[/b][/size]
[size=5][b]

سوف تكون المخرجات كاالاتي:




ROWNUM ENAME
---------- ----------
1 SMITH
2 ALLEN
3 WARD
4 JONES
5 MARTIN
6 BLAKE
7 CLARK
8 SCOTT
9 KING
10 TURNER
11 ADAMS
12 JAMES
13 FORD
14 MILLER



مثال اخر:

SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE JOB='CLERK';[/b][/size]
[size=5][b]

سوف تكون المخرجات كالاتي:

][/b]

ROWNUM ENAME JOB SAL
--------- ---------- --------- ----------
1 SMITH CLERK 800
2 ADAMS CLERK 1100
3 JAMES CLERK 950
4 MILLER CLERK 1300

كيفية كتابة الشرط على ROWNUM باستخدام جملة WHERE CLAUSE .

المثال الاول:

SELECT ROWNUM,EMPNO,ENAME,JOB,DEPTNO FROM EMP WHERE ROWNUM<=5;[/b][/size]
[size=5][b]

حيث المخرجات ستكون كالاتي:

][/b]

ROWNUM EMPNO ENAME JOB DEPTNO
------- ---------- ---------- --------- ----------
1 7369 SMITH CLERK 20
2 7499 ALLEN SALESMAN 30
3 7521 WARD SALESMAN 30
4 7566 JONES MANAGER 20
5 7654 MARTIN SALESMAN 30

مثال ثاني:

SELECT ROWNUM,EMPNO,ENAME,JOB,DEPTNO FROM EMP WHERE ROWNUM>=5;

سوف تكون المخرجات كالاتي:


no rows selected


في الحقيقة عندما نتسخدم هذا الكيوري حيث ان الشرط يقول بان ROWNUM يكون اكبر او يساوي 5

من المفروض ان يظهر لنا كل من العدد 5 ومافوق .. لكن حقيقة سوف يعطي لنا انه لاتوجد صفوف .

من هنا فصاعدا كان ينبغى علينا ان نتسخدم كتابة جملة FORM لكي تحقق ذلك .

نرى مثال اخر ايضا:

SELECT ROWNUM,EMPNO,ENAME,JOB,DEPTNO FROM EMP[/b][/size]
[size=5][b]WHERE ROWNUM BETWEEN 5 AND 8;

ايضا تكون المخرجات انها لايوجد صفوف .

no rows selected


اذا كما ذكرنا انفا انه لابد من استخدام جملة FORM لتحقيق ذلك ..


ماهي الصيغة العامة لذلك ..

][/b]
SELECT ...... FROM (SELECT QUERY) [TABLE_ALIAS];

حيث ان ROWNUM المستخدم كافي الكيوري الداخلي INNER QUERY لن يزود ولن يكون مدعوم مع مخرجاته الى OUTER QUERY ..

فلاستخدام ذلك ينبغى علينا ان اي اخذ مخرجات ROWNUM من INNER QUERY الى OUTER QUERY يجب علينا ان نتسخدم column alias ..

نرى ذلك عبر الامثلة :

المثال الاول:

select * from ( select ename,job,sal,deptno from emp)e;

المخرجات كالاتي:

][/b]

ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 800 20
ALLEN SALESMAN 1600 30
WARD SALESMAN 1250 30
JONES MANAGER 2975 20
MARTIN SALESMAN 1250 30
BLAKE MANAGER 2850 30
CLARK MANAGER 2450 10
SCOTT ANALYST 3000 20
KING PRESIDENT 5000 10
TURNER SALESMAN 1500 30
ADAMS CLERK 1100 20
JAMES CLERK 950 30
FORD ANALYST 3000 20
MILLER CLERK 1300 10

المثال الثاني:

select empno,ename,job,pay,deptno from
(select empno,ename,job,sal pay,deptno from emp)e
where pay <1000;

لاحظ المثال اسابق انه ايضا لايحتوي على rownum ..

لكن الشي المهم في ذلك ان لدينا inner query ,ويحتوي على الاعمدة empno.ename,job,deptno,sal حيث ان sal اعطيناه اسم مستعار او مايسمى

col_alias باسم pay .

اما في outer query فانه يحتوي على الاعمدة وهي نفسها في inner query باستثناء sal حيث ان اسم sal في outer query هو pay

وهذا يفيد ان col_alias الذي موجود في inner QUERY سوف يصبح اسم حقيقي في OUTER Q. فاذا كتبنا اسم sal في outerquery وهو في الاصل

في inner query باسم مستعار فسوف يقول كل ان sal لايوجد اي يعطي لك خطأ.

سوف تكون المخرجات كالاتي:

][/b]


EMPNO ENAME JOB PAY DEPTNO
------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 20
7900 JAMES CLERK 950 30

المثال الثالث:

select rownum rwi,empno,ename,sal ,deptno
from( select rownum ,empno,ename,sal,deptno from
emp order by sal)e;

نفس المثال السابق فقط الاختلاف اننا اضفنا rownum واعطيناه فقط col alias

سوف تكون المخرجات كالاتي:

][/b]


RWI EMPNO ENAME SAL DEPTNO
--------- ---------- ---------- ---------- ----------
1 7369 SMITH 800 20
2 7900 JAMES 950 30
3 7876 ADAMS 1100 20
4 7521 WARD 1250 30
5 7654 MARTIN 1250 30
6 7934 MILLER 1300 10
7 7844 TURNER 1500 30
8 7499 ALLEN 1600 30
9 7782 CLARK 2450 10
10 7698 BLAKE 2850 30
11 7566 JONES 2975 20
12 7788 SCOTT 3000 20
13 7902 FORD 3000 20
14 7839 KING 5000 10

المثال الرابع :

سوف نقوم بعرض من هو الموظف الذي لديه rownum الخامس فقط.

select rownum rwi,empno,ename,sal ,deptno
 from( select rownum rwi ,empno,ename,sal,deptno from
 emp order by sal)e
where rwi=5;

حيث ان الشرط لل rownum يجب ان يكون على حسب الاسم الذي في inner query

حيث ان في inner query هو rwi فاذا قمنا بتغيره مثلا الى rowno ولم نكتبه في outer query وابقينا

في الشرط rwi سوف يعطي لنا خطا.. اذا rownum ينفذ حسب الذي داخل inner query .

سوف تكون المخرجات كالاتي:

][/b]


RWI EMPNO ENAME SAL DEPTNO
------- ---------- ---------- ---------- ----------
1 7654 MARTIN 1250 30

المثال الخامس:

سوف نعرض كل الموظفين الذين بين rownum 5 و 8 .

select rownum rwi,empno,ename,sal ,deptno
 from( select rownum rwi ,empno,ename,sal,deptno from
 emp order by sal)e
where rwi between 5 and 8;

في الحقيقة هذا المثال قد راينه سابقا من دون ان نتسخدم جملة form فعندما نفذناه لم يعطي لنا اي صف لكن هنا راينا كيف تم التنفيذ عبر جمله form .

حيث ان المخرجات سوف تكون كالاتي:

][/b]

RWI EMPNO ENAME SAL DEPTNO
------ ---------- ---------- ---------- ----------
1 7654 MARTIN 1250 30
2 7782 CLARK 2450 10
3 7698 BLAKE 2850 30
4 7788 SCOTT 3000 20

وهكذا بقية الامثلة خمن على ذلك ...

ارجو ان تكون قد وصلت الفكرة بشكل مفهوم ..

التمرين الاول: قم بعرض الموظفين الذي لديهم rownum زوجي مثل 2..4..6..8..
التمرين الثاني: قم بعرض الموظفين الذي لديهم rownum حيث انه يكون الفارق 4 مثل ..4..8..12....
التمرين الثالث: قم بعرض الموظف الذي يكون ترتيبه اخر واحد في الجدول اي لو كان لدينا rownum من 1..100
يقوم بعرض الموظف الذي هو اخر واحد وهو rownum يساوي 100.

اراكم ان شاء الله في الدرس القادم

[programmer_85]

حل التمرين الاول :

select rownum ,ename,job,sal,deptno from
( select rownum rwi,ename,job,sal,deptno from emp)e
where mod(rwi,2)=0;

حل التمرين الثاني:


select rownum ,ename,job,sal,deptno from
( select rownum rwi,ename,job,sal,deptno from emp)e
where mod(rwi,4)=0;

حل التمرين الثالث:

select rownum ,ename,job,sal,deptno from
( select rownum rwi,ename,job,sal,deptno from emp)e
where rwi=(select count(*) from emp);

[programmer_85]

الدرس الخامس عشر

كيفية استخدام المعاملات some/any/all

حيث ان المعاملان some/any سوف يسمحان للكيوري بارجاع العديد من الصفوف في outer query والذي يكون في الاصل علاقة المعاملات فيمابيهم.

حيث ان هذا المعاملان يعاملان من كل المعاملات امثال =,<>,<=,>= ... الخ.

المثال الاول:

سوف نقوم بعرض بعرض كلا من empno,ename,job,sal,deptno للموظفين الذين يعملون كنفس الوظيفة ورقم القسم مثل الموظف scott.

select empno,ename,job,sal,deptno from emp 
where (job,deptno)=ANY (select job,deptno from emp where ename='SCOTT');[/size]
[size=5]

كما اشارنا سابقا يقوم هذا الكيوري بعرض الموظفين الذي لديهم نفس الوظيفة ورقم القسم مثل SCOTT

ونلاحظ ان سوف يرجع هذا الكيوري اكثر من صف ووفي نفس الشي اكثر من عمود MULTIPLE COLS-MULTIPLE ROWS

لاننا استخدمنا اكثر من عمود في جملة الشرط وفي نفس الوقت استخدمنا ANY التي تاتي باي يصف حقق هذا الكيوري

سوف تكون المخرجات كالاتي:

EMPNO ENAME JOB SAL DEPTNO
------ ---------- --------- ---------- ----------
7902 FORD ANALYST 3000 20
7788 SCOTT ANALYST 3000 20

المثال الثاني:

سوف نقوم بعرض كلا من empn,ename,job,sal للموظفين الذين راتبهم اقل من اي راتب موظف بشرط ان يكون الذي اقله منه وظيفته clerk

select empno,ename,job,sal from emp
where sal< any (select sal from emp
where job='CLERK');[/size]
[size=5]

في الكود السابق اشترطنا ان يكون هذا الموظف يكون راتبه اقل من راتب اي واحد منهم بشرط الوظفية تكون CLERK

سوف تكون المخرجات كالاتي:

EMPNO ENAME JOB SAL
------- ---------- --------- ----------
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250

المثال الثالث:

نقوم بعرض كلا من empno,ename,job,sal للموظف الذي يكون راتبه اقل من راتب اي موظف بحيث ان الوظفية لاتساوي clerk

select empno,ename,job,sal from emp
where sal< any (select sal from emp
where job='CLERK') and job<> 'CLERK';

في الاصل هذا الكيوري هو نفس الاول الا انه فقط يختلف في ان الوظيفة لاتساوي CLERK بعد ان

نجلب INNER QUERY سوف يجلب خمسه صفوف حيث 3 صفوف تحتوي على CLERK و2 صفوف على SALESMAN

فنحن نريد فقط 2 صفوف التي هي SALESMAN

حيث ان المخرجات تكون بالشكل الاتي:

EMPNO ENAME JOB SAL
------- ---------- --------- ----------
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250

المثال الرابع:

سوف نقوم بعرض الموظفين الذين راتبهم اكبر من راتب بعضهم حيث ان هولاء الموظفين يعملون في القسم 10

select empno,ename,job,sal from emp
where sal> SOME (select sal from emp
where DEPTNO=10) ;

سوف تكون المخرجات كالاتي:

EMPNO ENAME JOB SAL
------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7902 FORD ANALYST 3000
7788 SCOTT ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500

المعامل ALL:

المعامل ALL يسمح لSUBQUERY بارجاع العديد من الصفوف حتى ولو كان الشرط في OUTER QUERY يعمل علاقة فيما بينهم .

ناخذ مثال على ذلك:

نقوم بعرض كلا من الموظفين الذين راتبهم اكبر من كل اراتب الموظفين الذين وظفيتهم هي CLERK لكن لاتساوي الوظفية CLERK

SELECT ENAME,JOB,SAL,DEPTNO FROM EMP
WHERE SAL> ALL(SELECT SAL FROM EMP WHERE JOB='CLERK')AND JOB<>'CLERK';

حيث ان المخرجات سوف تكون كالاتي :

ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 30
JONES MANAGER 2975 20
BLAKE MANAGER 2850 30
CLARK MANAGER 2450 10
SCOTT ANALYST 3000 20
KING PRESIDENT 5000 10
TURNER SALESMAN 1500 30
FORD ANALYST 3000 20

ارجو ان تكون قد وصلت الفكرة.

تمرين: قم بعرض الموظفين الذين راتبهم اكبر من كل معدل رواتب الموظفين الذي معتمدا على كل قسم في ذلك

[Amgad]

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

الأخ الفاضل

شكرا لك على هذا الجهد المتميز من عضو متميز يريد مساعدة الأخوة الأعضاء في نشر العلم وتعلمه

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

سيتم تثبيت الموضوع

[programmer_85]

حل التمرين السابق :


SELECT empno, ename, job
FROM emp
WHERE sal > ALL
( SELECT AVG(sal)
FROM emp
GROUP BY deptno ) ;

[programmer_85]

الدرس السادس عشر

ذكرنا سابقا ان ال subquery يحتوي على اربعة اشياء في مرحلة التنفيذ وهي :

اما ان يكون subquery :

1-single column -single row

2- single column -multiple rows

3-multiple columns-single row

4- multiple cloumns-multiple rows


في الحقيقة ذكرنا الكثير من المثلة في الدروس السابقة تبين بعض من تلك الانواع في subquery ولكن لم نوضح ونبين تلك الانواع ..

سنتكل هنا في هذه الجلسة فقط على النوعين الاول والثاني :

النوع الاول: single column -single row

ان هذا يعتمد فقط على ارجاع صف وحيد مقابل فقط عمود واحد ..

مثال :
سوف نقوم بعرض الموظفين الذي رقم ادراتهم هي نفس رقم ادارة SCOTT

select empno,ename,job,sal ,deptno
from emp where deptno=(select deptno from emp where
ename='SCOTT');

اذا كيف نعرف هنا انه هذا الكيوري يقوم بارجاع صف وحيد ..

نلاحظ ان INNER QUERY هو في الاصل يهتم بالصف

select deptno from emp where
ename='SCOTT';[/b][/size]
[size=5][b]

حيث ان الكيوري هذا INNER QUERY سوف يقوم بارجاع صف واحد فقط وهو رقم الادارة الذي يحتويه SCOTT

حيث ان رقم الادارة هو 20 هذا فقط صف واحد قام بارجاعه INNER QUERY

اذا سوف يكون OUTER QUERY بعد ارجاع رقم القسم 20 كالشكل الاتي :

[/b][/size]
[size=5][b]select empno,ename,job,sal ,deptno
from emp where deptno=20;

اذا نلاحظ ايضا ان OUTER QUERY يحتوي على column واحد وهو deptno

مثال اخر:

select deptno,sal from emp where sal>(select avg(sal) from emp);

حيث ان avg(sal) سوف يكون 2073.21429

ومن ثم سوف يعرض كل الرواتب التي هي اكبر من هذا المعدل .

اذا المثالين السابقين يطبقان على الحالة الاولى single column-single row


النوع الثاني : single column -multiple rows

في هذا النوع سوف يقوم الكيوري بارجاع العديد من الصفوف من خلال فقط عمود واحد

مثال:

select empno,ename,job,deptno from emp
where deptno in(select deptno from emp where ename like '%A%');

حيث ان INNER QUERY وهو الكيوري الثاني سوف يرجع كالاتي :

select deptno from emp where ename like '%A';

سوف تكون المخرجات لINNER QUERY هو اكثر من صف مقابل فقط عمود واحد في OUTER QUERY

][/b]

DEPTNO
-------
30
30
30
30
10
20
30

اذا سوف يكون OUTER QUERY كالاتي:

select empno,ename,job,deptno from emp
where deptno in(30,30,30,30,10,20,30);

سوف تكون المخرجات النهاية التي تظهر لنا هي :

][/b]

EMPNO ENAME JOB DEPTNO
------ ---------- --------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
7654 MARTIN SALESMAN 30
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10
7844 TURNER SALESMAN 30
7876 ADAMS CLERK 20
7900 JAMES CLERK 30
7902 FORD ANALYST 20
7934 MILLER CLERK 10

مثال اخر :

select empno,ename,job,sal,deptno from emp
where sal < any (select losal from salgrade);[/b][/size]
[size=5][b]

ناخذ اولا inner query ونرى ماهي مخرجاته

select losal from salgrade;

حيث ان المخرجات تكون كالاتي :

][/b]

LOSAL
-------
700
1201
1401
2001
3001

اذا سوف يكون outer query كالاتي :

[/b][/size]
[size=5][b]select empno,ename,job,sal,deptno from emp
where sal < any (700,1201,1401,2001,3001);

وسوف تكون المخرجات كالاتي :

][/b]


EMPNO ENAME JOB SAL DEPTNO
--------- ---------- --------- ---------- ----------
7369 SMITH CLERK 800 20
7499 ALLEN SALESMAN 1600 30
7521 WARD SALESMAN 1250 30
7566 JONES MANAGER 2975 20
7654 MARTIN SALESMAN 1250 30
7698 BLAKE MANAGER 2850 30
7782 CLARK MANAGER 2450 10
7788 SCOTT ANALYST 3000 20
7844 TURNER SALESMAN 1500 30
7876 ADAMS CLERK 1100 20
7900 JAMES CLERK 950 30
7902 FORD ANALYST 3000 20
7934 MILLER CLERK 1300 10

مثال اخر :

select empno,ename,job,sal ,deptno from emp
where sal> all( select hisal from salgrade );[/b][/size]
[size=5][b]

ناخذ اولا ال inner query نرى ماهي المخرجات ..

select hisal from salgrade;

سوف تكون المخرجات كالاتي:

][/b]

HISAL
------
1200
1400
2000
3000
9999

اذا سوف يكون outer query كالاتي :

select empno,ename,job,sal ,deptno from emp
where sal> all( 1200,1400,2000,3000,9999);[/b][/size]
[size=5][b]

نلاحظ ان outer query مازال يتعامل فقط مع عمود واحد

وهنا هو sal حيث ان لابد ان يكون هناك راتب يكون اكبر من كل تلك الرواتب التي استرجناها في inner query

وسوف تكون المخرجات انه لايوجد اي راتب هو اكبر من تلك الرواتب التي في inner query

المخرجات كالاتي:

][/b]
no rows selected.

ارجو ان تكون قد وصلت الفكرة بشكل مفهوم .

ان شاء الله في الجلسة التالية سوف نتناول النوعين الاخريين .

[programmer_85]

الدرس السابع عشر

ذكرنا سابقا الاربعة الانواع في SUBQUERY وهي :

1-single column -single row

2- single column -multiple rows

3-multiple columns-single row

4- multiple cloumns-multiple rows

وتناولنا فيها القسمين الاوليين .. اليوم ان شاء الله في هذه الجلسة سوف ناخذ القسمين الاخريين وهما

3-multiple columns-single row

4- multiple cloumns-multiple rows

النوع الاول: multiple columns-single row

حتى الان لقد راينا كيف يتم الsubquery علي الصفوف المتعددة عندما يقوم بارجاع اكثر من صف .

في هذا النوع سوف نرى كيف يتم ارجاع اكثر من عمود من خلال subquery .

مثال على ذلك:

سوف نعرض empno,ename,job,sal,deptno للموظفين حيث ان deptno,sal

هما العمودان اللذان يوضعان في شرط الفلترة من اجل عرض اقل راتب موظف في كل قسم

select empno,ename,job,sal from emp where
(deptno,sal)in(select deptno,min(sal) from emp
group by deptno);

نحلل الكود وكيفية اجراء عمله .

ناخذ اولا inner query وهو :

select deptno,min(sal) from emp
group by deptno;[/b][/size]
[size=5][b]

حيث ان inner query سوف يقوم بارجاع اقل راتب موظف في كل دائرة

سوف تكون المخرجات لل inner query كالاتي:

][/b]

DEPTNO MIN(SAL)
-------- ----------
30 950
20 800
10 1300

حيث انا تلك المخرجات لاتظهر ابدا وانما تتم بها عملية المقارنة حسب الشرط في outer query

اذا لاحظ ان لدينا في شرط الفلترة ليس عمود واحد كما عهدناه من قبل .. بل هو عمودان كالاتي:

where (deptno,sal)

ولو نظرنا الى مخرجات inner query لراينا ان تلك المخرجات منفردة حسب كل عمود

فالراتب لديه 3 مخرجات وفقا لعدد الاقسام deptno

وسوف يكون outer query تحليليا بعد ان حصلنا على المخرجات كالاتي :

[/b][/size]
[size=5][b]select empno,ename,job,sal from emp where
(deptno,sal)in((30,950),(20,800),(10,1300));[/b][/size]
[size=5][b]

حيث ان المخرجات النهائية والتي هي المطلوبة سوف تكون كالاتي :

][/b]


EMPNO ENAME JOB SAL
-------- ---------- --------- ----------
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7934 MILLER CLERK 1300

مثال اخر :

سوف نقوم بعرض الموظفين وحيث ان الشرط سوف يكون فيها ثلاثة اعمدة .

select empno,ename,job,sal from emp
where (deptno,sal,mgr)in(select deptno,avg(sal),mgr from emp group by deptno,mgr);[/b][/size]
[size=5][b]

حيث ان هذا المثال يحتوي على ثلاثة اعمدة في شرط الفلترة .
inneryquery هو :

select deptno,avg(sal),mgr from emp group by deptno,mgr;[/b][/size]
[size=5][b]

بالنسبة للمخرجات في inner query سوف تكون كالاتي:

][/b]

DEPTNO AVG(SAL) MGR
------ -------- ------
20 2975 7839
10 2450 7839
30 1310 7698
20 3000 7566
10 1300 7782
20 800 7902
10 5000
30 2850 7839
20 1100 7788

اما بالنسبة للمخرجات في outer query سوف تكون كالمخرجات السابقة في inner query
باستثناء صف واحد وهو king الذي ليس لديه mgr وسوف يقوم باقصاه من outer query .

اذا المخرجات التي ستظهر وهي المطلوبة لدينا كالاتي:

][/b]

EMPNO ENAME JOB SAL
----- ---------- --------- ------
7566 JONES MANAGER 2975
7782 CLARK MANAGER 2450
7902 FORD ANALYST 3000
7788 SCOTT ANALYST 3000
7934 MILLER CLERK 1300
7369 SMITH CLERK 800
7698 BLAKE MANAGER 2850
7876 ADAMS CLERK 1100

وهكذا اذا كان لديك اربعة اعمدة او اكثر من ذلك .. الاهم في ذلك ان المخرجات التي تعود من inner query

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


النوع الثاني : multiple columns -multiple rows

عرفنا سابقا الصفوف المتعددة التي ترجع في الكيوري باكثر من صف وعرفنا كيف تتم ذلك عبر المعاملات any ,all,some,in

وفي الحقيقة ان الامثلة السابقة ربما قد تمثا اكثر من صف في الكيوري

ولكن هنا سنرى كيف نستخدم المعاملات all,any

مثال:

نفس المثال الاول لكن باستخدام المعامل any الذي يرجع اكثر من صف .

select empno,ename,job,sal from emp where
(deptno,sal)=any(select deptno,min(sal) from emp
group by deptno);

حيث ان المخرجات سوف تكون نفس المخرجات في المثال الاول وهي كالاتي :

][/b]

EMPNO ENAME JOB SAL
------ ---------- --------- ------
7900 JAMES CLERK 950
7369 SMITH CLERK 800
7934 MILLER CLERK 1300

اذا لماذا المخرجات كالمثال الاول لايوجد اختلاف

ببساطة لاننا استخدمنا المعامل any مع المعامل =

حيث ان المخرجات المستعادة من inner query هي سوف تكون نفسها الخرجات النهائية

وذلك بسبب انها سوف تعامل مقارنة مع كل الرواتب وارقام الاقسام وسوف يظهر تلك المخرجات هي نفسها

وذلك لانه عائد الى المعامل = لانها لاتوجد مخرجات غير تلك المخرجات في inner query مساوية لذلك

اذا سوف يظهر تلك المخرجات .

ملاحظة مهمة : لايمكن ان نتسخدم المعامل <,>,<=,>= لان لدينا اكثر من عمود للمقارنة

مثال اخر :

select empno,ename,job,sal from emp where
(deptno,sal)=all(select deptno,min(sal) from emp
group by deptno);[/b][/size]
[size=5][b]

نفس المثال السابق لكن فقط استخدمنا هنا المعامل all

حيث ان المخرجات من هذا الكيوري انها لاتوجد صفوف للعرض

وذلك ان INNER QUERY عندما يرجع تلك القيم الثلاث يعمل مقارنة في OUTER QUERY

حيث ان المخرجات في OUTER QUERY يعتمد على ان العمود SAL والعمود DEPTNO هل هما كل مخرجاتهم تساوي

مخرجات INNER QUERY وهذا بالطبع لا .. لانه يوجد هناك SAL غير التي في مخرجات INNER QUERY

اذا سوف تكون النتيجة النهائية لاتوجد صفوف للعرض


ارجو ان تكون قد وصلت الفكرة بشكل مفهوم وواضح ..

ان شاء الله القاكم في الدرس القادم

[programmer_85]

الدرس الثامن عشر

بعد ان اكلمنا كل انواع subquery ..وكان اخراها هو multiple columns .

اليوم ان شاء الله سنتاول في هذه الجلسة درس مهم وهو يعتبر ضمن mutiple columns وهو :

كيفية كتابة correlated subquery

اذا ماهو correlated subquery ؟؟

انه يشير الى عمود او اكثر من عمود في outer query حيث انه متعلق ومرتبط بال outer query من خلال نفس العمود.

يمكن ان نقول بانك عندما تحتاج جواب لسؤال فان ذلك يعتمد على القيمة في كل صف محتوى في outer query >

فمثلا : قد ربما تريد ان ترى سواء اذا كان يوجد علاقة بين البيانات ,, في الاصل انت لا تهتم في هذا الكيوري كم عدد الصفوف المعادة بواسطة subquery

حيث انك في الاصل تريد ان تفحصص فقط لاغير ان كانت هناك صفوف معادة من subquery ام لا

حيث ان correlated subquery يعمل مرة لكل صف في outer query >>>

لدينا هذا المثال في هذا القسم يوضح اكثر الى هذه المفاهيم .

مثال :

select empno,ename,job,sal ,deptno from emp outer
where sal>(select avg(sal) from emp inner where
inner.deptno = outer.deptno);

عندما ننفذ الكود السابق المتمثل على correlated subquery حيث اننا عملنا فيها subquery وjoin

ان التنفيذ اولا يتمد داخل inner query والذي يعود لنا بقيم طبقا للشروط الموضوعة في inner query

حيث انه يعتمد فقط على اعادة كل الرواتب التي هي اكبر من avg شرط ان يكون هناك مساواة بين ارقام الاقسام في شرط الجوين

داخل inner query وقد رينا ذلك مسبقا في دروسنا السابقة على الجوين

بعد ان يتم التفيذ في inner query يعمل مقارنة وفقا للشرط في outer query من اجل عرض كل المخرجات النهائية المطابقة للشرط

في outer query حيث يكون كل الراتب في outer query اكبر من معدل الرواتب في inner query >

سوف تكون المخرجات كالاتي :

EMPNO ENAME JOB SAL DEPTNO
------ ---------- --------- ------ ------
7499 ALLEN SALESMAN 1600 30
7566 JONES MANAGER 2975 20
7698 BLAKE MANAGER 2850 30
7788 SCOTT ANALYST 3000 20
7839 KING PRESIDENT 5000 10
7902 FORD ANALYST 3000 20

استخدام المعامل Exists و NOT Exists .مع correlated subquery

يمكن ان نتسخدم المعامل Exists من اجل الفحص للصفوف الموجودة المعادة من الكيوري

حيث ان المعامل Exists يقحص اذا كانت هناك صفوف معادة من قبل الكيوري ام لا.

اما المعامل Not Exists يعمل عكس المعامل Exists .



مثال على المعامل : Exists

سوف نعرض في هذا لمثال الموظفين الذين لديهم مدير .. حيث في الحقيقة نحن لانهتم بكم عدد الصفوف التس ستعود ولكن نهتم هل هناك صفوف معادة ام لا .

select empno,ename,job,deptno from emp outer
where Exists  (select empno from emp inner
where inner.mgr=outer.empno);

كما اشارنا سابقا ان مهمة المعامل Exists هو للفحص اذا كانت صفوف معادة ام لا .

حيث ان المخرجات كلالاتي:

EMPNO ENAME JOB DEPTNO
----- ---------- --------- ------
7566 JONES MANAGER 20
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10
7902 FORD ANALYST 20

مثال اخر : على المعامل Not Exists .

select empno,ename,job,deptno from emp outer
where not Exists  (select empno from emp inner
where inner.mgr=outer.empno);

مثل المثال السابق فقط في تغيرر المعامل من Exists الى not Exists

المخرجات كالاتي:

EMPNO ENAME JOB DEPTNO
----- ---------- --------- ------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7654 MARTIN SALESMAN 30
7844 TURNER SALESMAN 30
7876 ADAMS CLERK 20
7900 JAMES CLERK 30
7934 MILLER CLERK 10

ارجو ان تكون قد وصلت الفكرة بشكل مفهوم وواضح

اراكم ان شاء الله في الدرس القادم

[feras1983]

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

اتمنى الموافقة على مشاركتى معك

[رامي قشتم]

يشرفني الانضمام لكم وكل الاحترام لك

[programmer_85]

الدرس التاسع عشر

اكلمنا في الجلسة السابقة correlated subquery .

في هذه الجلشة ان شاء الله سوف نناقش ثلاثة اشياء تعمل مع الsubquery وهي:

كتابة nested subquery

كتابة الامر update مع subquery

كتابة الامر delete مع subquery


اولا:كتابة nested subquery

تستطيع ان تستخدم الكيوري subqueries داخل subqueies حتى 255 كيوري .

يمكن ان نتسخدم هذه التقنية المسماه nested subquery لعمل تلك الكيوريات queries.

لنين ذلك عبر مثال من اجل ان يوضح تلك التقنية اكثر فهما:

مثال :

[/b][/size]

[size=5][b]select deptno,ROUND(avg(sal)) from emp [/b][/size]
[size=5][b]group by deptno[/b][/size]
[size=5][b]having avg(sal)<[/b][/size]
[size=5][b](select round(max(avg(sal))) from emp[/b][/size]
[size=5][b]where deptno in(select deptno from dept [/b][/size]
[size=5][b]where dname='ACCOUNTING')GROUP BY DEPTNO)ORDER BY AVG(SAL);[/b][/size]
[size=5][b]

المثال السابق يمثل ال NESTED SUBQUERY وكما ذكرنا سابقا بانه يمتد له اي يسمح لان تنشئ كيوري داخل كيوري حتى 255 كيوري

لنحلل المثال السابق معا.

لناخذ اولا : ال SUBQUERY الذي يعتبر اخر subquery في inner query .

الكيوري الاول:

select deptno from dept
where dname='ACCOUNTING' GROUP BY DEPTNO

عندما ننفذ هذا الكيوري لوحاله نرى ان المخرجات له هي :

][/b]


DEPTNO
--------
10

اذا المخرجات من الكيوري الاخير في inner query هي 10 اي القسم 10

الان الرقم 10 سوف نعوض به مكان الكيوري السابق لنرى ماهي المخرجات في inner query اللاحق.

الكيوري الثاني:

select max(avg(sal)) from emp
where deptno in(10)GROUP BY DEPTNO

قد راينا سابقا ماهي المخرجات من الكيوري الاول وكانت النتيجة هي 10

لكن نلاحظ ان في هذا الكيوري هو group by deptno وهذا هو الزامي في حالة تعمل الكيوري لحاله اي منفصل

لكن في حالة nested subquery يكتفي فقط بالاخير في الكيوري الاخير من inner query

لنرى النتيجة والمخرجات من الكيوري الثاني :

][/b]


MAX(AVG(SAL))
-------------
2916.66667

الكيوري الثالث:

select deptno,ROUND(avg(sal)) from emp
group by deptno
having avg(sal)<
2916.66667 ORDER BY AVG(SAL);

اذا هذا هو الكيوري الاخير في الnested sunquery حيث يطلق عليه outer query

نلاحظ اننا حصلنا على القيمة 2916.66667 والتي تمثل max(avg(sal)) من الكيوري الثاني

ومن ثم نضع هذه القيمة مكان الكيوري الثاني لنرى ماهي المخرجات التي تظهر لنا اخيرا وهي المطلوبة لدينا

][/b]

DEPTNO ROUND(AVG(SAL))
--------- ---------------
30 1567
20 2175
10 2917

وهكذا خمن على ذلك عندما يكون لديك الكثير من nested query والتي تعتبر تلك الكيوري مرتبطة مع بعضها البعض ومتداخلة

حيث ان outer query وهو المطلوب مخرجاته نهائيا.وقس على ذلك في الامثلة الاخرى.



كتابة الامر update مع subquery

حتى الان لقد راينا فقط subquery التي تعمل مع select statement..

في هذا الموضوع سوف نرى كيف يتم عمل subquery داخل جملة update .

لنرى هذا المثال يوضح ذلك اكثر.

update emp set sal=(select avg(sal) from emp )where empno=7788;[/b][/size]
[size=5][b]

مع المعروف ان صيغة جملة update هي في الاصل تلك:

update table_name set col_name=col_name ,....;

حيث ان الجملة الثانية التي يتم التعديل فيها وهي التي تشير الى اي عمود تردي ان تعدل فيها بعض البيانات .

في تلك الجملة الثانية التي بعد المساوة نقوم بوضع فيها subquery وفقا لما نريد من مخرجات او شروط.

فنحن قمنا بوضع تلك الجملة والتي تشير الى ان يعدل الراتب للموظف الذي رقمه هو 7788 الى معدل كل رواتب الموظفين .

تلك الصيغة تحل ذلك

select avg(sal) from emp

ومن ثم وضعنا الشرط خارج ذلك ال subquery من اجل ان يعدل فقط الموظف الذي رقمه 7788

اما في حالة اذا وضعنا الشرط داخل ال subquery فانه سوف يقوم بتعديل الرواتب لكل الموظفين وليس للموظف المشار رقمه اليه

where empno=7788

اذا سوف تكون المخرجات فقط والتعديل في في رقمه الموظف 7788 والذي هو scott

قم بعمل select لترى ذلك ..ز نفذ select قبل جملة update وشوف ماهو راتب scott

ومن ثم قم بالتعديل له بعد ذلك واعمل select لترى ان الراتب قد تم تغييره الى 3000 وهو المعدل للموظفين.


كتابة الامر delete مع subquery

كما راينا سابقا في جملة update هنا الوضع لايختلف تمام وانا فقط في الصيغة .

لنرى ذلك بمثال اوضح .

[/b][/size]
[size=5][b]delete from emp where
sal>([code]select avg(sal) from emp

);
[/code]

حيث ان جملة delete هنا تقوم بحذف السجلات التي هي اكبر من القيمة التي داخل ال subquery

النتيجة في ال subquery سوف تكون كالاتي

select avg(sal) from emp

عندما تعرض هذا الكيوري لحاله .. حيث من الممكن معرفة ماهو الرقم الي يعرض من هذا ال subquery حتى اعرف يدويا ماهي السجلات التي

سوف تحذف من الجدول وفقا للقيمة المعادة من ال subquery .

المخرجات من subquery هي:

][/b]

AVG(SAL)
--------
2073.2

اذا سوف يقوم بحذف السجلات من الجدول emp التي راتب الموظفين هي اكبر من تلك القيمة 2037.2

اذا سوف يكون الكيوري الاخير في جملة delete هو كالاتي :

delete from emp where sal> 2073.2;

اذا سوف تحذف تلك السجلات وفقا لهذا الرقم المعاد من ال subquery

وهكذا في بقية الامثلة قياسا على ذلك.

ارجو ان تكون قد وصلت الفكرة بشكل مفهموم وواضح

الحمد لله تم اكمال نهاية هذه الدورة والتي درست فيها join و subquery

ان شاء الله في الدرس القادم سوف يكون على شكل امثلة كثيرة محلولة في كل الدروس التي اخذناها سابقا .

لاتسونا من دعائكم بارك الله فيكم

[سامي محمد خير]

على بركة الله .... و نسال الله ان ينفعنا و اباكم بما نتعلم و يعلمنا ما ينفعنا

ان شاء الله انا أود الاشتراك .... واريد المواعيد

 

بارك الله فيك

بارك الله فيك وجزاك الله خيرا وان شاء الله انا من اول المشتركين

[sameer_ym]

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

 

حبيت ان اضع بين ايديكم اهم موضوعين في بيئية الاس كيو ال وهما الجوين join والصب كيوري subqurey

 

صحيح ان الاس كيو ال مهمة ككل لكن اهم شي فيها هما هذان الموضوعان لانمهما يشكلان مافوق 70% من فهم الاس كيو ال

 

سوف اضع كل يوم درس ومن ثم تتطرع الاسئلة حول ذلك اذا فيه اي اشكال حول الدرس...

 

الشرط الوحيد اللذي اريده هو اريد اي شخص مهتم لهذه الدروة ان يسجل اسمه ومن ثم يتباع اول باول ...

 

كذا اريد على الاقل 10 اشخاص في هذه الدورة

 

لاننا في اخر الدرس سوف نطرح واجب,,,

 

فمن رغب في ذلك فليسجل ...

 

منتظر ردودكم ...

[zazo66]

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

[nadmoney]

بارك الله فيك اخي programmer_85

 

[menna22]

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

[saharodeh]

شكرا لجهودك

[programmer_85]

الدورة قد انتهت قبل فترة ...

 

لكن بامكانك ان تجعلها كمرجع لك 

 

عندما تحتاج موضوع ما 

 

تابعوان ع الدورة الثانية والتي باسم ..

 

دورة متكاملة حول ادارة كائنات السكيما  managing schema objects

[saja zareer]

بارك الله فيك وجعله الله في ميزان حسناتك

[arjd]

اسمي احمد جباره

وانا ان شاء الله جاهز لحضور هذه الدورة ولكن سؤالي .. انا معلوماتي خفيفة بالنسبة لل اس كيو ال و ايضا ال بي ال اس كيو ال

هل استطيع حضور هذه الدورة؟؟؟

اذا بدأت من الصفر بكون افضل بالنسبة لي .. وبارك الله في جهودكم

[ehab ibrahim]

توكل على الله ... ونحن معك مستمعين فاهمين ان شاء الله ... وربنا يبارك فيك