week04-exam03 - هفته‌ی چهارم، تمرین سوم، رستوران‌های زنجیره‌ای همکارمون

رستوران‌های زنجیره‌ای همکارمون

اون همکارمون که ناشناس بود اخیرا در حوزه مالی هم به خفانت طراحی سوالش عمل کرده و به پیشرفت‌های چشمگیری رسیده. به طوری که میتونید رستوران‌های زنجیره‌ای ایشون رو در سراسر دنیا مشاهده کنید! از اونجایی که در عصر دیتا به سر میبریم، همکارمون قصد گرفتن گزارش و خلاصه‌ای از داده‌های دیتابیس های رستورانش رو داره و علی‌رغم دیتاساینتیست‌های خبره‌ای که آرزوی همکاری با او را دارند ایشان در راستای حمایت از تولیدات داخلی و این حرفا... قصد دارد این ماموریت را به شما محول کند.

جزئیات پروژه

داده‌های اولیه‌ی این رستوران‌ها را از این لینک دانلود کنید. این فایل زیپ شامل یک فایل با پسوند .sql بوده که می‌توان آن را در یک دیتابیس import کرد.

جداول مربوط به این رستوران‌های زنجیره‌ای به‌شرح زیر هستند:

1- اطلاعات مشتریان (customers):

    نام ستون	نوع	تعریف	ملاحظات
    id	INT(11)	شناسه‌ی مشتری	PRIMARY KEY AUTO_INCREMENT
    name	VARCHAR(255)	نام مشتری	
    phone	VARCHAR(255)	شماره تماس مشتری	

2- اطلاعات شعبه‌ها (restaurants):

    نام ستون	نوع	تعریف	ملاحظات
    id	INT(11)	شناسه‌ی شعبه	PRIMARY KEY AUTO_INCREMENT
    name	VARCHAR(255)	نام شعبه	
    address	VARCHAR(255)	آدرس شعبه	

3- اطلاعات غذاها (foods):

    نام ستون	نوع	تعریف	ملاحظات
    id	INT(11)	شناسه‌ی غذا	PRIMARY KEY AUTO_INCREMENT
    name	VARCHAR(255)	نام غذا	

4- غذاهای موجود در هر رستوران (restaurant_foods):

    نام ستون	نوع	تعریف	ملاحظات
    id	INT(11)	شناسه‌ی غذا در رستوران	PRIMARY KEY AUTO_INCREMENT
    restaurant_id	INT(11)	شناسه‌ی رستوران	
    food_id	INT(11)	شناسه‌ی غذا	

5- سفارشات مشتریان (orders):

    نام ستون	نوع	تعریف	ملاحظات
    id	INT(11)	شناسه‌ی سفارش	PRIMARY KEY AUTO_INCREMENT
    customer_id	INT(11)	شناسه‌ی مشتری	
    restaurant_food_id	INT(11)	شناسه‌ی غذا در رستوران	
    rate	TINYINT(1)	امتیاز مشتری به سفارش (از ۱ تا ۵)	

همان‌طور که از ساختار جدول فوق مشخص است، در هر سفارش تنها یک غذا می‌توان سفارش داد!

مطلوبات

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

1- نام و شماره تماس فردی که بیش‌ترین تعداد سفارشات را از شعبه‌های مختلف داشته است برگردانید. نمونه خروجی:

    name	phone
    iazEYUOPc7	+989949076087

2- شناسه و نام ۱۰ غذای برتر برحسب میانگین امتیاز سفارشات را برگردانید. سطرها را ابتدا به‌ترتیب نزولی میانگین امتیاز و در صورت برابری امتیازات، به‌ترتیب صعودی شناسه‌ی غذا (foods.id) برگردانید. نمونه خروجی:

    id	name
    17	KGPHpsgggS
    22	txhDU2sxrr
    23	vJ3HFcpf7c
    29	4LFs3cg0jU
    42	c28CS kOrY
    44	oa8NKy7z8E
    47	OIDYwgW6CV
    35	9PqoinmwIb
    37	5li nJ 669
    40	eZKMcF04y1

3- شناسه و نام ۱۰ شعبه‌ی برتر برحسب میانگین امتیاز سفارشات را برگردانید. سطرها را ابتدا به‌ترتیب نزولی میانگین امتیاز و در صورت برابری امتیازات، به‌ترتیب صعودی شناسه‌ی رستوران (restaurants.id) برگردانید. نمونه خروجی:

    id	name
    24	xwOMuYeAvv
    19	ETb8c00aev
    31	kORo3RzlLE
    47	UqmSXEAJWR
    20	rpA9yg8bTK
    1	nS2lK0JMkT
    4	c0UzdJIDbx
    33	ZH b6lczWr
    41	9QPj3 MI8G
    48	e5AHRGHCsb

4- نام و شماره تماس افرادی که تاکنون حداقل از ۵ شعبه‌ی مختلف سفارش داشته‌اند را به‌ترتیب صعودی براساس نام برگردانید. نمونه خروجی (۱۰ سطر اول):

    name	phone
    2TPXTy 36M	+989288198491
    3mlJEltd4E	+989637083929
    3vLqxnZsRi	+989625530069
    4xdT97ykl9	+989642498171
    aAZJUupvHB	+989846169758
    aRhGDtYAwT	+989515147658
    bnKlH6pSfG	+989129382954
    EqHF02fVWm	+989575794633
    F5BEViuDBT	+989688468518
    goQ KusMli	+989954836194

5- آن‌چه باید آپلود کنید کد خود را در قالب زیر، در یک فایل با پسوند sql قرار داده و آن را ارسال کنید.

    -- Section1
        your 1st query here
    -- Section2
        your 2nd query here
    -- Section3
        your 3rd query here
    -- Section4
        your 4th query here