کد شما باید روی PostgreSQL قابل اجرا باشد.
در این سوال، دیتاست مربوط به ورزشکاران و مربیان المپیک ۲۰۱۶ در اختیار شما قرار گرفته است.
جزئیات پایگاه‌داده
داده‌های اولیه را از این لینک دانلود کنید.
ایمپورت کردن داده‌های اولیه
از نصب بودن PostgreSQL روی سیستم خود اطمینان حاصل کنید.
سپس برای ایمپورت کردن داده‌های اولیه می‌توانید از یکی از دو روش زیر اقدام کنید:
۱- با استفاده CLI به راحتی دستور زیر را وارد کنید تا داده‌های اولیه ایمپورت شوند:
psql -U postgres -f /path/to/initial.sql
که در این دستور مسیر فایل initial.sql را به صورت مطلق یا نسبی می‌توانید آدرس‌دهی کنید.
۲- اگر GUI را ترجیح می‌دهید. پس از نصب دیتاگریپ و اتصال به PostgreSQL با یوزر postgres، باید روی دیتاسورس و کانکشن postgres راست کلیک کنید و از منوی SQL Scripts گزینه Run SQL Script را انتخاب کنید. سپس فایل initial.sql را پیدا و تایید کنید. در انتها روی Run کلیک کنید تا اسکریپت اجرا شود و داده‌ها وارد دیتابیس quera شوند.
توضیحات جداول دیتاست
این دیتاست شامل اطلاعات و نتایج حدود یازده هزار ورزشکار و مربیان آن‌ها در المپیک ریو است و ساختار جدول athletes مربوط به این دیتاست به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
integer |
شناسه‌ی منحصر به فرد ورزشکار |
name |
character varying(255) |
نام |
sex |
character(1) |
جنسیت ورزشکار ('M' یا 'F') |
weight |
double precision |
وزن |
team |
character varying(255) |
تیم یا کشور |
sport |
character varying(255) |
ورزش برای مثال کشتی یا Wrestling |
event |
character varying(255) |
منظور از رویداد عنوان دقیق رشته ورزشی است. برای مثال کشتی آزاد سنگین‌وزن مردان یا Wrestling Men's Heavyweight, Freestyle |
medal |
character varying(10) |
مدالی که ورزشکار کسب کرده (Gold، Silver، Bronze، یا NULL اگر مدالی نگرفته) |
و ساختار جدول coaches به شکل زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
integer |
شناسه‌ی منحصر به فرد مربی (کلید اصلی) |
name |
character varying(255) |
نام مربی |
athlete_id |
integer |
شناسه‌ی ورزشکار مرتبط (کلید خارجی به ستون id از جدول ورزشکاران) |
مطلوبات
کوئری‌هایی بنویسید که خروجی‌های مطلوب زیر را به‌دست آورد (توجه کنید که هر کوئری نمره‌ای جداگانه دارد و اگر کوئری یک قسمت را نتوانستید بنویسید، کوئری‌هایی که حل کردید را بفرستید و کوئری آن قسمت را خالی بگذارید):
- نام و تعداد مدال‌های سه ورزش برتر ایران، که در المپیک بیشترین مدال را آورده‌اند، به ترتیب نزولی ستون تعداد مدال بیابید.
توضیحات مربوط به کوئری اول
نام ستون تعداد مدال‌ها باید medal_count باشد و مقدار ستون team این ورزشکاران برابر با Iran است:
| sport | medal_count |
|---|
- نام و نوع مدالِ مدال‌آوران ایرانی حاضر در المپیک ۲۰۱۶ را به ترتیب مرغوبیت مدال و سپس صعودی نام به دست آورید.
توضیحات مربوط به کوئری دوم
منظور از مرغوبیت مدال یعنی در ابتدا سطرهای شامل مدال طلا، سپس نقره و در نهایت برنز در نتیجه ظاهر شوند. مقدار ستون team این ورزشکاران برابر با Iran است. در جدول زیر چون هر دو طلا گرفته‌اند بر اساس نام مرتب شده‌اند.
| name | medal | |
|---|---|---|
| 1 | Hassan Aliazam Yazdanicharati | Gold |
| 2 | Kianoush Rostami | Gold |
در جدول بالا اولین ستون از سمت چپ نمایان‌گر شماره ستون در نتایج مورد انتظار است و نیازی به نمایش آن نیست.
- نام ده مربی، تیم مربوطه، مجموع تعداد مدال‌های طلا، نقره و برنز و همچنین مجموع امتیازی که ورزشکاران تحت هدایت آن مربی توانسته‌اند در کشتی مردان به دست آورند را در ابتدا به ترتیب نزولی مجموع امتیاز و در صورت برابر بودن به ترتیب صعودی نام مربی به دست آورید.
توضیحات مربوط به کوئری سوم
فرمول مجموع امتیازات به این شکل است که اگر ورزشکار تحت هدایت مربی موفق به کسب مدال طلا شده باشد ۲۵ امتیاز، نقره ۲۰ و برنز ۱۵ امتیاز به آن مربی تعلق پیدا خواهد کرد. در جدول زیر نمونه‌ای از خروجی مطلوب را مشاهده می‌کنید:
| coach_name | team | gold_medal | silver_medal | bronze_medal | total_point | |
|---|---|---|---|---|---|---|
| 1 | Saypulla Absaidov | Azerbaijan | 0 | 2 | 3 | 85 |
| ... | ... | ... | ... | ... | ... | ... |
| 10 | Mohammad Bana | Iran | 0 | 0 | 2 | 30 |
در جدول بالا اولین ستون از سمت چپ نمایان‌گر شماره ستون در نتایج مورد انتظار است و نیازی به نمایش آن نیست.
- لیست ۲۲ تیم برتر که حداقل هفت ورزشکار زن آن‌ها موفق به کسب مدال شده‌اند، به همراه تعداد زنان و مردان مدال‌آور و نسبت تعداد زنان مدال‌آور به مردان مدال‌آور را نمایش دهید. نتایج بر حسب نزولی تعداد ورزشکاران زن، که مدال کسب کرده‌اند مرتب شود و در صورت تساوی، بر اساس نسبت زنان به مردان مدال‌آور به صورت نزولی مرتب گردد.
توضیحات مربوط به کوئری چهارم
اگر هیچ مردی در آن تیم موفق به کسب مدال نشده بود، از آن‌جا که تقسیم بر صفر معتبر نیست، باید مقدار NULL در ستون نسبت مدال‌آوران زن به مرد درج شود و برای این ستون باید مقدار نسبت تا حداکثر دو رقم اعشار محاسبه شود و مابقی ارقام در اعشار گرد شوند. همچنین دقت کنید نام ستون‌ها دقیقا با مثال زیر یکسان باشد.
نمونه خروجی مورد انتظار:
| team | female_medalists | male_medalists | female_to_male_ratio | |
|---|---|---|---|---|
| 1 | United States | 95 | 71 | 1.34 |
| 2 | Russia | 60 | 22 | 2.73 |
| ... | ... | ... | ... | ... |
| 21 | Bulgaria | 7 | 0 | |
| ... | ... | ... | ... | ... |
در جدول بالا اولین ستون از سمت چپ نمایان‌گر شماره ستون در نتایج مورد انتظار است و نیازی به نمایش آن نیست.
نکته: برای حل کامل سوال و دریافت امتیاز کوئری‌ها حتما توضیحات مربوط به هر کوئری را مطالعه بفرمایید.
آن‌چه باید آپلود کنید
پس از پیاده‌سازی کوئری‌ها، آن را در قالب یک فایل با پسوند .sql آپلود کنید.
کوئری‌های شما باید به صورت زیر باشد و هیچ گونه تغییری در کامنت‌ها ندهید.
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
-- Section4
Your fourth query here
ارسال پاسخ برای این سؤال