الطريق لاحتراف الاوراكل .. دورة مطوري اوراكل .. من الالف الى الياء

[The Coder]

الان انظر الى قيود الجدول الاخر :

 
select * from user_constraints where table_name='CARS';

النتيجة كانت :

OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION 

 
---------- -------------------------- - ---------------- -----
 
SCOTT CARS_CARAGE_CK C CARS carage between 0 and 10 
 
SCOTT CARS_CARNO_PK P CARS 
 
SCOTT CARS_CARPANEL_UK U CARS 
 
SCOTT CARS_CNO_FK R CARS 

للتذكير ... ما فائدة المسميات ؟ لماذا المسميات مهمة لهذه الدرجة ؟


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

W )

هيا لنحذف القيد الخاص بالمفتاح الأساسي بجدول العملاء !!

 
alter table customer 
 
drop constraint customer_cno_pk ;

والنتيجة :

 
ORA-02273: this unique/primary key is referenced by some foreign keys

خطأ !!


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

Primary key أو ............ بطريقة اخرى وهي :

 
alter table customer 
 
drop constraint customer_cno_pk cascade ;

إضافة كلمة

cascade والذي سوف يمحي العلاقة برمتها , أي انه سوف يمحي القيد الخاص بالمفتاح الأساسي , و القيد الاخر الخاص بالمفتاح الاجنبي في الجدول الاخر .. تأكد بنفسك ... اذهب وشاهد ذلك في جدول القيود user_constraints

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


(باعتبار انك لم تكتب الامر الذي يحتوي على

cascade)

 
alter table cars 
 
drop constraint cars_cno_fk ;

 
alter table cars 
 
drop constraint cars_carage_ck ;

 
alter table cars 
 
drop constraint cars_carpanel_uk ;

 
alter table cars 
 
drop constraint cars_carno_pk ;

 
select * from user_constraints where table_name='CARS';

 
no rows selected

وبالمثل .. في جدول العملاء ... لو اردت ان تجرب .


لا ادري هل يوجد فرق فعلي بين "على مستوى الاعمدة" و "على مستوى الجداول" ؟

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

بقي شيء واحد نسيته !!

التحدي ثاني ....

وهو القيد not null كيف نحذفه ؟

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

هيا اذهب و امسح جميع الجداول التي عملناها صباح هذا اليوم ... فلا حاجة لنا بها !!


انتهينا من دوخة القيود ...

والان لنرجع الى الوراء .... الى الماضي , ونذكر شيء قد نسيناه , الا وهو جملة الاستعلام

select

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


واخر مثال كان :

select ename,sum(sal) from emp ;

وكانت النتيجة جميلة ورائعة ....

ORA-00937: not a single-group group function

الان , اريد ان اعرف .... مجموع الرواتب الكلية للقسم 30 و مجموع الرواتب الكلية للقسم 20 و مجموع الرواتب الكلية للقسم 10 (الرواتب الكلية = الرواتب الأساسية + الرواتب الإضافية )


اريد ان اعرضهم لكي أقارن مثلا ...

ماذا اعمل ...

اليك حل عبيط و متعب ..... متعب كثيرا .... ويمكن يحتاج ورقة وقلم ... و آلة حاسبة ,

 
select sum(sal) from emp 
 
where deptno=30 ;

 
SUM(SAL+NVL(COMM,0))
 
--------------------
 
11600

 
elect sum(sal + nvl(comm , 0) ) from emp
 
here deptno =20 ;

 
SUM(SAL+NVL(COMM,0))
 
--------------------
 
10875

 
select sum(sal + nvl(comm , 0) ) from emp 
 
where deptno =10 ;

 
SUM(SAL+NVL(COMM,0))
 
--------------------
 
8750

حل رائع ...


تكتب كل قسم ... على حدة ...

وترى النتائج متفرقة ...

تخيل انه 100 قسم ؟؟؟؟؟

هل تكتبها مئة مرة ؟ وتسجل البيانات مئة مرة ؟ لا لا ,, هذا غباء

الان سوف تتعلم شيء جديد

....

انظر الى الصورة اولا ... (الصورة 1101)

الصورة توضح عرض اكبر قيمة في القسم 10 , واكبر قيمة في القسم 20 واكبر قيمة في القسم 30 ....


الجدول الذي في اليسار هو جدول الموظفين ... أي عرض منه رقم القسم و الراتب ....

الجدول الذي في اليمين ... هو ما نريده !! ....

لو قلنا :

 
select max(sal) from emp ;

فإنه سوف يعرض لنا

...

 
MAX(SAL)
 
--------
 
5000

اذن نريد استخدام الدوال ذوات الصفوف المتعددة , ونريد تقسيم الناتج ...


دوال متعددة الصفوف .. نتيجتها صف واحد , ونحن نريد صف متعدد , لذا تنبهوا اولئك المبرمجين (مبرمجو أوراكل) , وعملو شيء يسمى بـ

group by

جملة

group by :


Group معناها بالعربي .... مجموعة , تقسيم .....

وكأن معنى هذه الجملة "تقسيم بحسب ".....

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


الان انظر الى الحل ....

اريد عرض مجموع رواتب الموظفين في قسم 10 و مجموع رواتب الموظفين في قسم 20 ومجموع رواتب الموظفين في قسم 30 في جملة استعلام واحدة ,

 
select sum(sal) from emp 
 
group by deptno ;

وكأنه يقول .. اعرض مجموع الرواتب ... مقسمة بحسب رقم القسم !!


انظر الى الناتج :

 
SUM(SAL+NVL(COMM,0))
 
--------------------
 
8750
 
10875
 
11600

يااااا سلام ... رائع

[The Coder]

الان نطورها قليلا , ونضع الرواتب الكلية :

select sum(sal+nvl(comm,0) ) from emp

group by deptno ;

SUM(SAL+NVL(COMM,0))

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

8750

10875

11600

ممممم لا تعجبني النتيجة هكذا ...

اريد ان اضيف رقم القسم بجانب النتائج ....

select deptno , sum(sal+nvl(comm,0) ) from emp

group by deptno ;

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

DEPTNO SUM(SAL+NVL(COMM,0))

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

10 8750

20 10875

30 11600

الان اقتنعت ...

لنجرب دالة القيمة الاعلى

max للرواتب ...

select deptno , max(sal) from emp 

group by deptno ;

DEPTNO MAX(SAL)

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

10 5000

20 3000

30 2850

الان ... انا لم اكتفي .. اريد تعقيد الامور !!

اريد ان اعرض اسم صاحب اكبر راتب و قسمه ... مقسما النتائج على حسب رقم القسم ..

ما رأيك في هذا الحل :

select ename,deptno,max(sal) from emp 

group by deptno ;

فكر في حلي ...

هل هو صحيح ؟؟ ولماذا ؟

لماذا هو صحيح ؟ لماذا هو خاطئ ؟؟

في الحقيقة .. هو خاطئ مئة بالمئة !!

لماذا خاطئ ؟ وضعنا سابقا العمود

deptno فصار الحل صحيح ... ولكن عندما اضفنا ename اصبح خاطئ !!

لماذا يا ترى ؟

