**کد شما باید روی PostgreSQL قابل اجرا باشد.** --- # جزئیات پایگاه‌داده دیتابیسی که در این سوال به آن می‌پردازیم، متشکل از اطلاعات تعدادی کاربر، شرکت و محصولات شرکت‌ها خواهد بود. داده‌های اولیه را از [این لینک](/contest/assignments/73612/download_problem_initial_project/253326/) دانلود کنید. <details class="yellow"> <summary> **ایمپورت کردن داده‌های اولیه** </summary> از نصب بودن *PostgreSQL* روی سیستم خود اطمینان حاصل کنید. برای ایمپورت کردن داده‌های اولیه می‌توانید از یکی از دو روش زیر اقدام کنید: ۱- با استفاده _CLI_ دستور زیر را وارد کنید تا داده‌های اولیه ایمپورت شوند: ```shell psql -U postgres -f /path/to/initial.sql ``` که در این دستور مسیر فایل `initial.sql` را به صورت مطلق یا نسبی می‌توانید آدرس‌دهی کنید. ۲- اگر _GUI_ را ترجیح می‌دهید، پس از نصب دیتاگریپ و اتصال به _PostgreSQL_ با یوزر `postgres`، باید روی دیتاسورس و کانکشن `postgres` راست‌کلیک کنید و از منوی `SQL Scripts`، گزینه‌ی `Run SQL Script` را انتخاب کنید. سپس فایل `initial.sql` را پیدا و تایید کنید. در انتها روی `Run` کلیک کنید تا اسکریپت اجرا شود و داده‌ها وارد دیتابیس شوند. </details> <details class="grey"> <summary> **توضیحات جداول دیتاست** </summary> ### جدول `address` (آدرس‌ها) این جدول اطلاعات مربوط به آدرس‌ها را ذخیره می‌کند. | نام ستون | نوع داده | توضیحات | |----------------|----------------|------------------------------------| | `id` | `integer` | شناسه‌ی یکتا برای آدرس (کلید اصلی) | | `city` | `varchar(100)` | شهر | | `country` | `varchar(100)` | کشور | | `country_code` | `varchar(100)` | کد کشور | | `zip_code` | `integer` | کد پستی | ### جدول `company` (شرکت‌ها) این جدول اطلاعات مربوط به شرکت‌ها را ذخیره می‌کند. | نام ستون | نوع داده | توضیحات | |--------------|----------------|---------------------------------------------------| | `id` | `integer` | شناسه‌ی یکتا برای شرکت (کلید اصلی) | | `name` | `varchar(100)` | نام شرکت | | `address_id` | `bigint` | شناسه‌ی آدرس مرتبط (کلید خارجی به جدول `address`) | ### جدول `product` (محصولات) این جدول اطلاعات مربوط به محصولات را ذخیره می‌کند. | نام ستون | نوع داده | توضیحات | |------------|----------------|-------------------------------------| | `id` | `integer` | شناسه‌ی یکتا برای محصول (کلید اصلی) | | `name` | `varchar(100)` | نام محصول | | `category` | `varchar(100)` | دسته‌بندی محصول | | `price` | `integer` | قیمت محصول | ### جدول `product_company` (شرکت-محصول) این جدول یک رابطه‌ی چند به چند بین شرکت‌ها و محصولات ایجاد می‌کند. | نام ستون | نوع داده | توضیحات | |--------------|-----------|----------------------------------------------| | `id` | `integer` | شناسه‌ی یکتا برای رابطه (کلید اصلی) | | `company_id` | `bigint` | شناسه‌ی شرکت (کلید خارجی به جدول `company`) | | `product_id` | `bigint` | شناسه‌ی محصول (کلید خارجی به جدول `product`) | ### جدول `user` (کاربران) این جدول اطلاعات مربوط به کاربران را ذخیره می‌کند. | نام ستون | نوع داده | توضیحات | |--------------|-----------|---------------------------------------------------| | `id` | `integer` | شناسه‌ی یکتا برای کاربر (کلید اصلی) | | `first_name` | `text` | نام کاربر | | `last_name` | `text` | نام خانوادگی کاربر | | `email` | `text` | ایمیل کاربر | | `address_id` | `integer` | شناسه‌ی آدرس مرتبط (کلید خارجی به جدول `address`) | | `company_id` | `integer` | شناسه‌ی شرکت مرتبط (کلید خارجی به جدول `company`) | | `username` | `text` | نام کاربری | این جداول دارای روابطی هستند که به کمک کلیدهای خارجی بین جداول ایجاد شده‌اند. برای مثال، هر شرکت می‌تواند یک آدرس داشته باشد و هر کاربر نیز می‌تواند یک شرکت و یک آدرس داشته باشد. </details> # مطلوبات کوئری‌هایی بنویسید که خروجی‌های مطلوب زیر را به‌دست آورد (توجه کنید که هر کوئری نمره‌ای جداگانه دارد و اگر کوئری قسمتی را نتوانستید بنویسید، کوئری‌هایی که حل کردید را بفرستید و کوئری آن قسمت را خالی بگذارید): 1. آیدی، نام کوچک،‌ فامیلی و نام‌ کاربری تمام کاربرانی که نام کوچک (`first_name`) آن‌ها با حرف **s** و فامیلی (`last_name`) آن‌ها با حرف **e** تمام می‌شود. کوچک یا بزرگ بودن حروف مهم نیست و رکوردها باید براساس آیدی به صورت صعودی مرتب شوند. <details class="blue"> <summary> *نمونه خروجی کوئری اول* </summary> ساختار نتیجه‌ی کوئری و سطر اول آن، به شکل زیر است: | id | first_name | last_name | username | |:-----|:-----------|:----------|:----------| | 6916 | Sarah | George | patrick32 | </details> 2. نام شرکت و مجموعه‌ای از محصولاتی که متعلق به آن شرکت هستند. رکوردها براساس تعداد محصولات شرکت به صورت نزولی مرتب می‌شوند، در صورت برابر بودن تعداد محصولات دو شرکت، ترتیب صعودی آیدی آن‌ها اولویت دارد. <details class="blue"> <summary> *نمونه خروجی کوئری دوم* </summary> ## نمونه خروجی کوئری دوم ساختار نتیجه‌ی کوئری و سطر اول آن، به شکل زیر است: | company_name | product_list | |:-------------|:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | Movies | {Hair Care Component,Fitness Equipment,Home Improvement Rig,Truck Item,Furniture Whatchamacallit,Music Widget,Party Gear,Mens Device,Aquarium Instrument,Movie Paraphernalia,Camera Attachment,Skin Care Implement,Makeup Gadget,Party Gadget,Personal Implement,Swimming Rig,Gardening Mechanism,Nutrition Component,Book Machine,Party Mechanism} | </details> 3. در این کوئری باید آدرس شرکتی که کاربر در آن حضور دارد و نام‌ کاربری او را برگردانید. در صورتی که کاربر در شرکتی حضور نداشت یا شرکتی که کاربر در آن حضور دارد، آدرسی نداشت، آدرسی را که در جدول کاربران برای کاربر مشخص‌ شده است، برگردانید. خروجی باید به ترتیب صعودی آیدی کاربران مرتب شده باشد. ترتیب قرار گیری آدرس در ستون `user_address` به صورت روبرو است: ```Country, City, Zip Code``` <details class="blue"> <summary> *نمونه خروجی کوئری سوم* </summary> ساختار نتیجه‌ی کوئری و سه سطر اول خروجی به شکل زیر است: | username | user_address | |:------------|:-------------------------------| | markbaker | Moenstead, Palestine, 4972 | | garciaamy | Murphyton, French Guiana, 5765 | | jonesandrea | Bauchland, Panama, 8125 | </details> 4. در این کوئری از شما می‌خواهیم با جست‌و‌جو در جدول محصولات، یک جدول با فیلدها‌ی زیر‌ را برگردانید: + نام، قیمت و کتگوری محصول + نام و شهرِ شرکت محصول + در انتها یک فیلد که در آن اگر قیمت محصول از میانگین قیمت کل محصولات بیش‌تر بود، `expensive` و اگر قیمت آن از میانگین کل محصولات کم‌تر بود `not expensive` قرار می‌گیرد. + خروجی باید براساس آیدی محصول مرتب شده باشد. <details class="blue"> <summary> *نمونه خروجی کوئری چهارم* </summary> سه سطر ابتدایی خروجی به شکل زیر خواهد بود: | product\_name | product\_price | product\_category | company\_name | company\_city | price\_comparison | |:-------------------|:---------------|:------------------|:--------------|:--------------|:------------------| | Bedding Attachment | 14 | Home | Shoes | Corkeryland | not expensive | | Dining Contraption | 112 | Home | Grocery | Hartford | expensive | | Truck Kit | 27 | Automotive | Tools | North Burley | not expensive | </details> # فایل نهایی پس از پیاده‌سازی کوئری‌ها، آن را در فایل `queries.sql`، وارد کرده و سپس این فایل را آپلود کنید. کد شما باید به صورت زیر باشد: ```sql -- Section1 Your first query here -- Section2 Your second query here -- Section3 Your third query here -- Section4 Your fourth query here ```