عبارت‌ها و توابع در SQL

فصل چهارم عبارت‌های حسابی، استفاده از عبارت‌ها در بندهای دیگر و نام مستعار ستون را معرفی می‌کند. سپس فصل پنجم توابع آماری، گروه‌بندی و استفاده از توابع در بندهای دیگر را پوشش می‌دهد.

عبارت‌های حسابی

عبارت حسابی (Arithmetic Expression) عبارتی است که از عملوندها ــ مقادیر عددی و/یا نام ستون‌ها ــ و عملگرهای حسابی تشکیل شده است. عبارت‌های حسابی توسط SQL ارزیابی می‌شوند و با مقدار عددی مناسب جایگزین می‌شوند.

عملگرهای حسابی
OPERATOR	MEANING	EXAMPLE
+	Add	2 + 2
-	Subtract	BDATE - 365
*	Multiply	POP * 1.25
/	Divide	PERCENT / 100
( )	Precedence	2 + (4 / 2)

عملوندها می‌توانند مقدار عددی یا نام ستون باشند

در یک عبارت، عملوند معتبر شامل عدد، نام ستون و عبارت‌های دیگر است. وقتی ستونی در عبارت استفاده می‌شود، مقدار همان ستون برای ردیف جاری در محاسبه قرار می‌گیرد. تنها عملوندهای عددی نتیجهٔ محاسبهٔ معتبر تولید می‌کنند.

عملگرها بین عملوندها قرار می‌گیرند

عملگر حسابی به روش معمول بین عملوندهایش نوشته می‌شود. اگرچه فاصله در دو سوی عملگر اجباری نیست، نمونه‌های کتاب برای خوانایی از فاصله استفاده می‌کنند.

عبارت‌های داخل پرانتز نخست ارزیابی می‌شوند

SQL ابتدا عبارت‌های حسابی داخل پرانتز را ارزیابی می‌کند. سپس نتیجه با عبارت‌های دیگر همان دستور ترکیب می‌شود. محل قرارگیری پرانتز می‌تواند مقدار عبارت را به‌طور قابل توجهی تغییر دهد.

علاوه بر ستاره و نام ستون‌ها، عبارت‌ها را نیز می‌توان در فهرست ستون‌های بند SELECT مشخص کرد. SQL ستون محاسبه‌شده‌ای را در جدول نتیجه در موقعیت تعیین‌شده قرار می‌دهد. نام ستون جدید به‌صورت پیش‌فرض همان متن عبارت است.

SELECT COUNTRY, POP / AREA
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN'
ORDER BY COUNTRY

COUNTRY	POP/AREA
Austria	246.66
Germany	590.15
Liechtenst...	494.41
Switzerland	444.47

SELECT COUNTRY, GNP * 1.1
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN'
ORDER BY COUNTRY

COUNTRY	GNP*1.1
Austria	147840
Germany	1464100
Liechtenst...	693
Switzerland	164010

عبارت‌ها در بند SELECT مشخص می‌شوند.
عبارت‌ها ستون‌های جدیدی در نتیجه ایجاد می‌کنند.
عبارت‌ها برای هر ردیف محاسبه می‌شوند.

خلاصهٔ نحو با عبارت‌ها
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	[ DISTINCT ] col list	LANGUAGE, COUNTRY
SELECT	expr list	POP / AREA
FROM	table	JOBS
WHERE	comparisons	AREA > 3000000
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3

خلاصهٔ عملگرهای حسابی
OPERATOR	MEANING	EXAMPLE
+	Add	2 + 2
-	Subtract	BDATE - 365
*	Multiply	POP * 1.25
/	Divide	PERCENT / 100
( )	Precedence	2 + (4 / 2)

تمرین‌ها

اگر ۲۰ درصد کانادایی‌ها به USA مهاجرت کنند، چند آمریکایی جدید خواهیم داشت؟
برای هر نفر در USA چقدر پول هزینه می‌شود؟
تعداد کل خودروهای نظامی ــ تانک‌ها به‌اضافهٔ کشتی‌ها به‌اضافهٔ هواپیماها ــ متعلق به United States را محاسبه کنید.

