Subquery چیست؟

Subquery چیست؟

Query ای که در دل query دیگر گنجانده شده باشد، subquery نامیده می شود.
فرض بگیرید می خواهیم تمامی کارکنانی که درآمد آن ها از حد متوسط بالاتر است را بازایابی کنید. برای این منظور ابتدا باید میانگین درآمد تمامی کارکنان را با استفاده از تابع AVG بدست آورده، سپس درآمد کارکنان را با درآمد محاسبه شده مقایسه کنید. این کار با استفاده از subquery امکان پذیر می باشد.
در مثال زیر sub query ابتدا میانگین درآمد کارکنان محاسبه کرده، سپس query اصلی اجرا می شود.

Select * from emp where sal > (select avg(sal) from emp);‎

دنبال رو مثال قبلی می خواهیم name و empno کارمندی که بیشترین درآمد را دارد، مشاهده کنیم.

Select * from emp where sal = (select max(sal) from emp);‎

برای مشاهده ی درآمدی که در رده ی دوم قرار می گیرد.

Select max(sal) from emp where

        ‎       sal < (select max(sal) from emp);‎

به همین شیوه برای مشاهده ی سومین بیشترین درآمد به ترتیب زیر عمل می کنیم.

Select max(sal) from emp where ‎

        ‎        sal < (select max(sal) from emp Where ‎

        ‎               sal < (select max(sal) from emp));‎

می خواهیم تعداد کارکنانی که درآمد آن ها از سطح متوسط بالاتر است را مشاهده کنیم.

Select count(*) from emp where

        ‎      sal > (select max(sal) from emp);‎

حال می خواهیم آن دسته از کارکنانی که در Hyderabad فعالیت دارند را مشاهده کنیم. همان طور که بخاطر دارید، emp و dept در جدولی به نام deptno با هم ترکیب می شوند و ستون city در جدول dept قرار دارد. کارکن مورد نظر در آن شهری فعالیت دارد که دپارتمان در آن واقع شده است.

Select * from emp where deptno

        ‎      in (select deptno from dept where city=’HYD’);‎

می توان از subquery در عبارت FROM دستور SELECT استفاده کرد.
برای مثال query زیر پنچ درآمدی که از نظر سطح در رده اول تا پنجم قرار می گیرند را از جدول employees بازیابی می کند.

Select sal from (select sal from emp order sal desc) ‎

        ‎             where rownum <= 5;‎

برای مشاهده ی حاصل جمع درآمد دپارتمان، می توان query زیر را نوشت.

Select sum(sal) from emp group by deptno;‎

اکنون برای بازیابی میانگین کل حقوق دپارتمان می توان از sub query در عبارت FROM استفاده کرد.

select  avg(depttotal) from (select sum(sal) as depttotal from emp group by deptno);‎

عبارت WITH

عبارت WITHصرفا یک اسم به قطعه (block) query subتخصیص می دهد که می توان در چندین مکان مختلف در query اصلی به آن اشاره کرد.
میانگین کل درآمد کارکنان دپارتمان را می توان در ویرایش 9i پایگاه داده ی oracle با استفاده از عبارت گفته شده بدین ترتیب بدست آورد.

WITH

        ‎  DEPTOT AS (select sum(sal) as dsal from emp ‎

        ‎                group by deptno)‎

        ‎  select avg(dsal) from deptot;‎

عبارت GROUP BY

می توان برخی از نتایج query ها را با استفاده از دستور GROUP BY بر اساس یک یا چند ستون مشخص شده گروه بندی کرد. هنگامی که یک دستور SELECT را بدون استفاده از عبارت GROUP BY بکار می بریم، تمامی سطرهای حاصل به عنوان یک سطر در نظر گرفته می شوند.
فرض بگیرید می خواهیم حاصل جمع درآمد تمامی کارکنان دپارتمان را مشاهده کنیم. برای این منظور query زیر را می نویسیم.

Select deptno,sum(sal) from emp group by deptno;‎

برای بازیابی میانگین درآمد دپارتمان

Select deptno,avg(sal) from emp group by deptno;‎

به منظور مشاهده ی بیشترین درآمد در هر دپارتمان، بدین ترتیب عمل می کنیم.

Select deptno,max(sal) from emp group by deptno;‎

به همین ترتیب برای محاسبه و بازگردانی پایین ترین درآمد.

Select deptno,min(sal) from emp group by deptno;‎

حال می خواهیم تعداد کارکنانی که در هر دپارتمان فعالیت دارند را مشاهده کنیم.

‏ ‏Select deptno,count(*) from emp group by deptno;‎

برای مشاهده ی دپارتمانی که درآمد کل آن بالغ بر 5000 می باشد، چگونه باید عمل کرد؟ برای نیل به این هدف باید از عبارت HAVING بهره جست. بخاطر داشته باشید که عبارت HAVING برای فیلتر کردن گروه ها تعبیه شده در حالی که عبارت WHERE ویژه ی فیلتر کردن سطرها بکار گرفته می شود.

select deptno,sum(sal) from emp group by deptno ‎

        ‎                  having sum(sal) >= 5000;‎

می خواهیم آن دپارتمان هایی را که تعداد کارکنان مشغول در آن بیش از دو نفر است را مشاهده کنیم.

