۶ مثال‌ سوال و جواب برای بهبود مهارت‌های SQL شما

منتشر‌شده در: towardsdatascience به تاریخ ۱۶ فوریه ۲۰۲۱
لینک منبع: 6 Query Examples to Boost Your SQL Skills

زبان SQL یک زبان برنامه‌نویسی است که برای مدیریت داده‌ها در یک پایگاه‌داده رابطه‌ای استفاده می‌شود. داده‌ها به شکل جدولی با سطرها و ستون‌های برچسب گذاری شده هستند. یک پایگاه‌داده رابطه‌ای معمولا شامل بسیاری از جداول است که با استفاده از ستون‌های به اشتراک گذاشته شده مرتبط هستند.

در این مقاله، ما بیش از ۶ مثال را برای پرس و جوی یک پایگاه‌داده رابطه‌ای بررسی خواهیم کرد. هدف مثال‌ها حل وظایف تجزیه و تحلیل داده‌ها و دستکاری شامل فیلترینگ، تجمع، مرتب‌سازی و پیوستن به جداول است.

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

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

• کلید اصلی، ستونی است که به طور منحصر به فرد هر ردیف را شناسایی می‌کند. این شبیه به شاخص یک پاندا است.
• کلید خارجی چیزی است که جدول را به جدول دیگر مرتبط می‌کند. کلید خارجی شامل کلید اصلی جدول دیگری است. به عنوان مثال، «item _ id» در جدول خرید یک کلید خارجی است. آن سطرها را از کلید اصلی در جدول آیتم ذخیره می‌کند.

پس از معرفی مختصر، بیایید با مثال‌ها شروع کنیم.

توجه: سیستم‌های مدیریت پایگاه‌داده رابطه‌ای زیادی وجود دارند (برای مثال، MySQL، PostgreSQL، SQL Server). اگرچه نحو SQL برای همه یک‌سان است، ممکن است تفاوت‌های کوچکی وجود داشته باشد. ما در این مقاله از MySQL استفاده خواهیم کرد.

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

مثال 1:

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

mysql> select * from item limit 3;
+---------+-------------+-------+----------+
| item_id | description | price | store_id |
+---------+-------------+-------+----------+
| 1 | apple | 2.45 | 1 |
| 2 | banana | 3.45 | 1 |
| 3 | cereal | 4.20 | 2 |
+---------+-------------+-------+----------+

mysql> select store_id, avg(price)
-> from item
-> group by store_id;+----------+------------+
| store_id | avg(price) |
+----------+------------+
| 1 | 1.833333 |
| 2 | 3.820000 |
| 3 | 3.650000 |
+----------+------------+

قیمت میانگین اقلام برای هر فروشگاه تابع تجمعی که در این مورد «avg» است در هنگام انتخاب ستون اعمال می‌شود.

مثال ۲:

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

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

mysql> select concat(customer.f_name," ", customer.l_name) as name,
-> count(purchase.cust_id) as number_of_purchases
-> from customer
-> join purchase
-> on customer.cust_id = purchase.cust_id
-> group by name;+--------------+---------------------+
| name | number_of_purchases |
+--------------+---------------------+
| Adam Gelvin | 2 |
| Alisha T. | 1 |
| Elaine Smith | 2 |
| Jane Doe | 2 |
| John Doe | 2 |
| Robert Sam | 1 |
+--------------+---------------------+

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

مثال ۳:

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

ما تاریخ، item_id، و item_qty را از جدول خرید انتخاب می‌کنیم. به منظور محاسبه مقدار، ما به قیمت یک آیتم نیاز داریم که از طریق جدول آیتم قابل‌دسترسی باشد.

mysql> select p.date, sum(p.item_qty * i.price) as total_amount
-> from purchase p
-> join item i
-> on p.item_id = i.item_id
-> group by p.date;+------------+--------------+
| date | total_amount |
+------------+--------------+
| 2020-05-10 | 52.95 |
| 2020-05-11 | 8.70 |
+------------+--------------+

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

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

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

mysql> select distinct(date) from purchase;+------------+
| date |
+------------+
| 2020-05-10 |
| 2020-05-11 |
+------------+

مثال ۴:

بیایید چند مثال کمی پیچیده‌تر انجام دهیم. حالتی را در نظر بگیرید که در آن ما باید مقدار خرید کل را برای زنان و مردان محاسبه کنیم.

این وظایف شامل پیوستن به سه جدول است.

• جنسیت از جدول مشتری
• مقدار آیتم از جدول خرید
• قیمت آیتم از جدول آیتم

mysql> select c.gender, sum(p.item_qty * i.price) as total_amount
-> from customer c
-> join purchase p on c.cust_id = p.cust_id
-> join item i on p.item_id = i.item_id
-> group by c.gender;+--------+--------------+
| gender | total_amount |
+--------+--------------+
| F | 29.15 |
| M | 32.50 |
+--------+--------------+

عملیات اتصال چندگانه را می توان در یک پرس و جو به سبک زنجیره مانند ترکیب کرد.

مثال ۵:

در نظر داشته باشید که ما می‌خواهیم مشتریان بستنی‌فروشی را پیدا کنیم. پس از پیوستن به جداول خرید و اقلام، ما باید یک فیلتر را با استفاده از جایی که بند در آن قرار دارد اعمال کنیم.

mysql> select p.cust_id, i.description
-> from purchase p
-> join item i
-> on p.item_id = i.item_id
-> where i.description = "icecream";+---------+-------------+
| cust_id | description |
+---------+-------------+
| 4 | icecream |
| 3 | icecream |
| 5 | icecream |
+---------+-------------+

مثال ۶:

در جدول آیتم، یک فروشگاه مرتبط با هر آیتم وجود دارد. ما می‌خواهیم مدیر فروشگاه را پیدا کنیم که با «غلات» در ارتباط است.

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

mysql> select manager from store
-> where store_id = (
-> select store_id from item
-> where description = "cereal"
-> );+---------+
| manager |
+---------+
| Max |
+---------+

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

نتیجه‌گیری

ما ۶ مثال برای پرس و جوی یک پایگاه‌داده رابطه‌ای انجام داده‌ایم. یکی از ویژگی‌های کلیدی پایگاه‌داده رابطه‌ای داشتن جداول زیادی برای ذخیره داده‌ها است.

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

به منظور بازیابی موثر داده‌های مورد نیاز، ما باید قادر به نوشتن پرسوجوهای پیچیده باشیم. بهترین راه برای راحت شدن با نوشتن چنین پرسوجوهایی تمرین کردن است.

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

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