امتیاز اضافه

۴۵ درصد بودجهٔ نظامی ما صرف چکش‌های ۱۲۵ دلاری می‌شود. چند چکش داریم؟

عبارت‌ها در بندهای دیگر

از آن‌جا که SQL عبارت‌های موجود در دستور SELECT را مانند «ستون‌های مجازی» پرشده با مقادیر محاسبه‌شده در نظر می‌گیرد، می‌توان در بندهای WHERE و ORDER BY نیز به‌جای نام ستون از عبارت استفاده کرد.

SELECT COUNTRY, POP / AREA
FROM COUNTRIES
WHERE POP / AREA > 2000

COUNTRY	POP/AREA
Maldives	2272.26
Malta	3029.58
Singapore	11702.29
Bahrain	2148.97
Bangladesh	2235.70

SELECT COUNTRY, POP / AREA
FROM COUNTRIES
WHERE POP / AREA > 2000
ORDER BY POP / AREA DESC

COUNTRY	POP/AREA
Singapore	11702.29
Malta	3029.58
Maldives	2272.26
Bangladesh	2235.70
Bahrain	2148.97

عبارت‌ها را می‌توان در بند WHERE استفاده کرد.
عبارت‌ها را می‌توان در بند ORDER BY استفاده کرد.

خلاصهٔ نحو عبارت‌ها در WHERE و ORDER BY
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	[ DISTINCT ] col list	LANGUAGE, COUNTRY
SELECT	expr list	POP / AREA
FROM	table	JOBS
WHERE	comparisons	AREA > 3000000
WHERE	expr oper value	POP / AREA > 300
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC

تمرین‌ها

کشور، جمعیت، مساحت و تراکم جمعیت POP/AREA را برای کشورهایی نمایش دهید که تراکمشان کمتر از ۷ نفر در هر مایل مربع است. بیشترین تراکم را در بالا قرار دهید. نتیجه ۷ ردیف دارد.
کشور، جمعیت، نرخ باسوادی و تعداد افراد باسواد را برای همهٔ کشورهایی با بیش از ۱۰۰ میلیون باسواد نمایش دهید. برای محاسبهٔ افراد باسواد، نرخ باسوادی را بر ۱۰۰ تقسیم و در جمعیت ضرب کنید. ۷ ردیف وجود دارد.

امتیاز اضافه

فرض کنید همهٔ ارتش‌ها ۳۰ درصد بودجهٔ خود را صرف زیرسیگاری می‌کنند و میانگین قیمت هر زیرسیگاری نظامی ۶۵۰ دلار است. کشور و تعداد زیرسیگاری به ازای هر سرباز را فقط زمانی نمایش دهید که این تعداد بیش از ۱۰ باشد؛ بزرگ‌ترین مقدار را بالا قرار دهید. ۹ کشور وجود دارد.

نام مستعار ستون

کلیدواژهٔ AS را می‌توان در بند SELECT برای تعریف نام مستعار ستون (Column Alias) ــ نامی که کاربر برای ستون تعیین می‌کند ــ به کار برد. Alias برای هر ستون قابل تعریف است، اما بیشتر برای اختصاص نام معنادار به ستون‌های محاسبه‌شده استفاده می‌شود.

SELECT COUNTRY, POP / AREA
FROM COUNTRIES
WHERE POP / AREA > 2000
ORDER BY POP / AREA DESC

COUNTRY	POP/AREA
Singapore	11702.29
Malta	3029.58
Maldives	2272.26
Bangladesh	2235.70
Bahrain	2148.97

SELECT COUNTRY,
       POP / AREA AS DENSITY
FROM COUNTRIES
WHERE DENSITY > 2000
ORDER BY DENSITY DESC

COUNTRY	DENSITY
Singapore	11702.29
Malta	3029.58
Maldives	2272.26
Bangladesh	2235.70
Bahrain	2148.97

