《SQL实验实验4至实验7的答案》由会员分享，可在线阅读，更多相关《SQL实验实验4至实验7的答案（21页珍藏版）》请在金锄头文库上搜索。
1、实验实验41.用select 语句查询departments和salary表中的所有数据：select salary.*, departments.*from salary ,departments 2、查询departments 中的departmentid：select departmentid from departments go3、查询 salary中的 income，outcome：select income,outcome from salarygo4、查询employees表中的部门号，性别，要用distinct消除重复行：selectdistinct(departmentid
2、), sexfrom employees 5、查询月收入高于2000元的员工号码：select employeeid from salarywhere income2000go6、查询1970年以后出生的员工的姓名和住址：selectname,address from employees where birthday1970go7、查询所有财务部的员工的号码和姓名：select employeeid ,namefrom employeeswhere departmentid in(select departmentid from departments where departmentname
3、=财务部)go8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，20003000元显示为中等收入，3000元以上显示为高收入：selectname,address,casewhen income-outcome3000 then高收入else中等收入endas收入等级from employees,salarywhere employees.employeeid=salary.employeeidgo9、计算salary表中员工月收入的评价数：selectavg(income)as平均收入from salary10、查找employees表中最大的员工号码：sel
4、ectmax(employeeid)as最大员工号码from employees11、计算salary表中的所有员工的总支出：selectsum(outcome)as总支出from salary12、查询财务部雇员的最高实际收入：selectmax(income-outcome)from salary ,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname=财务部go13、查询
5、财务部雇员的最低实际收入：selectmin(income-outcome)from salary ,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentid and departmentname=财务部go14、找出所用地址中含有“中山”的雇员的号码及部门号：select employeeid ,departmentid from employeeswhere address like%中山%go15、查找员工号码中
6、倒数第二个数字为0的员工的姓名，地址和学历：select education,address,namefrom employees where employeeid like%0_go16、使用into字句，由表employees创建“男员工1”表，包括编号和姓名：select employeeid,nameinto 男员工表from employees where sex=1go17、用子查询的方法查找收入在2500元以下的雇员的情况：select*from employees where employeeid in(select employeeid from salary where i
7、ncomeALL(SELECT InCome FROM Salary WHERE EmployeeID IN(SELECT EmployeeId FROM EmployeesWHERE DepartmentID IN(SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部)19、 用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：selectnamefrom employees where Birthday2500)26、按部门列出在该部门工作的员工的人数：select departmentid ,count(*)as
8、人数from employees groupby departmentid27、按员工的学历分组：select education ,count(*)as 人数from employees groupby education28、按员工的工作年份分组，统计年份人数：select workyear ,count(*)as 人数from employees groupby workyear29、按各雇员的情况收入由低到高排列：select employees.*,salary.incomefrom employees ,salary where employees.employeeid=salar
9、y.employeeidorderby income30、将员工信息按出生时间从小到大排列：select*from employees orderby birthday31、在order by 字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从大到小排列：selectname,sex,workyear,income-outcomefrom salary ,employeeswhere salary.employeeid=employees.employeeidorderby income-outcome desc视图部分1、创建view1：Createview view1 ass
10、elect employees.employeeid,name,departmentname,(income-outcome)as comefrom employees , departments , salary where employees.departmentid=departments.departmentid and employees.employeeid=salary.employeeid2、查询视图employeeid：3、向视图view1中插入一行数据：insertinto view1 values(111111,谎言,1,30000)4、查看视图（没有影响）基本表：实验51、 定义一个变量，用于描述YGGL数据库的salary表中000001号员工的实际收入
