کوئری‌های شما باید روی MySQL قابل اجرا باشند. برای چک کردن سینتکس کد SQL خود می‌توانید از این وبسایت کمک بگیرید.
در این سوال بخشی از پایگاه‌داده یک پیام‌رسان در اختیار شما قرار داده شده است، این بخش از پایگاه‌داده شامل اطلاعات حدود ۱۰۰۰ کاربر و ۲۵۰۰ گروه می‌باشد.
جزئیات پایگاه‌داده
داده‌های اولیه برای تست نهایی را از این لینک دانلود کنید.
توضیحات در مورد داده‌های اولیه
در فایل Messenger.zip فایلی به اسم initial.sql وجود دارد.
با اجرای این فایل، همه جداول و سطرهایی که برای تست نهایی مورد استفاده قرار می‌گیرد در پایگاه‌داده‌ای به Messenger، ایجاد می‌شوند.
توضیحات جداول پایگاه‌داده
ساختار جداول به‌شرح زیر است:
۱.جدول users: از این جدول برای نگه‌داری اطلاعات کاربران استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
INT |
شناسه کاربر (کلید اصلی) |
phone |
VARCHAR(20) |
شماره موبایل کاربر (یکتا) |
email |
VARCHAR(256) |
ایمیل کاربر (یکتا) |
name |
VARCHAR(128) |
نام و نام‌خانوادگی کاربر |
password |
VARCHAR(128) |
رمز عبور کاربر (هَش شده) |
reported_num |
int |
تعداد دفعاتی که کاربر گزارش (report) شده |
created_at |
datetime |
تاریخ عضویت |
updated_at |
datetime |
تاریخ به‌روز رسانی حساب |
is_suspend |
VARCHAR(1) |
آیا کاربر تعلیق شده‌است (مقدار یک) یا خیر (مقدار صفر) |
۲.جدول block_list: از این جدول برای نگه‌داری اطلاعات افراد بلاک (مسدود) شده استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
INT |
شناسه‌ی جدول (کلید اصلی) |
user_id |
INT |
شناسه کاربر بلاک کننده |
blocked_user_id |
INT |
شناسه کاربر بلاک شده |
۳.جدول group_conversations: از این جدول برای نگه‌داری اطلاعات گروه‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
INT |
شناسه گروه (کلید اصلی) |
string_id |
VARCHAR(20) |
آدرس (url) گروه (یکتا) |
onwer_id |
INT |
شناسه کاربر مالک گروه |
name |
VARCHAR(50) |
نام گروه |
۴.جدول group_conversation_users: از این جدول برای نگه‌داری اطلاعات کاربران هر گروه استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
INT |
شناسه جدول (کلید اصلی) |
group_id |
INT |
شناسه گروه |
user_id |
INT |
شناسه کاربر حاضر در گروه |
is_admin |
VARCHAR(1) |
آیا کاربر ادمین گروه هست (مقدار ۱) یا خیر (مقدار ۰) |
مطلوبات🔗
کوئری‌هایی بنویسید که مطلوبات خواسته شده در هر قسمت را انجام دهد:
۱. همان‌طور که در توضیحات جداول گفته شده در جدول کاربران (users) ستونی به نام is_suspend وجود دارد که نشان می‌دهد یک کاربر تعلیق شده است یا خیر، اگر به این ستون پایگاه‌داده دقت کنید، متوجه می‌شوید که این مقدار برای تمامی رکورد‌ها صفر است. حال شما باید کوئری بنویسید که این ستون را برای کاربرانی که ۵ بار یا بیشتر گزارش (ستون reported_num) شده‌اند، برابر یک قرار دهد.
۲. لیستی از کاربران و تعداد بلاک شدن آن‌ها توسط کاربران دیگر.
توضیحات مورد مطلوب دوم
نام هر کاربر را در ستونی به نام blocked_user_name و تعداد کاربرانی که این کاربر را بلاک کرده اند را در ستونی با نام block_count نمایش دهید. همچنین توجه داشته باشید خروجی شما باید بر اساس تعداد بلاک‌ها (ستون block_count) به صورت نزولی مرتب شود و در صورتی که این مقدار برابر بود بر اساس نام کاربر به صورت صعودی مرتب شود.
3 سطر اول خروجی شما باید به شکل زیر باشد.
| blocked_user_name | block_count |
|---|---|
| Sandra Garcia | 8 |
| Bryan Waters | 7 |
| Danielle Henry | 7 |
۳. لیستی از کاربران و کسانی که با آن‌ها در گروهی مشترک هستند و نام آن گروه.
توضیحات مورد مطلوب سوم
نام کاربر اول را در ستونی به نام first_user و نام کاربر دوم را در ستونی با نام second_user و نام گروهی که کاربران در آن مشترک هستند را در ستونی با نام mutual_group قرار دهید و به ترتیب ابتدا کابر اول سپس کاربر دوم و در نهایت براساس نام گروه به صورت صعودی مرتب شود.
توجه داشته باشید شناسه کاربر اول باید همیشه از کاربر دوم بیشتر باشد و همچنین اجرای این کوئری ممکن است کمی زمان‌گیر باشد.
3 سطر اول خروجی شما باید به شکل زیر باشد.
| first_user | second_user | mutual_group |
|---|---|---|
| Aaron Foster | Anna Little | Thompson, Sullivan and Turner |
| Aaron Foster | Ashley Scott | Anderson-Hill |
| Aaron Foster | Benjamin Porter | Carrillo-Parker |
۴. پس از اعمال کوئری اول ستون is_suspend دارای مقادیر معنی دار است، حال ساختاری را بر روی پایگاه‌داده خود پیاده کنید که هنگامی که تعداد گزارش شدن یک کاربر (ستون reported_num) به ۵ می‌رسد (به‌روزرسانی می‌شود) کاربر تعلیق شود (مقدار ستون is_suspend یک شود) و بر عکس، اگر کاربری تعداد گزارش شدنش کم‌تر از ۵ شد از تعلیق در بیاید (مقدار ستون is_suspend صفر شود).
توجه
به روش ارسال پاسخ قسمت چهارم توجه داشته باشید. همچنین توجه داشته باشید برای دریافت امتیاز این قسمت باید امتیاز قسمت اول (مطلوب اول همین سوال) را دریافت کرده باشید.
روش ارسال پاسخ
در یک فایل با نام code.sql کد خود را قرار دهید و آن را فشرده (zip ) کنید و در سایت بارگذاری نمایید. کد شما باید به صورت زیر باشد. (نام فایل zip مهم نیست).
-- Section1
your first query here
-- Section2
your second query here
-- Section3
your third query here
-- Section4
Delimiter $$
your fourth query here
$$
ارسال پاسخ برای این سؤال