اذا لم تفهم لماذا ... فسوف تضطر لقرائة عدة سطور مملة (ما بين علامتي التنصيص) ... اقرأها او لا تقرأها , الخيار لك ... سوف تستغرق قرائتها اقل من دقيقتين ونصف ...

"

انظر هذه الجملة .. للتذكير:

select deptno , max(sal) from emp 

group by deptno ;

في الجملة قلنا (اعرض رقم القسم , و أعلى راتب ..... و قسم النتائج بحسب رقم القسم) .... ما معنى هذا ؟؟

معناه انه لو يوجد لدينا الأقسام

10 و 20 و 30 و 40 مثلا ...

-

سوف يقسم الصفوف الى اربع مجموعات , المجموعة الاولى و بها الصفوف التي تحتوي على القسم 10

والمجموعة الثانية تخص الصفوف التي تحتوي على القسم 20 ...... الخ

-

المجموعة الاولى مثلا بها 5 صفوف ... وكل من هذه الصفوف تتبع القسم 10 صح ؟

أي ان المجموعة الاولى همها واحد , لغتها واحدة , قسمها واحد , تشترك في صفة واحدة وهي القسم 10

المجموعة الثانية تشترك في صفة واحدة وهي القسم 20 ... لا احد يشترك معها في هذه الصفة

كذلك المجموعة الثالثة والرابعة .....

-

مثلا المجموعة الاولى ... هل تشترك في اسم الموظف و اسم الموظفين جميعهم اسم واحد ؟؟ لا

هل تشترك في الراتب أي ان جميع الرواتب فيها راتب واحد ؟؟ طبعا لا .... الرواتب مختلفة بين كل صف وصف في المجموعة الاولى ...

-

الان بعد تقسيمها الى مجموعات نعمل اهم خطوة... وهي ضم كل صفوف المجموعة الواحدة لتمثل صف واحد !! انتبه ... فهذه اهم خطوة يجب التنبه فيها , الان .... قبل ان نضم... هل توجد دالة من نوع المتعددة الصفوف لنقل مثلا نعم توجد دالة .... إنها دالة max ... أي اكبر راتب موجود في هذه المجموعة .



- سوف يختار اكبر راتب ...و ينشئ عمودين هما رقم القسم , واكبر راتب لكي يمثلان المجموعة الاولى ....

وهكذا مع المجموعات الاخرى

-

اذن الخلاصة هي انه بإمكاننا ان نعرض رقم القسم لاننا وضعناه في جملة group by , ولا يمكننا ان نعرض عمود اخر كاسم الموظف ..., لذا لا تضع عمود لم يذكر في جملة group by

"

الان .... لنعقد الامور اكثر واكثر ....

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

أي انه بعد معرفة مجموع رواتب كل قسم ... كما شاهدنا سابقا , اريد ان اعرف اكبر مجموع !!

لو نظرنا الى مايلي :

DEPTNO SUM(SAL+NVL(COMM,0))

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

10 8750

20 10875

30 11600

لعرفنا ان اكبر مجموع بين الثلاثة هو 11600

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

فكر قليلا .... الجواب في رأسك وانت لا تعلم ....

اليك الحل :

select max(sum(sal+nvl(comm,0) ) ) from emp 

group by deptno ;

والنتيجة ...

MAX(SUM(SAL+NVL(COMM,0)))

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

11600

كل مافعلناه .... هو مايلي ..

Max(sum(….) )

وكما نعرف ... الدوال ترجع بقيمة (تخرج قيمة)

دالة الـ

sum هنا اخرجت لنا قيم .. وهي ثلاث صفوف ... (مجموع رواتب كل قسم من الأقسام الثلاثة ...)

هل عرضت لنا ثلاث صفوف ؟ لا !!

من استقبلها ؟ انها الدالة max , استقبلتها بكل سرور , واختارت القيمة الاعلى منها , ثم عرضته لنا

هل فهمت الفكرة ! اعتقد انها سهلة .

الان اليك التعقيد العالمي .....

تخيل انه يوجد لدينا جدول الطلبة .. مجرد تخيل لهذا الجدول ...به الاعمدة التالية :

رقم الطالب ... رقم الكلية .. عدد الأترام ... ومعدل الطالب .

اريدك ان تنسخ هذا الجدول كما هو .... :

create table stud(

stu_no number ,

col_no number ,

trm_no number ,

avg_po number(3,2),

constraint stud_stu_no_pk primary key(stu_no) );

Stu_no

رقم الطالب .

Col_no رقم الكلية.

trm_no رقم اخر ترم درسه .

Avg_po معدل الطالب .

الان .. انسخ جميع الصفوف كما هي , ثم الصقها في البرنامج

SQL PLUS* ... :

insert into stud values (10 ,1,1,3.74) ;

insert into stud values (20 ,1,1,5.00) ;

insert into stud values (30 ,1,1,4.85) ;

insert into stud values (40 ,1,2,3.12);

insert into stud values (50 ,1,2,3.66);

insert into stud values (60 ,1,2,4.02);

insert into stud values (70 ,1,2,2.00);

insert into stud values (80 ,1,2,2.88);

insert into stud values (100,2,1,4.89);

insert into stud values (110,2,1,4.00);

insert into stud values (120,2,2,1.89);

insert into stud values (130,2,3,4.61);

insert into stud values (140,2,3,4.22);

insert into stud values (150,2,3,4.57);

insert into stud values (160,2,3,4.64);

insert into stud values (170,2,3,3.01);

insert into stud values (180,2,4,4.42);

insert into stud values (190,2,4,3.64);

insert into stud values (200,2,4,4.99);

insert into stud values (210,2,4,4.50);

insert into stud values (220,3,1,3.32);

insert into stud values (230,3,1,2.23);

insert into stud values (240,3,1,3.73);

insert into stud values (250,3,2,4.77);

insert into stud values (260,3,3,2.40);

insert into stud values (270,3,3,2.93);

insert into stud values (280,3,3,3.40);

insert into stud values (290,3,3,3.66);

insert into stud values (300,3,4,2.71);

insert into stud values (310,3,4,4.17);

insert into stud values (320,3,5,4.98);

insert into stud values (330,3,5,2.53);

insert into stud values (340,3,5,4.24);

يا سلام ... الان انظر الى الجدول الجديد :

select * from stud ;

[The Coder]

الان اريد ان اعرض عدد الطلبة الذين اخذو عدد اترام معين , في الكلية الواحدة ,


أي انه يخبرني ان هنالك ثلاث طلاب يأخذون الترم الثاني في الكلية الاولى , وهنالك طالبين يأخذون الترم الثالث في الكلية الاولى , وان هنالك طالب واحد يأخذ الترم الاول في الكلية الثانية .... وهكذا ...

اريدك ان تفكر في السوال .. لا تفكر في الاجابة ..

يجب ان تفهم السوال , لكي تعيش المشكلة والتجربة . لاحظ انه يجب استخدام الدالة

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

النتيجة ستكون بهذا الشكل .....

 
NUMBER_OF_STUDENT COL_NO TRM_NO
 
----------------- ---------- ----------
 
3 1 1
 
5 1 2
 
2 2 1
 
1 2 2
 
5 2 3
 
4 2 4
 
