关于实验解答.pptVIP

数据库实验解答 * * * 实验2.2 练习下面简单的查询语句： a) 查询每个雇员的所有信息 select * from Employee b) 查询每个雇员的地址和电话 select mployeeID,Address,PhoneNumber from Employee c) 查询EmployeeID为000001的雇员的地址和电话。 select Address,PhoneNumber from Employee where EmployeeID=000001 * 实验2.2 练习下面简单的查询语句： d) 查询女雇员地址和电话，并用AS子句将结果中各列的标题分别指定为“地址”和“电话”。 select EmployeeID,Address as 地址,PhoneNumber as 电话 from Employee where sex=false e) 计算每个雇员的实际收入。 select EmployeeID,Income-OutCome from Salary f) 找出所有姓王的雇员的部门号。 select distinct DepartmentID from Employee where Name like王% * 3、练习多表连接查询和嵌套查询： 查询每个雇员的情况及工资情况（工资=Income - Outcome） select Employee.*,Income - Outcome from Employee,Salary where Employee.EmployeeID = Salary.EmployeeID 查询财务部工资在2200元以上的雇员姓名及工资情况 select Name,Income - Outcome as 工资 from Employee,Salary,Departments where Employee.EmployeeID = Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentID and Income - Outcome2200 * 3、练习多表连接查询和嵌套查询： 查询研发部在1966年以前出生的雇员姓名及其工资详情 select Name,Income - Outcome as 工资 from Employee,Salary,Departments where Employee.EmployeeID = Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentID and DepartmentName=研发部 and year(Birthday)1966 * 查询人力资源部雇员的最高和最低工资 select max(Income - Outcome),min(Income - Outcome) from Employee,Salary,Departments where Employee.EmployeeID = Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentID and DepartmentName=人力资源部 将各雇员的情况按工资由低到高排列 select Employee.*,Income - Outcome from Employee,Salary where Employee.EmployeeID = Salary.EmployeeID order by 2 asc 求各部门的雇员数 select count(*) from Employee group by DepartmentID * 找出所有在财务部和人力资源部工作的雇员的编号 select EmployeeID from Employee,Departments where Employee.DepartmentID=Departments.DepartmentID and (DepartmentName=财务部 or DepartmentName=人力资源部) 统计人力资源部工资在2500以上雇员的人数 select count(*) from Employee,Salary,Departments where Employee.EmployeeID = Salary.EmployeeID and Employee.DepartmentID=Departments.DepartmentID and DepartmentName=人力资源部 and Income - Outcome2500 求财务部雇员的总人数 select count(*) fr