Description

هدف اصلی از این تمرین، آشنایی عملی با دستورات پایه SQL بخصوص انواع اتصالات، گروهبندی و مرتب سازی خواهد بود. اگر قصد کار تخصصی در حوزه نرم افزار را دارید، آشنایی با SQL به صورت تخصصی جزء ملزومات اصلی این کار خواهد بود و در این تمرین هم برآنیم که تا حد امکان به این هدف برسیم .
دیتابیسی که برای این تمرین درنظر گرفته شده است، دیتابیس معروف آموزشی مایکروسافت با نام Northwind است که هر چند کمی قدیمی است اما برای اهداف آموزشی ما، بسیار مناسب است و نسخه پستگرس آن را در این تمرین استفاده خواهیم کرد.
این دیتابیس که یک فروشگاه بزرگ را مدلسازی میکند، از جداول اصلی زیر تشکیل شده است:
• Customer : مشتریان فروشگا ه
• Order : سفارشات هر مشتری – اطلاعات کلی
• OrderDetails : جزییات هر سفارش شامل آیتمهای خریداری شده .
• Products : محصولات فروشگاه
• Suppliers: تامین کنندگان کالا که محصولات از آنها تهیه می شوند.
• Ctaegories : گروه بندی کالاها
آنچه خواھید آموخت
• Shippers : شرکت های حمل و نقلی که وظیفه ارسال کالا به دست مشتری را بر عهده دارند.
• Employee: کارمندان فروشگاه که هرسفارش، توسط یک کارمند ثبت یا تایید می شود.
• Territory & Region: مناطق و نواحی ای که این فروشگاه در آنها شعبه دارد. هر منطقه شامل چندین ناحیه است و هر کارمند، می تواند به سفارشات چندین ناحیه رسیدگی کند.
• CustomerDemographics : برای رتبه بندی مشتریان استفاده می شود که دراین تمرین با آن سروکار نخواهیم داشت.
در صفحه بعد، نمودار ER این دیتابیس را مشاهده می کنید.
نسخه آنلاین آنرا می توانید از این آدرس دانلود کنید:
https://www.w3resource.com/mysql-exercises/northwind/products-table-
exercises/mysql-northwind-database.php

ایجاد و ایمپورت دادهھای دیتابیس Northwind
قبل از شروع به کار، باید خود دیتابیس Northwind را درون پستگرس بسازیم و سپس ساختار جداول و دادههای آنرا با اجرای مجموعه دستورات SQL ، ایجاد کنیم .
دیتابیسی با نام Northwind ایجاد کنید. (پستگرس به حروف بزرگ و کوچک حساس است بنابراین حواستان به این موضوع باشد). حال بر روی دیتابیس کلیک راست کرده، گزینه Set As Default را بزنید که دیتابیس پیشفرض شما در هنگام اجرای دستورات SQl ، این دیتابیس باشد .
سپس گزینه F3 را بزنید و یا از منوی بالا، SQL Editor را انتخاب کنید. یک فایل اسکریپت جدید باز می شود که در مرحله بعد از منوی SQL Editor گزینه Import SQL Script را انتخاب کرده، فایلی که در این آدرس قرار گرفته است را دانلود کرده ، آنرا ایمپورت نمایید. دقت کنید که دیتابیس پیش فرض شما حتما Northwind باشد(مطابق تصویر زیر) :
حال با زدن دکمه Ctrl+A همه دستورات را انتخاب کرده و مثلث کوچک نارنجی رنگ صفحه ویرایشگر SQL را برای اجرای تمام دستورات انتخاب شده، بزنید .
مطابق شکل بالا، درون شِمای پابلیک باید جداول شما ساخته شده باشد. (البته نیاز خواهید داشت روی دیتابیس Northwind کلیک راست کرده و گزینه Refresh را بزنید.) حال اگر جداول اصلی را باز کنید، در قسمت Data، دادههای آنها را می توانید مشاهده کنید.
اگر تا اینجا همه چیز بدون مشکل پیش رفته باشد، آماده انجام تمرینات این دستورکار شده اید .

