آموزش گام به گام SQL Server

سلام

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

 

در پایگاه داده SQL Server  دستوراتی به منظور اعمال تغییرات در داده ها تعبیـه شده است دستوراتی که می توان داده ها را به یک جدول ارسال یا تغییراتی بر رکوردهائی در جداول اعمال نمود از طرفی اکثریت Object های درون پایگاه داده قابلیت تصحیح (Alter) ، حذف (Drop) و ایجاد (Create)  را بااستفاده از دستورات برنامه نویسی در اختیار قرار می دهند که در این فصل به این موضوع پرداخته خواهد شد اما مهمترین دستور بدون شک در پایگاه داده SQL Server  دستور Select  به منظور استخراج داده ها از یک یا چند جدول پایگاه داده است که بطور مفصل در این فصل مورد بررسی قرار خواهد گرفت.

 

  در پایگاه داده SQL Server مشابه کلیه زبانهای برنامه نویسی OOP به هر Object درون پایگاه داده با ساختار کلی ذیل می توانید دسترسی داشته باشید.

 

[ [ DataBaseName ]Owner]ObjectName[ …]

 

 

بعنوان مثال درنظر بگیرید که در پایگاه داده Master  که از پایگاه داده های مهم خود SQL Server  است می خواهید به یک فیلد جدول درون پایگاه داده MyDB  دسترسی داشته باشید بدین منظور                  می نویسیم :

MyDB.dbo.MyTBL.MyFld

 

 

درصورتیکه از نوشتن نام Owner صرف نظر گردد و پیش فرض کاربر ورودی خواهد بود و درصورتیکه با dbo به پایگاه داده متصل شده باشیم خواهیم داشت :

MyDB..MyTBL.MyFld

 

در پست بعدی نحوه اعمال تغییرات در این آبجکت ها رو بیان می کنم.

موفق باشید.

سلام

 

  کلیه Object های اصلی درون پایگاه داده با استفاده از دستور Create ( ایجاد ) , Alter ( تصحیح ) Drop , ( حذف ) تغییرات را به پایگاه داده منعکس می نمایند اما باتوجه به اینکه عموماً در طراحی پایگاه داده به ندرت تغییرات در Object ها از طریق برنامه نویسی به وقوع می پیوندد و عموماً در مواردی اعمال تغییرات از طریق برنامه نویسی برای جدول متصور است      بدین منظور جزئیات این دستورات برای جداول مدنظر قرار خواهد گرفت اما دستورات Drop,Alter,Create  از طریق کدنویسی برای کلیه Object های اصلی قابل اجرا نمی باشد که جزئیات آن در جدول ذیل ارائه می گردد :

 

براساس جدول Object از نوع Index دستور Alter  جهت اصلاح ایندکس را شامل نمی شود و برای این منظور ابتدا می بایستی این Object توسط دستور Drop  حذف و سپس مجدداً با دستور Create  ایجاد گردد از طرفی این شرایط برای مقادیر پیش فرض برای یک فیلد بنام Default  نیز صادق است و تنها عملیات Create وDrop  برای آن متصور است درخصوص داده طراحی شده خاص توسط         برنامه نویس UDDT و ESP ها که قبلاً به آنها اشاره شده است از طریق کدنویسی هیچ عملیاتی با استفاده از سه دستور Drop , Alter , Create  قابل انجام نخواهد بود و برای ESP فرآیند ایجاد و حذف این Object از طریق روش های دیگری نظیر استفاده از SP های طراحی شده درون پایگاه داده مقدور خواهند بود.

 

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

موفق باشید

سلام دوباره

ایجاد جدول :

همانطورکه قبلاً نیز اشاره شد ایجاد Object های اصلی در پایگاه داده در اکثریت موارد توسط برنامه نویسان توسط Wizard ها صورت می گیرد که محیط EnterPrise Manager  یک نمونه آن است براساس توضیحات ارائه شده درصورتیکه با استفاده از کدنویسی قرار است جدولی به پایگاه داده کنونی اضافه گردد. Create Table  به شکل عمومی و خلاصه شده ذیل قابل ارائه می باشد :

 (نام جدول    Create Table

] Unique یا  Primary Key [  ] Not Null یا [ Null  فرمول محاسباتی AS  یا نوع داده   نام ستون

] مقدار پیش فرض [ Default  ] NonClustered  یا [ Clustered  

] شرط محدودیت ستون [ Check

[ , 

.

.

.

 

]  n

 ) 