3 3 1
 
1 3 2
 
4 3 3
 
2 3 4
 
3 3 5

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


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

اليك الحل ....

 
select count(*) number_of_student , col_no , trm_no
 
from stud
 
group by col_no , trm_no ;

جملة التقسيم

group by احتوت على عمودين !!


لاحظ انني وضعت رقم الكلية و الترم لكي اعرضهما ... أي كلاهما باستطاعتنا عرضهما ولا يوجد مشاكل .. لماذا ؟ لانهما ذكرا في جملة الـ group by...

العملية معقدة جدا , لو كان هنالك تقسيمات اكثر من عمودين .


في الجملة السابقة سوف يقسم الصفوف الى ثلاث مجموعات (لانه يوجد ثلاث كليات)

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

أي انه بعد تقسيمه الى ثلاث مجموعات ينظر الى المجموعة الاولى من الصفوف ... ويجدها 8 صفوف ...


الصفة المشتركة بينهما هي ان رقم الكلية هو 1

نأتي للتقسيم الاخر ...

وهو على حسب عدد الاترام ...

الثمانية صفوف التي بالمجموعة الاولى سوف تقسم الى مجموعتين(لانه في الصفوف الثمانية لا يوجد الا ترمين , الاول والثاني !!)... الاولى منها صفوف الطلبة الذين يدرسون في الترم الاول , والثانية هي صفوف الطلبة الذين يدرسون في الترم الثاني ... لو وجد ترم اخر مثلا .. سوف يكون هنالك مجموعة ثالثة .... وهلم جر ... الان لدينا مجموعتين في الكلية رقم 1

...

اذا كنت ما زلت غير مقتنع ... اذا كنت لا تفهم بلغة الكتابة ... اذا لم تعرف كيف اتت النتيجة السابقة ... اذا لم ترد ان تقرأ اصلا ... انظر الصورة :

لاحظ انه في حلنا السابق .. كتبنا مايلي :

group by col_no , trm_no

لو قلبناها وجعلناها هكذا :

group by trm_no , col_no

مالذي سوف يحدث ؟


ستكون نفس النتائج , ولكن بترتيب مختلف ...

في الاولى يقسم المجموعات بارقام الكليات ثم بالاترام ,

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

انظر النتائج واستنتجها بنفسك ...

 
select count(*) number_of_student , col_no , trm_no
 
from stud
 
group by trm_no , col_no ;

 
NUMBER_OF_STUDENT COL_NO TRM_NO
 
----------------- ---------- ----------
 
3 1 1
 
2 2 1
 
3 3 1
 
5 1 2
 
1 2 2
 
1 3 2
 
5 2 3
 
4 3 3
 
4 2 4
 
2 3 4
 
3 3 5

الان ... اريدك ان تتعرف على جملة الترتيب , والتي تجاهلتها منذ البداية لشيء ما في نفسي .


جملة

Order by :

اريد ترتيب جدول الموظفين تصاعديا على حسب رواتبهم ...


تصاعديا يعني

Ascending ... من تحت الى فوق , يعني يصعد ... يعني من الصغير الى الكبير .

تنازليا يعني Descending ... أي العكس .

اليك الجملة ....

 
select ename,sal from emp
 
order by sal asc;

 
ENAME SAL
 
---------- ----------
 
SMITH 800
 
JAMES 950
 
ADAMS 1100
 
WARD 1250
 
MARTIN 1250
 
MILLER 1300
 
TURNER 1500
 
ALLEN 1600
 
CLARK 2450
 
BLAKE 2850
 
JONES 2975
 
SCOTT 3000
 
FORD 3000
 
KING 5000

الان تنازليا ...

 
select ename,sal from emp
 
order by sal desc ;

 
ENAME SAL
 
---------- ----------
 
KING 5000
 
SCOTT 3000
 
FORD 3000
 
JONES 2975
 
BLAKE 2850
 
CLARK 2450
 
ALLEN 1600
 
TURNER 1500
 
MILLER 1300
 
WARD 1250
 
MARTIN 1250
 
ADAMS 1100
 
JAMES 950
 
SMITH 800

[The Coder]

بإمكاننا ان نتجاهل كلمة

ASC , أي لو كتبنا :

select ename,sal from emp

order by sal ;

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

ASC .

الان تعال لنعقد بعض الامور , ونخلط

group by مع order by ولنرى النتائج ...

على فكرة , group by ترتب النتيجة تصاعديا بشكل اوتوماتيكيا , أي :

select deptno from emp

group by deptno ;

DEPTNO

------

10

20

30

اريد ان اعرض مجموع رواتب الموظفين لكل قسم , بشكل تنازلي .

select sum(sal) , deptno from emp

group by deptno 

order by sum(sal) desc ;

SUM(SAL) DEPTNO

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

10875 20

9400 30

8750 10

هل لاحظت جملة الترتيب ؟ رتبنا ماذا ؟ هل رتبنا الرواتب ؟ بل رتبنا مجموع الرواتب ....

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

طيب , فكر لو كتبنا هكذا :

select sum(sal) , deptno from emp

order by sum(sal) desc

group by deptno ;

فهذا يعتبر خطأ !!

اذن يجب ان تكتب جملة التقسيم

group by ثم جملة الترتيب order by

انظر ما يلي :

select sum(sal) , deptno from emp

group by deptno 

order by sal desc ;

فهذا الامر .... خطأ !!

لان

order by قال "رتب بحسب الرواتب " , فنقول له , لقد قسمنا الناتج وضمينا الناتج , ونتوقع منك يا order by ان ترتب على حسب رقم القسم او على حسب مجموع الرواتب فقط لا غير .

بإمكاننا ان نعمق مفهوم الترتيب اكثر , ونقول لك ..

اريد ان اعرض جدول الموظفين على حسب رقم القسم تصاعديا , ويكون كل قسم مرتب فيه الرواتب تنازليا .

DEPTNO SAL ENAME

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

10 5000 KING

10 2450 CLARK

10 1300 MILLER



20 3000 SCOTT

20 3000 FORD

20 2975 JONES

20 1100 ADAMS

20 800 SMITH



30 2850 BLAKE

30 1600 ALLEN

30 1500 TURNER

30 1250 WARD

30 1250 MARTIN

30 950 JAMES

اليك الامر ...

select deptno,sal,ename from emp

order by deptno asc ,sal desc;

الان .... انا ارى في النتيجة السابقة لا تعجبني .

هنالك اثنين تعملون في القسم نفسه ... و يأخذون الراتب نفسه , احدهما

SCOTT والاخر FORD

وهنالك اثنان آخران نفس الحالة ...

لو انه يوجد الف شخص يعملون في نفس القسم , لهم نفس الراتب .. اريد ترتيبهم على حسب القسم تصاعدي , ثم الراتب تنازليا , ثم الاسم تصاعديا !! فكما شاهدنا في النتيجة السابقة , وضع

scott اولا ثم ford وانا اريده بالعكس .. وترتيب الاسم تصاعدي أي من A الى Z

select deptno,sal,ename from emp

order by deptno asc ,sal desc,ename asc ;

والان ستكون النتيجة :

DEPTNO SAL ENAME

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

10 5000 KING

