Create database 雇员数据库分析和总结.docx

Createdatabase雇员数据库

Use雇员数据库

Go

Createtableemployee(

Employee_namechar(8)notnull primarykey,Streetchar(24)notnull,

Citychar(24)notnull

)

go

Createtablecompany(

company_namechar(24)notnullprimarykey,Citychar(24)notnull

)

go

Createtableworks(

Employee_namechar(8)notnull,Company_namechar(24)notnull,Salaryintnotnull,

Foreignkey(employee_name)referencesemployee(employee_name),Foreignkey(company_name)referencescompany(company_name)

)

go

Createtablemanages(

Employee_namechar(8)notnull,Manager_namechar(10)notnull,

Foreignkey(employee_name)referencesemployee(employee_name)

)

Use雇员数据库

Go

Selectemployee_name,employee.cityasemployee_cityFromemployee

Whereemployee.employee_namein(

Selectworks.employee_nameFromworks

Wherepany_name=firstbankcorporation

)

Use雇员数据库

Go

Selectemployee_name,employee.cityasemployee_city,streetFromemployee

Whereemployee.employee_namein(

Selectworks.employee_nameFromworks

Wherepany_name=firstbankcorporationandsalary10000

)

Use雇员数据库

Go

Selectemployee_nameFromemployee

Whereemployee.employee_namenotin(

Selectworks.employee_nameFromworks

Wherepany_name=firstbankcorporation

)

Use雇员数据库

Go

Selectemployee_nameFromworks

Wheresalary(

Selectmax(salary)Fromworks

Wherepany_name=smallbankcorporation

)

Use雇员数据库

Go

Selectcompany_name

Fromcompany

Wherecompany.city=(

SelectcityFromcompany

Wherepany_name=smallbankcorporation

)

selecttop1company_name,count(employee_name)employee_countfromworks

groupbycompany_name

orderbycount(employee_name)desc

Use雇员数据库

Go

Selectpany_name,avg(works.salary)asavagesalaryFromworks,company

Wherepany_name=pany_namegroupbypany_name

havingavg(works.salary)(

Selectavg(works.salary)Fromworks

Wherepany_name=firstbankcorporation

)

Select读者.姓名,工作单位From读者innerjoin借阅On读者.读者号=借阅.读者号Where还期isnull

use图书借阅关系

go

Select读者.姓名,图书.书名as未还书名

From读者,借阅,图书

Where读者.姓名=马林and读者.读者号=借阅.读者号and借阅.