2. گزارش کاملی از تمرینات انجامشده در کلاس بر روی پایگاه دادهایی به طراحی خودتان (متفاوت از پایگاه داده و جدول ایجادشده در کلاس) بنویسید. گزارش باید دارای توضیحات کامل به همراه تصاویر اسکرینشات از پایگاه داده و مراحل انجام تمرینات باشد.

2 آزمایش دوم: آشنایی با دستورات DDL(1)

2-1 مقدمه

در این آزمایش با دسته فرمانهای SQL آشنا میشویم و سپس به صورت خاص بر روی دستورات DDL تمرکز کرده و ساخت دیتابیس و جدول را از طریق کدنویسی خواهیم آموخت. بر روی ستونهای یک جدول محدودیتهای (constraint) مختلف میتوان قرار داد که با این محدودیتها و کدنویسی آنها طی دو جلسه آشنا خواهیم شد.

در پایان این آزمایش شما باید بتوانید به سوالات زیر پاسخ دهید:

1. سه دسته فرمان کلی موجود در SQL را نام برده و هر یک را توضیح دهید.

2. سه دستور مهم DDL را نام ببرید.

3. دستور ایجاد یک پایگاه داده چیست؟

4. دو مفهوم درجه و کاردینالیتی در یک جدول را توضیح دهید.

5. شمای متنی یک جدول چگونه نوشته میشود؟

6. سه نکتهی اصلی در هنگام نامگذاری جدول و ستونهای یک جدول چیست؟

7. انواع محدودیتهایی (constraint) که میتوان بر روی یک جدول یا ستون قرار داد را توضیح دهید.

8. مزیت دادن نام به یک محدودیت چیست؟

9. چگونه میتوان بر روی دو ستون به صورت ترکیبی یک محدودیت قرار داد؟

10. دو نتیجهی اعمال کلید اصلی بر روی یک ستون چیست؟

11. دلیل استفاده از محدودیت unique چیست و تفاوت unique و primary key را بیان کنید.

2-2دستور کار

2-2-1 ایجاد پایگاه داده

ü از دستور Create DataBase برای ایجاد یک پایگاه داده استفاده میشود.

CREATE DATABASE < database_name >

1- پایگاه دادهایی به نام UI962 ایجاد کنید.(با کد نویسی)

2-2-2 ایجاد جدول

ü از دستور Create Table برای ایجاد یک جدول استفاده میشود.

CREATE TABLE <table_name>

(

<column_name1> data_type(size) <constraint_name>,

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

....

);

2-2-3 محدودیت Not Null

2- کد ایجاد جدولی به نام Student با مشخصات زیر را به صورتی بنویسید که ستون id آن دارای محدودیت Not Null باشد.

Student(id: string, name: string, family: string, birthdate: datetime)

3- به صورت دستی سطر زیر را وارد جدول Student نمایید و خطای رخ داده را خوانده و توضیح دهید:

birthdate	family	name	id
1995-01-02	ahmadi	ali	NULL

4- به صورت دستی محدودیت Not Null را بر روی ستون family از جدول Student قرار داده و از روی ستون id آن را حذف کنید.

ü اگر بعد از ایجاد جدول تصمیم به اضافه کردن محدودیت به ستونی از جدول گرفتید به دو ساختار کلی زیر میتوانید عمل کنید.(این دو ساختار مخصوص محدودیتهای Primary key، Unique، Check هستند)

ساختار 1:

ALTER TABLE <table_name>
ADD <constraint_name> <column_name>

ساختار 2:

ALTER TABLE <table_name>
ADD CONSTRAINT <identity_name> <constraint_name> (<column_name1>, <column_name2>,…)

استثناء: دستور Not Null، Default، Foreign Key در این مورد دارای ساختارهای خاص خود هستند.

ساختار افزودن محدودیت Not Null به ستونی از جدول از پیش ساختهشده:

ALTER TABLE <table_name>

ALTER COLUMN <column_name> INTEGER Not NULL ;

5- بر روی ستون name از جدول ساختهشدهی Student محدودیت Not Null قرار دهید.

ü اگر پس از ایجاد جدول، تصمیم به حذف کردن محدودیت قرار دادهشده بر روی ستونی از جدول گرفتید از ساختار کلی زیر میتوانید استفاده کنید. .(این فرم مخصوص محدودیتهای Primary key، Unique، Check،، Foreign Key است)

ALTER TABLE <table_name>
DROP CONSTRAINT <identity_name>

استثناء: دستور Not Null، Default در این مورد دارای ساختارهای خاص خود هستند.

قالب حذف محدودیت Not Null از روی ستونی از جدول از پیش ساختهشده:

ALTER TABLE <table_name>

ALTER COLUMN <column_name> INTEGER NULL ;

6- کد حذف محدودیت Not Null بر روی ستون id از جدول Student را بنویسید.

2-2-4 محدودیت Primary key

7- بر روی ستون id از جدول ساختهشدهی Student محدودیت Primary Key قرار دهید.

8- به صورت wizard دو سطر زیر را وارد جدول Student نمایید و خطای پیش آمده را خوانده و توضیح دهید.

birthdate	family	name	id
1995-01-02	ahmadi	ali	1
1995-02-03	salehi	sara	1

در صورتی که بخواهیم جدولی ایجاد و بر روی دو ستون آن به صورت ترکیبی محدودیتی اعمال کنیم از ساختار کلی زیر استفاده میکنیم:

CREATE TABLE <table_name>

(

<column_name1> data_type(size) <constraint_name>,

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

...

CONSTRAINT <identity_name> <constraint_name> (<column_name1>, <column_name2>,…)

…

);

9- جدولی به نام Teacher ایجاد کرده و دو ستون نام و نام خانوادگی را به صورت ترکیبی کلید اصلی آن قرار دهید:

