تمرین کوئری نویسی سناریو کارمند و پروژه در sql

در این تمرین قصد داریم تمرین کوئری نویسی سناریو کارمند و پروژه را در sql server  بررسی و حل نماییم .

سوال : نمودار er زیر را در نظر بگیرید .

1- ابتدا تمامی جدول های مورد نیاز نمودار ERD را با استفاده از کوئری ایجاد نمایید ؟

*اطلاعات زیر را از دیتابیس بالا استخراج نمایید .

2-اطلاعات 3 کارمند با بیشترین حقوق ؟ ( بدون در نظر گرفتن دپارتمانی که داخلش هستند )

3-تعداد کارمندان که روی هر پروژه کار میکنند به همراه مشخصات کامل هر پروژه ؟

4- کارمندانی که بر روی پروژه P_NO برابر با 1 ، بیشتر از میانگین ساعت های کار کردن همه ی کارمندان آن پروژه ، روی آن پروژه کار کرده اند ؟

5-نام تمامی کارمندانی که عضو دپارتمان دانشکده کامپیوتر هستند ؟

حل سوال :

جواب سوال اول :

برای استخراج جداول باید قوانین مربوط به تبدیل نمودار er و به جدول را مرور کنیم .

اولین قانون این است که موجودیت ها را به جدولی جداگانه تبدیل کنیم پس همه ی موجودیت ها را با کوئری نویسی به جدول تبدیل میکنیم .

کوئری مربوط به جدول کارمندان

CREATE TABLE Employee(
    E_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FirstName nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    Salary bigint NULL,
    Gender nvarchar(50) NULL,
    Adress nvarchar(50) NULL,
    DepartmentNo int NULL,
    FOREIGN KEY (DepartmentNo) REFERENCES Department(D_No)
)

کد مربوط به جدول دپارتمان

CREATE TABLE Department(
    D_No int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    D_Name nvarchar(50) NULL,
);

query مربوط به جدول پروژه ها

CREATE TABLE Projects (
    P_No int IDENTITY(1,1) NOT NULL Primary key,
    P_Name nvarchar(50) NULL,
    DepartmentNo int NULL,
    FOREIGN KEY (DepartmentNo) REFERENCES Department(D_No)
);

در قانون بعدی به ارتباطات نگاه میکنیم ارتباط worksIN جدول بعدی را تشکیل میدهد .

چون ارتباط از نوع چند به چند است و نیاز به تشکیل جدول جدید دارد .

کد sql مربوط به تشکیل جدول worksIN

CREATE TABLE WsorksIN(
    E_ID int NOT NULL  ,
    P_No int NOT NULL  ,
    working_hours int NULL,
    PRIMARY KEY (E_ID,P_No)
)

جهت دسترسی به تمرینات مشابه تمرین کوئری نویسی سناریو کارمند و پروژه در sql کلیک نمایید .

جواب سوال دوم :

select top 3 * from Employee 
order by Salary desc

چون سوال گفته بدون در نظر گرفتن بخش department میتوانیم کوئری ساده ای را در نظر بگیریم .

جواب سوال سوم :

select Projects.P_No,Projects.P_Name, count(Employee.E_ID) as CountofEmployee  from Employee,Projects,WorksIN
where Employee.E_ID = WorksIN.E_ID 
and Projects.P_No = WorksIN.P_No 
group by  Projects.P_No,Projects.P_Name

به دلیل اینکه میخواهیم تعداد کارمندان هر پروژه را شرح دهیم باید از count  استفاده کنیم.

برای اینکه گروه بندی کارمندان و پروژه ها انجام شود از group by استفاده میکنیم .

جواب سوال چهارم :

select Employee.FirstName,Employee.LastName,Projects.P_Name , working_hours ,(select  avg(working_hours) from WorksIN where P_No = 1 ) as averageHours  from WorksIN,Projects,Employee  where 
WorksIN.E_ID = Employee.E_ID and
WorksIN.P_No = Projects.P_No and 
working_hours >  (select  avg(working_hours) from WorksIN where P_No = 1 )

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

جواب سوال پنجم :

select Employee.FirstName from Employee,Department where 
Department.D_No = Employee.DepartmentNo and 
Department.D_Name = 'Computer'

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

جهت درخواست انجام پروژه در sql server کلیک نمایید .