عملگرهای پیشرفته SQL

این فصل عملگرهای LIKE، AND، BETWEEN، OR، IN و IS NULL را همراه با تقدم ارزیابی و نقیض بررسی می‌کند.

عملگر LIKE

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

SELECT NAME, COUNTRY
FROM PERSONS
WHERE NAME LIKE 'Z%'

SELECT NAME, COUNTRY
FROM PERSONS
WHERE NAME LIKE 'EINST__N'

SELECT NAME, COUNTRY
FROM PERSONS
WHERE NAME LIKE '%ST__N%'

• LIKE مقادیری را می‌یابد که با یک الگو منطبق‌اند.
• علامت درصد (%) نمایندهٔ صفر یا چند نویسه است.
• زیرخط (_) نمایندهٔ یک نویسه است.

تمرین‌ها

1. همهٔ کشورهایی را انتخاب کنید که عبارت guinea در نامشان وجود دارد. باید ۴ ردیف بیابید.
2. همهٔ ستون‌ها را برای افرادی نمایش دهید که حرف z دومین نویسهٔ نامشان است. ۲ نفر هستند.
3. همهٔ ستون‌های افراد متولد ۱۵ ژوئیه را نمایش دهید. ۶ نفر هستند.

امتیاز اضافه

1. همهٔ افرادی را که در نامشان apostrophe دارند بیابید. ۳ نفر هستند.
2. فهرستی از ادیانی بسازید که واژهٔ orthodox در نامشان وجود دارد. فهرست را مرتب کنید و مطمئن شوید ردیف تکراری ندارد. نتیجه ۱۰ ردیف است.

عملگر AND

عملگر AND برای ترکیب دو مقایسه و ساختن یک مقایسهٔ مرکب استفاده می‌شود. کلیدواژهٔ AND بین دو مقایسه قرار می‌گیرد. برای True شدن مقایسهٔ مرکب، هر دو مقایسه باید True باشند.

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME LIKE 'A%'
AND BDATE >= '1900-01-01'

SELECT COUNTRY, GNP
FROM COUNTRIES
WHERE GNP >= 1000
AND GNP <= 2000

• AND دو مقایسه را ترکیب می‌کند.
• AND بین دو مقایسه قرار می‌گیرد.
• هر دو مقایسه باید True باشند.

تمرین‌ها

1. همهٔ ستون‌ها را برای دانشمندان آلمانی فهرست کنید. باید ۷ دانشمند پیدا شود.
2. همهٔ ستون‌های کشورهایی را نمایش دهید که GNP آن‌ها کمتر از سه میلیارد و نرخ باسوادی کمتر از ۴۰ درصد است. به یاد داشته باشید GNP در میلیون دلار ذخیره شده است. بر اساس GNP مرتب کنید. نتیجه ۷ ردیف دارد.
3. نام کشور و نرخ باسوادی کشورهایی را نمایش دهید که باسوادی بین ۵۵ و ۶۰ درصد است. ۷ ردیف وجود دارد.

امتیاز اضافه

1. همهٔ ستون‌های انگلیسی‌هایی را که در قرن شانزدهم به دنیا آمده‌اند بر اساس نام مرتب کنید. نتیجه ۴ ردیف دارد.
2. همهٔ ستون‌های هشت ارتشی را نمایش دهید که بیش از ۱۰۰٬۰۰۰ سرباز، کمتر از ۱٬۰۰۰ تانک، کمتر از ۱٬۰۰۰ کشتی و کمتر از ۱٬۰۰۰ هواپیما دارند.

عملگر BETWEEN

برخی مقایسه‌های مرکب با AND را می‌توان با عملگر BETWEEN راحت‌تر بیان کرد. BETWEEN مقدار هر ستون را با یک بازه مقایسه می‌کند. بازه همیشه هر دو نقطهٔ انتهایی را شامل می‌شود.