Teacher (name: string, family: string, birthdate: datetime)

10- به صورت دستی سه سطر زیر را وارد جدول Teacher نمایید. آیا خطایی رخ میدهد؟ توضیح دهید.

birthdate	family	name
1995-01-02	ahmadi	ali
1995-02-03	salehi	ali
1995-03-04	ahmadi	ali

11- کد حذف محدودیت Primary key از جدول Teacher را بنویسید.

12- به صورت دستی محدودیت Primary key را بر روی ستون id از جدول Student قرار دهید.

2-2-5 محدودیت Unique

13- بر روی جدول ساختهشدهی Sudent کدی بنویسید که بتواند محدودیت Unique را به صورت ترکیبی بر روی دو ستون name و family قرار دهد.

14- آزمایش 10 را مجددا این بار بر روی جدول Student انجام دهید و نتیجه را توضیح دهید.

15- کد حذف محدودیت Unique از جدول Student را بنویسید.

16- به صورت دستی محدودیت Unique را بر روی ستون name از جدول Student قرار دهید.

2-3 گزارش کار

3. به سوالات مطرحشده در مقدمه پاسخ دهید.

4. گزارش کاملی از تمرینات انجام شده در کلاس بر روی پایگاه دادهایی به طراحی خودتان (متفاوت از پایگاه داده و جدول ایجاد شده در کلاس) بنویسید. گزارش باید دارای کد و توضیحات کامل در مورد کد هر تمرین، تصاویر اسکرینشات و توضیحات برای تمرینات بدون کد باشد.

3 آزمایش سوم: آشنایی با دستورات DDL(2)

3-1 مقدمه

این آزمایش در ادامهی آزمایش قبل است و در این آزمایش با محدودیتهای (constraint) دیگری که میتوان بر روی ستونهای یک جدول قرار داد، آشنا میشویم.

در پایان این آزمایش شما باید بتوانید به سوالات زیر پاسخ دهید:

1. Foreign Key چیست؟

2. هرگاه یک ستون Foreign Key تعریف شود، چه محدودیتهایی بر روی دادههای آن ستون اعمال میشود؟

3. دو پارامتر اصلی در هنگام قرار دادن محدودیت identity بر روی ستونی، چیست؟

3-2 دستور کار

3-2-1 محدودیت Foreign Key

ü نمودار ER زیر را در نظر بگیرید: رابطه تدریس یک درس توسط استاد به صورت زیر است.

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

• Lesson (Lid: char(5), LName: char(25), vahed smallint)

• Teacher (Tid: char(10), TName: char(50), course char(50))

• Teach (Lid: char(5), Tid: char(10), semester: DateTime)

در جدول Teach دو ستون Lid و Tid به صورت FK تعریف می شوند که پدر آنها کلیدهای اصلی در جداول Lesson و Teacher هستند.

ü برای پیاده سازی این محدودیت، از سه ساختار زیر می توان استفاده کرد:

ساختار1:

CREATE TABLE <table_name>

(

<column_name1> data_type (size) <constraint_name>,

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

...

Foreign Key (نام فیلد ارجاع) References < نام جدول مرجع>

[On Delete Cascade]

[On Update Cascade]

...);

ساختار2:

CREATE TABLE <table_name>

(

<column_name1> data_type (size) FOREIGN KEY REFERENCES < نام جدول مرجع> (نام فیلد مرجع)

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

...);

ساختار3:

CREATE TABLE <table_name>

(

<column_name1> data_type(size) <constraint_name>,

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

...

CONSTRAINT <identity_name> FOREIGN KEY (نام فیلد ارجاع)

REFERENCES < نام جدول مرجع> (نام فیلد مرجع)

…

);

1- برای نمودار ER زیر، جداول مرتبط را رسم نمایید.

ü فیلد‌های کلید خارجی ، سه نوع محدودیت را ایجاد می‌کنند :

1.اجازه حذف رکورد از جدول پدری که فرزندی داشته باشد، نداریم.

2.اجازه درج رکورد در جدول فرزند با کلیدی که در جدول پدر موجود نیست را نداریم.

3.مقدار کلید پدری را که دارای فرزند هست، نمی‌توانیم تغییر دهیم.

2- در جداول ایجاد شده دادههایی وارد کنید و درستی محدودیتهای گفته شده را بررسی نمایید.

ü در صورتی که بخواهیم بر روی ستونی از جدول از پیش ساختهشدهایی محدودیت Foreign Key اضافه کنیم از دو ساختار زیر میتوانیم استفاده کنیم:

ساختار 1:

ALTER TABLE <table_name>
ADD FOREIGN KEY (نام فیلد ارجاع) REFERENCES < نام جدول مرجع>(نام فیلد مرجع)

ساختار 2:

ALTER TABLE <table_name>
ADD CONSTRAINT <identity_name> FOREIGN KEY (نام فیلد ارجاع)

REFERENCES < نام جدول مرجع>(نام فیلد مرجع)

ü در صورتی که بخواهیم از ستونی از جدول از پیش ساخته شده محدودیت Foreign Key ر حذف کنیم به همان ساختار کلی گفتهشده در آزمایش قبل عمل میکنیم.

3- کد حذف همهی محدودیتهای Foreign key از جدول Select را بنویسید.

4- به صورت دستی محدودیت Foreign key را بر روی جدول Select قرار دهید.

3-2-2 محدودیت Check

ü نحوه قرار دادن محدودیت Check بر روی ستونی از جدول به یکی از دو فرم زیر امکان پذیر است:

ساختار 1:

CREATE TABLE <table_name>

(

<column_name1> data_type (size) CHECK (constraint),

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

...);

ساختار 2:

CREATE TABLE <table_name>

(

<column_name1> data_type(size) <constraint_name>,

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

...

CONSTRAINT <identity_name> CHECK (<constraint_on_column_name1>, < constraint_on_column_name2>,…)

…

);