10 2450 CLARK

10 1300 MILLER

20 3000 FORD

20 3000 SCOTT

20 2975 JONES

20 1100 ADAMS

20 800 SMITH

30 2850 BLAKE

30 1600 ALLEN

30 1500 TURNER

30 1250 MARTIN

30 1250 WARD

30 950 JAMES

اعتقد انني سئمت وزهقت من هذا الموضوع , وطولت فيه

مثال اخير :

select col_no , to_char(avg(avg_po) ,'0.00') , trm_no , count(*) number_of_student

from stud

group by col_no , trm_no 

order by col_no , avg(avg_po) ;

COL_NO TO_CH TRM_NO NUMBER_OF_STUDENT

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

1 3.14 2 5

1 4.53 1 3

2 1.89 2 1

2 4.21 3 5

2 4.39 4 4

2 4.45 1 2

3 3.09 1 3

3 3.10 3 4

3 3.44 4 2

3 3.92 5 3

3 4.77 2 1

جملة

HAVING :

الان اريد ان اعرض مجموع رواتب الموظفين في كل قسم , بشرط ان يكون مجموع الراتب لقسم ما اكبر من 9000 دولار !!

فكر .... فكر .... فكر ......

انظر الى الحل الصحيح ...

:

select sum(sal),deptno

from emp 

where sum(sal) > 9000 

group by deptno;

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

ORA-00934: group function is not allowed here

خطأ مع مرتبة الشرف .!!!!

[The Coder]

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

where , لذا لزم علينا اختراع جملة شرطية جديدة خاصة بالدوال المتعددة الصفوف , الا وهي having .

select sum(sal),deptno 

from emp 

group by deptno

having sum(sal) > 9000 ;

select sum(sal),deptno 

from emp 

group by deptno

having sum(sal) > 9000 ;

SUM(SAL) DEPTNO

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

10875 20

9400 30

هل فهمت ؟ سهلة جدا , كأنها

where لكنها خاصة بالنتائج النهائية لجملة التقسيم !!, هل تفهم ما معنى النتائج النهائية ؟ أي انظر فيما يلي :

select sum(sal),deptno 

from emp 

group by deptno ;

انظر الى النتيجة النهائية ...

SUM(SAL) DEPTNO

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

8750 10

10875 20

9400 30

طيب .. من النتيجة النهائية , اريد ان اعرض منها فقط , كل ارقام القسم التي هي اكبر من 10 .... :

select sum(sal),deptno 

from emp 

group by deptno

having deptno>10 ;

انظر الناتج :

SUM(SAL) DEPTNO

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

10875 20

9400 30

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

having , ثم عرض علينا الناتج النهائي .

مع العلم انه لو قلنا :

select sum(sal),deptno 

from emp 

group by deptno

having sal > 1000 ;

فهو خطأ !! , واعتقد انك تعرف لماذا .

الان دعنا نضع كل شيء في جملة واحدة !

اريد ان اعرض وظائف و مجموع الرواتب لكل وظيفة والتي هي اكبر من

4000 دولار , بشرط ان لا تكون هذه الوظيفة وظيفة مدير , ومرتبه ترتيبا تنازليا على حسب مجموع الرواتب !!

بالله عليك فكر قليلا , لان هذا المثال من اعقد ما يكون ..

فكر فكر فكر فكر فكر فكر .........

هل فكرت ؟ اليك الحل :

select job,sum(sal) 

from emp 

where job <> 'MANAGER'

group by job 

having sum(sal) > 4000

order by sum(sal);

هل تصدق ان هذا المثال الشامل الكامل لدرس اليوم !!

احفظ الترتيب ...

(clause = جزئية او جملة ... )

Select clause

From clause

Where clause

Group by clause

Having clause

Order by clause

الان .. ما رأيك ان تكتشف حقيقة برنامج الأوراكل في ترجمة جمل الـ

SQL ؟؟

مع العلم ان هذا استنتاجي ورأيي الخاص !! , اعتقد انه 90% صحيح , لكن الله العالم كيف عملوا طريقة ترجمة هذه الجمل , انا اجتهدت واختبرت كثيرا , واعتقد انه بإمكاني عمل برنامج شبيه ببرنامج SQL PLUS*

ترتيب قرائة برنامج الأوراكل :

اذا لم ترغب بالقرائة ... او انك لم تفهم السطور القادمة , يكفيك فقط ان تعرف ان البرنامج سوف ينفذ اولا الجملة الشرطية

where ثم group by ثم having ثم أخيرا order by

اليك مثالنا السابق :

select job,sum(sal) 

from emp 

where job <> 'MANAGER'

group by job 

having sum(sal) > 4000

order by sum(sal);

اولا : يذهب الى

from clause

يرى ما هو الجدول ... ثم يذهب ويبحث عنه اذا لم يجده فسوف يعطيك خطأ بأنه لم يجد الجدول .. اذا وجده يذهب للخطوة الثانية

ثانيا : يذهب الى

where clause (اذا وجده اصلا)

يرى الشرط هل هو صحيح اصلا او لا , اذا كان صحيحا يبدأ ويطبق الشرط على الجدول , ثم ينسخ الى الذاكرة الناتج (لنسميه الناتج أ ).

ثالثا : يذهب الى

group by clause (اذا وجده اصلا)

يرى هل العمود المراد تقسيم الناتج ( الناتج أ ) .. موجود اصلا في الجدول او لا !!

اذا لا ... فهذا خطأ .

طبعا اذا وجد عمودين , يقسم بالاول ثم يقسم بالثاني

...

المهم يقسم على حسب الاعمدة الموجودة , ثم يخزن في الذاكرة الناتج مقسما (لنسميه الناتج ب )

.

رابعا : يذهب الى

having clause (اذا وجده اصلا )

سوف يرى , هل الشرط صحيح او لا .. بمعنى هل الشرط يتناسب مع التقسيم (الناتج ب) او لا ...

في الحقيقة ان having اصلا لا يأتي نفعها فعليا الا اذا وجد group by .

الان سوف يطبق الشرط الموجود في الجملة

having على الناتج المقسم ( الناتج ب ) ومن ثم يكون الناتج الاخير في الذاكرة ( لنسميه الناتج ج )

خامسا : يذهب الى order by clause

سوف يرى اولا هل الاعمدة التي في

order by موجودة في الناتج ج ؟؟ او لا

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

سادسا

:الان يذهب الى select clause , ويرى الاعمدة الموجودة به , والتي نريد ان نعرضها ,

- يرى هل الاعمدة المذكورة موجودة اصلا في الجدولاو لا ... اذا لا يعطي خطأ ويتوقف .

-

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

- اذا وجد دالة متعددة الصفوف , فإنه ينظر في الناتج د , ويطبق الدالة على كل صف في الناتج د

[The Coder]

الان هذه الخطوات الخمسة نطبقها بشكل عملي على حسب المثال السابق :

select job,sum(sal) 

from emp 

where job <> 'MANAGER'

group by job 

having sum(sal) > 4000

order by sum(sal);

اولا : يذهب الى

from clause

يرى الملف ... اااقصد الجدول

emp موجود ولا زال على قيد الحياة .

ثانيا : يذهب الى

