资源预览内容
第1页 / 共21页
第2页 / 共21页
第3页 / 共21页
第4页 / 共21页
第5页 / 共21页
第6页 / 共21页
第7页 / 共21页
第8页 / 共21页
第9页 / 共21页
第10页 / 共21页
亲,该文档总共21页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
实验实验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), 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=财务部)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表中最大的员工号码:selectmax(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、查询财务部雇员的最低实际收入: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、查找员工号码中倒数第二个数字为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 incomeALL(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 人数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=salary.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 asselect 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号员工的实际收入
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号