همانطور که ملاحظه می گردد با دستور Create Table  یک جدول بصورت فیزیکی در پایگاه داده کنونی ایجاد می گردد که نکات ذیل در این خصوص می بایستی مدنظر قرار گیرد.

-  درصورتیکه قرار است یک فیلد اطلاعاتی در جدول اشاره شده ایجاد گردد نوع داده می بایستی ارائه گردد ولی اگر بجای نوع داده از فرمول محاسباتی استفاده گردد درون جدول اشاره شده ازنظر فیزیکی هیچ ستونی ایجاد نمی گردد.

-  هر ستون ایجاد شده در این دستور می تواند بصورت پیش فرض Null  باشد.

-  درصورتیکه ستون ایجاد شده یک کلید است می توانید از Primary Key  استفاده نمائید ولی ممکن است ستون موردنظر تنها یک مقدار منحصر به فرد باشد که با استفاده از Unique قابل تنظیم خواهد بود.

-  درصورتیکه یک Primary Key ایجاد گردد یک ایندکس به جدول اشاره شده اضافه می گردد که می تواند یک ایندکس از نوع Clustered  یا NonClustered باشد که بصورت پیش فرض درصورت عدم ارائه NonClustered  درنظر گرفته خواهد شد.

-  هر ستون ایجاد شده درصورتیکه یک رکورد به جدول اضافه گردد درصورت عدم ارائه مقدار با مقدار تعیین شده توسط Default  جانشین خواهد شد.

-  با استفاده از کلمه Check می توان محدودیت های لازم را برای فیلد تعریف شده در زمان ثبت اطلاعات ایجاد نمود.

-  تعداد ستونها و طول هر رکورد براساس ظرفیت های ارائه شده در بخش ظرفیت پایگاه داده قبلاً  اعلام شده است.

 

در پست بعدی مثال هایی از ایجاد جدول رو بیان خواهم کرد.

موفق باشید

سلام

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

 

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

 اگر فرض کنیم نام جدول Person باشد در این صورت خواهیم داشت :

 

Create   Table   Person  (

              PID   Int   Not  Null  Primary Key  Clustered ,

PName    nVarChar(20)   Not   Null ,

PFamily  nVarChar(30)   Not   Null ,

PFather   nVarChar(20) ,

BCity      SmallInt ,

UnitID    SmallInt   Not  Null )

 

 

در این دستور فیلد PID  که شماره کارمندی پرسنل می باشد بعنوان Primary Key  تعریف شده است که براین اساس نمی تواند Null  باشد.

بااستفاده از دستور Create Table  یک جدول پایه واحد سازمانی در سیستم پرسنلی ایجاد نمائید.

درصورتیکه فرض شود که نام جدول موردنظر Unit باشد بنابراین خواهیم داشت :

 

Create   Table   Unit (

UntiID   SmallInt  Not  Null  Primary  Key  Clustered ,

UnitName   nVarChar (100)  Not  Null

)

 

یک جدول که دارای اطلاعات سه ضلع یک مثلث قائم الزاویه است با دستور Create Table ایجاد نمائید.

باتوجه به اینکه رابطه a = b + c  بین اضلاع مثلث برقرار است بنابراین ضلع a یک ستون محاسباتی تلقی می گردد. بنابراین به شکل ذیل عمل خواهیم نمود.

 

Create   Table   Polygon (

b    Real   Not  Null ,

c    Real   Not  Null ,

a    As  SQRT (Power (b,2) + Power (c,2) )

)

 

 

همانطور که مشاهده می گردد چون ستون a  تابعی محاسباتی از فیلدهای c , b  می باشد بنابراین طبق دستور بصورت یک ستون محاسباتی ظاهر شده است بدیهی است که این ستون درون جدول از نظر فیزیکی هیچ فضائی را اشغال نخواهد کرد و درصورت دریافت براساس فرمول ارائه شده تعیین می گردد.

 

