کد شما باید روی PostgreSQL قابل اجرا باشد.
در این سوال، بخشی از پایگاه داده مربوط به مسابقات فوتبال اروپا در اختیار شما قرار گرفته است.
جزئیات پایگاه‌داده
داده‌های اولیه برای تست نهایی را از این لینک دانلود کنید.
توضیحات در مورد داده‌های اولیه
در فایل football.zip فایلی به اسم initial.sql وجود دارد.
ابتدا پایگاه‌داده‌ای با نام football در سیستم خود را بسازید و با اجرای این فایل برروی این پایگاه‌داده، همه جداول و سطرهایی که برای تست نهایی مورد استفاده قرار می‌گیرد در سیستم شما ایجاد می‌شود.
توضیحات جداول دیتابیس
ساختار جداول به‌شرح زیر است:
جدول players: از این جدول برای نگه‌داری اطلاعات بازیکن‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
player_id |
integer |
شناسه‌ی بازیکن |
current_club_id |
integer |
شناسه‌ی باشگاه فعلی بازیکن |
player_code |
character varying(64) |
کد بازیکن (نام کامل بازیکن) |
country_of_birth |
character varying(32) |
کشور تولد بازیکن |
city_of_birth |
character varying(64) |
شهر تولد بازیکن |
country_of_citizenship |
character varying(32) |
کشور ملیت بازیکن |
date_of_birth |
date |
تاریخ تولد بازیکن |
sub_position |
character varying(32) |
تخصص دوم بازیکن |
position |
character varying(16) |
تخصص اول بازیکن |
foot |
character varying(8) |
پای تخصصی بازیکن |
height_in_cm |
integer |
قد بازی کن به سانتی متر |
contract_expiration_date |
date |
تاریخ انقضای قرارداد بازیکن |
جدول clubs: از این جدول برای نگه‌داری اطلاعات باشگاه‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
club_id |
integer |
شناسه‌ی باشگاه |
name |
character varying(64) |
نام باشگاه |
domestic_competition_id |
character varying(4) |
لیگ باشگاه |
squad_size |
integer |
اندازه تیم |
foreigners_number |
integer |
تعداد افراد خارجی تیم |
national_team_players |
integer |
تعداد بازیکن‌های تیم ملی |
stadium_name |
character varying(64) |
نام استادیوم اختصاصی باشگاه |
stadium_seats |
integer |
کشور ملیت بازیکن |
net_transfer_record |
character varying(16) |
ارزش خالص باشگاه |
جدول competitions: از این جدول برای نگه‌داری اطلاعات مسابقات استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
competition_id |
character varying(4) |
شناسه مسابقات |
name |
character varying(64) |
نام مسابقات |
type |
character varying(32) |
نوع مسابقات |
country_name |
character varying(16) |
کشور مسابقات |
جدول games: از این جدول برای نگه‌داری اطلاعات بازی‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
game_id |
integer |
شناسه‌ی بازی |
competition_id |
character varying(4) |
شناسه سری مسابقات |
season |
integer |
فصل برگذاری بازی |
date |
date |
تاریخ بازی |
home_club_id |
integer |
شناسه‌ی تیم (باشگاه) میزبان |
away_club_id |
integer |
شناسه‌ی تیم مهمان |
home_club_goals |
integer |
اتعداد گل تیم میزبان |
away_club_goals |
integer |
تعداد گل تیم مهمان |
stadium |
character varying(64) |
نام استادیوم بازی |
attendance |
integer |
تعداد تماشاگر‌ها |
جدول apearances: از این جدول برای نگه‌داری اطلاعات حضور بازیکن‌ها در بازی استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
appearance_id |
character varying(16) |
شناسه‌ی جدول |
game_id |
integer |
شناسه‌ی بازی |
player_id |
integer |
شناسه‌ی بازیکن |
yellow_cards |
integer |
تعداد کارت زردهای بازیکن |
red_cards |
integer |
تعداد کارت قرمز‌های بازیکن |
goals |
integer |
تعداد گل‌های بازیکن |
assists |
integer |
تعداد پاس گل‌های بازیکن |
minutes_played |
integer |
تعداد دقایقی که بازیکن بازی کرده‌است |
جدول game_events: از این جدول برای نگه‌داری اطلاعات اتفاق‌های مسابقه (گل، موقیت گل، پنالتی و ...)استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
game_event_id |
integer |
شناسه‌ی جدول |
game_id |
integer |
شناسه‌ی بازی |
minute |
integer |
زمان(دقیقه) اتفاق |
type |
character varying(16) |
نوع اتفاق |
player_id |
integer |
شناسه‌ی بازیکن اصلی اتفاق |
player_in_id |
integer |
شناسه‌ی بازیکن که از گل جلوگیری کرده است |
player_assist_id |
integer |
شناسه‌ی بازیکنی که پاس گل داده است |
مطلوبات
۱. لیستی از ارزش خالص باشگاه را به صورت عددی (integer).
توضیحات مربوط به کوئری اول
نام باشگاه را در ستونی با نام name و ارزش خالص هر باشگاه را در ستونی با نام total نمایش دهید. توجه کنید خروجی شما باید به ترتیب نزولی بر حسب ارزش خالص باشگاه و در صورت که این مقدار برابر بود بر اساس نام باشگاه به‌صورت صعودی مرتب شود.
توجه
دقت داشته‌باشید در صورتی که ارزش خالص تیم مقدار +-0 داشت آن را به صورت 0 نمایش دهید. همچنین نماد k معادل هزار و نماد m معادل میلیون می‌باشد.
3 سطر اول خروجی شما باید به شکل زیر باشد.
| name | total |
|---|---|
| Villarreal Club de Fútbol S.A.D. | 99400000 |
| Brighton and Hove Albion Football Club | 86400000 |
| Verona Hellas Football Club | 84300000 |
۲. مجموع تعداد کارت‌های زرد و قرمز هر بازیکن در هر سری مسابقات را نمایش دهید.
توضیحات مربوط به کوئری دوم
نام یا همان کد بازیکن را در ستونی به نام player_name و نام سری مسابقات را در ستونی با نام competition_name ،تعداد کارت زردهای بازیکن در آن سری مسابقات را در ستونی با نام total_yellow_cards و تعداد کارت زردهای بازیکن در آن سری مسابقات را در ستونی با نام total_red_cards قرار دهید. ستون‌ها را ابتدا برحسب نام بازیکن به‌صورت صعودی , سپس برحسب تعداد کارت‌های زرد و سپس قرمز، به‌صورت نزولی و در نهایت براساس نام سری مسابقات به‌صورت صعودی مرتب کنید.
توجه
هنگامی که مجموع کارت‌های زرد و مجموع کارت‌های زرد قرمز در سری مسابقاتی برای بازیکنی صفر باشد (بازیکن هیچ کارتی دریافت نکرده باشد)، را نمایش ندهید.
3 سطر اول خروجی شما باید به شکل زیر باشد.
| player_name | competition_name | total_yellow_cards | total_red_cards |
|---|---|---|---|
| aaron-appindangoye | super-lig | 6 | 0 |
| aaron-appindangoye | europa-league | 1 | 0 |
| aaron-boupendza | super-lig | 7 | 0 |
۳. سری مسابقات را بر حسب مجموع تعداد اتفاق‌های آن (تعداد گل‌ها، موقیت‌های گل، کارت‌ها و ... ) رتبه بندی کنید.
توضیحات مربوط به کوئری سوم
رتبه سری مسابقات بر اساس مجموع تعداد اتفاق‌های آن (پر حادثه‌ترین رتبه اول) را در ستونی با نام ranking و نام سری مسابقات را در ستونی با نام name و تعداد اتفاق‌های در آن سری مسابقات را در ستونی با نام events قرار دهید. ستون‌ها را ابتدا برحسب رتبه، سپس براساس نام سری مسابقات به‌صورت صعودی مرتب کنید.
توجه
اگر رتبه دو سری مسابقات با بیشترین تعداد حوادث، یکسان بود، هردو رتبه یک می‌شوند و سری مسابقات بعدی رتبه‌اش دو می‌شود، این قانون برای تمامی رتبه‌ها صادق است.
3 سطر آخر خروجی شما باید به شکل زیر باشد.
| ranking | name | events |
|---|---|---|
| 39 | johan-cruijff-schaal | 47 |
| 40 | trophee-des-champions | 45 |
| 41 | ukrainian-super-cup | 33 |
۴.لیستی از بازیی‌ها، استادیوم آن بازی و بازیکن‌هایی که هنگام مسابقه حداقل 35 سال داشته‌اند به همراه تعداد گل‌های زده شده توسط آن بازیکن و سن وی در آن بازی.
توضیحات مربوط به کوئری چهارم
نام یا همان کد بازیکن را در ستونی به نام player_code, شناسه بازی را در ستونی با نام game_id, استادیومی که بازی در آن انجام شده را در ستونی به نام stadium، سن بازیکن را در آن مسابقه را در ستونی با نام age_at و تعداد گل‌های زده شده توسط بازیکن در آن بازی را در ستونی به نام total_goals قرار دهید. ستون‌ها را ابتدا برحسب نام بازیکن به‌صورت صعودی , سپس برحسب سن بازیکن در آن بازی به‌صورت نزولی و سپس برحسب تعداد گل به‌صورت نزولی, سپس براساس نام سری استادیوم و درنهایت براساس شناسه بازی به‌صورت صعودی مرتب کنید.
توجه
فقط بازیکن‌هایی را که حداقل یک گل در این بازی‌ها زده‌اند، نمایش دهید.
| player_code | game_id | stadium | age_at | total_goals |
|---|---|---|---|---|
| adam-le-fondre | 4121036 | Easter Road Stadium | 37 | 1 |
| adam-le-fondre | 4120961 | Global Energy Stadium | 37 | 1 |
| adam-le-fondre | 4120853 | Easter Road Stadium | 36 | 1 |
روش پیاده‌سازی
در یک فایل با نام code.sql کد خود را قرار دهید و آن را فشرده (zip ) کنید و در سایت بارگذاری نمایید.
کد شما باید به صورت زیر باشد(نام فایل zip مهم نیست).
ارسال پاسخ برای این سؤال