where clause (اذا وجده اصلا)

نعم يجد الجملة الشرطية هذه , والتي تقول كل الصفوف التي لا تحتوي على العمل "مدير"

سوف يكون الناتج أ :

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17/12/80 800 20

7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30

7521 WARD SALESMAN 7698 22/02/81 1250 500 30

7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30

7788 SCOTT ANALYST 7566 19/04/87 3000 20

7839 KING PRESIDENT 17/11/81 5000 10

7844 TURNER SALESMAN 7698 08/09/81 1500 0 30

7876 ADAMS CLERK 7788 23/05/87 1100 20

7900 JAMES CLERK 7698 03/12/81 950 30

7902 FORD ANALYST 7566 03/12/81 3000 20

7934 MILLER CLERK 7782 23/01/82 1300 10

ثالثا : يذهب الى

group by clause (اذا وجده اصلا)

سيجده يقول ترتيب بواسطة الـ job , نعم هذا عمود موجود في النتيجة أ

الان سوف يقسمه ويضم الناتج ويتجاهل الاعمدة ما عدى الاعمدة الموجودة في الـ group by

انظر الناتج ب

:

JOB

---------

ANALYST

CLERK

PRESIDENT

SALESMAN

رابعا : يذهب الى

having clause (اذا وجده اصلا )

سيجد الشرط يقول ... sum(sal) >4000

ينظر الى الناتج ب .... ويعمل الآتي

:

- يجد أول صف هو (العمل =

analyst)

- يبحث في جدول الـ emp (الجدول الأصلي الذي به جميع الصفوف !!) عن جميع الصفوف التي ( job <> 'MANAGER' ) و التي (job ='ANALYST') فسوف يكون الناتج ...

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7788 SCOTT ANALYST 7566 19/04/87 3000 20

7902 FORD ANALYST 7566 03/12/81 3000 20

-

قلنا ان الشرط sum(sal) >4000 , مجموع الرواتب هنا ... 6000 ... هل هو اكبر من 4000 ؟ نعم انه اكبر ...

اذن ANALYST ستكون في القائمة ....

- يرجع الى الصف الذي يليه ثم ينفذ الخطوات السابقة ..

في النهاية سوف يكون لدينا ... الناتج ج :

JOB 

---------

ANALYST 

CLERK 

PRESIDENT

SALESMAN

(بالصدفة وجد كل الوظائف ... مجموع رواتبها اكبر من 4000)

خامسا : سوف يذهب الى

order by clause والذي يطلب ترتيب على حسب مجموع الرواتب ..

سوف يمر على كل صف في الناتج ج

-

فمثلا .. يرى ANALYST , ويذهب الى الجدول emp (الجدول الأصلي الذي به جميع الصفوف !!)...

- يبحث عن كل الصفوف التي يكون العمل فيها لا يساوي مدير , والتي يكون العمل فيها يساوي ANALYST

ثم تظهر له عدة صفوف , ويبدأ يحسب مجموع الرواتب في هذه الصفوف .....

- انتهى من

ANALYST , يبدأ في الصف التالي ....

- في النهاية وبعد ان حفظ مجاميع الرواتب لكل صف في الناتج ج .... يبدأ يرتب بطريقته الخاصة !! وبالتالي تظهر النتيجة د :

JOB 

---------

CLERK 

PRESIDENT

SALESMAN 

ANALYST

مرتبة على حسب مجموع الرواتب .

سادسا :الان يذهب الى

select clause , ويرى الاعمدة الموجودة به , والتي نريد ان نعرضها ,

موجود يا سيدي ... الـ job .... اذن مقبولة لانها موجودة في النتيجة د وسوف تكون في الحسبان للعرض ,

وموجود شيء اخر وهو ... sum(sal)

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

انتهت الخطوات ....

وما بقي عليك الا معرفة انه من الممكن ان تكون طريقتي هذه غير كفئ 100% , اعتقد لو أمعنا وفكرنا , من المؤكد انه يوجد طريقة اخرى أسهل و أفضل

.

الان دعنا نعطي بعض الأمثلة

. من جدول stud السابق ....

اريد عرض عدد الطلبة الذين حصلوا على معدلما بين 1 و 1.99 أي المعدل الذي عدده الصحيح واحد .

وعدد الطلبة الذين حصلوا على معدل ما بين 2 و 2.99اي المعدل الذي عدده الصحيح اثنين ..... الخ

أي اريد الناتج كما يلي :

the avg the number of students

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

1 1

2 7

3 9

4 15

5 1

فكر قليلا !!

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

اليك الحل :

select trunc(avg_po) "the avg" , count(*) "the number of students"

from stud

group by trunc(avg_po) ;

انظر الى الحل ..

كم هو سهل ...

فكر لو حللنا هكذا .. هل ستصبح صحيحة ؟؟ :

select avg_po "the avg" , count(*) "the number of students"

from stud

group by trunc(avg_po) ;

في الحقيقة خاطئة !!

هل

avg_po نفس trunc(avg_po) ؟؟

انا شخصيا كنت اعتقد انهما متساويتين في العمل , وكنت اعتقد ان الحل السابق كان صحيح !!

لكن ظهر لي الخطأ فعرفت ان :

Group by trunc(avg_po)

سوف يظهر لي نتيجة غير :

Group by avg_po

فكر فيها .. , ستجد انه من المخزي عمل مثل الجملة السابقة !! فكر وارسم على ورق الناتج .

الان , اريد ان اعرض المعدل الحسابي ( المعدل الحسابي هو مجموع القيم مقسوم على عددها !! ) لمعدلات كلية ما والذين اخذو ترم ما ...

أي مثلا .. اريد عرض المعدل الحسابي لمعدلات جميع الطلبة في الكلية الاولى والذين درسوا ترم واحد كمجموعة ...

و عرض المعدل الحسابي لمعدلات جميع الطلبة في الكلية الاولى والذين درسوا ترمين كمجموعة اخرى ... وهكذا !!

اريد النتيجة كما يلي :

AVG COL_NO TRM_NO NUMBER_OF_STUDENT

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

1.89 2 2 1

3.09 3 1 3

3.10 3 3 4

3.14 1 2 5

3.44 3 4 2

3.92 3 5 3

4.21 2 3 5

4.39 2 4 4

4.45 2 1 2

4.53 1 1 3

4.77 3 2 1

فكر ....

اليك الحل :

select to_char(avg(avg_po) ,'0.00') "AVG" , col_no , trm_no , count(*) number_of_student

from stud

group by col_no , trm_no

order by avg(avg_po) ;

تم تقسيم الصفوف على حسب رقم الكلية , وعدد الأترام

!!

... فكر فيها ..... وحاول فهمها بطريقتك .

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

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

اليك مثال اخير نختم به الدرس .. واعتقد انه سهل عليك الان ,’,’,’

في جدول الطلبة

stud اريد عرض كم عدد الطلبة الذين اخذو معدل عدده الصحيح موحد , وقسمهم موحد , و لهم نفس عدد الأترام ,

أي عدد الطلبة الذين اخذوا معدل بين 1 و 1.99 في الكلية الاولى , وعدد اترامهم ترم واحد ...

