数据查询与表的创建作业Word格式文档下载.docx
《数据查询与表的创建作业Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《数据查询与表的创建作业Word格式文档下载.docx(14页珍藏版)》请在冰豆网上搜索。
BirthdayDatetimeNOTNULL,
SEXchar
(2)NOTNULL,
AddressCHAR(20),
ZipCHAR(6),
PhoneNumberCHAR(12),
EmailAddressCHAR(30),
DepartmentIDCHAR(3)NOTNULLREFERENCESDepartments(DepartmentID)ONDELETENOACTION
)
CREATETABLESalary
(EmployeeIDCHAR(6)NOTNULLREFERENCESEmployee(EmployeeID)ONDELETENOACTION,
IncomeFLOAT(8)NOTNULL,
OutComeFLOAT(8)NOTNULL
在数据库HRM中,建立如下所示的三个表:
Employee表,Departments表,Salary表
对上节建立的表输入数据:
Departments表:
Employee表
Salary表如下图:
练习下面简单的查询语句:
a)查询每个雇员的所有信息:
输入语句
select*fromEmployee
b)查询每个雇员的地址和电话
SELECTPhoneNumber,AddressfromEmployee
c)查询EmployeeID为000001的雇员的地址和电话
whereEmployeeID=1001
d)查询女雇员地址和电话,并用AS子句将结果中各列的标题分别指定为“地址”和“电话”。
SELECT电话=PhoneNumber,地址=AddressfromEmployee
whereSEX=0
e)计算每个雇员的实际收入。
select实际收入=Income-OutComefromSalary
selectDepartmentIDfromEmployee
whereNAMElike'
王%'
f)找出所有姓王的雇员的部门号
3
a)查询每个雇员的情况及工资情况(工资=Income-Outcome)
selectIncome-outcome,Employee.*fromEmployee,salary
whereEmployee.EmployeeID=Salary.EmployeeID
b)查询财务部工资在2200元以上的雇员姓名及工资情况
selectName,Income-outcomeas'
工资'
DepartmentNamefromDepartments,Salary,Employee
whereDepartments.DepartmentID=Employee.DepartmentIDandSalary.EmployeeID=Employee.EmployeeIDandDepartmentName='
财务部'
GroupbyDepartmentName,Income,Outcome,Employee.NAME
having(Income-outcome)>
2200
c)查询人力资源部雇员的最高和最低工资
selectMAX(Income-Outcome)as"
最高工资"
min(Income-Outcome)as"
最低工资"
fromSalary
whereEmployeeIDlike'
3%'
d)将各雇员的情况按工资由低到高排列
selectincome,employee.*
fromemployee,salary
wheresalary.employeeid=employee.employeeid
orderbyincomeasc
e)求各部门的雇员数
selectdepartments.departmentname,count(*)as'
员工数'
fromemployee,departments
whereemployee.departmentid=departments.departmentid
groupbydepartments.departmentname
f找出所有在财务部和人力资源部工作的雇员的编号
selectDepartmentName,EmployeeIDfromDepartments,Employee
whereDepartments.DepartmentID=Employee.DepartmentID
groupbyDepartmentName,EmployeeID
havingDepartmentName='
orDepartmentName='
人力资源部'
g.和统计人力资源部工资在2500以上雇员的人数
selectcount(*)as'
人力资源部以上的人数'
fromdepartments,employee,salary
wheredepartments.departmentid=employee.departmentid
andsalary.employeeid=employee.departmentid
anddepartmentnamein('
andincome>
=2500
h.求财务部雇员的总人数
selectcount(*)as'
财务部员工人数'
fromemployeewheredepartmentidin('
1'
i求财务部雇员的平均工资
selectAvg(Income-outcome)as"
平均工资"
whereDepartments.DepartmentID=Employee.DepartmentIDandSalary.EmployeeID=Employee.EmployeeID
groupbyDepartmentName,Departments.DepartmentID
havingDepartments.DepartmentName='
j查找比所有财务部的雇员工资都高的雇员的姓名
正确的为:
selectname
whereemployee.employeeid=salary.employeeid
andincome>
all(selectincomefromsalary
whereemployeeidin(selectemployeeidfrom
employee,departments
anddepartmentname='
))
k查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名
fromemployee,departments
wherebirthday<
all(selectbirthday
fromemployeewheredepartmentid='
2'
anddepartments.departmentid=employee.departmentid
andemployee.departmentid='
l查找在财务部工作的雇员的情况:
词法错误
应为:
selectemployee.*