نام‌های مستعار ستون در بند SELECT تعریف می‌شوند.
Alias را می‌توان در بند WHERE استفاده کرد.
Alias را می‌توان در بند ORDER BY استفاده کرد.
در برخی گویش‌ها AS اختیاری است.

خلاصهٔ نحو با Alias ستون
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	col [ AS alias ] list	LANGUAGE AS LANG, COUNTRY
SELECT	expr [ AS alias ] list	POP / AREA AS DENSITY
FROM	table	JOBS
WHERE	comparisons	AREA > 3000000
WHERE	expr oper value	POP / AREA > 300
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC

تمرین‌ها

کشور، جمعیت، نرخ باسوادی و تعداد افراد باسواد را برای همهٔ کشورهایی با بیش از ۱۰۰ میلیون باسواد نمایش دهید. ستون محاسبه‌شده را READERS بنامید و هر جا مناسب است از این Alias استفاده کنید. نتیجه ۷ ردیف دارد.
با استفاده از Alias ستون، کشور، جمعیت، GNP و GPP ــ مخفف Gross Personal Product و برابر با GNP * 1000000 / POP ــ را برای کشورهایی که GPP بیش از ۲۰٬۰۰۰ است نمایش دهید. بر اساس GPP نزولی مرتب کنید. نتیجه ۹ ردیف دارد.

امتیاز اضافه

فرض کنید کشورهای جهان نیروهای نظامی خود را ۲۰ درصد کاهش دهند و صرفه‌جویی را به نیروگاه‌های هسته‌ای اختصاص دهند. میانگین هزینهٔ هر سرباز ۲۰٬۰۰۰ دلار است. هر کشور چقدر کمک می‌کند؟ ستون محاسبه‌شده را FISSION_FUND بنامید، بزرگ‌ترین مقدار را بالا قرار دهید و کشورهای با مشارکت کمتر از دو میلیارد را حذف کنید.

توابع آماری

تابع آماری (Statistical Function) برنامه‌ای داخلی است که یک پارامتر می‌پذیرد و یک مقدار خلاصه بازمی‌گرداند. پارامتر می‌تواند نام ستون یا یک عبارت باشد. پنج تابع آماری پشتیبانی‌شده توسط SQL استاندارد در جدول زیر آمده‌اند.

توابع آماری
FUNCTION	MEANING	EXAMPLE
COUNT( )	Count all rows	COUNT(*)
COUNT( )	Count non-null rows	COUNT(JOB)
COUNT( )	Count unique rows	COUNT(DISTINCT JOB)
SUM( )	Total value	SUM(POP)
MIN( )	Smallest value	MIN(POP)
MAX( )	Largest value	MAX(POP)
AVG( )	Average value	AVG(POP / AREA)

توابع آماری پارامتر می‌پذیرند

پارامترها یا نام ستون هستند یا عبارت. پارامتر همیشه باید داخل پرانتز قرار گیرد. فاصلهٔ داخل و خارج پرانتز اختیاری است و در نمونه‌ها فقط برای خوانایی نمایش داده شده است.

توابع آماری مقادیر خلاصه برمی‌گردانند

توابع SUM و AVG فقط با ستون‌هایی قابل استفاده‌اند که دارای مقادیر عددی هستند. COUNT، MIN و MAX را می‌توان با هر نوع ستون استفاده کرد. همهٔ توابع آماری روی یک ستون یا عبارت واحد عمل می‌کنند.

COUNT پارامترهای گوناگونی می‌پذیرد

COUNT(*) تعداد ردیف‌ها را برمی‌گرداند. COUNT(column) تعداد ردیف‌هایی را می‌شمارد که ستون مشخص‌شده مقدار non-null دارد. COUNT(DISTINCT column) تعداد مقادیر یکتای non-null ستون را می‌شمارد.