بااستفاده از دستورCreate Table جدولی برای نسبت افراد تحت تکفل و افراد تحت تکفل شخص در سیستم پرسنلی طراحی نمائید. این جدول دارای دو فیلد اطلاعاتی کد نسبت و نام نسبت می باشد که کد نسبت بعنوان PK  عمل می کند اگر نام جدول را Relate  بنامیم آنگاه خواهیم داشت :

 

Create  Table  Relate (

RelateID  TinyInt  Not Null Primary Key Clustered ,

RelateName nVarChar (15)  Not Null )

همچنین درصورتیکه نام جدول افراد تحت تکفل شخص PrsChild باشد خواهیم داشت :

 

Create  Table  PrsChild (

PID  Int  Not  Null ,

RelateID  TinyInt  Default 1  Check  (RelateID > = 1),

CName   nVarChar (20)  Not  Null ,

CFamily  nVarChar (30) Not  Null ,

CFather  nVarChar (20) ,

BCity  SmallInt )

 

در پست بعد نحوه ایجاد تغییرات در جدول ایجاد شده رو توسط دستورات خواهم گفت.

موفق باشید

سلام

نحوه ایجاد تغییرات در جدول :

 

دستور Alter Table  به منظورتغییر در ساختار یک جدول بکار می رود و در اشکال خلاصه شده ذیل بادرنظر گرفتن محدودیت هائی مورد استفاده قرار می گیرد.

 

-         تصحیح یک ستون جدول

