实验三 SQL语言及其应用

实验三  SQL语言及其应用
实验报告


一、 实验目的
1） 掌握数据查询的各种应用
2） 掌握数据操纵的各种应用
3） 掌握事务处理方法
4） 了解常用SQL函数的应用
二、 预习内容
1） 根据SQL语言实现功能的不同，Oracle数据库中的SQL语言可以分为哪几类？                              
1.数据操作语言语句[Data manipulation language，DML]
2.数据定义语言语句[Data definition language，DDL]
3.事务控制语句[transaction control statement]
4.会话控制语句[session control statement]
2） SQL语言的特点是什么？
1.综合统一
2.高度非过程化
3.面向集合的操作方式
4.以同一种语法结构提供多种使用方式
5.语言简洁，易学易用
6.对于数据统计方便直观
3） Oracle中常用的数据查询方式有哪些？
数据查询基础
基本查询
分组查询
连接查询
子查询
层次查询 
合并查询
4） 什么是事务？事务处理的特性有哪些？有哪两种事务处理方式？
原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中的所有操作要么都做，要么都不做，不存在第三种情况。
一致性（Consistency）：事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态，不存在中间的状态。
隔离性（Isolation）：数据库中一个事务的执行不受其他事务干扰，每个事务都感觉不到还有其他事务在并发执行。
持久性（Durability）：一个事务一旦提交，则对数据库中数据的改变是永久性的，以后的操作或故障不会对事务的操作结果产生任何影响。 


三、 实验环境
32位Windows XP/32位或64位Win7/Windows Server2003 +Oracle10g环境
四、 实验内容
根据Oracle数据库scott模式下的emp表和dept表，完成下列操作。
1） 查询20号部门的所有员工信息select * from emp where deptno = 20;
2） 查询所有工种为CLERK的员工的员工号、员工名和部门号。             select empno,ename,deptno from emp where job like 'CLERK';
3） 查询奖金（COMM）高于工资（SAL）的员工信息。select * from emp where comm > sal;
4） 查询奖金高于工资的20%的员工信息。select * from emp where comm > (sal*0.2);
5） 查询所有工种不是MANAGER和CLERK, 且工资大于或等于2000的员工的详细信息。select * from emp where job not in ('MANAGER','CLERK') andsal >= 2000 ;
6） 查询没有奖金或奖金低于100的员工信息。select * from emp where (comm is null or comm < 100) ;
7） 查询工龄大于或等于10年的员工信息。select * from emp where (sysdate - hiredate)/365 >= 10 ;
8） 查询员工名正好为6个字符的员工的信息。select * from emp where length（ename）= 6 ;
9） 查询员工姓名的第2个字母为“M”的员工信息。select * from emp where ename like '_M%';
10） 显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排。                                           select ename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm')from emp order by to_char(hiredate,'mm'),to_char(hiredate,'yyyy');
11） 查询工资比SMITH员工工资高的所有员工信息。select * from emp where sal > (select sal from emp whereename like 'SMITH') ;
12） 查询所有员工的姓名及其直接上级领导的姓名。select staname,ename supname from (select ename staname,mgrfrom emp) t join emp on t.mgr=emp.empno ;
13） 查询入职日期早于其直接上级领导的所有员工信息。select * from emp where empno in (select staempno from(select empno staempno,hiredate stahiredate,mgr from emp) t join emp ont.mgr=emp.empno and stahiredate < hiredate) ;
14） 查询所有工种为CLERK的员工的姓名及其部门名称。select ename,dname from emp join dept on job like 'CLERK' andemp.deptno=dept.deptno ;
15） 查询平均工资低于2000的部门及其员工信息。select * from emp where deptno in (select deptno from (selectmin(sal) min_sal,deptno from emp group by deptno) where avg_sal < '2000') ;                            
16） 查询最低工资大于2500的各种工作。select job from (select min(sal) min_sal,job from emp groupby job) where min_sal > 2500 ;
17） 查询工资高于公司平均工资的所有员工信息。select * from emp where sal > (select avg(sal) from emp) ;
18） 查询工资高于30号部门中工作的所有员工工资的员工的姓名和工资。select ename,sal from emp where sal >all (select sal fromemp where deptno = 30) ;
19） 查询每个部门中的员工数量、平均工资和平均工作年限。              select dname,count,avg_sal,avg_date from dept join (selectcount(*) count,avg(sal) avg_sal,avg((sysdate-hiredate)/365) avg_date,deptnofrom emp group by deptno) t on dept.deptno = t.deptno ;
20） 统计各个部门中各工种的人数、平均工资和最高工资。                  select deptno,job,count(*),avg(sal) from emp group bydeptno,job order by deptno,job;
21） 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。select emp.* from emp join (select deptno,avg(sal) avg_salfrom emp group by deptno) t on emp.deptno=t.deptno and sal>avg_sal ;
22） 查询人数最多的部门信息。  select * from dept where deptno in (select deptno from(select count(*) count,deptno from emp group by deptno) where count in (selectmax(count) from (select count(*) count,deptno from emp group by deptno)));
23） 查询所有员工中工资排序在5-10名之间的员工信息。select * from emp where empno in (select empno from (selectempno,rownum num from (select empno,sal from emp order by sal desc)) where numbetween 5 and 10 ) ;
24） 向emp表中插入一条记录，员工名为FAN，员工号为8000，其他信息与SMITH员工的信息相同。insertinto emp(empno,ename,sal,deptno,hiredate) values(8000,'FAN',2050,20,to_date('2002年5月10日','yyyy"年"mm"月"dd"日"'));
25） 将各部门员工的工资修改为该员工所在部门平均工资加1000。update emp t1 set sal = (select new_sal from (selectavg(sal)+1000 new_sal,deptno from emp group by deptno) t2 wher e t1.deptno =t2.deptno ) ;
五、 实验步骤
写出每一条实验内容对应的SQL语句。
六、实验总结
对于嵌套查询掌握不牢，需进行多次推敲。今后会加强练习。