SQL实验实验至实验的答案.doc

ＳＱＬ实验 实验4 1.用select 语句查询departments和salary表中的所有数据： select salary.*, departments.* from salary ,departments 2、查询departments 中的departmentid： select departmentid from departments go 3、查询 salary中的 income，outcome： select income,outcome from salary go 4、查询employees表中的部门号，性别，要用distinct消除重复行： select distinct(departmentid), sex from employees 5、查询月收入高于2000元的员工号码： select employeeid from salary where income2000 go 6、查询1970年以后出生的员工的姓名和住址： select name ,address from employees where birthday1970 go 7、查询所有财务部的员工的号码和姓名： select employeeid ,name from employees where departmentid in(select departmentid from departments where departmentname=财务部) goselect name ,address, case when income-outcome2000 then 低收入 when income-outcome3000 then 高收入 else 中等收入 end as 收入等级 from employees,salary where employees.employeeid=salary.employeeid go 9、计算salary表中员工月收入的评价数： select avg(income)as 平均收入 from salaryselect max(employeeid)as 最大员工号码 from employees 11、计算salary表中的所有员工的总支出： select sum(outcome) as总支出 from salary 12、查询财务部雇员的最高实际收入： select max(income-outcome) from salary ,employees,departments where salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname=财务部 go 13、查询财务部雇员的最低实际收入： select min(income-outcome) from salary ,employees,departments where salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname=财务部 go 14、找出所用地址中含有“中山”的雇员的号码及部门号： select employeeid ,departmentid from employees where address like%中山% go 15、查找员工号码中倒数第二个数字为0的员工的姓名，地址和学历： select education,address,name from employees where employeeid like%0_ go 16、使用into字句，由表employees创建“男员工1”表，包括编号和姓名： select employeeid,name into 男员工表 from employees where sex=1 go 17、用子查询的方法查找收入在2500元以下的雇员的情况： select * from employees where employeeid in (select employeeid from salary where income2500) go 18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名： SELECT Name FROM Employees WHERE EmployeeID IN ( SELECT EmployeeID FROM Salary