وعدد الطلبة الذين اخذوا معدل بين 1 و 1.99 في الكلية الاولى ... وعدد اترامهم ترمين ..

.

.....

.....

.....

أي اريد النتيجة كما يلي :

COL_NO TRM_NO TRUNC(AVG_PO) NUMBER_OF_STUDENT

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

1 1 3 1

1 1 4 1

1 1 5 1

1 2 2 2

1 2 3 2

1 2 4 1

2 1 4 2

2 2 1 1

2 3 3 1

2 3 4 4

2 4 3 1

2 4 4 3

3 1 2 1

3 1 3 2

3 2 4 1

3 3 2 2

3 3 3 2

3 4 2 1

3 4 4 1

3 5 2 1

3 5 4 2

و الحل .... ؟؟

[The Coder]

اليك الحل ....

select col_no ,trm_no ,trunc(avg_po), count(*)number_of_student

from stud

group by col_no , trm_no , trunc(avg_po) ;

فكرته تقسيم بحسب ثلاث اعمدة ....

حاول رسم التقسيم ..... وفهمها , طبق وجرب وفكر وارسم وخطط وتعذب واشقى واتعب ...... وفي النهاية سوف تفهم مالم يفهمه غيرك .

الان انتهى الدرس ...

سوف تتعرف في الدرس القادم على الصلاحيات , و ربط الجداول , والاستعلامات الفرعية ....

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

[عجماني]

بصراحه احببتك في الله


صح شرحك طويل بس ممتع ومفيد

الى الامام يابرفسور

[The Coder]

بصراحه احببتك في الله


صح شرحك طويل بس ممتع ومفيد

الى الامام يابرفسور

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

[allahome]

بسم الله الرحمن الرحيم
أخى العزيز ...
بارك الله فيك و جعله الله فى ميزان حسناتك
اخى الفاضل
انا اقوم الآن و معى بعض الزملاء المتخصصين بعمل موسوعه كبيره تكون مرجع شامل و شافى و وافى للعرب و المسلمين و ذلك ابتغاء وجه الله تعالى من باب علم ينتفع به و يكون صدقه جاريه باذن الله
حيث نقوم بكل طاقتنا بشرح موضوعات عده ، كل فى تخصصه فهناك من يشرح الفيجوال بيسك دوت نت و السى شارب دوت نت و الدلفى و هناك من يشرح السكوال سيرفير و هناك من يشرح windows server 2003 و كيفيه عمل دومين و عمل و الإتصال به عن طريق خط adsl كل ذلك الشرح عن طريق الفيديو بالصوت و الصورة.
و انا كنت متابع لشركات كثيره فى هذا المجال و شركه ميكروسوفت التى اعجبت بها كثيرا و لكنى وجدت قصور شديد فى المكتبه العربيه فى الشرح بالصوت و الصورة
فكثيرا من الأشخاص يتابعون كتب كثيره مشروح بها و لكنهم لا يستطيعون التطبيق اما مع الشرح بالصوت و الصورة فالأمر يسير جدا جدا جدا و هناك اشياء كثيره تكون اسهل فى الكلام اكثر منها بالكتابه
صديقى الفاضل
نحن نمتلك افضل و اروع برنامج لتسجيل الشروحات بالفيديو البرنامج قمه فى الروعه دقع عاليه و مساحه قليله و يمكن التسجيل باكثر من نسق فهناك تنسيق الفلاش او الفيديو avi , wvm و غيرها دقه عاليه جدا جدا جدا فى الصوت و الصورة و كذلك حجم صغير نوعا ما فقد يكون حجم الملف wvm حوالى 13 ميجا لنصف ساعه شرح
و الحمد لله مع انتشار خطوط الـdsl فى الوطن العربى لا توجد مشكله من تحميل اى منها
ولو اردت حضرتك ان تأخذ بعض العينات من اعمالنا فى ذلك المجال فممكن ان تراسلنى لأن الأعمال لم تظهر للنور بعد فنحن نود ان نعمل سيرفير مجانى شامل كل هذه الشروحات و تكون شروحات فيديو و صوت من المتخصصين فى كل مجال حتى يبدع كل منهم
معذرة للإطاله و لكنى اردت ان تحول شرحك الى شرح فيديو حتى تعم الفائده للجميع
و ذلك سواء كان الشرح هذا عملى او نظرى لأن الشرح النظرى بالصوت يقرب الفكره جدا جدا للأنسان و لا تعجب عن كيفيه الشرح النظرى فانشاء الله ممكن ان اعطيك جزء من اعمالنا لترى كيفيه الشرح النظرى و العملى بكل سهوله و الشرح النظرى باستخدام برامج رسوميه ممتازه من الممكن ان نقوم بتوفير كل الأدوات اللازمه من تصميم شاشات عرض و غيرها حتى تكون جاهزه لسيادتكم على العمل
و معذرة للإطالة
تحياتى
د. علاء عبد الرحمن
محاضر لغات برمجه
AGC

[The Coder]

بسم الله الرحمن الرحيم
أخى العزيز ...
بارك الله فيك و جعله الله فى ميزان حسناتك
اخى الفاضل
انا اقوم الآن و معى بعض الزملاء المتخصصين بعمل موسوعه كبيره تكون مرجع شامل و شافى و وافى للعرب و المسلمين و ذلك ابتغاء وجه الله تعالى من باب علم ينتفع به و يكون صدقه جاريه باذن الله
حيث نقوم بكل طاقتنا بشرح موضوعات عده ، كل فى تخصصه فهناك من يشرح الفيجوال بيسك دوت نت و السى شارب دوت نت و الدلفى و هناك من يشرح السكوال سيرفير و هناك من يشرح windows server 2003 و كيفيه عمل دومين و عمل و الإتصال به عن طريق خط adsl كل ذلك الشرح عن طريق الفيديو بالصوت و الصورة.
و انا كنت متابع لشركات كثيره فى هذا المجال و شركه ميكروسوفت التى اعجبت بها كثيرا و لكنى وجدت قصور شديد فى المكتبه العربيه فى الشرح بالصوت و الصورة
فكثيرا من الأشخاص يتابعون كتب كثيره مشروح بها و لكنهم لا يستطيعون التطبيق اما مع الشرح بالصوت و الصورة فالأمر يسير جدا جدا جدا و هناك اشياء كثيره تكون اسهل فى الكلام اكثر منها بالكتابه
صديقى الفاضل
نحن نمتلك افضل و اروع برنامج لتسجيل الشروحات بالفيديو البرنامج قمه فى الروعه دقع عاليه و مساحه قليله و يمكن التسجيل باكثر من نسق فهناك تنسيق الفلاش او الفيديو avi , wvm و غيرها دقه عاليه جدا جدا جدا فى الصوت و الصورة و كذلك حجم صغير نوعا ما فقد يكون حجم الملف wvm حوالى 13 ميجا لنصف ساعه شرح
و الحمد لله مع انتشار خطوط الـdsl فى الوطن العربى لا توجد مشكله من تحميل اى منها
ولو اردت حضرتك ان تأخذ بعض العينات من اعمالنا فى ذلك المجال فممكن ان تراسلنى لأن الأعمال لم تظهر للنور بعد فنحن نود ان نعمل سيرفير مجانى شامل كل هذه الشروحات و تكون شروحات فيديو و صوت من المتخصصين فى كل مجال حتى يبدع كل منهم
معذرة للإطاله و لكنى اردت ان تحول شرحك الى شرح فيديو حتى تعم الفائده للجميع
و ذلك سواء كان الشرح هذا عملى او نظرى لأن الشرح النظرى بالصوت يقرب الفكره جدا جدا للأنسان و لا تعجب عن كيفيه الشرح النظرى فانشاء الله ممكن ان اعطيك جزء من اعمالنا لترى كيفيه الشرح النظرى و العملى بكل سهوله و الشرح النظرى باستخدام برامج رسوميه ممتازه من الممكن ان نقوم بتوفير كل الأدوات اللازمه من تصميم شاشات عرض و غيرها حتى تكون جاهزه لسيادتكم على العمل
و معذرة للإطالة
تحياتى
د. علاء عبد الرحمن
محاضر لغات برمجه
AGC

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