SELECT COUNTRY, LITERACY
FROM COUNTRIES
WHERE LITERACY >= 55
AND LITERACY <= 60
ORDER BY LITERACY

SELECT COUNTRY, LITERACY
FROM COUNTRIES
WHERE LITERACY BETWEEN 55 AND 60
ORDER BY LITERACY

• BETWEEN مقدار هر ستون را با یک بازه مقایسه می‌کند.
• بازه هر دو نقطهٔ انتهایی را شامل می‌شود.
• مقدار دوم باید بزرگ‌تر از مقدار اول باشد.

تمرین‌ها

1. با استفاده از BETWEEN، همهٔ ستون‌های کشورهایی را فهرست کنید که مساحتشان بزرگ‌تر یا مساوی ۵ و کوچک‌تر یا مساوی ۷۵ مایل مربع است. ۵ ردیف وجود دارد.
2. همهٔ ستون‌های کشورهایی را انتخاب کنید که جمعیتشان بین ۱۰۰٬۰۰۰ و ۲۰۰٬۰۰۰ نفر است. ۶ ردیف وجود دارد.
3. همهٔ ستون‌های ارتش‌هایی را انتخاب کنید که تجهیزاتشان بیش از ۳۰۰ اما کمتر از ۴۰۰ هواپیما دارد. ۹ ارتش وجود دارد.

امتیاز اضافه

1. همهٔ ستون‌های ارتش‌هایی را انتخاب کنید که دست‌کم ۳۰۰٬۰۰۰ سرباز و بودجه‌ای بین ۱۰ تا ۱۰۰ میلیارد دلار دارند. به یاد داشته باشید BUDGET در میلیون دلار است. ۴ ارتش وجود دارد.

عملگر OR

عملگر OR برای ترکیب دو مقایسه و ساختن یک مقایسهٔ مرکب به کار می‌رود. OR بین دو مقایسه قرار می‌گیرد. برای True شدن مقایسهٔ مرکب، یکی از مقایسه‌ها یا هر دو باید True باشند.

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME = 'LUTHER'
OR NAME = 'CALVIN'

SELECT COUNTRY, LANGUAGE
FROM COUNTRIES
WHERE COUNTRY = 'GHANA'
OR COUNTRY = 'USA'
OR COUNTRY = 'FIJI'

• OR دو مقایسه را ترکیب می‌کند.
• OR بین دو مقایسه قرار می‌گیرد.
• یک یا هر دو مقایسه باید True باشند.

تمرین‌ها

1. همهٔ ستون‌های جدول ARMIES را برای Israel و Iraq نمایش دهید.
2. همهٔ ستون‌های افرادی را نمایش دهید که در یکی از تاریخ‌های 1835-10-19 یا 1917-02-06 متولد شده‌اند. ۴ نفر هستند... یا هستند؟
3. نام‌ها و تاریخ تولدهای Poe، Hugo و Dahl را فهرست کنید.

امتیاز اضافه

1. چند نفر در پایگاه داده در قرن سیزدهم یا پانزدهم به دنیا آمده‌اند؟

عملگر IN

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

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME = 'POE'
OR NAME = 'HUGO'
OR NAME = 'DAHL'

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME IN ('POE', 'HUGO', 'DAHL')

• IN مقدار هر ستون را با یک فهرست مقایسه می‌کند.
• فهرست داخل پرانتز قرار می‌گیرد.
• مقادیر فهرست با ویرگول جدا می‌شوند.

تمرین‌ها

1. همهٔ ستون‌ها را برای Einstein، Galilei و Newton با استفاده از IN نمایش دهید.
2. همهٔ ستون‌های کشورهایی را نمایش دهید که نرخ باسوادی آن‌ها ۲۰، ۴۰ یا ۶۰ درصد است. ۵ کشور وجود دارد.
3. نام و کشور همهٔ دانشمندان پایگاه داده را که از Germany، Austria یا Italy هستند فهرست کنید. نتیجه ۱۱ ردیف دارد.

