اطلاعات کاربران


کد شما باید روی PostgreSQL قابل اجرا باشد.

جزئیات پایگاه‌داده🔗

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

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

ایمپورت کردن داده‌های اولیه

از نصب بودن PostgreSQL روی سیستم خود اطمینان حاصل کنید.

برای ایمپورت کردن داده‌های اولیه می‌توانید از یکی از دو روش زیر اقدام کنید:

۱- با استفاده CLI دستور زیر را وارد کنید تا داده‌های اولیه ایمپورت شوند:

psql -U postgres -f /path/to/initial.sql
Shell

که در این دستور مسیر فایل initial.sql را به صورت مطلق یا نسبی می‌توانید آدرس‌دهی کنید.

۲- اگر GUI را ترجیح می‌دهید، پس از نصب دیتاگریپ و اتصال به PostgreSQL با یوزر postgres، باید روی دیتاسورس و کانکشن postgres راست‌کلیک کنید و از منوی SQL Scripts، گزینه‌ی Run SQL Script را انتخاب کنید. سپس فایل initial.sql را پیدا و تایید کنید. در انتها روی Run کلیک کنید تا اسکریپت اجرا شود و داده‌ها وارد دیتابیس شوند.

توضیحات جداول دیتاست

جدول 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 نام کاربری

این جداول دارای روابطی هستند که به کمک کلیدهای خارجی بین جداول ایجاد شده‌اند. برای مثال، هر شرکت می‌تواند یک آدرس داشته باشد و هر کاربر نیز می‌تواند یک شرکت و یک آدرس داشته باشد.

مطلوبات🔗

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

  1. آیدی، نام کوچک،‌ فامیلی و نام‌ کاربری تمام کاربرانی که نام کوچک (first_name) آن‌ها با حرف s و فامیلی (last_name) آن‌ها با حرف e تمام می‌شود. کوچک یا بزرگ بودن حروف مهم نیست و رکوردها باید براساس آیدی به صورت صعودی مرتب شوند.
نمونه خروجی کوئری اول

ساختار نتیجه‌ی کوئری و سطر اول آن، به شکل زیر است:

id first_name last_name username
6916 Sarah George patrick32
  1. نام شرکت و مجموعه‌ای از محصولاتی که متعلق به آن شرکت هستند. رکوردها براساس تعداد محصولات شرکت به صورت نزولی مرتب می‌شوند، در صورت برابر بودن تعداد محصولات دو شرکت، ترتیب صعودی آیدی آن‌ها اولویت دارد.
نمونه خروجی کوئری دوم

نمونه خروجی کوئری دوم🔗

ساختار نتیجه‌ی کوئری و سطر اول آن، به شکل زیر است:

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}
  1. در این کوئری باید آدرس شرکتی که کاربر در آن حضور دارد و نام‌ کاربری او را برگردانید. در صورتی که کاربر در شرکتی حضور نداشت یا شرکتی که کاربر در آن حضور دارد، آدرسی نداشت، آدرسی را که در جدول کاربران برای کاربر مشخص‌ شده است، برگردانید. خروجی باید به ترتیب صعودی آیدی کاربران مرتب شده باشد. ترتیب قرار گیری آدرس در ستون user_address به صورت روبرو است: Country, City, Zip Code
نمونه خروجی کوئری سوم

ساختار نتیجه‌ی کوئری و سه سطر اول خروجی به شکل زیر است:

username user_address
markbaker Moenstead, Palestine, 4972
garciaamy Murphyton, French Guiana, 5765
jonesandrea Bauchland, Panama, 8125
  1. در این کوئری از شما می‌خواهیم با جست‌و‌جو در جدول محصولات، یک جدول با فیلدها‌ی زیر‌ را برگردانید:
  • نام، قیمت و کتگوری محصول
  • نام و شهرِ شرکت محصول
  • در انتها یک فیلد که در آن اگر قیمت محصول از میانگین قیمت کل محصولات بیش‌تر بود، expensive و اگر قیمت آن از میانگین کل محصولات کم‌تر بود not expensive قرار می‌گیرد.
  • خروجی باید براساس آیدی محصول مرتب شده باشد.
نمونه خروجی کوئری چهارم

سه سطر ابتدایی خروجی به شکل زیر خواهد بود:

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

فایل نهایی🔗

پس از پیاده‌سازی کوئری‌ها، آن را در فایل queries.sql، وارد کرده و سپس این فایل را آپلود کنید. کد شما باید به صورت زیر باشد:

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