دستورات پایه
برای انجام این تمرین، یک فایل SQL جدید ایجاد کنید و این فایلها را هم در انتها، کنار گزارش خود ارسال نمایید. (با زدن
( new script و انتخاب F3 دکمه
احیان اگر ابهامی در یک سوال مشاهده میکنید، با در نظرگرفتن فرضی که این ابهام را برطرف کند آنرا حل کنید .
اً
1. می خواهیم ببینیم به ازای هر منطقه ( region)، چه نواحیای تعریف شده است. دستور SQL متناظر را با استفاده از inner join جدول region و territories بنویسید. فقط نام منطقه و نام ناحیه در خروجی باید ظاهر شود.
دستور inner join مشابه این خواهد بود (r و t نام مستعار دو جدول هستند که برای سادهتر شدن نوشتن دستورات SQL معمولا از این شیوه استفاده می کنیم). :
from region r inner join territories t on r.region_id = t.region_id
2. حال میخواهیم تعداد کارمندان هر منطقه( region) را به دست آوریم. دستور SQL متناظر را بنویسید.
راهنمای ی : رابطه مستقیمی بین ناحیه و کارمند وجود ندارد و باید از جدول واسط این دو یعنی employee_territories استفاده کنیم. بنابراین در قسمت from با inner join این چند جدول سروکار داریم. از طرفی برای شمردن تعداد کارمندان بر حسب هر منطقه، باید دادهها بر حسب منطقه گروه بندی شده و سپس کارمندان آنها شمارش شوند. در اینجا باید از دستور group by r.region_id بعد از from استفاده کنیم. در مرحله آخر هم دستور شمارش را در جلوی select بنویسیم.
بنابراین در قسمت select خواهیم داشت :
select r .region_description , count(e.employee_id )

3. با در نظر گرفتن میزان تخفیف و تعداد هر محصول در سفارش، قیمت کل پرداختی مشتری به ازای هر سفارش را محاسبه کنید. از جدول OrderDetails استفاده کنید. خروجی این دستور، تمام شماره سفارشها و مبلغ پرداختی به ازای آن سفارشها خواهد بود .
4. ده محصولی که بیشترین تعداد خرید را به خود اختصاص دادهاند را بیابید. منظور از تعداد خرید، مجموع
.است OrderDetails آن محصول در جدول Quantity
5. آیا محصولی وجود دارد که هنوز فروش نرفته باشد ؟
6. می خواهیم گزارشی تهیه کنیم از محصولات و تعداد سفارشهایی که این محصول در آنها به کار رفته است (تعداد سفارشهایی که این محصول در آنها به کار رفته و نه تعداد کل فروش محصو ل) . دستور sql مربوطه را بنویسید.
دقت کنید که برای این سوال، نیاز دارید از جوین های خارجی (چپ یا راست) استفاده کنید.
7. می خواهیم به کارمندی که بیشترین فروش (از لحاظ مبلغ پرداختی مشتری که در گام سه محاسبه کرد هاید) را در یک سال خاص داشته است (یک سال فرضی در نظر بگیرید) ، جایزه بدهیم. این کارمند را چگونه به دست می آوریم ؟ از توابع تاریخ در پستگرس برای استخراج سال از روی OrderDate و گروهبندی دادهها بر اساس آن، استفاده کنید .توصیه میکنم برای راحتی کار از CTE در SQL استفاده کنید. یعنی جداول مورد نیاز را به صورت موقت
در ابتدا بسازید(با with) -مثل جدولی که خروجی دستور سوم است و اینجا به آن نیاز دارید- سپس از آن جدول در دستورات پایینتر استفاده کنید. به مثال زیر دقت کنید:

8. می خواهیم به سفارشاتی که همان روز سفارش ارسال شده اند(یعنی OrderDate و ShippedDate آنها برابر است)، برچسب عالی، آنهایی که تا سه روز بعد از سفارش ارسال شده اند، برچسب خوب و به بقیه سفارشات برچسب نامناسب بزنیم . در گزارش نهایی، کد سفارش و برچسب آن باید ظاهر شود. از دستور when و case استفاده کنید .
9. (سوال امتیازی) برای دستور WITH RECURSIVE در این دیتاست، یک مثال بزنید که خروجی مناسب تولید کند.
10. کوئری زیررا درنظر بگیرید:
a.OrderID, b.Subtotal, year(a.ShippedDate) as Year
from Orders a inner join
( select distinct OrderID, sum(UnitPrice * Quantity) as Subtotal
order by a.ShippedDate;
این کوئری چه کاری انجام میدهد؟ آنرا به گونه ای تغییر دهید که مجموع فروش هر سال از دادههای دیتابیس را تولید کند.
خروجی مورد نظر ،سال و میزان کل فروش آنخواهد بود .
11. میخواهیم سفارش جدیدی برای محصولاتی که تعداد آنها( UnitsInStock) از حد مجاز( ReorderLevel) کمتر شده است، بدهیم. این محصولات را بیابید و آنها بر اساس میزان موجودی (به صورت صعودی ) نمایش دهید. یک ویو برای ذخیره این دستور ایجاد کنید که همواره برای مشاهده محصولاتی که نیاز به خرید دارند، این ویو، استفاده شود .
12. از کدام دسته محصولات، به فرانسه چیزی ارسال نشده است؟
13. از کدام دسته محصولات، به فرانسه چیزی ارسال نشده است؟
14. مشتریانی را بیابید که شماره فکس آنها در سیستم موجود نیست . (مقایسه fax با null )
15. می خواهیم اطلاعات کارکنان را نمایش بدهیم و قصد داریم سن کارکنان را براساس تاریخ امروز محاسبه (توابع کار با تاریخ پستگرس) و به عنوان یک ستون جدید با نام Age در خروجی Select نمایش دهیم. خروجی این دستور را به عنوان یک View ذخیره کنید. سپس میانگی ن سن هر منطقه را با استفاده از این View محاسبه کنی د. (میتوانید از تابع age در پستگرس یا current_date استفاده کنید)