امتیاز اضافه

1. همهٔ افراد اهل Germany، Austria یا Italy را که نویسنده، سرگرمی‌ساز یا مدیر کسب‌وکار هستند پیدا کنید. ۱۱ نفر هستند. شناسه‌های رسمی شغل در جدول JOBS قرار دارند.

عملگر IS NULL

مقدار Null ورودیِ مفقود در یک ستون است. Null یعنی «ناشناخته» یا «قابل اعمال نیست». Null نه Blank است و نه Zero؛ دو Null نیز الزاماً با هم برابر نیستند و نمی‌توان با Null محاسبات عددی انجام داد. عملگر IS NULL ردیف‌هایی را پیدا می‌کند که مقدار Null دارند.

SELECT COUNTRY, POP
FROM COUNTRIES
WHERE POP IS NULL

SELECT NAME, BDATE
FROM PERSONS
WHERE COUNTRY = 'IRAN'
AND BDATE IS NULL

• Nullها مقادیر مفقود هستند.
• Null با Blank یکسان نیست.
• Null با Zero یکسان نیست.
• IS NULL مقادیر Null را پیدا می‌کند.

تمرین‌ها

1. همهٔ ستون‌های کشورهایی را نمایش دهید که GNP آن‌ها ناشناخته است. یک چنین کشوری وجود دارد.
2. همهٔ ستون‌های سرگرمی‌سازانی را نمایش دهید که جنسیتشان Null است. ۲ مورد وجود دارد.
3. همهٔ ستون‌های اسرائیلی‌هایی را انتخاب کنید که تاریخ تولدشان ناشناخته است. ۹ نفر هستند.

امتیاز اضافه

1. چند کشور مساحت کمتر از ۱۰ مایل مربع و GNP بیش از ۲۵۰ میلیون دارند؟ چند کشور مساحت کمتر از ۱۰ مایل مربع و GNP کمتر یا مساوی ۲۵۰ میلیون دارند؟ با توجه به این نتایج، حدس می‌زنید چند کشور مساحت کمتر از ۱۰ مایل مربع داشته باشند؟

تقدم و نقیض

پرانتز برای تعیین تقدم (Precedence) ــ یعنی ترتیب ارزیابی مقایسه‌ها ــ استفاده می‌شود. SQL ابتدا مقایسه‌های داخل پرانتز را ارزیابی می‌کند. کلیدواژهٔ NOT برای نقیض یا معکوس‌کردن نتیجهٔ یک مقایسه استفاده می‌شود.

SELECT JOB, NAME
FROM PERSONS
WHERE COUNTRY = 'ITALY'
AND (JOB = 'S' OR JOB = 'W')
ORDER BY JOB, NAME

SELECT JOB, NAME
FROM PERSONS
WHERE COUNTRY = 'ITALY'
AND NOT (JOB = 'S' OR JOB = 'W')
ORDER BY JOB, NAME

• مقایسه‌های داخل پرانتز نخست ارزیابی می‌شوند.
• NOT در جلوی یک مقایسه قرار می‌گیرد.
• NOT نتیجهٔ مقایسه را معکوس می‌کند.

تمرین‌ها

1. همهٔ ستون‌ها را برای آلمانی‌هایی نمایش دهید که یا الهی‌دان‌اند یا مدیر کسب‌وکار. نتیجه را بر اساس نام مرتب کنید. ۳ ردیف وجود دارد.
2. پرس‌وجوی قبلی را تغییر دهید تا همهٔ آلمانی‌هایی را بیابد که مدیر کسب‌وکار یا الهی‌دان نیستند. نتیجه ۱۱ ردیف دارد.

امتیاز اضافه

1. چند ارتش، به‌جز USA و Russia، بودجهٔ نظامی بیش از ۳۰ میلیارد دلار دارند؟ تعداد آن‌ها ...