اگر بند SELECT فقط شامل توابع آماری باشد، SQL مجموع کلی پرس‌وجو را نمایش می‌دهد. جدول نتیجه یک ردیف و برای هر تابع آماری یک ستون دارد. برای تغییر نام ستون‌ها می‌توان از Alias استفاده کرد.

SELECT AVG( POP )
FROM COUNTRIES
WHERE LANGUAGE = 'ENGLISH'

AVG(POP)
16025475

SELECT MIN( POP ) AS LOWEST,
       MAX( POP ) AS HIGHEST
FROM COUNTRIES
WHERE LANGUAGE = 'ENGLISH'

LOWEST	HIGHEST
16661	263814032

در بند SELECT فقط توابع مشخص شده‌اند.
نتیجه فقط یک ردیف دارد.
برای هر تابع یک ستون در نتیجه وجود دارد.
می‌توان برای ستون‌ها Alias تعریف کرد.

خلاصهٔ نحو با توابع
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	col [ AS alias ] list	LANGUAGE AS LANG, COUNTRY
SELECT	expr [ AS alias ] list	POP / AREA AS DENSITY
SELECT	func [ AS alias ] list	MIN(POP) AS LOWEST
FROM	table	JOBS
WHERE	comparisons	AREA > 3000000
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC

خلاصهٔ توابع آماری
FUNCTION	MEANING	EXAMPLE
COUNT( )	Count all rows	COUNT(*)
COUNT( )	Count non-null rows	COUNT(JOB)
COUNT( )	Count unique rows	COUNT(DISTINCT JOB)
SUM( )	Total value	SUM(POP)
MIN( )	Smallest value	MIN(POP)
MAX( )	Largest value	MAX(POP)
AVG( )	Average value	AVG(POP / AREA)

تمرین‌ها

تعداد کل نیروها را در جدول ARMIES بیابید. باید 17,846,400 به دست آید.
حداقل، حداکثر و میانگین نرخ باسوادی فرانسوی‌زبانان را نمایش دهید. حداقل ۱۸٪، حداکثر ۱۰۰٪ و میانگین ۵۱٫۳۸٪ است.
در یک پرس‌وجو، تعداد کشورها ــ ۱۹۰ ــ و تعداد زبان‌های متمایز ــ ۷۹ ــ را پیدا کنید.

گروه‌بندی

اگر بند SELECT هم نام ستون و هم تابع داشته باشد، SQL زیرمجموع‌ها (Subtotals) را نمایش می‌دهد. ستون‌های مشخص‌شده باید در بند GROUP BY نیز فهرست شوند. SQL جدول را به گروه‌ها تقسیم می‌کند، برای هر گروه زیرمجموع را محاسبه می‌کند و یک ردیف برای هر گروه نمایش می‌دهد.

SELECT JOB,
       COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY JOB

JOB	TOTAL
B	28
E	113
M	36
R	14
S	28
T	13
W	99

SELECT JOB, COUNTRY,
       COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB, COUNTRY
ORDER BY JOB, COUNTRY

JOB	COUNTRY	TOTAL
B	England	1
B	France	1
B	Germany	2
B	USA	24
E	Austria	1
E	Belgium	1
E	Canada	5
E	England	14

ستون‌های گروه در بند SELECT مشخص می‌شوند.
ستون‌های گروه در بند GROUP BY تکرار می‌شوند.
GROUP BY پس از بندهای FROM و WHERE می‌آید.

خلاصهٔ نحو با GROUP BY
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	[ DISTINCT ] col [ AS alias ] list	JOB, COUNTRY
SELECT	func [ AS alias ] list	MIN(POP) AS LOWEST
FROM	table	JOBS
WHERE	comparisons	AREA > 3000000
GROUP BY	col list	JOB, COUNTRY
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC

تمرین‌ها

