**کوئری‌های شما باید روی _MySQL_ قابل اجرا باشند.** برای چک کردن سینتکس کد *SQL* خود می‌توانید از **[این وبسایت](https://www.eversql.com/sql-syntax-check-validator/)** کمک بگیرید. در این سوال بخشی از پایگاه‌داده یک پیام‌رسان در اختیار شما قرار داده شده‌ است، این بخش از پایگاه‌داده شامل اطلاعات حدود ۱۰۰۰ کاربر و ۲۵۰۰ گروه می‌باشد. # جزئیات پایگاه‌داده داده‌های اولیه برای **تست نهایی** را از [این لینک](/contest/assignments/68130/download_problem_initial_project/234190/) دانلود کنید. <details class="yellow"> <summary> **توضیحات در مورد داده‌های اولیه** </summary> در فایل *Messenger.zip* فایلی به اسم initial.sql وجود دارد. با اجرای این فایل، همه جداول و سطرهایی که برای **تست‌ نهایی** مورد استفاده قرار می‌گیرد در پایگاه‌داده‌ای به `Messenger`، ایجاد می‌شوند. </details> <details class="grey"> <summary> **توضیحات جداول پایگاه‌داده** </summary> ساختار جداول به‌شرح زیر است: ۱.**جدول `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)`|آیا کاربر ادمین گروه هست (مقدار ۱) یا خیر (مقدار ۰)| </details> # مطلوبات🔗 کوئری‌هایی بنویسید که مطلوبات خواسته شده در هر قسمت را انجام دهد: ۱. همان‌طور که در توضیحات جداول گفته شده در جدول کاربران (`users`) ستونی به نام `is_suspend` وجود دارد که نشان می‌دهد یک کاربر تعلیق شده است یا خیر، اگر به این ستون پایگاه‌داده دقت کنید، متوجه می‌شوید که این مقدار برای تمامی رکورد‌ها **صفر** است. حال شما باید کوئری بنویسید که این ستون را برای کاربرانی که ۵ بار یا بیشتر گزارش (ستون `reported_num`) شده‌اند، برابر **یک** قرار دهد. ۲. لیستی از کاربران و تعداد بلاک شدن آن‌ها توسط کاربران دیگر. </details> <details class="blue"> <summary> *توضیحات مورد مطلوب دوم* </summary> نام هر کاربر را در ستونی به نام `blocked_user_name` و تعداد کاربرانی که این کاربر را بلاک کرده اند را در ستونی با نام `block_count` نمایش دهید. همچنین توجه داشته باشید خروجی شما باید بر اساس تعداد بلاک‌ها (ستون `block_count`) به صورت **نزولی** مرتب شود و در صورتی که این مقدار برابر بود بر اساس نام کاربر به صورت **صعودی** مرتب شود. 3 سطر اول خروجی شما باید به شکل زیر باشد. | blocked_user_name | block_count | | :---: | :---: | | Sandra Garcia | 8 | | Bryan Waters | 7 | | Danielle Henry | 7 | </details> ۳. لیستی از کاربران و کسانی که با آن‌ها در گروهی مشترک هستند و نام آن گروه. </details> <details class="blue"> <summary> *توضیحات مورد مطلوب سوم* </summary> نام کاربر اول را در ستونی به نام `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 | </details> ۴. پس از اعمال کوئری اول ستون `is_suspend` دارای مقادیر معنی دار است، حال ساختاری را بر روی پایگاه‌داده خود پیاده کنید که هنگامی که تعداد گزارش شدن یک کاربر (ستون `reported_num`) به ۵ می‌رسد (به‌روزرسانی می‌شود) کاربر تعلیق شود (مقدار ستون `is_suspend` **یک** شود) و بر عکس، اگر کاربری تعداد گزارش شدنش کم‌تر از ۵ شد از تعلیق در بیاید (مقدار ستون `is_suspend` **صفر** شود). <details class="red"> <summary> توجه </summary> به روش ارسال پاسخ قسمت چهارم توجه داشته باشید. همچنین توجه داشته باشید برای دریافت امتیاز این قسمت باید امتیاز قسمت اول (مطلوب اول همین سوال) را دریافت کرده باشید. </details> # **روش ارسال پاسخ** در یک فایل با نام `code.sql` کد خود را قرار دهید و آن را فشرده (`zip` ) کنید و در سایت بارگذاری نمایید. کد شما باید به صورت زیر باشد. (نام فایل zip مهم نیست). ```sql -- Section1 your first query here -- Section2 your second query here -- Section3 your third query here -- Section4 Delimiter $$ your fourth query here $$ ```

حل سؤال در بانک سؤالات