ملخص لجمل sql
ملخص لجمل sql
Admin الثلاثاء سبتمبر 16, 2008 6:17 am
جدول orders :رقم طلب الشراء ، تاريخ طلب الشراء ، رقم العميل ، رقم الموظف ، رقم المصنع ، رقم المنتج ، الكمية ، قيمة الطلب
order_num , order_date , cust , rep , mfr , product , qty , amount
جدول products : رقم المصنع (المصنع قد يقوم بتصنيع عدة منتجات ) ، اسم المنتج ، وصف المنتج ، سعر المنتج ، الكمية المتوفرة
mfr_id , product_id , description , price , oty_on_hand
جدول customers : رقم العميل ، اسم العميل ( الشركة ) ، رقم موظف المبيعات ، الحد الأعلى لذمة العميل
cust_num , company , cust_rep , credit_limit
جدول salesreps : رقم موظف المبيعات ، اسم الموظف ، عمر الموظف ، رقم مكتب البيع التابع له الموظف ، طبعة عمل الموظف ، تاريخ
بدا العمل للموظف ، رقم المدير التابع له الموظف ، المبيعات المتوقعة للموظف ، مبيعات الموظف
empl_num , name , age , rep_office , title , hire_date , manager , quota , sales
جدول offices : رقم مكتب البيع ، المدينة ، المنطقة ، مدير المكتب ، الهدف ( قيمة المبيعات المتوقعة للمكتب ) ، مبيعات المكتب
office , city , region , mgr , target , sales
إنشاء جدول products :
create table products ( mfr_id char(3) , product_id char(5) , description varchar2(20) ,
price money , oty_on_hand integer) ;
حذف جدول products :
drop table products ;
• إدخال القيم التالية لجدول offices
insert into offices (city , region , target , sales , office) values ('dallas' , 'western' , 25000, 0 , 10 ) ;
• حذف احد العملاء حسب الاسم
delete from customers where company = 'اسم العميل' ;
• حذف موظفين المبيعات الذين لم يحقق المبيعات المتوقعة
delete from salesreps where sales < quota ;
• تعديل الحد الأعلى لذمة أحد العملاء
update customers set credit_limit = 30000 where company = 'اسم العميل' ;
• تعديل المبيعات المتوقعة للموظفين حيث تم رفعها بمقدار 10000 لكل الموظفين
update salesreps set quota = quota + 10000 ;
عرض الفرق بين مبيعات الموظف والمبيعات المتوقعة للموظف (لكل الموظفين)
select name , sales , quota , ( sales - quota ) from salesreps ;
عرض الفرق بين مبيعات الموظف والمبيعات المتوقعة للموظف (للموظفين الذين كانت مبيعاتهم أقل من المتوقع)
select name , sales , quota , ( sales - quota ) from salesreps where sales < quota ;
عرض طلبات الشراء التي قيمتها أكبر من 25000 وترتيبها تصاعديا حسب القيمة
select order_num, cust, product, qty, amount from orders where amount > 25000 order by amount ;
عرض المتوسط الحسابي لقيم طلبات الشراء
select avg(amount) from orders ;
عرض المتوسط الحسابي لقيم طلبات الشراء للعميل رقم 1
select avg(amount) from orders where cust = 1 ;
عرض رقم العميل ومجموع طلبات الشراء لكل عميل (مجمعه حسب رقم العميل)
select cust , sum(amount) from orders group by cust ;
عرض المدن التي كان حجم المبيعات المتوقعة فيها أكبر من 10% من المبيعات الفعلية + 10000
select city from offices where target > (1.1 * sales ) + 10000 ;
عرض نسبة ( المبيعات الفعلية / المبيعات المتوقع ) لمكاتب البيع
select city , target , sales , ( sales / target ) * 100 from offices ;
إعطاء الصلاحية للموظف ali بإدخال المعلومات علي جدول customers
grant insert on customers to ali ; privilege granted يظهر علي الشاشة
إعطاء الصلاحية للموظف ali بالتعديل والاستعلام علي جدول customers
grant update , select on customers to ali ;
سحب صلاحية الموظف ali بإدخال المعلومات علي جدول customers
revoke insert on customers from ali ; privilege revoked يظهر علي الشاشة
سحب جميع الصلاحيات الممنوحة للموظف ali علي جدولcustomers
revoke all on customers from ali ;
التاريخ DATE :
select name , hiredate from salesreps where hiredate >= '1/1/2004' + 15 days ;
select name , hiredate from salesreps where hiredate >= ' 1/1/2004 12:00AM ' ;
select name , hiredate from salesreps where hiredate >= dateadd ( day , 15 , '1/1/2004' ) ;
select name , hiredate from salesreps where hiredate = to_date ('jun 1 2004' , 'mon dd yyyy') ;
select name , hiredate from salesreps where hiredate > current_date ;
select name , month( hiredate ) from salesreps ;
select name , year( hiredate ) from salesreps ;
select name , month( hiredate ) from salesreps where year(hiredate) = 2004 ;
select name , to_char( hiredate ,' day month dd , yyyy ' ) ; (Wednesday june 1 , 2004 )
select name , month( hiredate ) , year( hiredate ) from salesreps ;
ترتيب البيانات المسترجعة ORDER BY :
select city , target , sales from offices where region = 'Eastern' and sales > target order by city ;
الوسط الحسابي AVERAGE :
select avg(target) , avg(sales) from offices where region = 'Eastern' ;
عرض نص بين قيم عمودين :
select city , ' has sales of ' , sales from offices ;
استخدام BETWEEN …AND :
select name , sales , quota from salesreps where sales between (.8*quota) and (1.2*quota) ;
استخدام IN :
select name , quota , sales from salesreps where rep_office in ( 11 , 12 , 22 ) ;
select order_num , order_date , amount from orders where order_date in( '1-1-2005' , '30-4-2005' ) ;
استخدام LIKE : (حيث تستخدم _ بدل الحرف الذي له أكثر من احتمال)
select company , credit_limit from customers where company like 'abcde% corp' ;
select company , credit_limit from customers where company like 'ab_def corp' ;
select company , credit_limit from customers where company like 'ab_def % ' ;
استخدام OR و AND NOT و IS NULL و IN :
select name from salesreps where (rep_office in (22,11,12) )
or ( manager is null and hiredate >= '01 - jun - 99' )
or ( sales > quota and not sales > 10000.00 ) ;
استخدام ORDER BY ومعها ASC للترتيب التصاعدي و DESC للترتيب التنازلي :
select city , region , sales from offices order by region , city ;
select city , region , sales from offices order by sales desc ;
select city , region , ( sales - target ) from offices order by 3 desc ; حيث الرقم 3 يدل علي رقم العامود
select city , region , ( sales - target ) from offices order by region asc , 3 desc ;
استخدام UNION : حيث تقوم بجمع أو ضم نتيجة أكثر من استعلام في جدول مستقل وتقوم باستثناء الصفوف المكررة
select mfr_id , product_id from products where price > 2000 ; union
select mfr , product from orders where amount > 30000 ;
ولإظهار جميع الصفوف حتى لو كانت مكرر نستخدم UNION ALL
مثال :
create table a (aa varchar2(10) ) ; insert into a values ('&a') ; (a + b + c + d + e + f )
create table b (bb varchar2(10) ) ; insert into b values ('&a') ; (a + d + f + g + h )
create table c (cc varchar2(10) ) ; insert into c values ('&a') ; (b + c + f + t + y )
select* from a union ( select* from b union select* from c ) ;
النتيجة : 10 صفوف كما يلي a b c d e f g h t y
select* from a union all ( select* from b union select* from c ) ;
النتيجة : 15 صف كما يلي a b c d e f a b c d f g h t y وذلك لأن f مكرر في الجدول b والجدول c
الربط بالتساوي Equi-joins :
select order_num, amount, company, cridet_limit from orders, customers where cust = cust_num ;
select name , city , region from salesreps , offices where rep_office = office ;
select city , name , title from offices , salesreps where mgr = empl_num ;
select city , name , title from offices , salesreps where mgr = empl_num and target > 60000 ;
select order_num , amount , description from orders , products
where mfr = mfr_id and product = product_id ;
select order_num , amount , company , name from orders , customers , salesreps
where cust = cust_num and rep = empl_num and amount >10000 ;
select order_num , amount , company , name from orders , customers , salesreps
where cust = cust_num and cust_rep = empl_num and amount > 10000 ;
select order_num , amount , company , name , city from orders , customers , salesreps , offices
where cust = cust_num and cust_rep = empl_num and rep_office = office and amount >10000;
select order_num, amount, order_date, name from orders , salesreps where order_date = hiredate ;
select company , order_num , amount from customers , orders
where cust_num = cust , and cust_num = 2103 order by order_num ;
الربط بعدم التساوي Non-Equi-Joins :
select name , quota , city , target from salesreps , offices where quota > target ;
إذا تطابق اسم العمود في الجدول الأول مع الجدول الثاني ( اسم العمود sales موجود في الجدولين )
select name , salesreps.sales , city from salesreps , offices where rep_office = office ;
لاختيار جميع الأعمدة : ( حيث يتم استخدام * بعد العمود في ANS/ISO SQL2 كما يلي )
select * from salesreps , offices where rep_office = office ;
select salesreps.* , city , region from salesreps , offices where rep_office = office ;
الربط الداخلي لنفس الجدول Self-Joins :
1. حيث نقوم بنسخ الجدول مرتين ونسمي الجدولين emps و mgrs
select emps.name , mgrs.name from emps , mgrs where emps.manager = mgrs.empl_num ;
2. أو نقوم باستخدام الأسماء المستعارة للجداول كما يلي
select emps.name , mgrs.name from salesreps emps , salesreps mgrs
where emps.manager = mgrs.empl_num ;
select salesreps.name , mgrs.name from salesreps , salesreps mgrs
where salesreps.manager = mgrs.empl_num ;
select salesreps.name , salesreps.quota , mgrs.quota from salesreps , salesreps mgrs
where salesreps.manager = mgrs.empl_num and salesreps.quota > mgrs.quota ;
3. تحديد اسم الموظف ، المدينة بالنسبة للموظف ، مدير الموظف ، المدينة بالنسبة لمدير الموظف ، للموظفين الذين يعملون
في مكاتب مختلفة تابعة لمدير أخر
select emps.name , emp_office.city , mgrs.name , mgr_office
from salesreps emps , salesreps mgrs , offices emp_office , offices mgr_office
where emps.rep_office = emp_office .office and mgrs.rep_office = mgr_office.office
and emps.manager = mgrs.empl_num and emps.rep_office <> mgrs.rep_office ;
استخدام الأسماء المستعارة للجداول Table Aliases :
select salesreps.name , quota , sam.birthdays.birth_date from salesreps , birthdays
where salesreps.name = sam.birthdays.name ;
select s.name , s.quota , b.birth_date from salesreps s , sam.birthdays b where s.name = b.name ;
الربط الخارجي Outer Joins :
select name , city from salesreps , offices where rep_office = office ;
ملاحظة : إذا كانتrep_office لأحد الموظفين null لا يظهر اسمه حسب الاستعلام السابقة وحتى تظهر null نكتب الاستعلام كما يلي
select name , city from salesreps , offices where rep_office * = office ;
select name , city from salesreps , offices where office = * rep_office ; ناتج الاستعلام مطابق مع ما قبلة
إذا كان لدينا جدولين هما girls , boys وكل جدول يحتوي علي عمودين هما name , city
select* from girls , boys where girls.city = boys.city ; أو القيم غير المتطابقة من حيث المدينة null لان تظهر هنا
select* from girls , boys where girls.city * = boys.city ;
select* from girls , boys where girls.city = * boys.city ;
select* from girls , boys where girls.city * = * boys.city ;
ملاحظة : حيث يمكن أن نضيف (+) بدلا من * بجانب العمود الفاقد للبيانات وتكون النتيجة واحدة (حيث * لـ SQL2 )
order_num , order_date , cust , rep , mfr , product , qty , amount
جدول products : رقم المصنع (المصنع قد يقوم بتصنيع عدة منتجات ) ، اسم المنتج ، وصف المنتج ، سعر المنتج ، الكمية المتوفرة
mfr_id , product_id , description , price , oty_on_hand
جدول customers : رقم العميل ، اسم العميل ( الشركة ) ، رقم موظف المبيعات ، الحد الأعلى لذمة العميل
cust_num , company , cust_rep , credit_limit
جدول salesreps : رقم موظف المبيعات ، اسم الموظف ، عمر الموظف ، رقم مكتب البيع التابع له الموظف ، طبعة عمل الموظف ، تاريخ
بدا العمل للموظف ، رقم المدير التابع له الموظف ، المبيعات المتوقعة للموظف ، مبيعات الموظف
empl_num , name , age , rep_office , title , hire_date , manager , quota , sales
جدول offices : رقم مكتب البيع ، المدينة ، المنطقة ، مدير المكتب ، الهدف ( قيمة المبيعات المتوقعة للمكتب ) ، مبيعات المكتب
office , city , region , mgr , target , sales
إنشاء جدول products :
create table products ( mfr_id char(3) , product_id char(5) , description varchar2(20) ,
price money , oty_on_hand integer) ;
حذف جدول products :
drop table products ;
• إدخال القيم التالية لجدول offices
insert into offices (city , region , target , sales , office) values ('dallas' , 'western' , 25000, 0 , 10 ) ;
• حذف احد العملاء حسب الاسم
delete from customers where company = 'اسم العميل' ;
• حذف موظفين المبيعات الذين لم يحقق المبيعات المتوقعة
delete from salesreps where sales < quota ;
• تعديل الحد الأعلى لذمة أحد العملاء
update customers set credit_limit = 30000 where company = 'اسم العميل' ;
• تعديل المبيعات المتوقعة للموظفين حيث تم رفعها بمقدار 10000 لكل الموظفين
update salesreps set quota = quota + 10000 ;
عرض الفرق بين مبيعات الموظف والمبيعات المتوقعة للموظف (لكل الموظفين)
select name , sales , quota , ( sales - quota ) from salesreps ;
عرض الفرق بين مبيعات الموظف والمبيعات المتوقعة للموظف (للموظفين الذين كانت مبيعاتهم أقل من المتوقع)
select name , sales , quota , ( sales - quota ) from salesreps where sales < quota ;
عرض طلبات الشراء التي قيمتها أكبر من 25000 وترتيبها تصاعديا حسب القيمة
select order_num, cust, product, qty, amount from orders where amount > 25000 order by amount ;
عرض المتوسط الحسابي لقيم طلبات الشراء
select avg(amount) from orders ;
عرض المتوسط الحسابي لقيم طلبات الشراء للعميل رقم 1
select avg(amount) from orders where cust = 1 ;
عرض رقم العميل ومجموع طلبات الشراء لكل عميل (مجمعه حسب رقم العميل)
select cust , sum(amount) from orders group by cust ;
عرض المدن التي كان حجم المبيعات المتوقعة فيها أكبر من 10% من المبيعات الفعلية + 10000
select city from offices where target > (1.1 * sales ) + 10000 ;
عرض نسبة ( المبيعات الفعلية / المبيعات المتوقع ) لمكاتب البيع
select city , target , sales , ( sales / target ) * 100 from offices ;
إعطاء الصلاحية للموظف ali بإدخال المعلومات علي جدول customers
grant insert on customers to ali ; privilege granted يظهر علي الشاشة
إعطاء الصلاحية للموظف ali بالتعديل والاستعلام علي جدول customers
grant update , select on customers to ali ;
سحب صلاحية الموظف ali بإدخال المعلومات علي جدول customers
revoke insert on customers from ali ; privilege revoked يظهر علي الشاشة
سحب جميع الصلاحيات الممنوحة للموظف ali علي جدولcustomers
revoke all on customers from ali ;
التاريخ DATE :
select name , hiredate from salesreps where hiredate >= '1/1/2004' + 15 days ;
select name , hiredate from salesreps where hiredate >= ' 1/1/2004 12:00AM ' ;
select name , hiredate from salesreps where hiredate >= dateadd ( day , 15 , '1/1/2004' ) ;
select name , hiredate from salesreps where hiredate = to_date ('jun 1 2004' , 'mon dd yyyy') ;
select name , hiredate from salesreps where hiredate > current_date ;
select name , month( hiredate ) from salesreps ;
select name , year( hiredate ) from salesreps ;
select name , month( hiredate ) from salesreps where year(hiredate) = 2004 ;
select name , to_char( hiredate ,' day month dd , yyyy ' ) ; (Wednesday june 1 , 2004 )
select name , month( hiredate ) , year( hiredate ) from salesreps ;
ترتيب البيانات المسترجعة ORDER BY :
select city , target , sales from offices where region = 'Eastern' and sales > target order by city ;
الوسط الحسابي AVERAGE :
select avg(target) , avg(sales) from offices where region = 'Eastern' ;
عرض نص بين قيم عمودين :
select city , ' has sales of ' , sales from offices ;
استخدام BETWEEN …AND :
select name , sales , quota from salesreps where sales between (.8*quota) and (1.2*quota) ;
استخدام IN :
select name , quota , sales from salesreps where rep_office in ( 11 , 12 , 22 ) ;
select order_num , order_date , amount from orders where order_date in( '1-1-2005' , '30-4-2005' ) ;
استخدام LIKE : (حيث تستخدم _ بدل الحرف الذي له أكثر من احتمال)
select company , credit_limit from customers where company like 'abcde% corp' ;
select company , credit_limit from customers where company like 'ab_def corp' ;
select company , credit_limit from customers where company like 'ab_def % ' ;
استخدام OR و AND NOT و IS NULL و IN :
select name from salesreps where (rep_office in (22,11,12) )
or ( manager is null and hiredate >= '01 - jun - 99' )
or ( sales > quota and not sales > 10000.00 ) ;
استخدام ORDER BY ومعها ASC للترتيب التصاعدي و DESC للترتيب التنازلي :
select city , region , sales from offices order by region , city ;
select city , region , sales from offices order by sales desc ;
select city , region , ( sales - target ) from offices order by 3 desc ; حيث الرقم 3 يدل علي رقم العامود
select city , region , ( sales - target ) from offices order by region asc , 3 desc ;
استخدام UNION : حيث تقوم بجمع أو ضم نتيجة أكثر من استعلام في جدول مستقل وتقوم باستثناء الصفوف المكررة
select mfr_id , product_id from products where price > 2000 ; union
select mfr , product from orders where amount > 30000 ;
ولإظهار جميع الصفوف حتى لو كانت مكرر نستخدم UNION ALL
مثال :
create table a (aa varchar2(10) ) ; insert into a values ('&a') ; (a + b + c + d + e + f )
create table b (bb varchar2(10) ) ; insert into b values ('&a') ; (a + d + f + g + h )
create table c (cc varchar2(10) ) ; insert into c values ('&a') ; (b + c + f + t + y )
select* from a union ( select* from b union select* from c ) ;
النتيجة : 10 صفوف كما يلي a b c d e f g h t y
select* from a union all ( select* from b union select* from c ) ;
النتيجة : 15 صف كما يلي a b c d e f a b c d f g h t y وذلك لأن f مكرر في الجدول b والجدول c
الربط بالتساوي Equi-joins :
select order_num, amount, company, cridet_limit from orders, customers where cust = cust_num ;
select name , city , region from salesreps , offices where rep_office = office ;
select city , name , title from offices , salesreps where mgr = empl_num ;
select city , name , title from offices , salesreps where mgr = empl_num and target > 60000 ;
select order_num , amount , description from orders , products
where mfr = mfr_id and product = product_id ;
select order_num , amount , company , name from orders , customers , salesreps
where cust = cust_num and rep = empl_num and amount >10000 ;
select order_num , amount , company , name from orders , customers , salesreps
where cust = cust_num and cust_rep = empl_num and amount > 10000 ;
select order_num , amount , company , name , city from orders , customers , salesreps , offices
where cust = cust_num and cust_rep = empl_num and rep_office = office and amount >10000;
select order_num, amount, order_date, name from orders , salesreps where order_date = hiredate ;
select company , order_num , amount from customers , orders
where cust_num = cust , and cust_num = 2103 order by order_num ;
الربط بعدم التساوي Non-Equi-Joins :
select name , quota , city , target from salesreps , offices where quota > target ;
إذا تطابق اسم العمود في الجدول الأول مع الجدول الثاني ( اسم العمود sales موجود في الجدولين )
select name , salesreps.sales , city from salesreps , offices where rep_office = office ;
لاختيار جميع الأعمدة : ( حيث يتم استخدام * بعد العمود في ANS/ISO SQL2 كما يلي )
select * from salesreps , offices where rep_office = office ;
select salesreps.* , city , region from salesreps , offices where rep_office = office ;
الربط الداخلي لنفس الجدول Self-Joins :
1. حيث نقوم بنسخ الجدول مرتين ونسمي الجدولين emps و mgrs
select emps.name , mgrs.name from emps , mgrs where emps.manager = mgrs.empl_num ;
2. أو نقوم باستخدام الأسماء المستعارة للجداول كما يلي
select emps.name , mgrs.name from salesreps emps , salesreps mgrs
where emps.manager = mgrs.empl_num ;
select salesreps.name , mgrs.name from salesreps , salesreps mgrs
where salesreps.manager = mgrs.empl_num ;
select salesreps.name , salesreps.quota , mgrs.quota from salesreps , salesreps mgrs
where salesreps.manager = mgrs.empl_num and salesreps.quota > mgrs.quota ;
3. تحديد اسم الموظف ، المدينة بالنسبة للموظف ، مدير الموظف ، المدينة بالنسبة لمدير الموظف ، للموظفين الذين يعملون
في مكاتب مختلفة تابعة لمدير أخر
select emps.name , emp_office.city , mgrs.name , mgr_office
from salesreps emps , salesreps mgrs , offices emp_office , offices mgr_office
where emps.rep_office = emp_office .office and mgrs.rep_office = mgr_office.office
and emps.manager = mgrs.empl_num and emps.rep_office <> mgrs.rep_office ;
استخدام الأسماء المستعارة للجداول Table Aliases :
select salesreps.name , quota , sam.birthdays.birth_date from salesreps , birthdays
where salesreps.name = sam.birthdays.name ;
select s.name , s.quota , b.birth_date from salesreps s , sam.birthdays b where s.name = b.name ;
الربط الخارجي Outer Joins :
select name , city from salesreps , offices where rep_office = office ;
ملاحظة : إذا كانتrep_office لأحد الموظفين null لا يظهر اسمه حسب الاستعلام السابقة وحتى تظهر null نكتب الاستعلام كما يلي
select name , city from salesreps , offices where rep_office * = office ;
select name , city from salesreps , offices where office = * rep_office ; ناتج الاستعلام مطابق مع ما قبلة
إذا كان لدينا جدولين هما girls , boys وكل جدول يحتوي علي عمودين هما name , city
select* from girls , boys where girls.city = boys.city ; أو القيم غير المتطابقة من حيث المدينة null لان تظهر هنا
select* from girls , boys where girls.city * = boys.city ;
select* from girls , boys where girls.city = * boys.city ;
select* from girls , boys where girls.city * = * boys.city ;
ملاحظة : حيث يمكن أن نضيف (+) بدلا من * بجانب العمود الفاقد للبيانات وتكون النتيجة واحدة (حيث * لـ SQL2 )
Admin- لوحة الشرف
- المساهمات : 17
تاريخ التسجيل : 23/07/2007 -
رد: ملخص لجمل sql
Admin الثلاثاء سبتمبر 16, 2008 6:36 am
delete from customers where NOT EXISTS
( select* from orders where cust = cust_num and order_date < '10-nov-95' ) ;
delete from orders ;
التعديل في بيانات السجلات باستخدام Update :
update customers set credit_limit =10000 , cust_rep = 101 where company = ' aci ' ;
update salesreps set rep_office = 11 , quota = . 9 * quota where rep_office = 12 ;
update customers set cust_rep = 101 where cust_rep IN ( 102 , 103 , 105 ) ;
update salesreps set quota = 50000 where quota is null ;
update offices set quota = 50000 , sales = quota where quota < 50000 ;
update salesreps set quota = 1.05 * quota ;
update customers set credit_limit = credit_limit +1000 where cust_num IN
( select distinct cust from orders where amount > 25000 ) ;
update customers set cust_rep = 105 where cust_rep IN
( select empl_num from salesreps where sales < ( . 8 * quota ) ) ;
update salesreps set manager = 106 where 3 <
(select count(*) from customers where cust_rep = empl_num);
إنشاء الجداول Create Tables :
create table offices ( office integer not null , city varchar2(15) not null , عدد صحيح
region varchar2(10) not null , mgr integer ,
target number(7,2) , sales number(7,2) not null ) ;
إنشاء معاينة أو عرض Creating a View : ( حيث يتم إنشاء جدول اعتباري )
create view v_western as select* from salesreps where rep_office in ( 11 , 12 , 13 ) ;
create view v_eastern as select* from offices where region = ' eastern ' ;
create view v_orders as select* from orders where cust in
( select cust_num from customers where cust_rep = 102 ) ;
create view v_customer as select* from customers where 30000 <
( select sum(amount) from orders where cust = cust_num ) ;
create view v_rep_inform as select empl_num , name , rep_office from salesreps ;
create view v_office as select office , city , region from offices ;
create view v_cust_inform as select company , cust_rep from customers ;
create view ord_by_rep ( who , how_many , total , low , high , average ) AS
select ( rep , count(*) , sum(amount ) , min(amount) , max(amount) , avg(amount)
from orders group by rep ;
استخدام الجدول الاعتباري السابق ( ord_by_rep ) في استعلام جديد :
select name , how_many , total , average from salesreps , ord_by_rep
where who = empl_num order by total desc total ترتيب تنازلي حسب قيمة
إنشاء View باستخدام أكثر من جدول :
create view order_info ( order_num , company , rep_name , amount ) AS
select ( order_num , company , name , amount )
from orders , customers , salesreps where cust = cust_name and rep = empl_num ) ;
select rep_name , company , sum(amount) from order_info group by rep_name , company ;
select company , amount , rep_name from order_info where amount > 2000 order by amount desc );
إدخال وتعديل البيانات أو السجلات داخل الجدول الاعتباري View باستخدام insert :
عند كتبة معادلة إدخال البيانات في الجدول الاعتباري view وكانت هذه البيانات تتطابق مع ما يتضمنه الجدول الاعتباري فإن البيانات سوف تسجل في هذا الجدول الاعتباري كما أنها سوف تسجل في الجدول الأصلي الذي تكون منه الجدول الاعتباري وإذا كانت هذه البيانات لا تتطابق مع ما يتضمنه الجدول الاعتباري فإنها سوف تسجل في الجدول الأصلي فقط وذلك حسب المثال التالي
create view eastern as select* from salesreps where rep_office in ( 10 , 11 ) ;
insert into eastern (empl_num , name , rep_office , age , sales )
values ( 100 , 'ali' , 10 , 30 , 1000 ) ; هنا سوف يتم الإدخال في الجدولين
insert into eastern (empl_num , name , rep_office , age , sales )
values ( 100 , 'ali' , 15 , 30 , 1000 ) ; salesreps هنا سوف يتم الإدخال فقط في جدول
update eastern set rep_office = 21 where empl_num = 104 ; salesreps هنا سوف يتم التعديل فقط في جدول
ملاحظة : إذا كان أحد الأعمدة في الجدول view يحتوي علي عمليات تجميع أو دوال فإنه لا يمكن إجراء عمليات إدخال أو حذف أو تعديل علي
هذا العمود وعلي سبيل المثال الجدول الاعتباريview ord_by_rep ) ) حيث لا يمكن إجراء أي عملية عليه
استخدام القيد with check option : عند استخدام هذا القيد فإنه لا تتم عمليات الإدخال أو التعديل أو الحذف علي الجدول الأصلي من
خلال الجدول الاعتباري view إلا إذا كانت متطابقة مع ما يتضمنه الجدول الاعتباري view
create view eastern as select* from salesreps where rep_office in ( 10 , 11 )
with check option ;
حذف الجدول الاعتباري view :
drop view eastern ;
drop view eastern cascade ;
cascade : تم استخدامها هنا لحذف أي view أخر مرتبط أو يعتمد علي هذا view eastern
منح الامتيازات Granting Privileges :
grant select , insert , delete , update on orders to ali ;
grant all privileges on salesreps to ali ;
all privileges : تستخدم لمنح الامتيازات التالية (select , insert , delete , update , references )
grant select on offices to public ;
public : لمنح هذا الامتياز لكل المستخدمين
أمثلة :
create view v_aaa as select* from eastern where office = 10 ; grant all privileges on v_aaa to ali;
grant select on eastern to jamal with grant option ;
grant insert , delete , update on eastern to jamal ;
منح الامتيازات علي أعمدة معينة في جدول :
grant update ( company , cust_rep ) on customers to ali ;
grant select ( empl_num , name , rep_office ) on salesreps to ali ;
منع امتيازات العناصر باستخدام Revoke :
revoke all privileges on offices from ali ;
revoke all privileges on offices from public ;
revoke update , delete on offices from ali , jamal ;
revoke select , update on orders from ali cascade ; ( SQL2 )
revoke select , update on orders from ali restrict ; ( SQL2 )
restrict : عند استخدامها يتم إظهار إشارة خطاء إذا كان هنالك مستخدم آخر قد حصل علي هذه الامتيازات من قبل المستخدم ali
The System Catalog :
Tables : user_catalog , user_tables , all tables , user_synonyms
Columns : user_tab_columns , all_tab_columns
Users : all_users
Views : user_views , all_views
Privileges : user_tab_privs , user_col_privs , user_sys_privs
select table_name , owner from all_tables ;
select column_name , data_type from user_tab_columns where table_name = 'offices' ;
select view_name , text_length , text from user_views ;
select username from all_users ;
Programmatic SQL Techniques :
DBMS : Oracle
Callable API : oracle call interface (ORI) , ODBC , JDBC
Embedded SQL Language Support : C , COBOL , FORTRAN , PASCAL , PL/I , JAVA
إنشاء الروتين (الإجراء) Procedure :
مثال : حيث يمكن كتابة الصيغ التالية بالروتين add_cust
insert into customers ( cust_num , company , cust_rep , credit_limit) values (1001,'x corp',77 , 20000);
update salesreps set quota = quota + 30000 where empl_num = 77 ;
update offices set target = target + 30000 where city = 'chicago' ; commit ;
create procedure add_cust ( c_name in varchar(20) , c_num in integer , c_rep in integer ,
cred_lim in number(10,2) , tgt_sls in number(10,2) , c_offc in varchar(15) ) AS begin
insert into customers(cust_num,company,cust_rep,credit_limit) values(c_num,c_name,c_rep,cred_lim);
update salesreps set quota = quota + quota + tgt_sls where empl_num = c_rep ;
update offices set target = target + tgt_sls where city = c_offc ; commit ; end ;
لمناداة ( لتطبيق ) الروتين باستخدام Execute ( تتابع البيانات هنا حسب ما تسجيله في إنشاء الروتين ) أو باستخدام Call
execute add_cust( 'x corp' , 1001 , 77 , 20000 , 30000 , 'chicago' )
لحذف الروتين drop procedure add_cust
مثال : استخدام الروتين مع المتغيرات ( check order total for a customer )
create procedure chk_tot ( c_num in integer ) AS
declare tot_ord number(10,2) ; msg_text varchar2(30) ;
begin select sum(amount) into tot_ord from orders where cust = c_num ;
if tot_ord < 30000 msg_text := 'high order total' ; else msg_text := 'low order total' ; end ;
مثال : روتين يحتوي علي باراميتر مخرج ( get customer name , sales rep and office )
create procedure get_cust_info ( c_num in integer , c_name out varchar(20) ,
r_name out varchar(15) , c_offc out varchar(15) ) AS begin
select company , name , city into c_name , r_name , c_offc from customers , salesreps , offices
where cust_num = c_num and empl_num = cust_rep and office = rep_office ;
حيث يمكن مناداة الروتين السابق وعرض قيم البارامترات كما يلي
declare the_name varchar(20) , the_rep varchar(15) , the_city varchar(15) ;
execute get_cust_info( 2111, the_name , the_rep , the_city ) ;
مثال : إنشاء روتين لتعديل quota و target وذلك عندما يتم إدخال عميل جديد في جدول customers وذلك حسب ما يلي
create procedure add_cust ( c_name in varchar(20) , /* input customer name */
c_num in integer , /* input customer number */
cred_lim in number(10,2) , /* input credit limit */
tgt_sls in number(10,2) , /* input target sales */
c_rep in integer , /* input salesrep empl */
c_offc in varchar(15) ) /* input office city */
AS begin insert into customers ( cust_num , company , cust_rep , credit_limit )
values ( c_num , c_name , c_rep , cred_lim )
if tgt_sales <= 20000 then
update salesreps set quota = quota + quota + tgt_sls where empl_num = c_rep ;
else update salesreps set quota = quota + quota + 20000 where empl_num = c_rep ; end if ;
update offices set target = target + tgt_sls where city = c_offc ; commit ; end ;
الدوال أو الاقترانات Function :
مثال : يبين إجمالي قيمة طلبات الشراء للعميل ( return total order amount for a customer )
create function get_tot_ords ( c_num in integer ) return number(10,2) AS
declare tot_ord number(10,2) ; begin
select sum(amount) into tot_ord from orders where cust = c_num ; return tot_ord ;
exception when no_data_found then raise_application_error (-20123 , 'bad cust' ) ;
when others then raise_application_error (-20199 , 'unknown error' ) ; end ;
حيث يمكن استخدام نتيجة الدالة في select كما يلي
select company , name from customer , salesreps where cust_rep = empl_num and
get_tot_ords(cust_num) > 10000 ;
استخدام loop :
select sum(target) into total_tgt from offices ; while ( total_tgt < 2400000 ) loop
update offices set target = target - 10000 ;
select sum(target) into total_tgt from offices ; end loop ;
استخدام المشيرات Cursors :
create procedure sort_orders( )
declare cursor o_cursor is select amount , company , name from orders , customers , salesreps
where cust = cust_num and rep = empl_num ; curs_row o_cursor%rowtype ; begin
for curs_row in o_cursor loop
if (curs_row.amount < 1000) then insert into smallorders values (curs_row.name , curs_row.amount);
elsif (curs_row.amount >10000) then insert into bigorders values (curs_row.name , curs_row.amount);
end if ; end loop ; end ;
الزناد Trigger :
مثال : زناد لتعديل target عند إضافة quota جديدة
create trigger upd_tgt before insert on salesreps for each row
when ( new.quota is not null ) begin
update offices set target = target + new.quota ; end ;
مثال :
create trigger bef_upd_ord before update on orders begin
old_total = add_orders( ) ; end ;
create trigger aft_upd_ord after update on orders begin
new_total = add_orders ( ) ; end ;
create trigger dur_upd_ord before update of amount on orders
referencing old as pre new as post for each row when ( post.amount != pre.amount ) begin
if ( post.amount < pre.amount ) then insert into ord_less
values ( pre.cust , pre.order_date , pre.amount , post.amount ) ;
elsif ( post.amount > pre.amount ) then insert into ord_more
values ( pre.cust , pre.order_date , pre.amount , post.amount ) ; end if ; end ;
إنشاء الجداول :
جدول الزبائن :
create table customers ( cust_num integer not null , company varchar(20) not null ,
cust_rep integer , credit_limit money ,
primary key ( cust_num ) , foreign key hasrep ( cust_rep ) references salesreps on delete set null );
جدول مكاتب البيع :
create table offices ( office integer not null , city varchar(15) not null ,
region varchar(10) not null , mgr integer , target money , sales money not null ,
primary key ( office ) , foreign key hasmgr ( mgr ) references salesreps on delete set null ) ;
جدول موظفين البيع :
create table salesreps ( empl_num integer not null , name varchar(15) not null , age integer
rep_office integer , title varchar(10) , hire_date date not null , manager integer ,
quota money , sales money not null ,
primary key ( empl_num ) , foreign key ( manager ) references salesreps on delete set null ,
foreign key worksin ( rep_office ) references offices on delete set null ) ;
جدول طلبات الشراء :
create table orders ( order_num integer not null , order_date date not null , cust integer not null ,
rep integer , mfr char(3) not null , product char(5) not null ,
qty integer not null , amount money not null ,
primary key ( order_num ) ,
foreign key placedby ( cust ) references customers on delete cascade ,
foreign key takenby ( rep ) references salesreps on delete set null ,
foreign key isfor ( mfr , product ) references products on delete restrict )
جدول البضائع ( المنتجات ) :
create table products ( mfr_id char(3) not null , product_id char(5) not null ,
description varchar(20) not null , price money not null , qty_on_hand integer not null ,
primary key ( mfr_id , product_id ) ) ;
( select* from orders where cust = cust_num and order_date < '10-nov-95' ) ;
delete from orders ;
التعديل في بيانات السجلات باستخدام Update :
update customers set credit_limit =10000 , cust_rep = 101 where company = ' aci ' ;
update salesreps set rep_office = 11 , quota = . 9 * quota where rep_office = 12 ;
update customers set cust_rep = 101 where cust_rep IN ( 102 , 103 , 105 ) ;
update salesreps set quota = 50000 where quota is null ;
update offices set quota = 50000 , sales = quota where quota < 50000 ;
update salesreps set quota = 1.05 * quota ;
update customers set credit_limit = credit_limit +1000 where cust_num IN
( select distinct cust from orders where amount > 25000 ) ;
update customers set cust_rep = 105 where cust_rep IN
( select empl_num from salesreps where sales < ( . 8 * quota ) ) ;
update salesreps set manager = 106 where 3 <
(select count(*) from customers where cust_rep = empl_num);
إنشاء الجداول Create Tables :
create table offices ( office integer not null , city varchar2(15) not null , عدد صحيح
region varchar2(10) not null , mgr integer ,
target number(7,2) , sales number(7,2) not null ) ;
إنشاء معاينة أو عرض Creating a View : ( حيث يتم إنشاء جدول اعتباري )
create view v_western as select* from salesreps where rep_office in ( 11 , 12 , 13 ) ;
create view v_eastern as select* from offices where region = ' eastern ' ;
create view v_orders as select* from orders where cust in
( select cust_num from customers where cust_rep = 102 ) ;
create view v_customer as select* from customers where 30000 <
( select sum(amount) from orders where cust = cust_num ) ;
create view v_rep_inform as select empl_num , name , rep_office from salesreps ;
create view v_office as select office , city , region from offices ;
create view v_cust_inform as select company , cust_rep from customers ;
create view ord_by_rep ( who , how_many , total , low , high , average ) AS
select ( rep , count(*) , sum(amount ) , min(amount) , max(amount) , avg(amount)
from orders group by rep ;
استخدام الجدول الاعتباري السابق ( ord_by_rep ) في استعلام جديد :
select name , how_many , total , average from salesreps , ord_by_rep
where who = empl_num order by total desc total ترتيب تنازلي حسب قيمة
إنشاء View باستخدام أكثر من جدول :
create view order_info ( order_num , company , rep_name , amount ) AS
select ( order_num , company , name , amount )
from orders , customers , salesreps where cust = cust_name and rep = empl_num ) ;
select rep_name , company , sum(amount) from order_info group by rep_name , company ;
select company , amount , rep_name from order_info where amount > 2000 order by amount desc );
إدخال وتعديل البيانات أو السجلات داخل الجدول الاعتباري View باستخدام insert :
عند كتبة معادلة إدخال البيانات في الجدول الاعتباري view وكانت هذه البيانات تتطابق مع ما يتضمنه الجدول الاعتباري فإن البيانات سوف تسجل في هذا الجدول الاعتباري كما أنها سوف تسجل في الجدول الأصلي الذي تكون منه الجدول الاعتباري وإذا كانت هذه البيانات لا تتطابق مع ما يتضمنه الجدول الاعتباري فإنها سوف تسجل في الجدول الأصلي فقط وذلك حسب المثال التالي
create view eastern as select* from salesreps where rep_office in ( 10 , 11 ) ;
insert into eastern (empl_num , name , rep_office , age , sales )
values ( 100 , 'ali' , 10 , 30 , 1000 ) ; هنا سوف يتم الإدخال في الجدولين
insert into eastern (empl_num , name , rep_office , age , sales )
values ( 100 , 'ali' , 15 , 30 , 1000 ) ; salesreps هنا سوف يتم الإدخال فقط في جدول
update eastern set rep_office = 21 where empl_num = 104 ; salesreps هنا سوف يتم التعديل فقط في جدول
ملاحظة : إذا كان أحد الأعمدة في الجدول view يحتوي علي عمليات تجميع أو دوال فإنه لا يمكن إجراء عمليات إدخال أو حذف أو تعديل علي
هذا العمود وعلي سبيل المثال الجدول الاعتباريview ord_by_rep ) ) حيث لا يمكن إجراء أي عملية عليه
استخدام القيد with check option : عند استخدام هذا القيد فإنه لا تتم عمليات الإدخال أو التعديل أو الحذف علي الجدول الأصلي من
خلال الجدول الاعتباري view إلا إذا كانت متطابقة مع ما يتضمنه الجدول الاعتباري view
create view eastern as select* from salesreps where rep_office in ( 10 , 11 )
with check option ;
حذف الجدول الاعتباري view :
drop view eastern ;
drop view eastern cascade ;
cascade : تم استخدامها هنا لحذف أي view أخر مرتبط أو يعتمد علي هذا view eastern
منح الامتيازات Granting Privileges :
grant select , insert , delete , update on orders to ali ;
grant all privileges on salesreps to ali ;
all privileges : تستخدم لمنح الامتيازات التالية (select , insert , delete , update , references )
grant select on offices to public ;
public : لمنح هذا الامتياز لكل المستخدمين
أمثلة :
create view v_aaa as select* from eastern where office = 10 ; grant all privileges on v_aaa to ali;
grant select on eastern to jamal with grant option ;
grant insert , delete , update on eastern to jamal ;
منح الامتيازات علي أعمدة معينة في جدول :
grant update ( company , cust_rep ) on customers to ali ;
grant select ( empl_num , name , rep_office ) on salesreps to ali ;
منع امتيازات العناصر باستخدام Revoke :
revoke all privileges on offices from ali ;
revoke all privileges on offices from public ;
revoke update , delete on offices from ali , jamal ;
revoke select , update on orders from ali cascade ; ( SQL2 )
revoke select , update on orders from ali restrict ; ( SQL2 )
restrict : عند استخدامها يتم إظهار إشارة خطاء إذا كان هنالك مستخدم آخر قد حصل علي هذه الامتيازات من قبل المستخدم ali
The System Catalog :
Tables : user_catalog , user_tables , all tables , user_synonyms
Columns : user_tab_columns , all_tab_columns
Users : all_users
Views : user_views , all_views
Privileges : user_tab_privs , user_col_privs , user_sys_privs
select table_name , owner from all_tables ;
select column_name , data_type from user_tab_columns where table_name = 'offices' ;
select view_name , text_length , text from user_views ;
select username from all_users ;
Programmatic SQL Techniques :
DBMS : Oracle
Callable API : oracle call interface (ORI) , ODBC , JDBC
Embedded SQL Language Support : C , COBOL , FORTRAN , PASCAL , PL/I , JAVA
إنشاء الروتين (الإجراء) Procedure :
مثال : حيث يمكن كتابة الصيغ التالية بالروتين add_cust
insert into customers ( cust_num , company , cust_rep , credit_limit) values (1001,'x corp',77 , 20000);
update salesreps set quota = quota + 30000 where empl_num = 77 ;
update offices set target = target + 30000 where city = 'chicago' ; commit ;
create procedure add_cust ( c_name in varchar(20) , c_num in integer , c_rep in integer ,
cred_lim in number(10,2) , tgt_sls in number(10,2) , c_offc in varchar(15) ) AS begin
insert into customers(cust_num,company,cust_rep,credit_limit) values(c_num,c_name,c_rep,cred_lim);
update salesreps set quota = quota + quota + tgt_sls where empl_num = c_rep ;
update offices set target = target + tgt_sls where city = c_offc ; commit ; end ;
لمناداة ( لتطبيق ) الروتين باستخدام Execute ( تتابع البيانات هنا حسب ما تسجيله في إنشاء الروتين ) أو باستخدام Call
execute add_cust( 'x corp' , 1001 , 77 , 20000 , 30000 , 'chicago' )
لحذف الروتين drop procedure add_cust
مثال : استخدام الروتين مع المتغيرات ( check order total for a customer )
create procedure chk_tot ( c_num in integer ) AS
declare tot_ord number(10,2) ; msg_text varchar2(30) ;
begin select sum(amount) into tot_ord from orders where cust = c_num ;
if tot_ord < 30000 msg_text := 'high order total' ; else msg_text := 'low order total' ; end ;
مثال : روتين يحتوي علي باراميتر مخرج ( get customer name , sales rep and office )
create procedure get_cust_info ( c_num in integer , c_name out varchar(20) ,
r_name out varchar(15) , c_offc out varchar(15) ) AS begin
select company , name , city into c_name , r_name , c_offc from customers , salesreps , offices
where cust_num = c_num and empl_num = cust_rep and office = rep_office ;
حيث يمكن مناداة الروتين السابق وعرض قيم البارامترات كما يلي
declare the_name varchar(20) , the_rep varchar(15) , the_city varchar(15) ;
execute get_cust_info( 2111, the_name , the_rep , the_city ) ;
مثال : إنشاء روتين لتعديل quota و target وذلك عندما يتم إدخال عميل جديد في جدول customers وذلك حسب ما يلي
create procedure add_cust ( c_name in varchar(20) , /* input customer name */
c_num in integer , /* input customer number */
cred_lim in number(10,2) , /* input credit limit */
tgt_sls in number(10,2) , /* input target sales */
c_rep in integer , /* input salesrep empl */
c_offc in varchar(15) ) /* input office city */
AS begin insert into customers ( cust_num , company , cust_rep , credit_limit )
values ( c_num , c_name , c_rep , cred_lim )
if tgt_sales <= 20000 then
update salesreps set quota = quota + quota + tgt_sls where empl_num = c_rep ;
else update salesreps set quota = quota + quota + 20000 where empl_num = c_rep ; end if ;
update offices set target = target + tgt_sls where city = c_offc ; commit ; end ;
الدوال أو الاقترانات Function :
مثال : يبين إجمالي قيمة طلبات الشراء للعميل ( return total order amount for a customer )
create function get_tot_ords ( c_num in integer ) return number(10,2) AS
declare tot_ord number(10,2) ; begin
select sum(amount) into tot_ord from orders where cust = c_num ; return tot_ord ;
exception when no_data_found then raise_application_error (-20123 , 'bad cust' ) ;
when others then raise_application_error (-20199 , 'unknown error' ) ; end ;
حيث يمكن استخدام نتيجة الدالة في select كما يلي
select company , name from customer , salesreps where cust_rep = empl_num and
get_tot_ords(cust_num) > 10000 ;
استخدام loop :
select sum(target) into total_tgt from offices ; while ( total_tgt < 2400000 ) loop
update offices set target = target - 10000 ;
select sum(target) into total_tgt from offices ; end loop ;
استخدام المشيرات Cursors :
create procedure sort_orders( )
declare cursor o_cursor is select amount , company , name from orders , customers , salesreps
where cust = cust_num and rep = empl_num ; curs_row o_cursor%rowtype ; begin
for curs_row in o_cursor loop
if (curs_row.amount < 1000) then insert into smallorders values (curs_row.name , curs_row.amount);
elsif (curs_row.amount >10000) then insert into bigorders values (curs_row.name , curs_row.amount);
end if ; end loop ; end ;
الزناد Trigger :
مثال : زناد لتعديل target عند إضافة quota جديدة
create trigger upd_tgt before insert on salesreps for each row
when ( new.quota is not null ) begin
update offices set target = target + new.quota ; end ;
مثال :
create trigger bef_upd_ord before update on orders begin
old_total = add_orders( ) ; end ;
create trigger aft_upd_ord after update on orders begin
new_total = add_orders ( ) ; end ;
create trigger dur_upd_ord before update of amount on orders
referencing old as pre new as post for each row when ( post.amount != pre.amount ) begin
if ( post.amount < pre.amount ) then insert into ord_less
values ( pre.cust , pre.order_date , pre.amount , post.amount ) ;
elsif ( post.amount > pre.amount ) then insert into ord_more
values ( pre.cust , pre.order_date , pre.amount , post.amount ) ; end if ; end ;
إنشاء الجداول :
جدول الزبائن :
create table customers ( cust_num integer not null , company varchar(20) not null ,
cust_rep integer , credit_limit money ,
primary key ( cust_num ) , foreign key hasrep ( cust_rep ) references salesreps on delete set null );
جدول مكاتب البيع :
create table offices ( office integer not null , city varchar(15) not null ,
region varchar(10) not null , mgr integer , target money , sales money not null ,
primary key ( office ) , foreign key hasmgr ( mgr ) references salesreps on delete set null ) ;
جدول موظفين البيع :
create table salesreps ( empl_num integer not null , name varchar(15) not null , age integer
rep_office integer , title varchar(10) , hire_date date not null , manager integer ,
quota money , sales money not null ,
primary key ( empl_num ) , foreign key ( manager ) references salesreps on delete set null ,
foreign key worksin ( rep_office ) references offices on delete set null ) ;
جدول طلبات الشراء :
create table orders ( order_num integer not null , order_date date not null , cust integer not null ,
rep integer , mfr char(3) not null , product char(5) not null ,
qty integer not null , amount money not null ,
primary key ( order_num ) ,
foreign key placedby ( cust ) references customers on delete cascade ,
foreign key takenby ( rep ) references salesreps on delete set null ,
foreign key isfor ( mfr , product ) references products on delete restrict )
جدول البضائع ( المنتجات ) :
create table products ( mfr_id char(3) not null , product_id char(5) not null ,
description varchar(20) not null , price money not null , qty_on_hand integer not null ,
primary key ( mfr_id , product_id ) ) ;
Admin- لوحة الشرف
- المساهمات : 17
تاريخ التسجيل : 23/07/2007 -
رد: ملخص لجمل sql
Admin الثلاثاء سبتمبر 16, 2008 6:40 am
where column1 = colmun2 (+) where column1 (+) = colmun2
Joins and the SQL2 Standard :
inner joins in SQL2* :
select* from girls inner join boys on girls.city = boys.city ;
select* from girls inner join boys on ( girls.city = boys.city ) and ( girls.age = boys.age ) ;
select* from girls inner join boys using ( city , age ) ; حيث هنا أسماء الأعمدة متطابقة في كل من الجدولين
select* from girls inner join boys on ( girls.city = boys.city and girls.age > boys.age ) ;
ملاحظة : لعمل استعلام يكون فيه الـ city و age متساويين في كلا الجدولين نستخدم natural كما يلي
select* from girls natural inner join boys ;
outer joins in SQL2* :
select* from girls full outer join boys on girls.city = boys.city ;
select* from girls natural full outer join boys ;
select* from girls full outer join boys using ( city ) ;
select* from girls full join boys using ( city ) ;
select* from girls left outer join boys using ( city ) ;
select* from girls right outer join boys using ( city ) ;
cross joins and union joins in SQL2* :
select* from girls cross join boys ;
select* from girls , boys ; هنا في تطابق أسماء الأعمدة وعددها
select* from girls union all select* from boys ; لا تأخذ القيم الفارغة
select* from girls union join boys ; هنا في تطابق أسماء الأعمدة وعددها
multitable joins in SQL2 :
مثال : لو فرضنا جدول اسمه parents ويحتوي علي العمود child متطابق مع العمود name في جدول girls وجدول boys كما يحتوي
علي العمود type ( يحتوي علي التصنيف father أو mother ) والعمود pname ( يحتوي علي الاسم الأول للوالدين )
select girls.name , pname , boys.name from ( ( girls join parents on parent.child = name )
join boys on ( girls.city = boys.city) ) where type = " mother" ;
select girls.name , pname , boys.name from ( ( girls left join parents on parent.child = name )
join boys on ( girls.city = boys.city) ) where ( type = " mother" ) or ( type is null ) ;
select girls.name , pname , boys.name from ( ( girls left join parents on parent.child = name )
left join boys on ( girls.city = boys.city) ) where ( type = " mother" ) or ( type is null ) ;
مثال : لو قمنا بنسخ جدول parents حيث نستخدم النسخة الأولي لربط father مع boys والثانية لربط girls مع mother كما يلي
select girls.name , mothers.pname , boys.name , fathers.pname
from ( ( girls left join parents as mothers on ( ( child = girls.name ) and (type ="mother" ) ) )
join ( boys left join parents as fathers on ( ( child = boys.name ) and (type ="father" ))) ) using (city);
الدوال التجميعية لأكثر من صف ( Column Functions ) : ( sum , avg , min , max , count , count(*) )
count (*) : عدد صفوف نتيجة الاستعلام (حيث جميع هذه الدوال تتجاهل القيمة null )
select avg(quota) , avg(sales) from salesreps ;
select avg( 100 * ( sales / quota ) ) from salesreps ;
select sum(quota) , sum(sales) from salesreps ;
select sum(amount) from orders , salesreps where name = 'bill' and rep = empl_num ;
select avg(price) from products where mfr_id = 'aci' ;
select avg(amount) from orders where cust = 1000 ;
select min(quota) , max(quota) from salesreps ;
select min(order_date) from orders ;
select max( 100 * ( sales / quota ) ) from salesreps ;
select count(cust_num) from customers ;
select count(name) from salesreps where sales > quota ;
select count(amount) from orders where amount > 25000 ;
select count(order_num) from orders where amount > 25000 ; نتيجة هذا الاستعلام مساوية للاستعلام السابق
select count(*) from orders where amount > 25000 ; نتيجة هذا الاستعلام مساوية للاستعلام السابق
select avg(amount) , sum(amount) , (100*avg(amount / credit_limit)) , (100*avg(amount / quota))
from orders , customers , salesreps where cust = cust_num and rep = empl_num ;
select count(*) , count(sales) , count(quota) from salesreps ; في العمود null لإظهار عدد القيم
select sum(sales) , sum(quota) , (sum(sales) - sum(quota)) , sum(sales - quota) from salesreps ;
استخدام Distinct لمنع تكرار السجلات :
select count( distinct title ) from salesreps ;
select count( distinct rep_office ) from salesreps where sales > quota ;
استخدام Group By لتقسيم البيانات إلي مجموعات حسب عمود معين أو أكثر :
select rep , avg(amount) from orders group by rep ;
select rep_office , min(quota) , max(quota) from salesreps group by rep_office ;
select rep_office , count(*) from salesreps group by rep_office ; لإظهار رقم المكتب وعدد الموظفين التابعين له
select count( distinct cust_num) ,'customer for salesrep', cust_rep from customers group by cust_rep;
select rep , cust , sum(amount) from orders group by rep , cust ;
select cust , rep , sum(amount) from orders group by cust , rep order by cust , rep ;
select rep , cust , amount from orders order by rep , cust
compute sum(amount) by rep , cust
compute sum(amount) , avg(amount) by rep ;
The compute clause calculates subtotals and sub-subtotals : (SQL Server)
select empl_num , name , sum(amount) from orders , salesreps
where rep = empl_num group by empl_num , name ; مجموع مبالغ طلبات الشراء لكل موظف في المبيعات
استخدام Having بدلا من where : حيث لا يمكن استخدام الدوال التجميعية في الجزء where
select rep , avg(amount) from orders group by rep having sum(amount) > 30000 ;
select city , sum(quota) , sum(salesreps.sales) from offices , salesreps
where office = rep_office group by city having count(*) >= 2
ملاحظة : الاستعلام التالي يعرض وصف المنتج ، القيمة ، الكمية المتوفرة من المنتج حاليا ، كمية المنتج الواردة في طلبات الشراء والتي تزيد
عن 75% من الكمية المتوفر من المنتج حاليا
select description , price , price , qty_on_hand, sum(qty) from products , orders
where mfr = mfr_id and product = product_id
group by mfr_id , product_id , description , price , qty_on_hand
having sum(qty) > ( .75 * qty_on_hand ) order by qty_on_hand desc ;
استخدام الاستعلامات الفرعية Using Subqueries :
الاستعلامات الفرعية الأحادية الصف أي التي تكون نتيجتها صف واحد فقط :
select name from salesreps where quota < ( .1 * ( select sum(target) from offices ) ) ;
select city from offices where target > ( select sum(quota) from salesreps where rep_office = office );
select name from salesreps where quota >= ( select target from offices where city = ' atlanta ' ) ;
select company from customers where cust_rep =(select empl_num from salesreps where name ='bill');
select description , qty_on_hand from products where mfr_id = 'aci' and qty_on_hand >
( select qty_on_hand from products where mfr_id = 'aci' and product_id = '4104' ) ;
الاستعلامات الفرعية المتعددة الصفوف أي التي تكون نتيجتها أكثر من صف :
• استخدام IN :
select name from salesreps where rep_office IN ( select office from offices where sales > target ) ;
select name from salesreps where rep_office not IN (select office from offices where mgr = 108) ;
select company from customers where cust_num IN ( select distinct cust from orders where
mfr ='aci' and product like '4104' and order_date between '01-1-2004' and '31-1-2004') ;
• استخدام EXISTS : حيث من الأفضل استخدامها مع select*
select distinct description from products where EXISTS ( select order_num from orders
where product = product_id and mfr = mfr_id and amount >= 25000 ) ;
select description from products where EXISTS ( select * from orders
where product = product_id and mfr = mfr_id and amount >= 25000 ) ;
select company from customers where cust_rep =(select empl_num from salesreps where name ='bill')
and not EXISTS (select * from orders where cust = cust_num and amount > 3000 ) ;
select city from offices where EXISTS
(select* from salesreps where rep_office = office and quota > ( .55 * target) ) ;
• استخدام ANY :
select name from salesreps where (.1*quota) < any(select amount from orders where rep =empl_num);
select name , age from salesreps where empl_num <> any ( select mgr from offices ) ;
select name , age from salesreps where not ( empl_num = any ( select mgr from offices ) ;
ملاحظة : يمكن كتابة الاستعلام السابق كما يلي وتكون نتيجة الاستعلام متطابقة
select name , age from salesreps where not exists ( select* from offices where empl_num = mgr ) ;
• استخدامALL :
select city , target from offices where ( .50 * target ) < all
( select sales from salesreps where rep_office = office ) ;
الاستعلامات الفرعية و الربط Subqueries and joins :
select name , age from salesreps where rep_office in
( select office from offices where region = ' western' ) ; حيث يمكن كتابة الاستعلام بطريقة أخري كما يلي
select name , age from salesreps , offices where rep_office = office and region = ' western' ) ;
select name , age from salesreps where quota > ( select avg(quota) from salesreps ) ;
الاستعلامات الفرعية المتداخلة Nested Subqueries :
select company from customers where cust_rep in
( select empl_num from salesreps where rep_office in
( select office from offices where region = 'eastern' ) ;
الاستعلامات الفرعية المرتبطة بعلاقة متبادلة Correlated Subqueries :
select city from offices where sales < ( select avg(target) from offices ) ;
select city from offices where target > ( select sum(quota) from salesreps where rep_office = office);
select name from salesreps where age > 40 and empl_num in
( select manager from salesreps where sales > quota ) ;
select name from salesreps mgrs where age > 40 and mgrs.empl_num in
( select manager from salesreps emps where emps.quota > emps.sales and
emps.rep_office <> mgrs.rep_office ) ; المدير الذي يعمل في مكتب البيع وهو ليس مديره
استخدام الاستعلامات الفرعية كجزء من جملة Having
حيث يتم استخدام having مع الدوال التجميعية لأنه لا يمكن استخدام where)
select name , avg(amount) from salesreps , orders where empl_num = rep and mfr = 'aci'
group by name having avg(amount) > ( select avg(amount) from orders ) ;
select name , avg(amount) from salesreps , orders where empl_num = rep and mfr = 'aci'
group by name , empl_num avg(amount) >= ( select avg(amount) from orders where rep = empl_num);
ملاحظة : أولا تم الاستعلام عن متوسط مبيعات الموظفين التي هي أكبر من متوسط المبيعات لأوامر البيع حسب المنتجات المصنعة من قبل aci
ثانيا تم الاستعلام عن متوسط مبيعات الموظفين التي هي أكبر أو تساوي متوسط مبيعات موظفين البيع حسب المنتجات المصنعة من قبل aci
إضافة سجلات جديدة لجدول عن طريق نسخها من عدة جداول :
سوف نقو هنا بإنشاء جدول جديد اسمه Bigorders ويحتوي علي الأعمدة التالية
amount : order amount ( from orders table ) ( key cust , key rep )
company : customer name ( from customers table ) ( key cust_num )
name : salesperson name ( from salesreps table ) ( key empl_num )
perf : amount over / under quota ( calculated from salesreps )
mfr : manufacturer ID ( from orders )
product : product ID ( from orders )
qty : quantity ordered (from orders )
insert into bigorders ( amount , company , name , perf , product , mfr , qty )
select amount , company , name , ( sales - quota ) , product , mfr , qty
from orders , customers , salesreps
where cust = cust_num and rep = empl_num and amount > 15000 ;
حذف السجلات Delete From :
delete from orders where order_date < ' 15-nov-95 '
delete from customers where cust_rep IN ( 101 , 102 , 103 )
delete from salesreps where hire_date < '01-jul-99' and quota is null ;
delete from orders where rep = ( select empl_num from salesreps where name = ' ali ' ) ;
delete from customers where cust_rep IN (select empl_num from salesreps where sales >( .8*quota));
delete from salesreps where ( .02*quota ) > (select sum(amount) from orders where rep = empl_num);
Joins and the SQL2 Standard :
inner joins in SQL2* :
select* from girls inner join boys on girls.city = boys.city ;
select* from girls inner join boys on ( girls.city = boys.city ) and ( girls.age = boys.age ) ;
select* from girls inner join boys using ( city , age ) ; حيث هنا أسماء الأعمدة متطابقة في كل من الجدولين
select* from girls inner join boys on ( girls.city = boys.city and girls.age > boys.age ) ;
ملاحظة : لعمل استعلام يكون فيه الـ city و age متساويين في كلا الجدولين نستخدم natural كما يلي
select* from girls natural inner join boys ;
outer joins in SQL2* :
select* from girls full outer join boys on girls.city = boys.city ;
select* from girls natural full outer join boys ;
select* from girls full outer join boys using ( city ) ;
select* from girls full join boys using ( city ) ;
select* from girls left outer join boys using ( city ) ;
select* from girls right outer join boys using ( city ) ;
cross joins and union joins in SQL2* :
select* from girls cross join boys ;
select* from girls , boys ; هنا في تطابق أسماء الأعمدة وعددها
select* from girls union all select* from boys ; لا تأخذ القيم الفارغة
select* from girls union join boys ; هنا في تطابق أسماء الأعمدة وعددها
multitable joins in SQL2 :
مثال : لو فرضنا جدول اسمه parents ويحتوي علي العمود child متطابق مع العمود name في جدول girls وجدول boys كما يحتوي
علي العمود type ( يحتوي علي التصنيف father أو mother ) والعمود pname ( يحتوي علي الاسم الأول للوالدين )
select girls.name , pname , boys.name from ( ( girls join parents on parent.child = name )
join boys on ( girls.city = boys.city) ) where type = " mother" ;
select girls.name , pname , boys.name from ( ( girls left join parents on parent.child = name )
join boys on ( girls.city = boys.city) ) where ( type = " mother" ) or ( type is null ) ;
select girls.name , pname , boys.name from ( ( girls left join parents on parent.child = name )
left join boys on ( girls.city = boys.city) ) where ( type = " mother" ) or ( type is null ) ;
مثال : لو قمنا بنسخ جدول parents حيث نستخدم النسخة الأولي لربط father مع boys والثانية لربط girls مع mother كما يلي
select girls.name , mothers.pname , boys.name , fathers.pname
from ( ( girls left join parents as mothers on ( ( child = girls.name ) and (type ="mother" ) ) )
join ( boys left join parents as fathers on ( ( child = boys.name ) and (type ="father" ))) ) using (city);
الدوال التجميعية لأكثر من صف ( Column Functions ) : ( sum , avg , min , max , count , count(*) )
count (*) : عدد صفوف نتيجة الاستعلام (حيث جميع هذه الدوال تتجاهل القيمة null )
select avg(quota) , avg(sales) from salesreps ;
select avg( 100 * ( sales / quota ) ) from salesreps ;
select sum(quota) , sum(sales) from salesreps ;
select sum(amount) from orders , salesreps where name = 'bill' and rep = empl_num ;
select avg(price) from products where mfr_id = 'aci' ;
select avg(amount) from orders where cust = 1000 ;
select min(quota) , max(quota) from salesreps ;
select min(order_date) from orders ;
select max( 100 * ( sales / quota ) ) from salesreps ;
select count(cust_num) from customers ;
select count(name) from salesreps where sales > quota ;
select count(amount) from orders where amount > 25000 ;
select count(order_num) from orders where amount > 25000 ; نتيجة هذا الاستعلام مساوية للاستعلام السابق
select count(*) from orders where amount > 25000 ; نتيجة هذا الاستعلام مساوية للاستعلام السابق
select avg(amount) , sum(amount) , (100*avg(amount / credit_limit)) , (100*avg(amount / quota))
from orders , customers , salesreps where cust = cust_num and rep = empl_num ;
select count(*) , count(sales) , count(quota) from salesreps ; في العمود null لإظهار عدد القيم
select sum(sales) , sum(quota) , (sum(sales) - sum(quota)) , sum(sales - quota) from salesreps ;
استخدام Distinct لمنع تكرار السجلات :
select count( distinct title ) from salesreps ;
select count( distinct rep_office ) from salesreps where sales > quota ;
استخدام Group By لتقسيم البيانات إلي مجموعات حسب عمود معين أو أكثر :
select rep , avg(amount) from orders group by rep ;
select rep_office , min(quota) , max(quota) from salesreps group by rep_office ;
select rep_office , count(*) from salesreps group by rep_office ; لإظهار رقم المكتب وعدد الموظفين التابعين له
select count( distinct cust_num) ,'customer for salesrep', cust_rep from customers group by cust_rep;
select rep , cust , sum(amount) from orders group by rep , cust ;
select cust , rep , sum(amount) from orders group by cust , rep order by cust , rep ;
select rep , cust , amount from orders order by rep , cust
compute sum(amount) by rep , cust
compute sum(amount) , avg(amount) by rep ;
The compute clause calculates subtotals and sub-subtotals : (SQL Server)
select empl_num , name , sum(amount) from orders , salesreps
where rep = empl_num group by empl_num , name ; مجموع مبالغ طلبات الشراء لكل موظف في المبيعات
استخدام Having بدلا من where : حيث لا يمكن استخدام الدوال التجميعية في الجزء where
select rep , avg(amount) from orders group by rep having sum(amount) > 30000 ;
select city , sum(quota) , sum(salesreps.sales) from offices , salesreps
where office = rep_office group by city having count(*) >= 2
ملاحظة : الاستعلام التالي يعرض وصف المنتج ، القيمة ، الكمية المتوفرة من المنتج حاليا ، كمية المنتج الواردة في طلبات الشراء والتي تزيد
عن 75% من الكمية المتوفر من المنتج حاليا
select description , price , price , qty_on_hand, sum(qty) from products , orders
where mfr = mfr_id and product = product_id
group by mfr_id , product_id , description , price , qty_on_hand
having sum(qty) > ( .75 * qty_on_hand ) order by qty_on_hand desc ;
استخدام الاستعلامات الفرعية Using Subqueries :
الاستعلامات الفرعية الأحادية الصف أي التي تكون نتيجتها صف واحد فقط :
select name from salesreps where quota < ( .1 * ( select sum(target) from offices ) ) ;
select city from offices where target > ( select sum(quota) from salesreps where rep_office = office );
select name from salesreps where quota >= ( select target from offices where city = ' atlanta ' ) ;
select company from customers where cust_rep =(select empl_num from salesreps where name ='bill');
select description , qty_on_hand from products where mfr_id = 'aci' and qty_on_hand >
( select qty_on_hand from products where mfr_id = 'aci' and product_id = '4104' ) ;
الاستعلامات الفرعية المتعددة الصفوف أي التي تكون نتيجتها أكثر من صف :
• استخدام IN :
select name from salesreps where rep_office IN ( select office from offices where sales > target ) ;
select name from salesreps where rep_office not IN (select office from offices where mgr = 108) ;
select company from customers where cust_num IN ( select distinct cust from orders where
mfr ='aci' and product like '4104' and order_date between '01-1-2004' and '31-1-2004') ;
• استخدام EXISTS : حيث من الأفضل استخدامها مع select*
select distinct description from products where EXISTS ( select order_num from orders
where product = product_id and mfr = mfr_id and amount >= 25000 ) ;
select description from products where EXISTS ( select * from orders
where product = product_id and mfr = mfr_id and amount >= 25000 ) ;
select company from customers where cust_rep =(select empl_num from salesreps where name ='bill')
and not EXISTS (select * from orders where cust = cust_num and amount > 3000 ) ;
select city from offices where EXISTS
(select* from salesreps where rep_office = office and quota > ( .55 * target) ) ;
• استخدام ANY :
select name from salesreps where (.1*quota) < any(select amount from orders where rep =empl_num);
select name , age from salesreps where empl_num <> any ( select mgr from offices ) ;
select name , age from salesreps where not ( empl_num = any ( select mgr from offices ) ;
ملاحظة : يمكن كتابة الاستعلام السابق كما يلي وتكون نتيجة الاستعلام متطابقة
select name , age from salesreps where not exists ( select* from offices where empl_num = mgr ) ;
• استخدامALL :
select city , target from offices where ( .50 * target ) < all
( select sales from salesreps where rep_office = office ) ;
الاستعلامات الفرعية و الربط Subqueries and joins :
select name , age from salesreps where rep_office in
( select office from offices where region = ' western' ) ; حيث يمكن كتابة الاستعلام بطريقة أخري كما يلي
select name , age from salesreps , offices where rep_office = office and region = ' western' ) ;
select name , age from salesreps where quota > ( select avg(quota) from salesreps ) ;
الاستعلامات الفرعية المتداخلة Nested Subqueries :
select company from customers where cust_rep in
( select empl_num from salesreps where rep_office in
( select office from offices where region = 'eastern' ) ;
الاستعلامات الفرعية المرتبطة بعلاقة متبادلة Correlated Subqueries :
select city from offices where sales < ( select avg(target) from offices ) ;
select city from offices where target > ( select sum(quota) from salesreps where rep_office = office);
select name from salesreps where age > 40 and empl_num in
( select manager from salesreps where sales > quota ) ;
select name from salesreps mgrs where age > 40 and mgrs.empl_num in
( select manager from salesreps emps where emps.quota > emps.sales and
emps.rep_office <> mgrs.rep_office ) ; المدير الذي يعمل في مكتب البيع وهو ليس مديره
استخدام الاستعلامات الفرعية كجزء من جملة Having
حيث يتم استخدام having مع الدوال التجميعية لأنه لا يمكن استخدام where)select name , avg(amount) from salesreps , orders where empl_num = rep and mfr = 'aci'
group by name having avg(amount) > ( select avg(amount) from orders ) ;
select name , avg(amount) from salesreps , orders where empl_num = rep and mfr = 'aci'
group by name , empl_num avg(amount) >= ( select avg(amount) from orders where rep = empl_num);
ملاحظة : أولا تم الاستعلام عن متوسط مبيعات الموظفين التي هي أكبر من متوسط المبيعات لأوامر البيع حسب المنتجات المصنعة من قبل aci
ثانيا تم الاستعلام عن متوسط مبيعات الموظفين التي هي أكبر أو تساوي متوسط مبيعات موظفين البيع حسب المنتجات المصنعة من قبل aci
إضافة سجلات جديدة لجدول عن طريق نسخها من عدة جداول :
سوف نقو هنا بإنشاء جدول جديد اسمه Bigorders ويحتوي علي الأعمدة التالية
amount : order amount ( from orders table ) ( key cust , key rep )
company : customer name ( from customers table ) ( key cust_num )
name : salesperson name ( from salesreps table ) ( key empl_num )
perf : amount over / under quota ( calculated from salesreps )
mfr : manufacturer ID ( from orders )
product : product ID ( from orders )
qty : quantity ordered (from orders )
insert into bigorders ( amount , company , name , perf , product , mfr , qty )
select amount , company , name , ( sales - quota ) , product , mfr , qty
from orders , customers , salesreps
where cust = cust_num and rep = empl_num and amount > 15000 ;
حذف السجلات Delete From :
delete from orders where order_date < ' 15-nov-95 '
delete from customers where cust_rep IN ( 101 , 102 , 103 )
delete from salesreps where hire_date < '01-jul-99' and quota is null ;
delete from orders where rep = ( select empl_num from salesreps where name = ' ali ' ) ;
delete from customers where cust_rep IN (select empl_num from salesreps where sales >( .8*quota));
delete from salesreps where ( .02*quota ) > (select sum(amount) from orders where rep = empl_num);
Admin- لوحة الشرف
- المساهمات : 17
تاريخ التسجيل : 23/07/2007 -
مواضيع مماثلةصلاحيات هذا المنتدى:
لاتستطيع الرد على المواضيع في هذا المنتدى