هر زبان و تعداد کل افرادی را که به آن زبان صحبت می‌کنند نمایش دهید. نتیجه را به Hebrew، Spanish، English و French محدود کنید.
حداقل، حداکثر و میانگین نرخ باسوادی زبان‌های بالا را فهرست کنید.
در یک پرس‌وجو تعداد مردان و زنان هر یک از کشورها ــ Canada و France ــ را محاسبه کنید. ابتدا بر اساس جنسیت درون کشور مرتب کنید.

امتیاز اضافه

تعداد افراد هر جنسیت را که هر شغل را انجام می‌دهند فهرست کنید. افراد با جنسیت ناشناخته را وارد نکنید. نتیجه را بر اساس جنسیت درون شغل مرتب کنید. جدول نتیجه باید ۱۲ ردیف داشته باشد.

توابع در بندهای دیگر

از آن‌جا که SQL نتیجه را پس از انجام همهٔ پردازش‌های دیگر مرتب می‌کند، تابع را می‌توان در بند ORDER BY مشخص کرد. تابع می‌تواند مستقیماً وارد شود یا از طریق Alias ستونی تعریف‌شده در بند SELECT به آن ارجاع داده شود.

SELECT JOB, COUNT(*)
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY COUNT(*) DESC

JOB	COUNT(*)
E	113
W	99
M	36
B	28
S	28
R	14
T	13

SELECT JOB, COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY TOTAL DESC

JOB	TOTAL
E	113
W	99
M	36
B	28
S	28
R	14
T	13

توابع را می‌توان در بند ORDER BY به کار برد.
توابع را می‌توان مستقیماً وارد کرد.
توابع را می‌توان از طریق Alias ستون ارجاع داد.

توابع را نمی‌توان در بند WHERE مشخص کرد، زیرا WHERE پیش از گروه‌بندی و اجرای تابع ارزیابی می‌شود. با این حال، توابع می‌توانند در بند HAVING ظاهر شوند؛ HAVING پس از انجام گروه‌بندی پردازش می‌شود.

SELECT JOB, COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
ORDER BY TOTAL DESC

JOB	TOTAL
E	113
W	99
M	36
B	28
S	28
R	14
T	13

SELECT JOB, COUNT(*) AS TOTAL
FROM PERSONS
WHERE GENDER = 'MALE'
GROUP BY JOB
HAVING TOTAL > 30
ORDER BY TOTAL DESC

JOB	TOTAL
E	113
W	99
M	36

بند HAVING پس از GROUP BY می‌آید.
HAVING شبیه WHERE است، با این تفاوت که...
HAVING پس از گروه‌بندی اجرا می‌شود.

خلاصهٔ نحو با HAVING
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	[ DISTINCT ] col [ AS alias ] list	LANGUAGE AS LANG, COUNTRY
SELECT	expr [ AS alias ] list	POP / AREA AS DENSITY
SELECT	func [ AS alias ] list	MIN(POP) AS LOWEST
FROM	table	JOBS
WHERE	comparisons	AREA > 3000000
GROUP BY	col list	JOB, COUNTRY
HAVING	comparisons with funcs	COUNT(*) > 30
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC
ORDER BY	func [ DESC ] list	COUNT(*) DESC

تمرین‌ها

تعداد نویسندگان هر کشور را بشمارید. بر اساس تعداد به‌صورت نزولی مرتب کنید. ۱۲ ردیف وجود دارد.
زبان‌هایی را فهرست کنید که کمتر از یک میلیون نفر به آن‌ها صحبت می‌کنند. ۱۱ ردیف وجود دارد.
حداقل، حداکثر و میانگین نرخ باسوادی هر زبانی را که حداقل آن با حداکثرش برابر نیست پیدا کنید. نتیجه ۱۳ ردیف دارد.

امتیاز اضافه

زبان، مجموع مساحت و مجموع جمعیت همهٔ زبان‌هایی را که در بیش از یک کشور صحبت می‌شوند نمایش دهید. بزرگ‌ترین مجموع مساحت را در بالا قرار دهید. ۱۳ ردیف وجود دارد.

عبارت‌های محاسباتی، نام مستعار ستون و توابع آماری SQL