اهلا بك ... اهلا وسهلا ويا مرحبا .
حيا الله الدكتور علاء ^^

شكرا لمرورك , وشكرا لاطالتك .. تعبناك ويانا , واسف لتأخر ردي !!
يابوي .. لا مانع لدي .. ابدا في عرضك ...
ولكن .... انا اشتغل الان على الديال اب ..
يلزمني شهرين او شهر ... لكي اركب خدمة نت سريع ... اما ساتلايت .. او دي اس ال

5 ميقا الان كثيرة علي .. لاني بكل بساطة ... ديال اب !!
نفسي اشرح بالصوت والصورة ....
عرضك جميل يا دكتور ... ولكن .. المشكلة الديال اب ..
مارأيك .؟؟
وهل اطلعتني على عمل من اعمالكم ... ولكن ملف صغير ^^
شكرا لك يا دكتور ...
و انتظر ردك ....

يادكتور ,’,’,’,’,’,’,’,’,’

[allahome]

السلام عليكم

و بارك الله فيك و انا انتظر خط النت حتى يصلك

يمكنك مراسلتى على

alaa_eg80@msn.com

تحياتى

[The Coder]

السلام عليكم

و بارك الله فيك و انا انتظر خط النت حتى يصلك

يمكنك مراسلتى على

alaa_eg80@msn.com

تحياتى

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




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

[The Coder]

بسم الله الرحمن الرحيم

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

اسم الدرس

: جملة الاستعلام select والدوال

نوع الدرس

: تطبيقي

صعوبة الدرس

: **** من *****

اهميــة الدرس

: ****** من ***** (Over)

درجة احتراف لغة

SQL المتوقعة بعد هذا الدرس : **** من ***** (شبه خبير)

الوقت المتوقع منك لفهم الدرس

: ثماني ساعات .

المتوقع منك في هذا الدرس

:

- الاستعلامات الفرعية

- ربط الجداول

-

- الصلاحيات

هيا لنؤركل ^^

السلام عليكم

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

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

الان اريد ان اعرف اسماء الموظفين الذين يأخذون رواتب اكبر من راتب الاخ الفاضل ..

scott

كيف نعرف ؟؟

بسيطة .....

نذهب ونوجد راتب الاخ

scott

ثم نعرف راتبه كم ... ثم نكتبه في جملة استعلام اخر .....

اليك الحل :

select sal from emp where ename='SCOTT';

سوف تعرض لنا النتيجة .... كما يلي

SAL

----

3000

اذن راتب الرجل يساوي 3000

هيا لنوجد كل من راتبه اكبر من او يساوي 3000 واسمه ليس اسكوت ...

select ename ,sal from emp where sal >=3000 and ename <>'SCOTT';

الاجابة الجميلة .... :

ENAME SAL

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

KING 5000

FORD 3000

طيب .... نريد اسماء كل من هم موجودين بقسم صاحبنا

scott

والذين رواتبهم اكبر من او تساوي راتب

Jones

والذين حرفهم الاول مثل اول حرف الموظف صاحب اصغر راتب !!

الان شاهد كيف سأحصل عليها ...

يريد اسماء الذين في قسم

scott , ممممم ..... ماهو قسم السيد اسكوت ؟؟؟

دعني اذهب واتأكد ...

SQL> select deptno from emp where ename='SCOTT' ;



DEPTNO

----------

20

اذن ... القسم 20 !!

هيا ... لنكتشف من هم هؤلاء !!

SQL> select ename from emp where deptno=20;



ENAME

----------

SMITH

JONES

SCOTT

ADAMS

FORD

الان ..... يقول لي انه يريد من هؤلاء .. الذي راتبه اكبر من او يساوي راتب جونز ....

لنرى ذلك ... ولكن ... كم راتب جونز ؟؟؟؟

ممممم لنرى ذلك :

SQL> select sal from emp where ename='JONES';



SAL

----------

2975

اها .... 2975 , اذن ... كل من في قسم 20 وراتبه اكبر من او يساوي 2975

لقد استنتجت هذه الارقام بنفسي ....

هيا لنرى :

SQL> select ename from emp where deptno=20 and sal >=2975 ;



ENAME

----------

JONES

SCOTT

FORD

ماذا يريد بعد ؟؟

كل الموظفين الذين حرفهم الاول مثل حرف الموظف صاحب اصغر راتب !!

من هو صاحب اصغر راتب ؟؟ لا اعرف .. سوف اكتشف ذلك :

SQL> select min(sal) from emp ;



MIN(SAL)

----------

800

ممممم .... راتبه =800 ..... من هو يا ترى ؟؟ لنرى ذلك :

SQL> select ename from emp where sal=800;



ENAME

----------

SMITH

انه سميث ... مسكين سميث ... يأخذ فقط 800 دولار في الشهر ... اشفق عليه ....

اوبس .. أين وصلنا ؟؟؟

نسيت ؟؟؟ لقد تشابكت علي الدنيا ....

اها .... وصلنا عند " كل الموظفين الذين حرفهم الاول مثل حرف الموظف صاحب اصغر راتب"

سميث ... حرفه الاول

S

اذن .... كأن المسئلة التي تقول :

" نريد اسماء كل من هم موجودين بقسم صاحبنا

scott

والذين رواتبهم اكبر من او تساوي راتب

Jones

والذين حرفهم الاول مثل اول حرف الموظف صاحب اصغر راتب !! "

هي نفسها :

" نريد اسماء كل من هم موجودين بقسم 20

والذين رواتبهم اكبر من او تساوي 2975

والذين حرفهم الاول

S !! "

الان لأرى مالنتيجة . لأرى من هم هؤلاء :

[The Coder]

SQL> select ename from emp where sal>=2975 and deptno=20 and ename like 'S%';



ENAME

----------

SCOTT

تبا لك يا اسكوت ... هل هو انت صاحب هذه المواصفات القياسية ؟؟

اووووف .. انا من ناحيتي تعبت .. ماذا عنك وانت تقرأ ؟؟

كل مافي المسئلة اشياء مجهولة لا نعرفها , ولكي نعرفها يجب ان نوجدها على انفراد ... ماهذا يا أوراكل هل بالله عليك هذا حل ؟؟

