یه دونه ر داره!


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

ابتدا داده‌ها را که مربوط به لیست افراد است را از این لینک دانلود کنید.

جدول 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 آپلود کنید.

-- Section1
    Your first query here
-- Section2
    Your second query here
-- Section3
    Your third query here
SQL

تیم سنجش مهارت پایا


کد شما باید روی MySQL قابل اجرا باشد. برای چک کردن سینتکس کد SQL خود می‌توانید از این وبسایت کمک بگیرید.

معین که اخیراً در شرکت کوئرا استخدام شده است، با سرمایه‌گذاری در لاستیک و در پی افزایش شدید قیمت آن اوضاع مالی خوبی پیدا کرده و قرار است یک تیم فوتبال در فصل بعد تشکیل دهد که در فینال لیگ قهرمانان آسیا به مصاف النصر برود. اکنون او در تحلیل بازیکن‌ها از شما کمک می‌خواهد، چون قرار است بهترین بازیکنان جهان را به خدمت بگیرد!

جزئیات پروژه🔗

داده‌های سؤال را از این لینک دانلود کنید.

برای این کار، پایگاه داده‌ای شامل اطلاعات بازیکن‌ها و تعداد گل‌هایی که زده‌اند به شما داده شده که شامل دو جدول 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 قرار دهید و بدون زیپ کردن، آن را ارسال کنید:

-- Section1
   your first query here
-- Section2
   your second query here
SQL

تحلیل بهینه‌ی سفارش‌ها


کوئری‌های شما باید روی MySQL قابل اجرا باشند.

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

جزئیات پروژه🔗

داده‌های سؤال را از این لینک دانلود کنید.

جدولی با نام orders برای نگه‌داری اطلاعات سفارش‌ها موجود است که ساختار آن به شرح زیر است:

نام ستون نوع تعریف
id BIGINT(20) شناسه‌ی سفارش
user_id BIGINT(20) شناسه‌ی کاربر سفارش‌دهنده
total DECIMAL(10,2) مبلغ سفارش
created_at DATETIME زمان ثبت سفارش

مطلوبات🔗

کوئری‌های SQL خواسته‌شده از شما، موارد زیر است:

  1. کوئری ساخت ایندکس جهت بهینه‌سازی حداکثری سرعت محاسبه‌ی مجموع مبلغ سفارش‌ها در سال ۲۰۲۰:
SELECT SUM(total)
FROM orders
WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59'
SQL
  1. کوئری ساخت ایندکس جهت بهینه‌سازی حداکثری سرعت محاسبه‌ی مجموع مبلغ سفارش‌های کاربری با شناسه‌ی ۳۴۵ در سال ۲۰۲۰:
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
SQL

نکته: تعداد کاربران بسیار بیشتر از حداکثر تعداد سفارش‌های یک کاربر است.

  1. مجموع مبلغ سفارش‌ها به ازای هر روز، به‌ترتیب صعودی تاریخ؛ نتیجه به‌ترتیب شامل ستون تاریخ و ستون مجموع مبلغ سفارش‌ها باشد. تاریخ باید به فرمت Y-m-d باشد. همچنین، تاریخ‌هایی بین تاریخ‌های موجود در جدول که در آن‌ها سفارشی ثبت نشده است نیز باید در نتیجه موجود باشند. نام ستون‌های خروجی اهمیت ندارد.

نکته: هر کوئری نمره‌ای جداگانه دارد و اگر کوئری یک قسمت را نتوانستید بزنید، کوئری‌هایی که حل کردید را بفرستید و قسمت آن کوئری را خالی بگذارید.

آن‌چه باید آپلود کنید🔗

پس از طراحی کوئری‌ها، کد خود را در قالب زیر در یک فایل با پسوند .sql آپلود کنید.

-- Section1
    Your first query here
-- Section2
    Your second query here
-- Section3
    Your third query here
SQL

انبارگردانی


کوئری‌های شما باید روی آخرین نسخه‌ی MySQL قابل اجرا باشند.

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

از آن‌جایی که محمد فرصت نوشتن کوئری‌های مدنظرش را ندارد، از شما می‌خواهیم تا این کوئری‌ها را برایش بنویسید.

جزئیات پروژه🔗

داده‌های اولیه برای تست نمونه را از این لینک دانلود کنید.

ساختار جداول به‌شرح زیر است:

جدول 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 آپلود کنید.

-- Section1
    Your first query here
-- Section2
    Your second query here
-- Section3
    Your third query here
SQL

شرکت فروش آنلاین


کد شما باید روی MySQL قابل اجرا باشد. برای چک کردن سینتکس کد SQL خود می‌توانید از این وبسایت کمک بگیرید.

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

جزئیات پروژه🔗

داده‌های سؤال را از این لینک دانلود کنید.

ساختار جداول به‌شرح زیر است:

جدول 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 قرار دهید و بدون زیپ کردن، آن را ارسال کنید:

-- Section1
   Your first query here
-- Section2
   Your second query here
-- Section3
   Your third query here
SQL