Select deptno, count(*) from emp group by deptno

        ‎                        Having count(*) >=2;‎

به منظور مشاهده ی deptname و میانگین درآمد کارکنان آن، از شرط JOIN استفاده می کنیم.

Select dname,avg(sal) from emp,dept ‎

        ‎           where emp.deptno=dept.deptno group by dname;‎

به همین شیوه، برای مشاهده ی حاصل جمع درآمد کارکنان.

Select dname,sum(sal) from emp,dept ‎

        ‎   where emp.deptno=dept.deptno group by dname;‎

اکنون می خواهیم اسم شهرها و تعداد کارکنانی که در هر شهر فعالیت دارند را مشاهده کنیم. بیاد دارید که دو جدول emp و dept با استفاده از دستور join با هم ترکیب شده و جدول deptno را پدید آورند. همچنین به خاطر دارید که ستون city در جدول dept قرار دارد. کارکنان نیز در همان شهری فعالیت دارند که دپارتمان مربوط در آن واقع شده.

Select dept.city,count(empno) from emp,dept ‎

        ‎       where emp.deptno=dept.deptno ‎

        ‎             Group by dept.city;‎

عملیات ROLLUP

با استفاده از ROLLUP می توان یک سطر خلاصه (summary row) برای هر گروه ایجاد کرد. تابع تحلیلی ROLLUP در گروه simple_grouping_clause سطرهای انتخابی را بر اساس مقادیر اولین عبارت n, n-1, n-2, ... 0 در تعریف عبارت GROUP BY و یک سطر خلاصه برای هر گروه برمی گرداند. می توان با استفاده از عملیات ROLLUP، مقادیر تجمعی (جمع های جزئی که سازنده ی یک جمع کلی هستند) را همراه با تابع sum بدست آورد. هنگام استفاده از rollup با تابع sum، این تابع یک جمع جزئی از سطرهای گروه فرزند (گروه فرزند، گروهی است که آخرین دسته بندی بر اساس آن انجام می شود) را محاسبه نموده و به عنوان مقداری برای گروه والد در نظر می گیرد. با بکار گیری توابع aggregation دیگر مثل count، می توان انواع خروجی را تهیه نمود.
نظر به اینکه سه عبارت (n=3) در بند rollup گروه simple_grouping_clause وجود دارد، عملیات به گروه بندی های n+1 = 3+1 = 4 منتج می شود. سطرهایی که بر اساس مقادیر عبارت های 'n' گروه بندی می شوند، regular rows (سطرهای منظم) خوانده می شوند و سطرهای باقی مانده همگی superaggregate rows (سطرهای اضافه بر سازمان) نامیده می شوند.
Query زیر با استفاده از عملیات rollup، مقدار فروش مربوط به یک سال و محصول را نمایش می دهد.

Select prod,year,sum(amt) from sales

        ‎     group by rollup(prod,year);‎

تابع تحلیلی CUBE

عملیات CUBE در گروه simple_grouping_clause سطرهای انتخابی را بر اساس مقادیر تمامی ترکیب های ممکن عبارت ها گروه بندی می کند، سپس یک سطر خلاصه از اطلاعات به ازای هر گروه بازگردانی می نماید. همچنین می توان با استفاده از عملیات CUBE مقادیر cross- tabulation (جدول پیشایندی) تولید کرد.
CUBE به دستور SELECT این قابلیت را می دهد که زیرمجموع تمامی ترکیب های ممکن یک گروه از بعد ها را محاسبه کند. این تابع همچنین یک جمع کل برمی گرداند.
در نظر بگیرید سه عبارت (n=3) در بند simple_grouping_clause وجود دارد، عملیات cube به گروه بندی های 2n = 23 = 8 منتج خواهد شد. سطرهایی که بر اساس مقادیر عبارت های 'n' گروه بندی شده اند، regular rows و باقی سطرها superaggregate rows اطلاق می گردند.
Query زیر با استفاده از عملیات CUBE مقدار فروش را بر اساس نوع محصول و سال تولید آن، نمایش می دهد.

Select prod,year,sum(amt) from sales

        ‎     group by CUBE(prod,year);‎

تفاوت بین ROLLUP و CUBE

• ROLLUP: توابع جمعی را روی سلسله مرابتی از فیلدهای مدنظر محاسبه خواهد کرد.
• CUBE: توابع جمعی را روی تمامی حالات ممکن از فیلدهای مد نظر محاسبه خواهد کرد.

عبارت CASE

عبارت های CASE به شما این امکان را می دهند که بدون نیاز به فراخوانی procedure ها (رویه) از منطق IF ... THEN ... ELSE در دستورات SQL خود بهره ببرید.
Query زیر با استفاده از عبارت CASE اسامی دپارتمان ها را بر مبنای جدول deptno نمایش می دهد.

Select empno,ename,sal,CASE deptno when 10 then ‘Accounts’‎

        ‎                    When 20 then ‘Sales’‎

        ‎                    When 30 then ‘R&D’‎

        ‎                    Else “Unknown’ end

        ‎            From emp;‎

دستور زیر میانگین در آمد کارکنان موجود در جدول employees را با در نظر گرفتن 2000 دلار به عنوان پایین ترین درآمد، محاسبه می کند.

SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal

        ‎   ELSE 2000 END) "Average Salary" from emp e;‎