5- کد جدولی به نام Student با مشخصات زیر را بنویسید:

Student(id: string, name: string, family: string, average: float, city: string, country: string)

· دادههای ستون id اعددی مثبت باشند.

· کاربر نتواند در ستون grade اعدادی که در بازهی 0 تا 20 نیستند وارد کند.

6- به جدول Student محدودیت های زیر را اضافه کنید.

· شهر دانشجو Isfahan نباشد یا کشور دانشجو Iran باشد.

7- به جدول Student به صورت دستی دادههای متضاد با محدودیتهای اعمال شده در آزمایش 5 و 6 وارد کنید و خطای رخ داده را بررسی کنید.

3-2-3 محدودیت Default

ü نحوه قرار دادن محدودیت Default بر روی ستونی از جدول به فرم زیر امکان پذیر است:

CREATE TABLE <table_name>

(

<column_name1> data_type (size) DEFAULT ‘<string>’,

<column_name2> data_type(size) <constraint_name>,

<column_name3> data_type(size) <constraint_name>,

...);

ü اگر بعد از ایجاد جدول تصمیم به اضافهکردن محدودیت Default به ستونی از جدول گرفتید به دو فرم کلی زیر میتوانید عمل کنید.

ساختار 1:

ALTER TABLE <table_name>
ADD DEFAULT ('< string >‘) For <column_name>

ساختار 2:

ALTER TABLE <table_name>
ADD CONSTRAINT <identity_name> DEFAULT ('< string >‘) For <column_name>

9- محدودیت Default ‘Isfahan’ را به ستون city از جدول Student اضافه کنید.

10- به جدول Student به صورت دستی داده وارد کنید و وضعیت ستون city را بررسی نمایید.

ü اگر بعد از ایجاد جدول تصمیم به حذف کردن محدودیت Default قرار دادهشده بر روی ستونی از جدول گرفتید به همان فرم کلی گفته شده در آزمایش قبل میتوانید عمل کنید.

13- به صورت دستی محدودیت Default country=’Iran’ را بر روی جدول Student قرار دهید.

3-2-4 محدودیت Identity

14-کد جدولی به نام Course را بنویسید و بر روی ستون id آن محدودیت identity قرار دهید.

Course(id: int, title: string)

15-به صورت دستی به جدول Course، 5 سطر داده وارد کنید سپس سطر پنجم را حذف کنید و سطر ششم را وارد کنید. ستون id چگونه عمل کرد؟

16-به صورت دستی بر روی ستون id از جدول Course محدودیت identity را به گونه ایی قرار دهید که از 950 شروع شود و به ازای هر id ، 10 عدد افزایش یابد.

17-به صورت دستی به جدول Course چهار سطر داده اضافه کنید.

3-3 گزارش کار

1. به سوالات مطرح شده در مقدمه پاسخ دهید.

2. گزارش کاملی از تمرینات انجام شده در کلاس بر روی پایگاه دادهایی به طراحی خودتان (متفاوت از پایگاه داده و جدول ایجاد شده در کلاس) بنویسید. گزارش باید دارای کد و توضیحات کامل در مورد کد هر تمرین، به همراه تصاویر اسکرینشات و توضیحات برای تمرینات بدون کد باشد.

4 آزمایش چهارم: SELECT

4-1 مقدمه

در این آزمایش با دستورات DML آشنا میشویم. اولین دستور مهم در این دسته از دستورات، دستور Select میباشد که در این جلسه با نحوهی بازیابی دادهها از جدول و قالبهای مختلف این دستور آشنا خواهیم شد.

در پایان این آزمایش شما باید بتوانید به سوالات زیر پاسخ دهید:

1. بنیادیترین دستورات DML را نام ببرید.

2. سادهترین قالب دستور Select چیست؟

3. برای تغییر برچسب یک فیلد در هنگام نمایش نتایج از چه کلمهی کلیدی استفاده میشود.

4. تفاوت کلمات کلیدی In و Between در دستور Select چیست؟

5. از کلمهکلیدی Distinct در دستور Select به چه منظور استفاده میشود؟

6. توابع جمعی مهم را نام ببرید. ورودی و خروجی هر تابع را مشخص کنید.

7. تفاوت Having و Where در دستور Select چیست؟

8. کاربرد کلمهی کلیدی Top در دستور Select چیست؟

4-2 دستور کار

1. پایگاه دادهایی(database) به نام UI964 ایجاد نمایید.

2. در پایگاه دادهی ساخته شده جداول زیر را ایجاد نمایید.

Student(id: int, name: string, average: float)

Course(id: int, name: string, prof: string)

Selection(st_id: int, co_id: int, grade:float)

3. در جداول ساخته شده دادههای زیر را وارد نمایید:

Selection			Course			Student
grade	co_id	st-id	prof	name	id	average	name	id
18	20	10	Barani	DB	20	19.5	Ali	10
14	21	11	Zamanifar	OS	21	14	Ahmad	11
15	22	12	zamani	SW	22	15.5	Reza	12
12	20	13				18	Sara	13
11	21	14				10	zahra	14
9	22	10
19	20	11
16	21	12
15	22	13
14	20	14
13	21	10
8	22	11

قالب کلی دستور Select به صورت زیر است:

SELECT [ALL|DISTINCT] [TOP (<expression>) [PERCENT]]

[FROM <source table(s)/view(s)>]

[WHERE <restrictive condition>]

[GROUP BY <column name or expression using a column in the SELECT list>]

[HAVING <restrictive condition based on the GROUP BY results>]

[ORDER BY <column list>]

4-2-1 Where

ü در دستور Select، بخش بعد از کلمه Where مشخص کنندهی شرط دستور میباشد که این شرط بررسیشده و نتیجهی آن در رکوردهای بازگشتی دستور منعکس میشود. بعد از کلمه Where یک عبارت شرطی بیان میشود که می تواند ترکیبی از شروط منطقی و حسابی باشد.

4. دانشجویی با شماره ی 10 چه نمراتی اخذ کرده است؟

5. کدام دانشجویان درس 21 یا 22 را با نمراتی بین 14 تا 16 پاس کردهاند؟

4-2-2 As

ü اگر بخواهیم برچسب نام یک فیلد را (هنگام نمایش نتایج پرس و جو) عوض کنیم میتوان از قالب زیر استفاده کرد.

Select نام فیلد As ‘برچسب جدید’

From نام جدول;

6. دادههای جدول Course را با برچسبهای زیر بازیابی نمایید:

Course(CID, Title, prof)

4-2-3 IN

ü از این کلمهی کلیدی در شرط وقتی استفاده می کنیم که مقدار یکی از فیلد ها را دقیقا تعیین کنیم. شما میتوانید بیش از یک مقدار را برای فیلد ها تعیین کنید و بین هر مقدار یک کاما بگذارید. مقادیر می توانند کاراکتر و عددی باشند .

SELECT <column_name>
FROM <table_name>

WHERE <column_name> IN ('value1', 'value2', ...)

ü اگر از عبارت Not IN بجای IN استفاده شود مقادیری که مخالف مجموعهی تعیین شده هستند انتخاب میشوند.

7. کدام دانشجویان درس 21 را با نمراتی به جز 14 ، 15 ، 16 پاس کردهاند؟

4-2-4 Between

ü کلمه Between در شرط کمک میکند تا بتوانیم یک بازه تعریف کنیم. در این حال تمام مقادیری که بین value1 و value2 باشند انتخاب میشوند.

SELECT <column_name>
FROM <table_name>

WHERE <column_name> BETWEEN 'value1' AND 'value2‘

ü اگر از عبارت Not Between استفاده شود تمام مقادیری که بین Value1 و Value2 نیستند انتخاب میشوند.

8. کدام دانشجویان درس 22 را با نمرهی بین 15 تا 20 پاس نکردهاند؟

4-2-5 Like

ü این کلمه نیز با کلمه where بکار میرود. اساساً Like به شما اجازه میدهد که جستجویی را بر مبنای عبارت باقاعده (Regular Expression) انجام دهید.

SELECT <column_name>
FROM <table_name>
WHERE <column_name> LIKE {PATTERN}

ü اگر از عبارت Not Like استفاده شود، عملی عکس عبارت Like اتفاق می افتد.

9. کدام دانشجویان نام آنها به a ختم می شود؟

4-2-6 Order By

ü برای مرتبسازی اطلاعات بازیابی شده بر حسب حروف یا اعداد موجود در یک ستون از عبارت ORDER BY استفاده میکنیم .

SELECT <column_name>

FROM <table_name>

[WHERE <condition>]

ORDER BY <column_name> [ASC, DESC]

ü ASC به معنای صعودی بودن (a to z) و DESC به معنای نزولی بودن است (z to a).پیش فرضش ASC است.

10. دانشجویان را به ترتیب نمراتی که در درس 22 کسب کردهاند به صورت صعودی مرتب کنید، در صورتی که دو نمره یکسان باشند برحسب id دانشجو مرتب شوند.

4-2-7 Distinct

ü اگر بخواهیم اطلاعات تکراری بازیابی شدهی ستونی نمایش داده نشوند باید از عبارت Distinct استفاده کنیم.

SELECT DISTINCT <Column_name> FROM <Table_Name>

11. دانشجویان چه نمراتی اخذ کرده اند؟ (با حذف مقادیر تکراری)

4-2-8 Aggregation Functions

ü در SQL می توان از توابع آمادهی جمعی (محاسباتی و ریاضی) در دستور Select استفاده کرد. به عنوان مثال اگر بخواهیم میانگین مقادیر یک فیلد عددی را محاسبه کنیم و یا مثلاً تعداد رکوردهای بازگشتی از یک پرسوجو را بدست آوریم میتوانیم از توابع آمادهای که به همین منظور ایجاد شدهاند استفاده کنیم.

ü مهمترین توابع جمعی مورد استفاده در SQL عبارتند از Count، Max، Min، Avg، Sum.

12. میانگین نمرات در جدول انتخاب واحد را نمایش دهید.

13. تعداد نمرات غیر تکراری در جدول انتخاب واحد را نمایش دهید.

4-2-9 Group By

ü دستور Group By باعث میشود تا اطلاعات بازیابیشده بر اساس یک یا چند ستون گروهبندی شوند.

SELECT <column_name1>, Function(<column_name2>)

FROM <table_name>

GROUP BY <column_name1>

14. میانگین هر درس را نمایش دهید.

4-2-10 Having

• اگر بخواهیم بر روی گروههای ایجادشده توسط Group By شرطی قرار دهیم، از عبارت Having استفاده میکنیم.

SELECT <column_name1>, Function(<column_name2>)

FROM <table_name>

GROUP BY <column_name1>

HAVING (arithmetic function condition)

15. میانگین نمرات دانشجوی 21 و 22 را نمایش دهید.

16. میانگین نمرات دانشجویی که ‌مجموع نمراتش کمتر از 40 است را نمایش دهید.

4-2-11 Top

با استفاده از Top در دستور Select میتوان تعداد یا درصد مشخصی از سطرها را بازیابی نمود.

SELECT TOP (<expression>) [PERCENT] <column list>

FROM <source table>

17. بدون استفاده از دستور Max مشخص کنید کدام دانشجو بالاترین نمره را کسب کردهاست.

4-3 گزارش کار

1. به سوالات مطرح شده در مقدمه پاسخ دهید.

2. گزارش کاملی از تمرینات انجام شده در کلاس بنویسید. گزارش باید دارای کد و توضیحات کامل در مورد کد هر تمرین باشد.

5 آزمایش پنجم: JOIN (CROSS، INNER، SELF)

5-1 مقدمه

در این آزمایش با مفهوم پیوند جداول و انواع پیوند بین دو جدول آشنا خواهیم شد.

در پایان این آزمایش شما باید بتوانید به سوالات زیر پاسخ دهید:

1. انواع پیوندهایی که میتوان بین جداول تعریف نمود را نام ببرید.

2. Cross Join چگونه جداول را Join میکند؟

3. یک Cross Join چگونه به Inner Join تبدیل میشود؟

4. Inner Join چگونه جداول را Join میکند؟

5. شرط اصلی یک Inner Join چیست؟

6. انواع Outter Join را نام برده و هریک را توضیح دهید.

5-2 دستور کار

ü از دستورJoin برای پیوند جدولها در پایگاه دادهها استفاده میشود.

ü انواع دستورات join عبارتاند از:

· Cross Join

· Inner Join

· Self-join

· Outer Join

5-2-1 Cross Join

1. یک Cross Join دو جدول درگیر در پیوند را در یکدیگر ضرب کارتزین میکند. به این صورت که تمام سطرهای جدول اول را با تمام سطرهای جدول دوم ترکیب میکند. برای Cross Join نمودن دو جدول از دو ساختار زیر میتوان استفاده نمود:

ساختار 1:

SELECT <column_names>

FROM <table_name1>

CROSS JOIN <table_name2>

ساختار 2:

SELECT <column_names>

FROM <table_name1, table_name2,…>

· تمرینات مطرح شده در این آزمایش را بر روی پایگاه دادهی UI964 که در آزمایش چهارم ساختهاید اجرا کنید.

1. دو جدول دانشجو و درس را ضرب کارتزین کنید.

2. سه جدول دانشجو، درس و انتخاب را ضرب کارتزین کنید

5-2-2 Inner Join

2. در این Join پیوند دو جدول بر اساس مقادیر یک ستون صورت میگیرد. Inner join سطرهایی را بر می گرداند که در شرط Join صدق کنند و سطرهایی که در شرط صدق نمیکنند را حذف می کند. برای Inner Join نمودن دو جدول از سه ساختار زیر میتوان استفاده نمود:

ساختار 1:

SELECT <column_names>
FROM <table_name1>
INNER JOIN <table_name2>
ON table_name1.column_name=table_name2.column_name

ساختار 2:

SELECT <column_names>
FROM <table_name1>
JOIN <table_name2>
ON table_name1.column_name=table_name2.column_name

ساختار 3:

SELECT <column_names>
FROM <table_name1, table_name2,…>
WHERE table_name1.column_name=table_name2.column_name

3. هر دانشجویی (نام دانشجو) چه نمراتی اخذ کرده است؟

4. هر دانشجو (نام دانشجو)کدام نمراتش از معدلش بیشتر است؟

5. میانگین نمرات دانشجویان هر استاد را محاسبه کنید.

6. هر دانشجویی چه دروسی را اخذ کرده است؟

7. دانشجویان هر استاد را نمایش دهید.

8. هر دانشجو (نام دانشجو) در کدام درس نمره ایی که کسب کرده از معدلش بیشتر است؟

9. هر دانشجو (نام دانشجو) چه دروسی را اخذ نکردهاست؟

10. میانگین نمرات کسب شده توسط هر دانشجو (نام دانشجو) را نمایش دهید.

11. کدام دانشجو (نام دانشجو) در ترم جاری معدل بهتری نسبت به معدل کلش به دست آوردهاست؟

12. در هر درس کدام دانشجو (نام دانشجو) بالاترین نمره را کسب کردهاست؟

5-2-3 Self Join

Self Join یک جدول را با خودشJoin میکند. از همان قالبهای Inner Join برای Self Join نمودن دو جدول استفاده میشود با این تفاوت که تنها یک جدول درگیر این نوع پیوند است:

ساختار 1:

SELECT <column_names>
FROM <table_name> as <alias_name1>
INNER JOIN <table_name> as <alias_name2>
ON table_name1.column_name=table_name2.column_name

ساختار 2:

SELECT <column_names>
FROM <table_name> as <alias_name1>
JOIN <table_name> as <alias_name2>
ON alias_name1.column_name= alias_name2.column_name

ساختار 3:

SELECT <column_names>
FROM <table_name> as <alias_name1>, <table_name> as <alias_name2>
WHERE alias_name1.column_name= alias_name2.column_name

1. جدول زیر را ایجاد کنید و به سوالات مطرح شده پاسخ دهید.

mg_id	name	emp_id
2	Ali	1
1	Ahmad	2
1	Reza	3
1	Sara	4
2	Zahra	5
3	Mina	6
3	Hamed	7

13. نام هر کارمند را به همراه نام مدیرش نمایش دهید. (خروجی: ستون اول نام کارمند، ستون دوم نام مدیر)

14. نام هر مدیر را به همراه تعداد کارمندانش نمایش دهید.

· برای حل ادامهی تمرینات از پایگاه دادهی UI964 استفاده کنید:

15. معدل هر دانشجو را با سایر دانشجویان دو به دو مقایسه کنید و در جدولی نمایش دهید. در جدول ابتدا نام دانشجو با معدل بیشتر و سپس نام دانشجو با معدل کمتر سپس معدل دانشجوی اول و در نهایت معدل دانشجوی دوم.

16. مجموع نمرات هر دانشجو را با سایر دانشجویان دو به دو مقایسه کنید و در جدولی نمایش دهید. در جدول ابتدا نام دانشجو با مجموع نمرات بیشتر و سپس نام دانشجو با مجموع نمرات کمتر سپس مجموع نمرات دانشجوی اول و در نهایت مجموع نمرات دانشجوی دوم.

17. نمره ی کدام دانشجویان از نمرهایی که دانشجوی شماره 14 در درس با آی دی 20 اخذ کردهاست، بیشتر است؟

18. هر دانشجویی چه تعداد درس اخذ کردهاست؟

5-3 گزارش کار

1. به سوالات مطرح شده در مقدمه پاسخ دهید.

2. گزارش کاملی از تمرینات انجام شده در کلاس بنویسید. گزارش باید دارای کد و توضیحات کامل در مورد کد هر تمرین باشد.

6 آزمایش ششم: OUTER JOIN، INSERT، UPDATE، DELETE

6-1 مقدمه

در این آزمایش با ادامهی دستورات DML آشنا میشویم. در دو آزمایش قبل با دستور Select و سه نوع Join بین جداول که به کمک این دستور میتوان ایجاد کرد به طور کامل آشنا شدیم. در این آزمایش با آخرین نوع Join یعنی Outer Join آشنا میشویم و در ادامه به سایر دستورات باقی مانده در گروه DML خواهیم پرداخت.

در پایان این آزمایش شما باید بتوانید به سوال زیر پاسخ دهید:

1. انواع Outer Join را نام برده و هریک را توضیح دهید.

6-2 دستور کار

6-2-1 Outer Join

ü این نوع Join به سه دسته تقسیم می شود:

· Left Outer join

· Right Outer join

· Full Outer join

1. بر روی دو جدول Student و Selection هر سه نوع Outer Join را به کار ببرید و نتایج حاصل را بررسی کنید.

6-2-2 Insert

ü از دستور Insert برای درج یک سطر یا چند سطر به یک جدول استفاده میشود. قالب کلی دستور Insert به صورت زیر است:

INSERT [TOP ( <expression> ) [PERCENT] ] [INTO] <tabular object>

[(<column list>)]

[ OUTPUT <output clause> ]

{ VALUES (<data values>) [,(<data values>)] [, …n]

| <table source>

| EXEC <prodecure>

| DEFAULT VALUES

2. به جدول Student سه سطر با دادههای زیر اضافه کنید.

average	name	Id
16.4	Mohammad	15
13	Hamed	16
15	Hossein	17

6-2-3 Update

ü از دستور Update برای ویرایش داده های یک یا چند رکورد استفاده می شود. قالب کلی دستور Update به صورت زیر است:

UPDATE [TOP ( <expression> ) [PERCENT] ] <tabular object>

SET <column> = <value>[WRITE(<expression>, <offset>, <length>)]

[,<column> = <value>[.WRITE(<expression>, <offset>, <length>)]]

[ OUTPUT <output clause> ]

[FROM <source table(s)>]

[WHERE <restrictive condition>]

3. به معدل علی یک نمره اضافه شود.

4. از نمره ی درس دیتابیس که توسط احمد اخذ شده یک نمره کم شود.

5. دانشجویی که میانگین نمراتش از پانزده بیشتر است یک نمره به معدلش اضافه شود.

6. به کسانی که دارای معدل کل بالای شانزده هستند نیم نمره به نمرهی درسSWی آنها اضافه کنید و در نهایت همهی مشخصات دانشجویان مشمول این شرط را نمایش دهید.

6-2-4 Delete

ü از دستور Delete برای پاک کردن یک یا چند رکورد از جدول استفاده میشود و ساختار کلی آن به صورت زیر است:

DELETE [TOP ( <expression> ) [PERCENT] ]

[FROM] <tabular object>

[OUTPUT <output clause> ]

[FROM <table or join condition>]

[WHERE <search condition> | CURRENT OF [GLOBAL] <cursor name>]

1. از جدول Selection دانشجویان با نمرات کمتر از 11 را حذف کنید.

2. از جدول Student دانشجویی را حذف کنید که مجموع نمراتش از شصت کمتر است.

3. پنج نمرهی پایین در جدول Selection را حذف کنید.

6-3 گزارش کار

3. به سوالات مطرح شده در مقدمه پاسخ دهید.

4. گزارش کاملی از تمرینات انجام شده در کلاس بنویسید. گزارش باید دارای کد و توضیحات کامل در مورد کد هر تمرین باشد.

7 آزمایش هفتم: STORED PROCEDURE

7-1 مقدمه

در این آزمایش به بررسی نحوهی ایجاد و استفاده از روالهای ذخیره شده یا Stored Procedures میپردازیم SP ها زیربرنامههایی هستند که در بانک اطلاعاتی به عنوان یک شیء ذخیره میشوند و در زمان مورد نیاز به درخواست کاربر استفاده میشوند.

در پایان این آزمایش شما باید بتوانید به سوالات زیر پاسخ دهید:

1. مزایای استفاده از SP چیست؟

2. برای مشاهدهی لیست همهی روالهای سیستمی از چه مسیری باید وارد شویم؟

3. برای مشاهدهی و یا تغییر کد یک روال سیستمی چه باید کرد؟

4. مسیر wizard ایجاد یک SP استاندارد چگونه است؟

7-2 دستور کار

انواع SP ها عبارتاند از:

ü SP های سیستمی

ü SP های استاندارد

7-2-1 SP های سیستمی

در SQL تعداد زیادی روال ذخیرهشده وجود دارد که به آنها روالهای سیستمی میگوییم. این روالها با SP_ شروع میشوند.

1. پایگاه دادهایی به نام UI967 ایجاد کنید.

2. کدهای زیر را اجرا کنید و نتیجهی هریک را بیان کنید.

- SP_renamedb UI967,Test

- SP_dbremove test

- SP_helpdb

7-2-2 SP های استاندارد

ü سادهترین ساختار یک روال بدون پارامترهای ورودی و خروجی به صورت زیر است:

CREATE PROCEDURE | PROC <proc name>

<sql-code>

1. روال به نام ShowCourses بنویسید که همهی دروس ارائهشده را نمایش دهد.

ü حال برای اجرای یک روال کافیست کد زیر را اجرا کنید و یا تنها نام روال ذخیرهشده را نوشته و اجرا کنید.

EXECUTE | EXEC <stored_procedure_name>

2. روال ShowCourses در تمرین 1 را اجرا کنید.

ü برای حذف یک روال از دستور زیر استفاده کنید.

DROP PROCEDURE <stored_procedure_name>

ü در تعریف روالهای ذخیرهشده میتوانید پارامترهای ورودی و خروجی نیز برای آنها تعیین نمایید که برای این منظور از ساختار زیر استفاده میشود.

CREATE PROCEDURE | PROC <proc name>

[<parameter name> <data type> [=<default value>] [OUT [PUT]] ]

<sql-code>

3. روال به نام CalculateAvg بنویسید که نام دانشجویی را دریافت میکند و معدلش را محاسبه میکند.

ü برای فراخوانی روال دارای پارامتر ابتدا نام روال و سپس پارامترها (ورودی و خروجی) را وارد میکنیم.

4. روال CalculateAvg را به نحوی فراخوانی کنید که معدل دانشجویی به نام علی را محاسبه کند.

7-2-3 ساختار شرطی IF…ELSE

ü از ساختار شرطی IF…ELSE در Sql نیز میتوان استفاده کرد که ساختار کلی آن به صورت زیر است.

IF <condition>

Begin

<sql-code>

End

Else

Begin

<sql-code>

End

5. روالی به نام CalAvgAli بنویسید که نام شخصی را ازورودی دریافت میکند. اگر نام شخص علی بود معدل ترم او را محاسبه میکند در غیر این صورت معدل کل دانشجویان را محاسبه میکند، سپس روال را اجرا کنید.

6. روالی به نام CalAvgCourse بنویسید که نام درسی را به عنوان ورودی دریافت میکند و میانگین نمرات اخذ شده در آن درس را محاسبه و حاصل را به برنامه اصلی بر میگرداند، سپس روال را اجرا کنید و در برنامهی اصلی خروجی روال چاپ شود.

7. روالی به نام GradeAndSum بنویسید که نام شخص و نام درسی را به عنوان ورودی میگیرد و نمرهی آن شخص در آن درس و مجموع نمرات کل آن شخص را محاسبه میکند و حاصل را به برنامه اصلی بر میگرداند، در نهایت روال را اجرا کنید.

7-2-4 حلقهی تکرار WHILE

از حلقهی تکرار WHILE در Sql نیز میتوان استفاده کرد که ساختار کلی آن به صورت زیر است.

WHILE <condition>

Begin

<sql-code>

End

8. روالی بنویسید که دو عدد را به عنوان دو پارامتر دریافت میکند و عدد اول را به توان عدد دوم میرساند و خروجی را به برنامه اصلی بر میگرداند، در نهایت روال را اجرا کنید و در برنامهی اصلی خروجی روال چاپ شود.

7-3 گزارش کار

1. به سوالات مطرحشده در مقدمه پاسخ دهید.

2. گزارش کاملی از تمرینات انجام شده در کلاس بنویسید. گزارش باید دارای کد و توضیحات کامل در مورد کد هر تمرین باشد.

8 آزمایش هشتم: FUNCTION، TRANSACTION

8-1 مقدمه

در این آزمایش به بررسی نحوهی ایجاد و استفاده از Functionها و تفاوت آنها با SPها خواهیم پرداخت. در ادامه با تراکنش و مزایای استفاده از آن آشنا خواهیمشد.

در پایان این آزمایش شما باید بتوانید به سوالات زیر پاسخ دهید:

1. تفاوت Function و SP در SQL چیست؟

2. مقدار برگشتی یک Function چند نوع میتواند باشد؟

3. خواص یک تراکنش چیست؟

4. در SQL چند نوع تراکنش وجود دارد آنها را نام برده و توضیح دهید.

8-2 دستور کار

انواع Function ها در SQL عبارتاند از:

ü توابع داخلی

ü توابع تعریفشده توسط کاربر

8-2-1 توابع داخلی

در SQL تعدادی تابع آماده وجود دارد که به آنها توابع داخلی میگوییم و شما میتوانید از آنها استفاده کنید.

1. توابع داخلی زیر را اجرا کنید و نتیجهی هریک را بیان کنید.

- Select USER

- Select GETDATE()

- Select HOST_NAME()

- Select SUSER_NAME()

8-2-2 توابع تعریفشده توسط کاربر

ü مقدار برگشتی یک تابع ممکن است از نوع اسکالر و یا از نوع جدول باشد. اگر مقدار برگشتی تابع از نوع اسکالر باشد، برای نوشتن این نوع تابع باید از ساختار زیر استفاده کنیم:

CREATE FUNCTION <function_name> ([<parameter name> <data type>])

RETURNS <data type>

BEGIN

<sql-code>

RETURN <value>

END

1. تابعی به نام AddTwoNumbers بنویسید که دو عدد را از ورودی دریافت و خروجی تابع، حاصل جمع آن دو عدد باشد.

ü حال برای صدا زدن تابع فوق میتوانید از ساختار زیر استفاده کنید.

SELECT DBO.function_name([<parameters>])

2. تابع AddTwoNumbers را اجرا کنید.

ü اگر مقدار برگشتی تابع از نوع جدول باشد باید از ساختار زیر برای نوشتن آن استفاده کنیم.

CREATE FUNCTION <function_name> ([<parameter name> <data type>])

RETURNS Table

RETURN <table>

3. تابعی به نام ShowProfInf بنویسید که دانشجویان هر استاد را نمایش دهد.

ü حال برای صدا زدن تابع فوق میتوانید از ساختار زیر استفاده کنید.

SELECT * FROM DBO.function_name([<parameters>])

4. تابع ShowProfInf را اجرا کنید به نحوی که نام اساتید دانشجویی به نام ‘Ali’ را نمایش دهد.

8-2-3 TRANSACTION

ü تراکنش یک یا چند دستور SQL مانند به روزرسانی، درج و یا حذف یک یا چندین سطر از داده ها که با همدیگر یک واحد منظقی از کار را شکل می دهند، میباشد. این دستورات SQL که شکل دهندهی تراکنش هستند، نوعا کاملا مرتبط به هم هستند و اعمال وابسته به هم را انجام میدهند. هر دستور در تراکنش قسمتی از یک کار را انجام میدهد، اما برای تکمیل کار، اجرای همهی دستورات مذکور ضروری هستند.

ساختار کلی یک تراکنش به صورت زیر تعریف میشود:

BEGIN TRAN | TRANSACTION [<transaction name>]

<sql-code>

COMMIT TRAN[<transaction name>] | ROLLBACK TRAN[<transaction name]

5. در قالب تراکنش دو نمره از همهی دانشجویان کم کرده و در نهایت تراکنش Commit شود. تغییرات جدول را بررسی کنید.

6. در قالب تراکنش معدل دانشجویی به نام علی صفر شود و در نهایت تراکنش RollBack شود. تغییرات جدول را بررسی کنید.

7. تراکنشی طراحی کنید که دو عمل آپدیت انجام دهد اما کد آپدیت دوم دارای باگ باشد. بررسی کنید آیا عمل آپدیت اول جدول مورد نظر را تغییر دادهاست؟

8. تراکنشی طراحی کنید که دارای دو عمل آپدیت باشد، اگر هرکدام از عملیات آپدیت نتوانست بر روی هیچ سطری اثر گذارد کل تراکنش باید لغو شود.

ü دستور SAVE TRANSACTION یک نقطهی ذخیرهسازی تغییرات یا savepoint در یک تراکنش ایجاد میکند و وضعیت بانک اطلاعاتی را در موقعیت فعلی ذخیره کرده و به موقعیت ذخیرهشده، یک نام اختصاص می دهد.

SAVE TRAN | TRANSACTION [<save point name>]

9. تراکنشی طراحی کنید که دو عمل درج انجام دهد اما عمل درج دوم را لغوکند. نتیجهی اجرای تراکنش بر روی جدول را بررسی کنید.

10. تراکنشی بنویسید که دانشجویی به نام علی با شماره دانشجویی و معدل 13 و 18 را در صورتی به جدول Student اضافه کند که چنین نام دانشجویی در جدول وجود نداشته باشد.

8-3 گزارش کار

1. به سوالات مطرح شده در مقدمه پاسخ دهید.

2. گزارش کاملی از تمرینات انجام شده در کلاس بنویسید. گزارش باید دارای کد و توضیحات کامل در مورد کد هر تمرین باشد.

9 آزمایش نهم: اتصال C# به SQL

9-1 مقدمه

در این آزمایش با نحوهی نوشتن برنامههای کوچک و مبتنی بر ویندوز به زبان سی شارپ در محیط visual studio و نحوهی اتصال آنها به دیتابیس (data base) آشنا خواهیمشد و دستورات مختلفی از جمله درج، به روز رسانی، حذف و بازیابی که در آزمایشهای قبلی با آنها آشنا شدیم را بر روی دیتابیس از طریق برنامه اعمال خواهیم نمود.

در پایان این آزمایش شما باید بتوانید به سوال زیر پاسخ دهید:

5. مراحل اتصال C# به SQL را به صورت کامل توضیح دهید.

9-2 دستور کار

ü مراحل اتصال یک برنامهی C# به SQL عبارت است از:

1. Create A Connection

2. Create A Command

3. Create A DataReader

4. Specify connection string

5. Specify Connection for the Command

6. Specify the CommandText for Command

7. Add values to command parameters (if any).

8. Open the Connection

9. Execute DataReader

10. Read every row from the result set

11. Close the Reader

12. Close the Connection

که کد هر مرحله در C# عبارت است از:

1. SqlConnection connection = new SqlConnection();

2. SqlCommand command = new SqlCommand();

3. SqlDataReader reader;

4. connection.ConnectionString= "Data Source=(local); Initial Catalog=AdventureWorks2008; Integrated Security=SSPI";

5. command.Connection = connection;

6. 6.command.CommandText = "SELECT JobTitle, BusinessEntityID FROM HumanResources.Employee";

7. //…

8. connection.Open();

9. reader = command.ExecuteReader();

10. while (reader.Read()) {

string jobtitle = reader[" JobTitle "].ToString();

int id= Convert.ToInt32(reader[" BusinessEntityID "]);

List1.Items.Add(String.Format("{0},{1}", jobtitle,id));

}

11. reader.Close();

12. connection.Close();

1. برنامهایی بنویسید که دارای یک لیست باکس و یک دکمه به نام نمایش باشد. با کلیک بر روی دکمه، کلیهی دادههای موجود در جدول Student در لیست باکس نمایش داده شود.

2. برنامهایی بنویسید که دارای یک لیست باکس، دو تکست باکس به نامهای name و id، یک دکمه به نام درج باشد. به محض اجرای برنامه تمام اطلاعات جدول Student در لیست باکس نمایش داده شود و با نوشتن نام و شماره دانشجویی یک دانشجوی جدید در تکست باکسها و کلیک بر روی دکمهی درج، دانشجوی جدید به جدول دانشجو اضافه و لیست باکس تازهسازی شده و اطلاعات را مجددا نمایش دهد.