على الرغم من أن تجميع استعلامات SQL ليس هو الشيء الأكثر إثارة للاهتمام لعلماء البيانات ، فإن الفهم الجيد لـ SQL مهم للغاية لأي شخص يريد النجاح في أي نشاط يتعلق بمعالجة البيانات. النقطة هنا هي أن SQL ليست فقط SELECT، FROMو WHERE. كلما عرف المزيد من تراكيب SQL ، كان من الأسهل عليه إنشاء طلبات للحصول على كل شيء قد يحتاجه من قواعد البيانات. يقول كاتب المقال ، الذي ننشر ترجمته اليوم ، أنه يهدف إلى حل مشكلتين:
- استكشاف الآليات التي تتجاوز معرفة SQL الأساسية.
- النظر في العديد من المهام العملية للعمل مع SQL.
تتناول هذه المقالة 5 أسئلة SQL من Leetcode. تمثل المهام العملية التي كثيرا ما تصادف في المقابلات.السؤال الأول: المرتبة الثانية من حيث الراتب
اكتب استعلام SQL للحصول من الجدول على معلومات راتب الموظف ( Employee) إدخال يحتوي على ثاني أعلى راتب.على سبيل المثال ، يجب إرجاع مثل هذا الاستعلام الذي تم تنفيذه للجدول أدناه 200. إذا كان الجدول لا يحتوي على قيمة أقل من أعلى راتب ، يجب أن يعود الطلب null.+
| Id | Salary |
+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+
A القرار أ: الاستخدام IFNULLوOFFSET
فيما يلي الآليات الرئيسية التي سيتم استخدامها في هذا الحل للمشكلة:IFNULL(expression, alt): ترجع هذه الدالة وسيطتها expressionإذا لم تكن متساوية null. خلاف ذلك ، يتم إرجاع الحجة alt. سنستخدم هذه الوظيفة للعودة nullإذا لم يكن الجدول يحتوي على القيمة المطلوبة.OFFSET: يستخدم هذا العامل مع تعبير ORDER BYلتجاهل nالأسطر الأولى . هذا مفيد لنا لأننا مهتمون بالصف الثاني من النتيجة (أي ثاني أكبر راتب ، البيانات الموجودة في الجدول).
هنا طلب جاهز:SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
), null) as SecondHighestSalary
FROM Employee
LIMIT 1
▍ الحل ب: الاستخدام MAX
يستخدم الاستعلام أدناه الوظيفة MAX. هنا ، يتم تحديد أعلى قيمة للراتب ، ولا تساوي الحد الأقصى للراتب المستلم طوال الجدول. ونتيجة لذلك ، نحصل على ما نحتاج إليه - ثاني أكبر راتب.SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)
السؤال الثاني: عناوين البريد الإلكتروني المكررة
اكتب استعلام SQL الذي يكتشف Personجميع عناوين البريد الإلكتروني المكررة في الجدول .+
| Id | Email |
+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+
A القرار أ: COUNTفي الاستعلام الفرعي
أولاً ، نقوم بإنشاء استعلام فرعي يتم فيه تحديد تكرار حدوث كل عنوان في الجدول. ثم يتم تصفية النتيجة التي أرجعها الاستعلام الفرعي باستخدام التعليمات WHERE count > 1. سيقوم الاستعلام بإرجاع معلومات حول العناوين الموجودة في الجدول المصدر أكثر من مرة.SELECT Email
FROM (
SELECT Email, count(Email) AS count
FROM Person
GROUP BY Email
) as email_count
WHERE count > 1
▍ الحل ب: التعبير HAVING
HAVING: هذا تعبير يسمح لك باستخدام تعليمة WHEREمع تعبير GROUP BY.
SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1
السؤال رقم 3: ارتفاع درجة الحرارة
اكتب استعلام SQL الذي يجد في الجدول Weatherجميع التواريخ (معرفات التاريخ) عندما تكون درجة الحرارة أعلى من درجة الحرارة في التواريخ التي تسبقها. أي أننا مهتمون بالتواريخ التي تكون فيها درجة حرارة "اليوم" أعلى من درجة حرارة "الأمس".+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+
▍ الحل: DATEDIFF
DATEDIFF: تحسب هذه الوظيفة الفرق بين تاريخين. يتم استخدامه لتوفير مقارنة بين درجات حرارة "اليوم" و "أمس" بدقة.
إذا قمنا بصياغة الاستعلام التالي بلغة عادية ، فقد اتضح أنه يعبر عن الفكرة التالية: نحتاج إلى اختيار معرفات بحيث تكون درجة الحرارة المقابلة للتواريخ التي تمثلها أكبر من درجة الحرارة لتواريخ "الأمس" فيما يتعلق بها.SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1
السؤال الرابع: أعلى راتب في الوحدة
Employeeيخزن الجدول معلومات حول موظفي الشركة. يحتوي كل إدخال في هذا الجدول على معلومات حول معرف ( Id) الموظف واسمه ( Name) والمرتب ( Salary) وقسم الشركة التي يعمل بها ( Department).+
| Id | Name | Salary | DepartmentId |
+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+
Departmentيحتوي الجدول على معلومات حول أقسام الشركة.+
| Id | Name |
+
| 1 | IT |
| 2 | Sales |
+
اكتب استعلام SQL الذي يتم العثور عليه في كل قسم من إدارات الموظفين مع الحد الأقصى للراتب. على سبيل المثال ، بالنسبة للجداول أعلاه ، يجب أن يعرض استعلام مماثل النتائج التي يمثلها الجدول التالي (لا يهم ترتيب الصفوف في الجدول):+
| Department | Employee | Salary |
+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+
olution الحل: فريق IN
INيسمح لك الأمر بتعيين WHEREالشروط في التعليمات التي تتوافق مع استخدام عدة أوامر OR. على سبيل المثال ، البنيات التالية متطابقة:WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).
هنا نريد الحصول على جدول يحتوي على اسم القسم ( Department) واسم الموظف ( Employee) والمرتب ( Salary). للقيام بذلك ، نقوم بإنشاء جدول يحتوي على معلومات حول معرف الوحدة ( DepartmentID) والحد الأقصى للراتب لهذه الوحدة. ثم نحن الجمع بين اثنين على طاولة حالة بموجبها دخول في خريف الجدول الناتج إلا إذا DepartmentIDو Salaryشكلوا سابقا في الجدول.SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary)
IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
السؤال رقم 5: زرع الطلاب
مريم معلمة في المدرسة الثانوية. لديها جدول seatيخزن أسماء الطلاب ومعلومات حول أماكنهم في الفصل الدراسي. القيمة idفي هذا الجدول تتزايد باستمرار. تريد مريم تبادل الطلاب الجيران.فيما يلي جدول بالموضع الأولي للطلاب:+
| id | student |
+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+
إليك ما يجب أن يحدث بعد زرع الطلاب المجاورين:+
| id | student |
+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+
اكتب طلبًا سيسمح للمعلم بحل المشكلة أعلاه.يرجى ملاحظة أنه إذا كان عدد الطلاب فرديًا ، فلن تحتاج إلى نقل آخر طالب إلى أي مكان.▍ الحل: استخدام عامل WHEN
CASE WHEN THENيمكن اعتبار بناء SQL عامل تشغيل ifفي البرمجة.في حالتنا ، يتم WHENاستخدام عامل التشغيل الأول للتحقق مما إذا تم تعيين معرّف فردي للصف الأخير في الجدول. إذا كان الأمر كذلك ، فإن الخط لا يخضع للتغيير. العامل الثاني WHENمسؤول عن إضافة 1 إلى كل معرف فردي (على سبيل المثال ، 1 ، 3 ، 5 يتحول إلى 2 ، 4 ، 6) وطرح 1 من كل معرف زوجي (2 ، 4 ، 6 يتحول إلى 1 ، 3 ، 5).SELECT
CASE
WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
WHEN id%2 = 1 THEN id + 1
ELSE id - 1
END AS id, student
FROM seat
ORDER BY id
ملخص
لقد درسنا العديد من مهام SQL ، وناقشنا على طول الطريق بعض الأدوات المتقدمة التي يمكن استخدامها لتجميع استعلامات SQL. نأمل أن يكون ما تعلمته اليوم مفيدًا خلال المقابلات في SQL وأن يثبت فائدته في العمل اليومي.PS لدينا في السوق هناك صورة عامل الميناء مع SQL Server Express أو التي يتم تثبيتها في نقرة واحدة. يمكنك التحقق من تشغيل الحاويات على VPS. جميع العملاء الجدد لديهم 3 أيام مجانية للاختبار.القراء الأعزاء! ما الذي يمكن أن تنصح به لأولئك الذين يريدون إتقان فن إنشاء استعلامات SQL؟