اذا كانت المسئلة الصغيرة تعمل فينا ما عملت !! فما بالك بـمسئلة اكبر قليلا , او مسئلة اعقد واكبر !!

مالحل ؟ كيف نوجد المجاهيل وعلى ضوئها نوجد الاستعلام الذي نريده ؟

اوجدنا قسم اسكوت , وراتب جونز , وصاحب اصغر راتب , وايضا حرفه الاول

هذا كثيــــــر .... كثير جدا !

- الاستعلامات الفرعية

( Sub-query):

عملنا في المسئلتين السابقة .. استعلامات خارجية صح ؟

أي ذهبنا واستعلمنا عن قسم اسكوت في استعلام خارجي ..... وهو استعلام فرعي !

أوراكل يقترح علينا استخدام الاستعلامات الفرعية والتي تجعل أوراكل يبدو ذكيا جدا ...

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

اريد ان اعرف اسماء الموظفين الذين يأخذون رواتب اكبر من راتب

scott

عرفنا جملة الاستعلام التي توجد راتب الموظف

scott , اليك جملة الاستعلام الفرعية وهي تأتي بين قوسين :

SQL> select ename from emp where sal >= (select sal from emp where ename='SCOTT');



ENAME

----------

SCOTT

KING

FORD

واو .. لاحظ انه في جملة استعلام واحدة وفي خطوة واحدة عملنا كل ذلك الجهد في المسئلة الاولى ...

لاحظ جملة الاستعلام الفرعية .. والتي بين قوسين , وهي تخبر الأوراكل مايلي :

(اختر الصفوف يا أوراكل من جدول

emp لكل من اسمه = اسكوت )

هل سيعرضها الأوراكل ؟؟؟ هل نستطيع رؤيتها ؟؟

لا .... لن نستطيع رؤيتها ...

جملة الاستعلام الفرعية التي بين قوسين ... كأنها لا تقول "اعرض" .. كأنها تقول "أوجد"

لاحظ جملة الاستعلام انه اختار كم عمود ؟؟ عشرة ؟؟ عشرون ؟؟

بل عمود واحد , وهو الرواتب ....

لماذا الرواتب ؟ لأننا نريد ان نقارنه مع الراتب الموجود في جملة الاستعلام الرئيسية ...

كيف ذلك ؟

سوف يوجد اولا الجملة الفرعية كما يلي :

SQL> select sal from emp where ename='SCOTT' ;



SAL

----------

3000

لاحظ انه صف واحد , وعمود واحد ..

وهي قيمة واحدة .. كم ؟ 3000

أين يضعها ؟ هل يعرضها على الشاشة ؟؟

لا بل يرسلها الى جملة الاستعلام الرئيسية !!

كيف يرسلها ؟

يستبدل القيمة الناتجة بالاستعلام الفرعي ... أي :

select ename from emp where sal >= (select sal from emp where ename='SCOTT');

يوجد اولا الاستعلام الفرعي .. ثم يحول الجملة كما يلي :
select ename from emp where sal >= (3000 );

الان فهمنا الديناميكية التي تحدث داخل أوراكل ونحن اخر من يعلم عن ذلك ؟؟؟

يا سلام يا أوراكل ... كم انت ذكي جدا !!

الان ... لو كتبنا مايلي .... فإنه سيكون خاطئ ... لماذا ياترى ؟؟ :

SQL> select ename from emp where sal >= (select sal ,ename from emp where ename='SCOTT');

هل دققت ؟ هل فكرت ؟؟

اليك السبب بلسان أوراكل :

select ename from emp where sal >= (select sal ,ename from emp where ename='SCOTT');

سوف احولها الى مايلي : 
select ename from emp where sal >= ( 3000 , 'SCOTT')
اوبس ... كيف أقارن .. هل مع القيمة الاولى ام القيمة الثانية ؟؟

خطأ يا ايها المستخدم .... لا يجوز ... مع ماذا أقارن هل أقارن العمود رواتب مع 3000 او مع "اسكوت"؟؟

هل فهمت ؟

يجب ان تقارن عمود مع عمود .

الان اريد ان احل المسئلة المعقدة تلك والتي تنص على :

"

نريد اسماء كل من هم موجودين بقسم صاحبنا scott

والذين رواتبهم اكبر من او تساوي راتب

Jones

والذين حرفهم الاول مثل اول حرف الموظف صاحب اصغر راتب !!

"

هل مستعد ..... فكر فيها قليلا .. ستجدها سهلة ...

لم تعرف ؟؟؟؟ لم تستطع ؟؟ هل هي معقدة لهذه الدرجة ؟؟

اذن حاول ان تحل المسئلة التالية وسوف نحل السابقة قريبا

:

" نريد اسماء كل من هم موجودين بقسم صاحبنا scott

والذين رواتبهم اكبر من او تساوي راتب

Jones "

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

انظر حل المسئلة ....

select ename from emp where deptno=(select deptno from emp where ename='SCOTT' )

and sal >= (select sal from emp where ename='JONES');

لاحظ كلمة

AND وليست OR ... على حسب السؤال .

سهلة اعتقد ولا يوجد فيها اختراع ...

نرجع للمسئلة التي تركناها ...

"

نريد اسماء كل من هم موجودين بقسم صاحبنا scott

والذين رواتبهم اكبر من او تساوي راتب

Jones

والذين حرفهم الاول مثل اول حرف الموظف صاحب اصغر راتب !!

"

اعتقد انك تضررت في الطلب الاخير صح ؟؟ اليك الحل يا فتى :

SQL> select ename from emp where deptno=(select deptno from emp where ename='SCOTT' )

and sal >= (select sal from emp where ename='JONES')

and ename like (select substr(ename,1,1) from emp where sal=(select min(sal) from emp) ) ||'%';



 

ENAME

----------

SCOTT

والجواب كان اسكوت ...

اليك خطوات الحل بلسان أوراكل نفسه ....

اولا ... سأرى الجملة كاملة : 
select ename from emp where deptno=(select deptno from emp where ename='SCOTT' )

and sal >= (select sal from emp where ename='JONES')

and ename like (select substr(ename,1,1) from emp where sal=(select min(sal) from emp) ) ||'%';


ممممم ... الجمل الفرعية كثيرة هنا ... سوف اوجد الاعمق فيها .... 

أي الجمل الفرعية للجمل الفرعية ....

وهي جملة الاستعلام عن اصغر راتب ... 

اصغر راتب هو 800


select ename from emp where deptno=(select deptno from emp where ename='SCOTT' )

and sal >= (select sal from emp where ename='JONES')

and ename like (select substr(ename,1,1) from emp where sal= (800) ) ||'%' ;


الان ... تخلصت من الاستعلامات الفرعية الاعمق ... وسوف اتخلص من البقية ..... 


select ename from emp where deptno= (20)

and sal >= (2975)

and ename like ('S')||'%' ;
الان ...
.. 

'S' || '%' = 'S%'

الان ...... الجملة شكلها هكذا .... 
select ename from emp where deptno= (20)

and sal >= (2975)

and ename like 'S%' ;
هه.. الان سهل الحل :


ENAME

---------

SCOTT