资源描述：
《数据查询与表的创建作业.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、图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(
2、6),PhoneNumberCHAR(12),EmailAddressCHAR(30),DepartmentIDCHAR(3)NOTNULLREFERENCESDepartments(DepartmentID)ONDELETENOACTION)CREATETABLESalary(EmployeeIDCHAR(6)NOTNULLREFERENCESEmployee(EmployeeID)ONDELETENOACTION,IncomeFLOAT(8)NOTNULL,OutComeFLOAT(8)NOTNULL)在数据库HRM中，建立如下所示的三个表：Employee表,De
3、partments表,Salary表对上节建立的表输入数据：Departments表：Employee表Salary表如下图:练习下面简单的查询语句：a)查询每个雇员的所有信息:输入语句select*fromEmployeeb)查询每个雇员的地址和电话SELECTPhoneNumber,AddressfromEmployeec)查询EmployeeID为000001的雇员的地址和电话SELECTPhoneNumber,AddressfromEmployeewhereEmployeeID=1001d)查询女雇员地址和电话，并用AS子句将结果中各列的标题分别指定为“地址”和
4、“电话”。SELECT电话=PhoneNumber,地址=AddressfromEmployeewhereSEX=0e)计算每个雇员的实际收入。select实际收入=Income-OutComefromSalaryselectDepartmentIDfromEmployeewhereNAMElike'王%'f)找出所有姓王的雇员的部门号3a)查询每个雇员的情况及工资情况（工资=Income-Outcome）selectIncome-outcome,Employee.*fromEmployee,salarywhereEmployee.EmployeeID=Salary.E
5、mployeeIDb)查询财务部工资在2200元以上的雇员姓名及工资情况selectName,Income-outcomeas'工资',DepartmentNamefromDepartments,Salary,EmployeewhereDepartments.DepartmentID=Employee.DepartmentIDandSalary.EmployeeID=Employee.EmployeeIDandDepartmentName='财务部'GroupbyDepartmentName,Income,Outcome,Employee.NAMEhaving(Inco
6、me-outcome)>2200a)查询人力资源部雇员的最高和最低工资selectMAX(Income-Outcome)as"最高工资",min(Income-Outcome)as"最低工资"fromSalarywhereEmployeeIDlike'3%'b)将各雇员的情况按工资由低到高排列selectincome,employee.*fromemployee,salarywheresalary.employeeid=employee.employeeidorderbyincomeasca)求各部门的雇员数selectdepartments.departmentnam
7、e,count(*)as'员工数'fromemployee,departmentswhereemployee.departmentid=departments.departmentidgroupbydepartments.departmentnamef找出所有在财务部和人力资源部工作的雇员的编号selectDepartmentName,EmployeeIDfromDepartments,EmployeewhereDepartments.DepartmentID=Employee.DepartmentIDgroupbyDepartmentN