نام جدول  Alter Table

] Not Null  یا Null  [  نوع ستون     نام ستون Alter Column

 

-         ایجاد ستون های جدید در جدول

نام جدول  Alter Table

فرمول محاسباتی    یا    نوع ستون       نام ستون    ADD

] , … n [

 

-         حذف ستون هایی از جدول

     نام جدول  Alter Table

 ] [ , …  نام ستون    Drop    Column 

برنامه ای بنویسید که به جدول Person یک فیلد جدید بنام کد ملی اضافه کند.

در اینصورت از ساختار ADD  به شکل ذیل استفاده می کنیم.

 

Begin  Tran

Alter  Table  Person

ADD  NationalCode VarChar (10)

Commit  Tran

بااستفاده از ساختار Alter  فیلد PFather  را افزایش طول دهید.

بدین منظور از ساختار  Alter Column استفاده می نمائیم.

Begin  Tran

Alter  Table  Person

Alter Column  PFather  nVarChar (25) Not  Null

Commit

 

 

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

 

Begin  Tran

Alter  Table  Person

Drop  Column  NationalCode

Commit  TransAction

 

در پست های بعدی با ما همراه باشید.

موفق باشید

سلام

این پست کوتاهه. به دلیل اینکه مطلب پست بعدی زیاده من خواستم اونو توی یک پست بگم.

 

دستور حذف جدول :

همانطور که قبلاً نیز اشاره شده از دستور Drop  به منظور حذف یک Object  از پایگاه داده استفاده می گردد دستور Drop  برای حذف یک جدول از پایگاه داده به شکل کلی ذیل قابل ارائه می باشد.

نام جدول Drop  Table

 

دستوری بنویسید که جدول پرسنلی از فهرست جداول سیستم پرسنلی حذف گردد. بدین منظور  می نویسیم :

 

Drop  Table  Person

 

در ادامه نحوه select کردن از بانک اطلاعاتی رو بیان می کنم.

موفق باشید

سلام

 

بدون شک دستور Select  از مهمترین دستورات در SQL  است، این دستور جهت استخراج  داده ها از یک یا چند جدول تحت شرایطی بکار برده می شود شکل کلی دستور Select  بصورت خلاصه و کاربردی بصورت ذیل قابل ارائه است:

 

, …  ستون ] = نام مستعار ستون [    یا    ] نام مستعار ستون [ [AS]  ستونها     یا   Select [Top  n]  

] نام جدول جدید    [ Into

] بخش اتصال به سایر جداول [ ] نام مستعار [ [AS]  نام جدول اصلی From

]  عبارت شرطی   [ Where

] ستون های قابل گروه بندی [ Group By

] عبارت شرطی درصورت استفاده از گروه بندی   [Having  

] نام مستعار ستون   یا   شماره ستونهایی که بایستی مرتب شوند   یا   ستونهای قابل مرتب سازی  [ Order By

 

در این ساختار خیلی کلی اجزاء اشاره شده به شرح ذیل خواهند بود:

 

-         بااستفاده از دستور Into  خروجی ستون ها به جدول اعلام شده ارسال خواهند شد.

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

 

که در اینجا  " عبارت "  می تواند یک SubQuery ، فیلد ، متغیر و یا ترکیبی از آنها باشد.

-  ستون عبارت است یک مقدار استخراج شده تحت شرایط ذیل :

 

و در صورتیکه از استفاده گردد کلیه ستونهای جدول یا جداول درخواستی ارائه می گردد و درصورتیکه نام ستونها تعیین گردد تنها ستونهای ذکر شده ارائه خواهند شد و با استفاده از AS  یا  =  می توان برای ستون بازگشتی یک نام مستعار نیز برای برنامه دریافت کننده ارسال نمود درغیراینصورت نام فیلد یا یک نام خاص بصورت سریالی توسط SQL Server  ارائه خواهد شد. از طرفی Top  n  تعداد  n   رکورد اولیه استخراج شده را تنها به درخواست کننده ارسال خواهد کرد که در مواردی بسیار مفید است.

-  ستونهای قابل مرتب سازی می تواند به شکل ذیل ارائه گردد :

که کلمات ASC  Desc ,  مرتب سازی صعودی و نزولی را برای ستون اشاره شده را تعیین خواهند کرد و درصورت عدم اعلام مقدار ASC  یعنی صعودی درنظر گرفته خواهد شد.

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

 

خب دوستان این پست کمی طولانی شد. ادامه این بخش رو در پست بعدی که مربط به الحاق جداول می باشد خواهم گفت.

موفق باشید

سلام

الحاق جداول :

 

- بخش اتصال به سایر جداول به منظور تعیین روابط بین جداول لازم برای استخراج توسط دستور                Select تعبیه شده است که دارای ساختار ذیل می باشد :

 

·         Inner Join  زمانی بکار می رود که سطرهای معادل در دو جدول می بایستی وجود داشته باشد.

·         Left Join  زمانی بکار می رود که جدول سمت چپ یا اصلی ملاک انتخاب رکورد باشد.

·         Right Join زمانی بکارمی رودکه جدول سمت راست یامتصل شده ملاک انتخاب رکورد باشد.

·         مقدار شرط اتصال عبارت منطقی است که رابطه بین جدول اصلی و جدول Join شده را تعیین می کند که عموماً از طریق PK و FK  این ارتباط تعیین می گردد در غیر اینصورت به منظور افزایش سرعت شایسته است که ایندکس های مناسب برای ارتباط بین جداول درحجم داده های زیاد پیش بینی شده باشد. ازطرفی درصورتیکه قرار است گروه بندی روی جدول اصلی صورت پذیرد می بایستی از دستورGroup By استفاده گردد و شرط گروه بندی نیز در جلوی دستور Having  اعلام می گردد.

·         همانطور که مشاهده می کنید با تک دستور Select و مفاهیم اشاره شده یک برنامه نویس باتجربه          می بایستی ستونهای مورد نیاز را در یک Query  استخراج نماید که این موضوع با درک مفاهیم درست پایگاه داده و ممارست بیشتر محقق خواهد شد بدین منظور برای درک بیشتر به مثال های ذیل که براساس سیستم پرسنلی اشاره شده در فصل اول می باشد دقت فرمائید.

 

در بحث بعدی توابع تجمعی رو بحث خواهیم کرد.

موفق باشید

سلام

دوستان کسی نظری, پیشنهادی, انتقادی چیزی نداره؟

سلام

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

 

  این توابع عملیات روی مجموعه ای از رکوردها را بعهده دارند و عموماً در دستورات گروه بندی ( Group By )  ظاهر می گردند که اهم این توابع به شرح ذیل می باشند :

 

مقدار می نیمم یک عبارت را محاسبه می کند    Min

مقدار ماکزیمم یک عبارت را محاسبه می کند     Max

مقدار میانگین یک عبارت را محاسبه می کند      Avg

مقدار جمع یک عبارت را محاسبه می کند                     Sum

تعداد رکوردهای اشاره شده را شمارش می کند            Count

 

اما این توابع در SubQuery ها می توانند بعنوان یک ستون اطلاعاتی منعکس شوند دراین خصوص به مثال های ذیل توجه فرمائید.

 

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

 

                        تعداد فرزند    ,  تعداد تحت تکفل   ,    نام    ,    نام خانوادگی    ,    شماره کارمندی

) RID = 2 , 3 (

  در این حالت جدول اصلی Person  و جدول ارتباطی Child  خواهد بود بنابراین خواهیم داشت :

 

Select  PrsID , PFamily , PName,

(Select  Count () From Child Where PrsID=P.PrsID) As CT ,

(Select  Count () From  Child  Where  PrsID = P.PrsID

and  RID In ( 2 , 3 ) ) As  CC

From  Person  P

Where  (Select Count (*)  From  Child Where PrsID = P.PrsID ) > 1

Order  By  2, PName

 در این حالت جدول اصلی Child  و جدول ارتباطی Person  خواهد بود بنابراین خواهیم داشت :

 

Select  C1.PrsID,

(Select  PFamily From Person Where PrsID = C1.PrsID) As  PFamily,

(Select  PName  From  Person Where PrsID = C1.PrsID) As  PName,

Count ()  As  CT,

(Select  Count() From Child C2 Where  PrsID=C1.PrsID and RID In(2,3)) As CC

From  Child  C1

Group  By  C1.PrsID

Having  Count () > 1

Order  By  PFamily , 3

 

 

همانطور که ملاحظه می گردد در روش دوم شرط استخراج رکوردهای گروه بندی در جلوی دستور Having  ظاهر شده است و از طرفی چون ستونهای 3 , 2  استخراج شده یک SubQuery  می باشند بنابراین دستور Order By  با ظاهر متفاوت جهت افزایش سرعت پیاده سازی شده است.

 

خب دوستان در پست بعد مثالی دیگری از توابع تجمعی رو بیان می کنم.

 

  یک SP  جهت استفاده در خروجی مدیریتی ذیل طراحی نمائید.

           

                        واحد سازمانی                  تعداد کارکنان                    تعداد تحت تکفل

 

           

 

 

همانطور که از ظاهر جدول مشاهده می گردد به دو روش گزارش مذکور قابل وصول است.

 

  جدول اصلی جدول Unit  و جداول ارتباطی Child , Person  خواهند بود بنابراین خواهیم داشت :

Create  Proc MIS1 AS

Select  UnitName ,

(Select  Count ()  From  Person  Where  UnitID = U.UnitID )As PCnt ,

(Select  Count ()  From  Child  Where

(Select UnitID From Person Where PrsID=Child.PrsID)=U.UnitID)As TCnt,

From  Unit  As  U

Order  By  UnitName

  جدول اصلیPerson  و جدول ارتباطیChild ,Unit خواهند بود بنابراین خواهیم داشت:

 

Create  Proc  MIS2 AS

Select  (Select  UnitName Form Unit Where UnitID = P1.UnitID)As UnitName,

Count () As PCnt,

(Select Count() From Child Where (Select UnitID From Person P2 Where PrsID

= Child.PrsID ) = P1.UnitID ) As  TCnt

From  Person P1

Group  By  UnitID

Order  By  1

 

  اختلاف اطلاعات نمایش داده شده در دو روش اول و دوم را تشریح نمائید.

 

یکSP  طراحی کنید که فهرست کارکنانی را که بین 3 تا 6  تحت تکفل دارند را استخراج نماید.

 

به منظور سهولت در طراحی جدول Person  را بعنوان جدول اصلی در نظر می گیریم بنابراین خواهیم داشت :

 

Create  Proc  MySP1 AS

Select  P.,

(Select  Count() From Child  C  Where PrsID = P.PrsID )As  Cnt

From  Person  As P

Where  (Select  Count () From Child Where  PrsID= P.PrsID)   BetWeen 3 and 6

Order  By  PrsID

 

ادامه مثالها در پست بعدی ...

منتظر ما باشید

مو فق باشید

  یک SP  جهت استفاده در خروجی مدیریتی ذیل طراحی نمائید.

           

                        واحد سازمانی                  تعداد کارکنان                    تعداد تحت تکفل

 

           

 

 

همانطور که از ظاهر جدول مشاهده می گردد به دو روش گزارش مذکور قابل وصول است.

 

  جدول اصلی جدول Unit  و جداول ارتباطی Child , Person  خواهند بود بنابراین خواهیم داشت :

Create  Proc MIS1 AS

Select  UnitName ,

(Select  Count ()  From  Person  Where  UnitID = U.UnitID )As PCnt ,

(Select  Count ()  From  Child  Where

(Select UnitID From Person Where PrsID=Child.PrsID)=U.UnitID)As TCnt,

From  Unit  As  U

Order  By  UnitName

  جدول اصلیPerson  و جدول ارتباطیChild ,Unit خواهند بود بنابراین خواهیم داشت:

 

Create  Proc  MIS2 AS

Select  (Select  UnitName Form Unit Where UnitID = P1.UnitID)As UnitName,

Count () As PCnt,

(Select Count() From Child Where (Select UnitID From Person P2 Where PrsID

= Child.PrsID ) = P1.UnitID ) As  TCnt

From  Person P1

Group  By  UnitID

Order  By  1

 

  اختلاف اطلاعات نمایش داده شده در دو روش اول و دوم را تشریح نمائید.

 

یکSP  طراحی کنید که فهرست کارکنانی را که بین 3 تا 6  تحت تکفل دارند را استخراج نماید.

 

به منظور سهولت در طراحی جدول Person  را بعنوان جدول اصلی در نظر می گیریم بنابراین خواهیم داشت :

 

Create  Proc  MySP1 AS

Select  P.,

(Select  Count() From Child  C  Where PrsID = P.PrsID )As  Cnt

From  Person  As P

Where  (Select  Count () From Child Where  PrsID= P.PrsID)   BetWeen 3 and 6

Order  By  PrsID

 

ادامه مثالها در پست بعدی ...

منتظر ما باشید

مو فق باشید

  یک SP  جهت استفاده در خروجی مدیریتی ذیل طراحی نمائید.

           

                        واحد سازمانی                  تعداد کارکنان                    تعداد تحت تکفل

 

           

 

 

همانطور که از ظاهر جدول مشاهده می گردد به دو روش گزارش مذکور قابل وصول است.

 

  جدول اصلی جدول Unit  و جداول ارتباطی Child , Person  خواهند بود بنابراین خواهیم داشت :

Create  Proc MIS1 AS

Select  UnitName ,

(Select  Count ()  From  Person  Where  UnitID = U.UnitID )As PCnt ,

(Select  Count ()  From  Child  Where

(Select UnitID From Person Where PrsID=Child.PrsID)=U.UnitID)As TCnt,

From  Unit  As  U

Order  By  UnitName

  جدول اصلیPerson  و جدول ارتباطیChild ,Unit خواهند بود بنابراین خواهیم داشت:

 

Create  Proc  MIS2 AS

Select  (Select  UnitName Form Unit Where UnitID = P1.UnitID)As UnitName,

Count () As PCnt,

(Select Count() From Child Where (Select UnitID From Person P2 Where PrsID

= Child.PrsID ) = P1.UnitID ) As  TCnt

From  Person P1

Group  By  UnitID

Order  By  1

 

  اختلاف اطلاعات نمایش داده شده در دو روش اول و دوم را تشریح نمائید.

 

یکSP  طراحی کنید که فهرست کارکنانی را که بین 3 تا 6  تحت تکفل دارند را استخراج نماید.

 

به منظور سهولت در طراحی جدول Person  را بعنوان جدول اصلی در نظر می گیریم بنابراین خواهیم داشت :

 

Create  Proc  MySP1 AS

Select  P.,

(Select  Count() From Child  C  Where PrsID = P.PrsID )As  Cnt

From  Person  As P

Where  (Select  Count () From Child Where  PrsID= P.PrsID)   BetWeen 3 and 6

Order  By  PrsID

 

ادامه مثالها در پست بعدی ...

منتظر ما باشید

مو فق باشید

  یک SP  جهت استفاده در خروجی مدیریتی ذیل طراحی نمائید.

           

                        واحد سازمانی                  تعداد کارکنان                    تعداد تحت تکفل

 

           

 

 

همانطور که از ظاهر جدول مشاهده می گردد به دو روش گزارش مذکور قابل وصول است.

 

  جدول اصلی جدول Unit  و جداول ارتباطی Child , Person  خواهند بود بنابراین خواهیم داشت :

Create  Proc MIS1 AS

Select  UnitName ,

(Select  Count ()  From  Person  Where  UnitID = U.UnitID )As PCnt ,

(Select  Count ()  From  Child  Where

(Select UnitID From Person Where PrsID=Child.PrsID)=U.UnitID)As TCnt,

From  Unit  As  U

Order  By  UnitName

  جدول اصلیPerson  و جدول ارتباطیChild ,Unit خواهند بود بنابراین خواهیم داشت:

 

Create  Proc  MIS2 AS

Select  (Select  UnitName Form Unit Where UnitID = P1.UnitID)As UnitName,

Count () As PCnt,

(Select Count() From Child Where (Select UnitID From Person P2 Where PrsID

= Child.PrsID ) = P1.UnitID ) As  TCnt

From  Person P1

Group  By  UnitID

Order  By  1

 

  اختلاف اطلاعات نمایش داده شده در دو روش اول و دوم را تشریح نمائید.

 

یکSP  طراحی کنید که فهرست کارکنانی را که بین 3 تا 6  تحت تکفل دارند را استخراج نماید.

 

به منظور سهولت در طراحی جدول Person  را بعنوان جدول اصلی در نظر می گیریم بنابراین خواهیم داشت :

 

Create  Proc  MySP1 AS

Select  P.,

(Select  Count() From Child  C  Where PrsID = P.PrsID )As  Cnt

From  Person  As P

Where  (Select  Count () From Child Where  PrsID= P.PrsID)   BetWeen 3 and 6

Order  By  PrsID

 

ادامه مثالها در پست بعدی ...

منتظر ما باشید

مو فق باشید

سلام

ادامه مثالهای توابع تجمعی :

 

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

 

ابتدا تابعی بنام NoChild  که کد پرسنلی ورودی آن است به شکل ذیل طراحی می کنیم.

 

Create  Function  NoChild (@P Int )

Returns  TinyInt

As

Begin

Declare  @Cnt  TinyInt

Set  @Cnt = (Select  Count()  From  Child  Where  PrsID = @P )

Return   @Cnt

END

البته در این تابع بدون تعریف متغیر Cnt  می توانید دستور SubQuery  را مستقیماً  توسط Return  باز گردانید پس ازطراحی تابعNoChild می توانیدSP موردنظر را به شکل ذیل با پارامترها ورودی n طراحی نمائید.

 

Create  Proc  MySP2  @n  TinyInt

As

Select  , dbo.NoChild (PrsID)

From  Person

Where  dbo.NoChild (PrsID) > = @n

Order  By  PrsID

 

  بااستفاده از تابع فهرست کارکنان را براساس بالاترین مدرک تحصیلی افراد تحت تکفل آنها مرتب کنید.

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

Create  Function  dbo.MaxCL (@PID   Int)

Returns  TinyInt

As

Begin

Return  (Select  Max(LID)  From  ChdLvL

Where  PrsID = @PID )

END

حال دستور SQL  درخواست شده را می نویسیم بنابراین :

 

Select  , dbo.MaxCL(PrsID) AS  CMCL ,

(Select LName From LvL Where LID=dbo.MaxCL(P.PrsID)) AS NMCL

From  Person  AS  P

Order  By  CMCL  Desc , PrsID

 

  بااستفاده از تابع تعیین کنید که هر واحد سازمانی در چه نسبت جمعیت شهری واقع شده است.

ابتدا تابعی می نویسیم که تعیین کند که هر واحد سازمانی در چه جمعیتی واقع شده است بدین منظور به شکل ذیل عمل می کنیم:

 

Create  Function  SumPoP (@CID  SmallInt)

Returns  Int  AS  Begin

Return  (Select  PoPulate  From  City  Where  CityID = @CID)

END

براساس این تابع می توان دستور  SQL  موردنظر را به شکل ذیل پیاده سازی کرد.

 

Declare    @S  BigInt

Set   @S = ( Select  Sum( PoPulate ) From  City )

Select  * , dbo. Sumpop (CityID) / @S * 100  AS  SPP

From  Unit

Order  By  SPP  Desc

 

خب دوستان, تا اینجا توابع تجمعی به اتمام رسید.

در پست بعد دستور Insert رو توضیح می دم.

موفق باشید