数据查询与表的创建作业.docx
- 文档编号:965140
- 上传时间:2022-10-14
- 格式:DOCX
- 页数:14
- 大小:1.15MB
数据查询与表的创建作业.docx
《数据查询与表的创建作业.docx》由会员分享,可在线阅读,更多相关《数据查询与表的创建作业.docx(14页珍藏版)》请在冰豆网上搜索。
数据查询与表的创建作业
图1
用两种方法建立一个新数据库HRM(也可任意取名),如上图所示(图1);
鼠标右击数据库HRM-数据查询,然后输入如下SQLServer语句:
DepartmentIDCHAR(3)NOTNULLPRIMARYKEY,
DepartmentNameCHAR(20)NOTNULL,
NoteTEXT)
CREATETABLEEmployee
(
EmployeeIDCHAR(6)NOTNULLPRIMARYKEY,
NAMECHAR(10)NOTNULL,
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的雇员的地址和电话
SELECTPhoneNumber,AddressfromEmployee
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"平均工资",DepartmentNamefromDepartments,Salary,Employee
whereDepartments.DepartmentID=Employee.DepartmentIDandSalary.EmployeeID=Employee.EmployeeID
groupbyDepartmentName,Departments.DepartmentID
havingDepartments.DepartmentName='财务部'
j查找比所有财务部的雇员工资都高的雇员的姓名
正确的为:
selectname
fromemployee,salary
whereemployee.employeeid=salary.employeeid
andincome>all(selectincomefromsalary
whereemployeeidin(selectemployeeidfrom
employee,departments
wheredepartments.departmentid=employee.departmentid
anddepartmentname='财务部'))
k查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名
selectname
fromemployee,departments
wherebirthday fromemployeewheredepartmentid='2') anddepartments.departmentid=employee.departmentid andemployee.departmentid='1' l查找在财务部工作的雇员的情况: 词法错误 应为: selectemployee.* fromemployee,departments wheredepartments.departmentid=employee.departmentid andemployee.departmentid='1'
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据 查询 创建 作业