**کد شما باید روی PostgreSQL قابل اجرا باشد.** ---------- در این سوال، بخشی از پایگاه داده مربوط به اطلاعات پرواز کشور روسیه در اختیار شما قرار گرفته است. # جزئیات دیتابیس داده‌های اولیه برای **تست نهایی** را از [این لینک](/contest/assignments/60605/download_problem_initial_project/210972/) دانلود کنید. <details class="yellow"> <summary> **توضیحات در مورد داده‌های اولیه** </summary> در فایل `Travel.zip` فایلی به اسم `initial.sql` وجود دارد. با اجرای این فایل، همه‌ی جداول و سطرهایی که برای **تست‌ نهایی** مورد استفاده قرار می‌گیرد در پایگاه داده شما ایجاد می‌شود. پیش از اجرای این فایل، حتما پایگاه داده با اسم دلخواه ایجاد کنید و در ابتدای این فایل، کد `use YOUR_DB_NAME;` را وارد کنید. </details> <details class="grey"> <summary> **توضیحات جداول دیتابیس** </summary> ساختار جداول به‌شرح زیر است: **جدول `aircraft_data`**: از این جدول برای نگه‌داری اطلاعات هواپیما‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است: |نام ستون|نوع|تعریف| |:-------:|:---:|:----:| |`aircraft_code`|`character varying(8)`|کد هواپیما| |`model`|`character varying(64)`|مدل هواپیما| |`range`|`integer`|برد هواپیما| **جدول `airports_data`**: از این جدول برای نگه‌داری اطلاعات فرودگاه‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است: |نام ستون|نوع|تعریف| |:-------:|:--:|:----:| |`airport_code`|`character varying(4)`|کد فرودگاه| |`airport_name`|`json`|نام فرودگاه با فرمت JSON| |`city`|`json`|شهری که فرودگاه در آن قرار دارد با فرمت JSON| |`timezone`|`character varying(32)`|منطقه زمانی فرودگاه| **جدول `boarding_passes`**: از این جدول برای نگه‌داری اطلاعات کارت‌های پرواز استفاده می‌شود. ساختار این جدول به‌صورت زیر است: |نام ستون|نوع|تعریف| |:-------:|:--:|:----:| |`ticket_no`|`character varying(8)`|شماره بلیط| |`flight_id`|`integer`|شناسه پرواز| |`boarding_no`|`integer`|شماره کارت پرواز| |`seat_no`|`character varying(4)`|شماره صندلی| **جدول `flights`**: از این جدول برای نگه‌داری اطلاعات پروازها استفاده می‌شود. ساختار این جدول به‌صورت زیر است: |نام ستون|نوع|تعریف| |:-------:|:--:|:----:| |`flight_id`|`integer`|شناسه پرواز| |`flight_no`|`character varying(8)`|شماره پرواز| |`scheduled_departure`|`character varying(32)`|زمان حرکت برنامه ریزی شده| |`scheduled_arrival`|`character varying(32)`|زمان ورود برنامه ریزی شده| |`departure_airport`|`character varying(4)`|کد فرودگاه مبدا| |`arrival_airport`|`character varying(4)`|کد فرودگاه مقصد| |`status`|`character varying(16)`|وضعیت پرواز| |`aircraft_code`|`character varying(4)`|کد هواپیما| **جدول `seats`**: از این جدول برای نگه‌داری اطلاعات صندلی‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است: |نام ستون|نوع|تعریف| |:-------:|:--:|:----:| |`aircraft_code`|`character varying(4)`|کد هواپیما| |`seat_no`|`character varying(4)`|شماره صندلی| |`fare_conditions`|`character varying(8)`|کلاس صندلی هواپیما| **جدول `ticket_flights`**: از این جدول برای نگه‌داری اطلاعات بلیط‌های هر پرواز استفاده می‌شود. ساختار این جدول به‌صورت زیر است: |نام ستون|نوع|تعریف| |:-------:|:--:|:----:| |`ticket_no`|`character varying(16)`|شماره بلیط| |`flight_id`|`integer`|شناسه پرواز| |`fare_conditions`|`character varying(8) `|کلاس بلیط هواپیما| |`amount`|`integer`|مبلغ بلیط| **جدول `tickets`**: از این جدول برای نگه‌داری اطلاعات بلیط‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است: |نام ستون|نوع|تعریف| |:-------:|:--:|:----:| |`ticket_no`|`character varying(16)`|شماره بلیط| |`book_ref`|`character varying(8)`|کد مرجع رزرو| |`passenger_id`|`character varying(16)`|شناسه مشتری| </details> # مطلوبات ۱. تعداد صندلی‌های هر هواپیما به تفکیک کلاس آن‌ها <details class="blue"> <summary> *توضیحات مربوط به کوئری اول* </summary> خروجی این کوئری شامل سه ستون با نام‌های `aircraft_code` و `fare_conditions` و `count` است که به ترتیب کد هواپیما، نوع کلاس صندلی و تعداد صندلی‌های موجود در هواپیمای مذکور با کلاس مشخص شده است. در نهایت جدول، باید بر اساس ستون `aircraft_code` به صورت صعودی و سپس براساس `count` به صورت نزولی مرتب شود و در صورت برابر بودن این دو ستون بر اساس ستون‌ `fare_conditions` به صورت صعودی مرتب شود. ۴ سطر اول خروجی شما باید به شکل زیر باشد: | aircraft_code| fare_conditions| count| |:---:|:---:|:---:| | 319 | Economy | 96 | | 319 | Business | 20 | | 320 | Economy | 120 | | 320 | Business | 20 | </details> ۲. مجموع تعداد پرواز‌های کنسل شده در روز‌های مختلف هفته </details> <details class="blue"> <summary> *توضیحات مربوط به کوئری دوم* </summary> خروجی این جدول شامل دو ستون با نام‌های `day` و `count‍` است که به ترتیب شامل روز‌های هفته و تعداد پرواز‌های کنسل شده در آن روز هستند. جدول نهایی باید بر اساس ستون `count` به صورت نزولی و سپس بر اساس ستون `day` به صورت صعودی مرتب شود. ۲ سطر اول خروجی شما باید به شکل زیر باشد | day | count | |:---:|:---:| | Wednesday | 2 | | Thursday | 1 | </details> ۳. رتبه‌بندی تعداد مسافر جابه‌جا شده در فرود‌گاه‌ها توسط هواپیما‌های مختلف <details class="blue"> <summary> *توضیحات مربوط به کوئری سوم* </summary> جدول خروجی شامل چهار ستون با نام‌های `airport` و `aircraft_model` و `count` و `rank` است که به ترتیب شامل نام فرود‌گاه، مدل هواپیما، تعداد مسافرانی که توسط این هواپیما یا به این فرودگاه آمدند یا از این فرودگاه رفتند ( **طبیعتا پرواز‌های کنسل شده نباید حساب شوند**)، است و رتبه این هواپیما در بین تمام هواپیما‌ها که در این فرودگاه تردد کردند. دقت کنید که رتبه‌بندی بر اساس تعداد دفعات تردد (ستون `count`) به این صورت محاسبه می‌شود که اگر دو هواپیما با بیشترین `count`، یکسان بودند هردو رتبه یک می‌شوند و هواپیما بعدی رتبه‌اش دو می‌شود. جدول نهایی، باید به ترتیب بر اساس ستون‌های `airport` و `rank` و `aircraft_model` به صورت صعودی مرتب باشد. **توجه ۱: لازم به ذکر است برای نام شهر‌ها، باید از نام انگلیسی که در JSON مربوطه وجود دارد، استفاده کنید.** ۴ سطر اول خروجی شما باید به شکل زیر باشد. | airport | aircraft_model | count | rank | |:---:|:---:|:---:|:---:| | Abakan Airport | Cessna 208 Caravan | 435 | 1 | | Abakan Airport | Airbus A319-100 | 405 | 2 | | Anapa Vityazevo Airport | Boeing 737-300 | 2765 | 1 | | Anapa Vityazevo Airport | Sukhoi Superjet-100 | 2017 | 2 | </details> ۴. فاصله زمانی شهر‌های مختلف <details class="blue"> <summary> *توضیحات مربوط به کوئری چهارم* </summary> جدول خروجی شامل سه ستون با نام‌های `city1` و `city2` و `time_dist` است که به ترتیب شهر‌های دو سر مسیر و فاصله زمانی شهر‌هاست. جدول نهایی باید بر اساس ستون `time_dist` به صورت نزولی و سپس بر اساس `city1` و `city2` به صورت صعودی مرتب باشد. توجه کنید که هر زوج شهر باید یک بار تکرار شود و همچنین از لحاظ ترتیب الفبایی باید`city1` نسبت به `city2` مقدم باشد. **توجه: فاصله زمانی شهر‌ها برابر است با میانگین فاصله زمانی بین فرودگاه‌های آن دو شهر و فاصله زمانی دو فرودگاه برابر است با میانگین اختلاف زمان حرکت و ورود برنامه‌ریزی شده در بلیط‌های مختلفی که برای این دو فرود‌گاه وجود دارد (دقت کنید که بلیط‌های کنسل شده هم باید حساب شوند):** $$ time\ distance(c_1, c_2) = Average_{a_1\in c_1, a_2\in c_2}\ \{time\ distance(a_1, a_2)\} $$ $$ time\ distance(a_1, a_2) = Average_{f\in flights}\ \{f.scheduled\_arrival - f.scheduled\_departure\} $$ ۳ سطر اول خروجی شما باید به شکل زیر باشد. | city1 | city2 | time_dist | |:---:|:---:|:---:| | Moscow | Petropavlovsk | 530 | | Moscow | Yuzhno-Sakhalinsk | 527.5 | | Magadan | Mineralnye Vody | 525 | </details> # روش پیاده‌سازی در یک فایل با نام `code.sql` کد خود را قرار دهید و آن را فشرده (`zip` ) کنید و در سایت بارگذاری نمایید. کد شما باید به صورت زیر باشد(نام فایل zip مهم نیست). ```sql -- Section1 Your first query here -- Section2 Your second query here -- Section3 Your third query here -- Section4 Your fourth query here ```

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