数据库原理与应用----课后习题笔记

课 程 实 验 报 告

实验项目 名称	实验二 数据查询操作
实验 目的及要求	实验目的： （1）掌握SQL程序设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询、分组统计查询和连接查询。 （2）掌握SQL嵌套查询、集合查询、相关子查询等各种高级查询的设计方法等。 实验要求： 针对SQL server数据库数据库设计各种单表查询SQL语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握SQL查询语句各个子句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并调试通过。 针对SQL server数据库，正确分析用户查询要求，设计各种嵌套查询和集合查询。锻炼和提升学生掌握数据表的单表查询、多表查询以及嵌套查询、查询子句的SQL编程能力。 实验准备： 熟悉数据库company的四张表   1 雇员表 emp     empno number(4) 表示雇员编号     ename varchar2(10) 表示雇员姓名     job varchar2(9) 表示工作职位     mgr number(4) 表示领导编号     hiredate date 表示雇佣日期     sal number(7,2) 表示月薪工资     comm number(7,2) 表示奖金,佣金     deptno number(2) 部门编号     2 部门表dept       deptno number(2) 部门编号      dname varchar2(14) 部门名称      loc varchar2(13) 部门位置    3 工资等级表 salgrade      grade number 等级名称      losal number 此等级最低工资      hisal number 此等级最高工资    4 奖金表 bonus      ename varchar2(10) 雇员姓名      job varchar2(9) 雇员工作      sal number 雇员工资      comm number 雇员资金 在正式开始本实验内容之前，需要先下载相关代码company.sql， 并执行。 该代码可以新建一个数据库company， company数据库中建 4 个表（emp，dept，salgrade，bonus），并往这些表中写入数据，然后根据实验内容对该数据库进行操作。
实验 内容	单表查询操作 1 查询雇员表中所有信息 2 查询雇员编号,姓名,工作,工资 3 查询雇员编号,姓名,工作,工资,并显示中文 4 消除重复列,查询雇员工作种类     --可以消除多余的列     --不可以,查询多列时只有同时重复才能消除 5 字符串连接操作 --查询雇员编号,姓名,工作.按以下格工显示:编号:7369,姓名:Smith,工作:Clerk 6 查询列支持四则运算 --查询雇员编号,姓名,工作,年薪 7 Where条件查询 -- 查询工资大于1500的所有雇员 --查询可以得到奖金的所有雇员 --查询工资大于1500或可以得到奖金的雇员 --查询工资大于1500并且可以领取奖金的雇员 --查询工资不大于1500或者不可以领取奖金的雇员 --查询工资在1500到3000的所有雇员信息 --查询在1981年雇用的员工信息 --用like必写上面 --查询雇员姓名中第二个字母为"M"的雇员 --查询雇员工资中带8这个数字的 --查询编号是7369,7499,7521,7799的雇员信息 --查询雇员编号不是7369,7499,7521,7799的所有雇员信息 --查询雇员编号为7369的雇员信息 --查询雇员编号不为7369的雇员信息 --查询雇员信息,按工资由低到高排序 --查询雇员信息,按工资由高到低排序 多表查询操作 1.查询雇员姓名,所在部门编号和名称 2.查询雇员姓名,工作,领导的姓名 3.查询雇员姓名,工作,领导姓名及部门名称 4.查询雇员姓名,工作,工资及工资等级 排序\分组 1.查询雇员姓名,年薪,按年薪从高到低排序 2.查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序 3.查询每个部门的雇员数量 4.求出每个部门的平均工资 5.按部门分组，并显示部门的名称，以及每个部门的员工数 6.要求显示平均工资大于2000的部门编号和平均工资 7.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员 的月工资大于$1500，输出结果按月工资的合计升序排列 求出平均工资最高的部门 子查询 1.要求查询出比7654工资要高的全部雇员的信息 2.要求查询工资比7654高，与7788从事相同工作的全部雇员信息 3.查询出工资最低的雇员姓名，工作，工资 4.要求查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员姓名
实验步骤	实验前准备:将数据表格式,基本表按照实验数据下载并完成,如图所示: create database company; use company; create table dept(     deptno int primary key,     dname varchar(15),     loc varchar(50)     ); create table emp(     empno int primary key,     ename varchar(15),     job varchar(10),     mgr int,     hiredate date,     sal decimal(7,2),     comm decimal(7,2),     deptno int,     foreign key(deptno) references dept(deptno) ); create table salgrade(     grade int,     losal int,     hisal int    ); create table bonus(     ename varchar(10),     job varchar(9),     sal decimal(7,2),     comm decimal(7,2) ); INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'); INSERT INTO dept VALUES(20,'RESEARCH','DALLAS'); INSERT INTO dept VALUES(30,'SALES','CHICAGO'); INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON'); INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30); INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30); INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20); INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30); INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30); INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10); INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20); INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30); INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20); INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30); INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20); INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10); INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999); SELECT *FROM dept SELECT *FROM emp SELECT *FROM salgrade 单表查询操作 查询雇员表中所有信息SELECT *FROM emp 查询雇员编号,姓名,工作,工资SELECT empno,ename,job,sal FROM emp 3. 查询雇员编号,姓名,工作,工资,并显示中文,SELECT empno "雇员编号",ename "雇员姓名" ,job "工作",sal "工资" from emp;如图所示. 4. 消除重复列,查询雇员工作种类 select distinct job from emp ;      --可以消除多余的列 select distinct * from emp ;        --不可以,查询多列时只有同时重复才能消除如图所示, 5 字符串连接操作 --查询雇员编号,姓名,工作.按以下格工显示:编号:7369,姓名:Smith,工作:Clerk select '编号:'||empno||',姓名:'||ename||',工作:'||job 雇员资料 from emp ; 6 查询列支持四则运算 --查询雇员编号,姓名,工作,年薪 select empno 编号,ename 姓名,job 工作,sal 月薪,sal*12 年薪 from emp ; 7 Where条件查询 -- 查询工资大于1500的所有雇员 select empno 编号,ename 姓名,job 工作,sal 月薪 from emp where sal>1500 ; --查询可以得到奖金的所有雇员 --查询可以得到奖金的所有雇员 select empno 编号,ename 姓名,job 工作,comm 奖金 from emp where comm is not null ; --查询工资大于1500或可以得到奖金的雇员 select empno 编号,ename 姓名,job 工作,sal 月薪,comm 奖金 from emp where sal>1500 or comm is not null ; --查询工资大于1500并且可以领取奖金的雇员 select empno 编号,ename 姓名,job 工作,sal 月薪,comm 奖金 from emp where sal>1500 and comm is not null ; --查询工资不大于1500或者不可以领取奖金的雇员 select empno 编号,ename 姓名,job 工作,sal 月薪,comm 奖金 from emp where not (sal>1500 and comm is not null) ; --查询工资在1500到3000的所有雇员信息 select empno 编号,ename 姓名,job 工作,sal 月薪,comm 奖金 from emp where sal between 1500 and 3000 ; --查询在1981年雇用的员工信息 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where hiredate between '1981-01-01' and '1981-12-31' ; --用like必写上面 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where hiredate like '%81%' ; --查询雇员姓名中第二个字母为"M"的雇员 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where ename like '_M%' ; --查询雇员工资中带8这个数字的 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where sal like '%8%' ; --查询编号是7369,7499,7521,7799的雇员信息 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where empno in (7369,7499,7521,7799) ; --查询雇员编号不是7369,7499,7521,7799的所有雇员信息 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where empno not in (7369,7499,7521,7799) ; --查询雇员编号为7369的雇员信息 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where empno = 7369 ; --查询雇员编号不为7369的雇员信息 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp where empno != 7369 ; --查询雇员信息,按工资由低到高排序 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp order by sal asc ; --查询雇员信息,按工资由高到低排序 select empno 编号,ename 姓名,job 工作,sal 月薪,hiredate 雇用日期 from emp order by sal desc ; 多表查询 查询雇员姓名,所在部门编号和名称 select e.ename 雇员姓名,e.deptno 部门编号,d.dname 部门名称 from emp e,dept d where e.deptno = d.deptno; 查询雇员姓名,工作,领导的姓名 select e.ename,e.job,e2.ename mgrname from emp e,emp e2 where e.mgr = e2.empno; 查询雇员姓名,工作,领导姓名及部门名称 select e.ename,e.job,e2.ename mgrname,d.dname from emp e,emp e2,dept d where e.mgr = e2.empno and d.deptno = e.deptno; 查询雇员姓名,工作,工资及工资等级 select e.ename,e.job,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal; 排序\分组 查询雇员姓名,年薪,按年薪从高到低排序 select ename,(sal*12) yearSal from emp order by yearSal desc; 2.查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序 select e.ename,e.job,e.sal,dd.dname from emp e,(select e.deptno,d.dname,max(e.sal) maxsal from emp e,dept d where e.deptno = d.deptno group by e.deptno,d.dname) dd where e.deptno = dd.deptno and e.sal = dd.maxsal order by e.sal desc; 3.查询每个部门的雇员数量 select count(e.deptno) from emp e,dept d where d.deptno = e.deptno group by d.deptno; 4.求出每个部门的平均工资 select d.dname,trunc(avg(e.sal),2) from emp e,dept d where e.deptno = d.deptno group by d.dname; 5.按部门分组，并显示部门的名称，以及每个部门的员工数 select d.deptno,d.dname,count(e.deptno) from emp e,dept d where d.deptno = e.deptno group by d.deptno,d.dname; 6.要求显示平均工资大于2000的部门编号和平均工资 select d.deptno,trunc(avg(e.sal),2) avgSal from emp e,dept d where e.deptno = d.deptno group by d.deptno having trunc(avg(e.sal),2) >2000 ; 7.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员 的月工资大于$1500，输出结果按月工资的合计升序排列 select job,sum(sal) sumsal from emp where job <>'SALESMAN' and sal > 1500 group by job order by sumsal; 8,求出平均工资最高的部门 select d.dname from emp e,dept d where e.deptno = d.deptno group by d.dname having avg(e.sal) =(select max(avgsal) from(select d.dname,avg(e.sal) as avgsal from dept d,emp e where e.deptno = d.deptno group by d.dname)) ; 子查询 要求查询出比7654工资要高的全部雇员的信息 select * from emp where sal >(select sal from emp where empno = 7654); 2.要求查询工资比7654高，与7788从事相同工作的全部雇员信息 select * from emp where sal >(select sal from emp where empno = 7654) and job =(select job from emp where empno = 7788); 3.查询出工资最低的雇员姓名，工作，工资 select ename,job,sal from emp where sal = (select min(sal) from emp); 4.要求查询出部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员姓名 select e.ename,dd.dname,dd.counts,dd.avgsal,dd.minsal from emp e,(select d.deptno, d.dname,count(e.empno) counts,avg(e.sal) avgsal,min(e.sal) minsal from emp e,dept d where d.deptno=e.deptno group by d.dname,d.deptno) dd where dd.deptno = e.deptno and e.sal in (dd.minsal);
实验环境	硬件：计算机 软件：SQL server
实验心得
教师评语

注：可根据实际情况加页