اخیرا داده‌هایی در اختیار علی قرار گرفته است و از او خواسته شده تا داده‌های اضافی را از آن پاک کند. او که بعد از دیدن این داده‌ها به‌شدت عصبانی شده، قرار است موارد خواسته شده را از این داده‌ها پاک کند، در پاک کردن داده‌ها به علی کمک کنید.
ابتدا داده‌ها را که مربوط به لیست افراد است را از [این لینک](/contest/assignments/50382/download_problem_initial_project/170876/) دانلود کنید.
جدول `users` مرتبط با این داده‌ها به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`BIGINT`|ردیف|
|`level`|`ENUM('admin', 'user')`|سطح کاربر|
|`name`|`VARCHAR(300)`|نام|
|`family`|`VARCHAR(300)`|نام خانوادگی|
|`birth_date`|`DATETIME`|تاریخ تولد|
|`salary`|`INT`|حقوق|
دستوراتی بنویسید که داده‌های زیر را از مجموعه داده‌ها پاک کند:
1. اطلاعات تمامی کاربرانی که در نام خانوادگی‌شان نه `m` وجود داشته باشد و نه `d`
2. اطلاعات تمامی کاربرانی با نام خانوادگی `mohammadi` یا حقوقی برابر با 7356، 9701، 2885، 7414 یا 3801
3. اطلاعات تمامی کاربرانی با نام خانوادگی `booazar` یا سال تولدی بین 1995 تا 2000 (از ابتدای سال ۱۹۹۵ تا پایان سال ۲۰۰۰)
# آن‌چه باید آپلود کنید
پس از طراحی کوئری‌ها، آن‌ها را در قالب زیر در یک فایل با پسوند `.sql` آپلود کنید.
```sql
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
```
یه دونه ر داره!
**کد شما باید روی MySQL قابل اجرا باشد.** برای چک کردن سینتکس کد *SQL* خود می‌توانید از **[این وبسایت](https://www.eversql.com/sql-syntax-check-validator/)** کمک بگیرید.
---
معین که اخیراً در شرکت کوئرا استخدام شده است، با سرمایه‌گذاری در لاستیک و در پی افزایش شدید قیمت آن اوضاع مالی خوبی پیدا کرده و قرار است یک تیم فوتبال در فصل بعد تشکیل دهد که در فینال لیگ قهرمانان آسیا به مصاف النصر برود. اکنون او در تحلیل بازیکن‌ها از شما کمک می‌خواهد، چون قرار است بهترین بازیکنان جهان را به خدمت بگیرد!
# جزئیات پروژه
داده‌های سؤال را از [این لینک](/contest/assignments/50382/download_problem_initial_project/171762/) دانلود کنید.
برای این کار، پایگاه داده‌ای شامل اطلاعات بازیکن‌ها و تعداد گل‌هایی که زده‌اند به شما داده شده که شامل دو جدول `players` و `teams` است.
در جدول `players` تعداد گل‌ها و پاس گل‌های بازیکنان مانند جدول زیر تهیه شده است:
| team_id | assists | goals | id | name |
| ------- | ------- | ----- | ---- | ------------------- |
| 4 | 10 | 11 | 1 | Lionel Messi |
| 4 | 2 | 15 | 2 | Kylian Mbappé |
| 13 | 2 | 0 | 3 | Leonardo Spinazzola |
| 16 | 3 | 3 | 4 | Marco Reus |
| 2 | 3 | 11 | 5 | Karim Benzema |
| 1 | 5 | 15 | 6 | Robert Lewandowski |
در جدول `teams` نیز اطلاعات هر تیم نوشته شده است:
| name | id |
| ------------------------ | ---- |
| FC Barcelona | 1 |
| Real Madrid CF | 2 |
| Paris Saint-Germain F.C. | 3 |
# مطلوبات
کوئری‌های زیر را طوری بنویسید که خروجی خواسته شده به‌دست بیاید.
1. معین در ابتدا می‌خواهد بداند که سه تا از بازیکنانی که بیشترین گل را به ثمر رسانده‌اند چه کسانی هستند، چند گل زده‌اند و چند پاس گل داده‌اند. در نتیجه، کوئری *SQL* بنویسید که سه تا از برترین گل‌زنان را به همراه نام بازیکنان، تعداد گل‌های زده و پاس گل‌های داده‌شده گزارش کند. سطرهای خروجی باید به‌ترتیب نزولی تعداد گل‌ها باشد. اگر تعداد گل‌ها مساوی بود، سطرها باید به‌ترتیب نزولی مجموع تعداد پاس گل باشند. به ترتیب و نام ستون‌ها دقت کنید (ابتدا `name`، سپس `goals` و نهایتاً `assists`).
2. حال، معین می‌خواهد ببیند که بهترین گل‌زن هر تیم کیست و چند گل را به برای تیمش به ثمر رسانده است و چند تا پاس گل داده است. حال با نوشتن یک کوئری *SQL* به معین کمک کنید که بتواند افرادی که در هر تیم بیشترین گل را زده‌اند شناسایی کند. برای مرتب‌سازی سطرهای خروجی، به‌ترتیب نزولی نام تیم عمل کنید.
در نوشتن کوئری، به نام ستون‌های جدول خروجی و ترتیب آن‌ها (اولین ستون با نام `team` که نشان‌دهنده‌ی نام تیم است، دومین ستون با نام `player` که نشان‌دهنده‌ی نام بازیکن است، سومین ستون `goals` و آخرین ستون `assists`) دقت کنید.
# آن‌چه باید آپلود کنید
کد خود را در حتما در قالب زیر، در یک فایل با پسوند `.sql` قرار دهید و بدون زیپ کردن، آن را ارسال کنید:
```sql
-- Section1
your first query here
-- Section2
your second query here
```
تیم سنجش مهارت پایا
**کوئری‌های شما باید روی _MySQL_ قابل اجرا باشند.**
---
یکی از تیم‌های فنی گروه اسنپ قصد دارد برخی از کوئری‌های خود که مربوط به بخش سفارش‌ها هستند را با استفاده از ایندکس‌ها سریع‌تر کند. به آن‌ها در این کار کمک کنید.
# جزئیات پروژه
داده‌های سؤال را از [این لینک](/contest/assignments/50382/download_problem_initial_project/170878/) دانلود کنید.
جدولی با نام `orders` برای نگه‌داری اطلاعات سفارش‌ها موجود است که ساختار آن به شرح زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`BIGINT(20)`|شناسه‌ی سفارش|
|`user_id`|`BIGINT(20)`|شناسه‌ی کاربر سفارش‌دهنده|
|`total`|`DECIMAL(10,2)`|مبلغ سفارش|
|`created_at`|`DATETIME`|زمان ثبت سفارش|
# مطلوبات
کوئری‌های *SQL* خواسته‌شده از شما، موارد زیر است:
1. کوئری ساخت ایندکس جهت بهینه‌سازی حداکثری سرعت محاسبه‌ی مجموع مبلغ سفارش‌ها در سال ۲۰۲۰:
```sql
SELECT SUM(total)
FROM orders
WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59'
```
2. کوئری ساخت ایندکس جهت بهینه‌سازی حداکثری سرعت محاسبه‌ی مجموع مبلغ سفارش‌های کاربری با شناسه‌ی ۳۴۵ در سال ۲۰۲۰:
```sql
SELECT SUM(total)
FROM orders
WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59'
AND user_id = 345
```
**نکته:** تعداد کاربران بسیار بیشتر از حداکثر تعداد سفارش‌های یک کاربر است.
3. مجموع مبلغ سفارش‌ها به ازای هر روز، به‌ترتیب صعودی تاریخ؛ نتیجه به‌ترتیب شامل ستون تاریخ و ستون مجموع مبلغ سفارش‌ها باشد. تاریخ باید به فرمت `Y-m-d` باشد. همچنین، **تاریخ‌هایی بین تاریخ‌های موجود در جدول که در آن‌ها سفارشی ثبت نشده است نیز باید در نتیجه موجود باشند**. نام ستون‌های خروجی اهمیت ندارد.
**نکته:** هر کوئری نمره‌ای جداگانه دارد و اگر کوئری یک قسمت را نتوانستید بزنید، کوئری‌هایی که حل کردید را بفرستید و قسمت آن کوئری را خالی بگذارید.
# آن‌چه باید آپلود کنید
پس از طراحی کوئری‌ها، کد خود را در قالب زیر در یک فایل با پسوند `.sql` آپلود کنید.
```sql
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
```
تحلیل بهینه‌ی سفارش‌ها
**کوئری‌های شما باید روی آخرین نسخه‌ی _MySQL_ قابل اجرا باشند.**
---
محمد به‌تازگی به تیم مهندسی دیجی‌کال‍ا پیوسته. او در یکی از تسک‌هایش لازم دارد تا کوئری‌های مختلفی برای کار با داده‌هایی که با آن‌ها سروکار دارد بنویسد. داده‌هایی که محمد با آن‌ها سروکار دارد مربوط به محصولات و سفارش‌های مربوط به آن‌ها است. او یک *denormalization* روی داده‌ها اعمال کرده، به این‌صورت که علاوه بر اطلاعات سفارش‌های محصولات، مجموع فروش هر محصول نیز در جدول اطلاعات محصولات وجود دارد.
از آن‌جایی که محمد فرصت نوشتن کوئری‌های مدنظرش را ندارد، از شما می‌خواهیم تا این کوئری‌ها را برایش بنویسید.
# جزئیات پروژه
داده‌های اولیه برای تست نمونه را از [این لینک](/contest/assignments/50382/download_problem_initial_project/170877/) دانلود کنید.
ساختار جداول به‌شرح زیر است:
**جدول `products`**: از این جدول برای نگه‌داری اطلاعات محصولات استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`BIGINT(20)`|شناسه‌ی محصول|
|`name`|`VARCHAR(255)`|نام محصول|
|`description`|`TEXT`|توضیحات محصول|
|`price`|`DECIMAL(15, 2)`|قیمت محصول|
|`total_profit`|`DECIMAL(15, 2)`|مجموع مبلغ فروش محصول|
|`created_at`|`TIMESTAMP`|زمان درج محصول|
**جدول `orders`**: از این جدول برای نگه‌داری لیست سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`BIGINT(20)`|شناسه‌ی سفارش|
|`user_id`|`BIGINT(20)`|شناسه‌ی کاربر سفارش‌دهنده|
|`created_at`|`TIMESTAMP`|زمان ایجاد سفارش|
**جدول `order_details`**: از این جدول برای نگه‌داری اطلاعات اقلام موجود در سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`BIGINT(20)`|شناسه‌ی اطلاعات محصول در سفارش|
|`order_id`|`BIGINT(20)`|شناسه‌ی سفارش|
|`product_id`|`BIGINT(20)`|شناسه‌ی محصول|
|`quantity`|`INT(11)`|تعداد سفارش‌داده‌شده‌ی محصول|
**جدول `delivery_centers`**: از این جدول برای نگه‌داری اطلاعات مراکز ارسال استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`BIGINT(20)`|شناسه‌ی مرکز ارسال|
|`name`|`VARCHAR(255)`|نام مرکز ارسال|
**جدول `deliveries`**: از این جدول برای نگه‌داری اطلاعات تحویل سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`order_id`|`BIGINT(20)`|شناسه‌ی سفارش|
|`delivery_center_id`|`BIGINT(20)`|شناسه‌ی مرکز ارسال|
|`received_at`|`TIMESTAMP`|زمان دریافت محصولات سفارش از انبار|
|`delivered_at`|`TIMESTAMP`|زمان تحویل سفارش به مشتری (در صورتی که سفارش هنوز تحویل داده نشده باشد، مقدار آن برابر با `NULL` خواهد بود.)|
توجه داشته باشید که جدول کاربران صرفاً جهت سادگی در سؤال نیامده است. نیازی به اطلاعات چنین جدولی نیست.
# مطلوبات
کوئری‌های زیر را طوری بنویسید که خروجی خواسته‌شده به‌دست آید:
1. محمد اکنون متوجه شده که یک باگ در بک‌اند برنامه‌اش وجود داشته و مقدار ستون `total_profit` در جدول `products` لزوماً مطابق با اطلاعات موجود در جداول `orders` و `order_details` نیست (در داده‌ها *inconsistency* وجود دارد). کوئری‌ای بنویسید که مقدار ستون `total_profit` جدول `products` را طبق مقادیر موجود در جداول `orders` و `order_details` به‌روزرسانی کند تا *inconsistency* در صورت وجود رفع شود.
2. کوئری‌ای بنویسید که شناسه‌ی ۵ مرکز ارسال برتر از نظر *چابک* بودن را برگرداند. یک مرکز ارسال در صورتی *چابک* است که میانگین اختلاف زمانی بین دریافت محصول از انبار تا تحویل آن به مشتری در سفارش‌های تحویل‌داده‌شده‌اش کمتر از سایر مراکز ارسال باشد. نتایج را به‌ترتیب نزولی میزان *چابک* بودن مرتب کنید. **تضمین می‌شود** که میزان *چابک* بودن مراکز ارسال با یکدیگر متفاوت است. نام ستون خروجی اهمیتی ندارد.
3. کوئری‌ای بنویسید که شناسه‌ی محصولاتی که تا ۷ روز پس از انتشارشان کمتر از ۱۰ فروش داشتند و حدقل ۱ فروش داشتند را به‌ترتیب صعودی شناسه‌ی محصول دریافت کند. نام ستون خروجی اهمیتی ندارد.
# نکات
+ کوئری‌های شما باید روی آخرین نسخه‌ی *MySQL* قابل اجرا باشند.
+ کوئری هر بخش باید تنها شامل یک *statement* باشد.
+ هر کوئری امتیاز جداگانه دارد و اگر کوئری یک قسمت را نتوانستید بنویسید، کوئری‌هایی که حل کردید را بفرستید و قسمت آن کوئری را خالی بگذارید.
+ در تست نمونه‌ی این سؤال، فقط کوئری بخش اول تست می‌شود.
# آن‌چه باید آپلود کنید
پس از طراحی کوئری‌ها، آن‌ها را در قالب زیر در یک فایل با پسوند `.sql` آپلود کنید.
```sql
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
```
انبارگردانی
**کد شما باید روی MySQL قابل اجرا باشد.** برای چک کردن سینتکس کد *SQL* خود می‌توانید از **[این وبسایت](https://www.eversql.com/sql-syntax-check-validator/)** کمک بگیرید.
---
در این سؤال، پایگاه داده‌ی یک شرکت فروش آنلاین که وظیفه‌ی فروش محصولات از برندهای مختلف را دارد، در اختیار شما قرار گرفته است.
# جزئیات پروژه
داده‌های سؤال را از [این لینک](/contest/assignments/50382/download_problem_initial_project/171627/) دانلود کنید.
ساختار جداول به‌شرح زیر است:
**جدول `customers`**: از این جدول برای نگه‌داری اطلاعات مشتریان استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`customerNumber`|`INT`|شناسه‌ی مشتری|
|`customerName`|`VARCHAR(50)`|نام مشتری|
|`contactLastName`|`VARCHAR(50)`|نام مخاطب|
|`contactFirstName`|`VARCHAR(50)`|فامیلی مخاطب|
|`phone`|`VARCHAR(50)`|شماره تلفن مشتری|
|`addressLine1`|`VARCHAR(50)`|خط اول آدرس|
|`addressLine2`|`VARCHAR(50)`|خط دوم آدرس|
|`city`|`VARCHAR(50)`|شهر|
|`state`|`VARCHAR(50)`|ایالت|
|`postalCode`|`VARCHAR(15)`|کد پستی|
|`country`|`VARCHAR(50)`|کشور|
|`salesRepEmployeeNumber`|`INT`|شماره کارمند فروش مربوطه|
|`creditLimit`|`DECIMAL(10,2)`|محدودیت بودجه|
**جدول `employees`**: از این جدول برای نگه‌داری اطلاعات کارمندان استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`employeeNumber`|`INT`|شناسه‌ی کارمند|
|`lastName`|`VARCHAR(50)`|فامیلی کارمند|
|`firstName`|`VARCHAR(50)`|نام کارمند|
|`extension`|`VARCHAR(10)`|اضافه|
|`email`|`VARCHAR(100)`|ایمیل|
|`officeCode`|`VARCHAR(10)`|کد دفتر|
|`reportsTo`|`INT`|گزارش میدهد به کارمند|
|`jobTitle`|`VARCHAR(50)`|شغل|
**جدول `offices`**: از این جدول برای نگه‌داری اطلاعات دفترها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`officeCode`|`VARCHAR(10)`|کد دفتر|
|`city`|`VARCHAR(50)`|شهر|
|`phone`|`VARCHAR(50)`|تلفن|
|`addressLine1`|`VARCHAR(50)`|خط اول آدرس|
|`addressLine2`|`VARCHAR(50)`|خط دوم آدرس |
|`state`|`VARCHAR(50)`|ایالت|
|`country`|`VARCHAR(50)`|کشور|
|`postalCode`|`VARCHAR(15)`|کد پستی|
|`territory`|`VARCHAR(10)`|منطقه|
**جدول `orderdetails`**: از این جدول برای نگه‌داری جزئیات سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`orderNumber`|`INT`|شناسه‌ی سفارش|
|`productCode`|`VARCHAR(15)`|شناسه‌ی محصول|
|`quantityOrdered`|`int`|تعداد سفارش|
|`priceEach`|`DECIMAL(10,2)`|قیمت به ازای هر یک عدد محصول|
|`orderLineNumber`|`SMALLINT`|شناسه خط تولید|
**جدول `orders`**: از این جدول برای نگه‌داری سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`orderNumber`|`INT`|شناسه‌ی سفارش|
|`orderDate`|`DATE`|تاریخ سفارش|
|`requiredDate`|`DATE`|تاریخ نیاز|
|`shippedDate`|`DATE`|تاریخ ارسال|
|`status`|`VARCHAR(15)`|وضعیت ارسال|
|`comments`|`TEXT`|نظرات ارسال|
|`customerNumber`|`INT`|شناسه مشتری|
**جدول `payments`**: از این جدول برای نگه‌داری جزئیات پرداخت‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`customerNumber`|`INT`|شناسه‌ی مشتری|
|`checkNumber`|`VARCHAR(15)`|شماره چک|
|`paymentDate`|`DATE`|تاریخ پرداخت|
|`amount`|`DECIMAL(10,2)`|امقدار پرداخت|
**جدول `productlines`**: از این جدول برای نگه‌داری خط تولید ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`productLine`|`VARCHAR(15)`|شناسه‌ی خط تولید|
|`textDescription`|`VARCHAR(4000)`|توضیحات|
|`htmlDescription`|`MEDIUMTEXT`|لینک توضیحات|
|`image`|`MEDIUMBLOB`|عکس|
**جدول `products`**: از این جدول برای نگه‌داری اطلاعات محصولات استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`productCode`|`VARCHAR(15)`|شناسه‌ی محصول|
|`productName`|`VARCHAR(70)`|نام محصول|
|`productLine`|`VARCHAR(50)`|شناسه خط تولید|
|`productScale`|`VARCHAR(10)`|اسکیل محصول|
|`productVendor`|`VARCHAR(50)`|برند محصول|
|`productDescription`|`TEXT`|توضیحات محصول|
|`quantityInStock`|`SMALLINT`|تعداد موجود|
|`buyPrice`|`DECIMAL(10,2)`|قیمت خرید|
|`MSRP`|`DECIMAL(10,2)`|قیمت فروش|
# مطلوبات
کوئری‌های زیر را طوری بنویسید که خروجی خواسته‌شده به‌دست بیاید:
1. لیست کارمندان با بیشترین مشتری: شناسه کارمندان را در یک ستون با نام `employeeNumber` و اسم و فامیل کارمندان را با یک *space* به هم بچسبانید و در یک ستون با نام `Name` و تعداد مشتری‌های آن کارمند را در یک ستون با نام `CustomersCount` قرار دهید و به ترتیب نزولی بر اساس تعداد مشتریان مرتب کنید. اگر تعداد مشتریان دو کارمند یکسان بود، آن سطرها را به‌ترتیب صعودی شناسه‌ی کارمند مرتب کنید.
2. رتبه‌بندی سفارش‌ها براساس هر مشتری: شناسه‌ی ۵ مشتری‌ای که مجموع مبلغ سفارش‌های‌شان بیشتر از بقیه است را به‌ترتیب نزولی مجموع مبلغ سفارش‌ها در یک ستون با نام `customerNumber` برگردانید. **تضمین می‌شود** که مجموع مبالغ سفارش‌های این مشتریان با یکدیگر متفاوت است.
3. رتبه‌بندی کالاها بر اساس ارزش‌شان (سود خالص) در خط تولیدشان: در ستون اول با نام `productline` شناسه خط تولید بیاید و در ستون دوم، نام کالا در ستونی با نام `productName` بیاید و در ستون سوم (با نام `net_profit`) سود خالصی که کالا میدهد یعنی «قیمت فروش منهای قیمت خرید»، تعداد موجود در انبار را در ستونی با نام `quantityInStock` بریزید و در ستون آخر با نام `net_profit_rank`، رتبه‌ی هر محصول بر اساس ارزشش در هر خط تولید را بریزید. رتبه‌ی محصولات به گونه‌ای باشد که محصولی که ارزشش بیشتر است، عدد رتبه‌اش کمتر باشد. **تضمین می‌شود** که سود خالص محصولات با یکدیگر متفاوت است. سطرهای خروجی باید به‌ترتیب صعودی شناسه‌ی محصول باشند.
**نکته:** برای بخش سوم دقت کنید که حتما `productline`ها در هر دو جدول `productline` و `products` موجود باشد.
# آن‌چه باید آپلود کنید
کد خود را در حتما در قالب زیر، در یک فایل با پسوند `.sql` قرار دهید و بدون زیپ کردن، آن را ارسال کنید:
```sql
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
```