1 Oracle基础查询综合示例

有职员表 emp，表结构如表－1所示：

emp 表中的示例数据如图－1所示：

有部门表 dept，表结构如表－2所示：

dept表中的示例数据如图－2所示：

需要完成如下查询：

1、查询职员表中，在20和30号部门工作的员工姓名和部门号。

2、查询职员表中，没有管理者的员工姓名及职位，并按职位排序。

3、查询职员表中，有绩效的员工姓名、薪资和绩效，并按工资倒序排列。

4、查询职员表中，员工姓名的第三个字母是A的员工姓名。

5、查询职员表中的职员名字、职位、薪资，并显示为如图－3所示效果：

提示：列之间用逗号连接，列头显示成OUT_PUT。

6、查询职员表中员工号、姓名、工资，以及工资提高百分之20%后的结果。

7、查询员工的姓名和工资，条件限定为：工资必须大于1200，并对查询结果按入职时间进行排列，早入职排在前面，晚入职排在后面。

8、查询ACCOUNT部门以外的其他部门的编号、名称以及所在地。

参考答案

各练习的参考答案如下所示：

1．

select ename, deptno from emp where deptno in (20, 30);

2．

select ename, job from emp where mgr is null order by job;

3．

select ename, sal, comm From emp where comm is not null order by  sal desc;

4．

select ename from emp where ename like ‘__A%’;

5．

select ename || ', ' || job || ', ' ||  sal OUT_PUT from emp;

6．

select empno, ename, sal,  sal * 1.2 salary from emp;

7．

select ename,  sal from emp where  sal > 1200 order by hiredate;

8．

select deptno, dname, loc from dept where dname <>  'ACCOUNT';

2 Oracle分组查询综合示例

对于如前所示的职员表 emp，需要完成如下查询：

1、查询每个部门中每个职位的最高薪水。

2、有SQL语句如下所示：

SELECT a.ename, a.sal, a.deptno, b.maxsal	
FROM emp  a,
(SELECT deptno, max(sal) maxsal
FROM emp
GROUP BY deptno)  b
WHERE a.deptno = b.deptno
AND a. sal < b.maxsal;

此 SQL 语句的功能是什么？写出其查询结果。

3、假设员工表中，员工和管理者中间只有一个层级，也就是说，每个员工最多只有一个上级，作为管理者的员工不再有上级管理者，并且，上级管理者相同的员工，他们属于同一个部门。找出EMP 中那些工资高于他们所在部门的管理者工资的员工。

4、找出EMP 中那些工资高于他们所在部门普通员工平均工资的员工。

参考答案

各练习的参考答案如下所示：

1．

select deptno, job, max(sal) from emp group by deptno, job;

2．本题的查询结果为：查询比本部门最高工资低的员工，查询结果中显示员工的部门名称、工资、部门编号以及员工对应部门的最高工资。查询结果如图-4所示。

3．

select * from emp e1,emp e2 
where e1.mgr = e2.empno and e1.sal>e2.sal;

4．

select a.* 
from emp a,(select deptno,avg(sal) sal from emp group by deptno) b
where a.deptno = b.deptno
and a.sal>b.sal

3 Oracle分组查询综合示例（提高题，选做）

1、下列 SQL语句出错的原因是（）。

    SELECT classid,AVG(MONTHS_BETWEEN(SYSDATE,entertime))
    FROM student
    WHERE AVG( MONTHS_BETWEEN (SYSDATE, entertime))>12
    GROUP BYclassid
    ORDER BY AVG(MONTHS_BETWEEN (SYSDATE, entertime));

A) select短语中不能出现组函数。

B) where短语中不能限制分组结果。

C) order by子句中不能包含组函数。

D) 组函数中不能包含单行函数。

2、有学员表Student，该表的结构如表－3所示：

编写 SQL 语句，查询每班中每个科目的最高成绩。

3、针对表－3所示的 Student 表，执行下述SQL语句：

1)  SELECT a.name, a.score, a.classid, b.avgscore
2)  FROM studenta,								
3)  (SELECT classid, avg(score) avgscore				
4)  FROM student								
5)  GROUP BY classid)b							
6)  WHERE a.classid = b.classid					
7)  AND a.score> b. avgscore;							

关于运行结果，下列描述正确的是（）。

A) 第一行出现错误

B) 第三行出现错误

C) 第六行出现错误

D) 语句正常执行

参考答案

各个练习的参考答案如下：

1．B

2．

select classid, subject, max(score)from student group by classid, subject;

3．D

4 Oracle关联查询综合示例

1、有职员表emp，若需列出所有薪水高于平均薪水值的员工信息，则有 SQL语句如下：

SELECT ename, job FROM emp WHERE sal > avg(sal);

上述语句是否正确？如果有错，写出正确的SQL语句。

2、有学员分数表，如图－5所示：

编写一条SQL 语句，查询出每门课都大于80 分的学生姓名。

3、有USERLIST 表如图－6所示：

有 CHAEGE 表如图－7所示：

请用最少的SQL 语句，产生如表－4所示的查询结果：

其中，数据是经过USERLIST、CHAEGE 表进行合适的连接，并以ACCOUNT 字段为关键字分组求和得到。

特别注意：电话号码421004 在USERLIST 表中有一条记录，在CHARGE 表中并没有记录。但是，在查询结果中，合同CCCC 具有一条记录。

4、有两个表emp和taxgrade，其字段分别为：

emp（员工）表: empname，empno，sal

taxgrade（税别）表： taxmin，taxmax，grade

上述字段中，除字段empname外，其他字段均为数值类型。

emp 表的数据如表－5所示：

taxgrade 表的数据如表－6所示：

编写SQL语句，查询编号为1的员工的税别。

5、有学员表 student，用于记录：学号，姓名，性别，年龄，组织部门；有课程表course，用于记录：课程编号，课程名称；还有选课表 sc，用于记录：学号，课程编号，成绩。三表的结构以及关联如图－8所示：

完成如下要求的 SQL 语句：

1)写一个SQL语句，查询选修了’计算机原理’的学生学号和姓名

2)写一个SQL语句，查询’周星驰’同学选修了的课程名字

6、有表test ，表结构如表－7所示：

编写 SQL 语句，查询所有年龄比所属主管年龄大的人的ID和 NAME。

7、有表 city 记载城市信息，如表－8所示：

有表 state，记载省份信息，如表－9所示：

欲得到如表－10所示的查询结果：

请编写相应的SQL语句。

参考答案

各个练习的参考答案如下：

1．上述语句错误，正确的应该是：

SELECT last_name, first_name FROM employee WHERE salary > (SELECT avg(salary) FROM emp);

2．

select * from stuscore 
where name not in 
(select distinct name from stuscore 
where fenshu < 80) 

3．

select account, count(userlist.telephone),sum(rent), 
nvl(sum(fee01), 0), 
nvl(sum(fee02), 0), 
nvl(sum(fee03), 0), 
nvl(sum(fee04), 0) 
from userlist LEFT OUTER JOIN charge 
on charge.telephone=userlist.telephone 
 group by account;

4．

Select  e.empname, e.empno, e.sal, t.grade
From emp e join taxgrade  t
on e.sal between t.taxmin and t.taxmax;

5．

1）.

select s.name
from student s join sc on s.id = sc.sid
join course c on c.id = sc.cid
where c.name = 'computer';

2）.

select c.name
from student s join sc on s.id = sc.sid
join course c on c.id = sc.cid
where s.name = ‘zhouxingchi';

6．

select employee.name 
from test employee 
where employee.age > (select manager.age 
from test manager 
where manager.id=employee.manager);

7．

select c.cityno, c.cityname, c.stateno, s.statename 
from city c left outer join  state s 
on c.stateno=s.stateno
order by(c.cityno);

5 Oracle关联查询综合示例（提高题，选做）

有科目表，记载学员的学习科目数据，如表－11所示：

有学员表，记载学员的信息，如表－12所示：

有考核规则表，记载考核规则，如表－13所示：

有学员成绩表，如表－14所示（注，各科的成绩计算方法根据考核规则中占有的比率计算）：

1、如何算出一班(class_id=1)每个学生各科的成绩？

2、请对一班(class_id=1)每个学生各科成绩的总分进行排序。

参考答案

首先创建表及插入测试数据。

创建表t_student以及插入数据的语句如下：

create table t_student(
student_id number(1) primary key,
class_id number(1),
student_name char(20));

insert into t_student values(1,1,'刘一');
insert into t_student values(2,1,'陈二');
insert into t_student values(3,1,'张三');
insert into t_student values(4,1,'李四');
insert into t_student values(5,2,'王五');
insert into t_student values(6,2,'赵六');

创建表t_assess_rule以及插入数据的语句如下：

create table t_assess_rule(
rule_id  number(1) primary key,
class_id number(1),
assess_type char(20),
scale number(2),
desc1 char(20));

insert into  t_assess_rule values(1,1,'test_score',70,'成绩(固定)');
insert into  t_assess_rule values(2,1,'check_in',10,'考勤');
insert into  t_assess_rule values(3,1,'task_scale',10,'作业完成率');
insert into  t_assess_rule values(4,1,'task_score',10,'作业成绩');
insert into  t_assess_rule values(5,2,'test_score',70,'成绩(固定)');
insert into  t_assess_rule values(6,2,'check_in',15,'考勤');
insert into  t_assess_rule values(7,2,'task_scale',15,'作业完成率');

创建表t_subject以及插入数据的语句如下：

create table t_subject(
subject_id number(1) primary key,
subject_name char(10)
);

insert into  t_subject values(1,'语文');
insert into  t_subject values(2,'数学');

创建表t_performance以及插入数据的语句如下：

create table t_performance(
performance_id number(2) primary key ,
student_id number(1),
subject_id number(1),
test_score number(3),
check_in number(3),
task_scale number(3),
task_score number(3)
);

insert into t_performance values(1,1,1,90,100,100,90);
insert into t_performance values(2,1,2,67,100,100,78);
insert into t_performance values(3,2,1,71,100,100,80);
insert into t_performance values(4,2,2,100,100,100,95);
insert into t_performance values(5,3,1,85,100,100,90);
insert into t_performance values(6,3,2,88,100,100,90);
insert into t_performance values(7,4,1,81,100,100,90);
insert into t_performance values(8,4,2,78,100,100,88);
insert into t_performance values(9,5,1,63,95,100,66);
insert into t_performance values(10,5,2,87,95,100,90);
insert into t_performance values(11,6,1,84,91,100,82);
insert into t_performance values(12,6,2,72,91,100,70);

本题中两个问题的参考答案如下：

1．

select s.student_name, sub.subject_name, p.test_score
from t_student s join t_performance p
on s.student_id = p.student_id
join t_subject sub 
on sub.subject_id = p.subject_id
where s.class_id = 1; 

2．

select s.student_name, sum(p.test_score) total_score
from t_student s join t_performance p
on s.student_id = p.student_id
join t_subject sub 
on sub.subject_id = p.subject_id
where s.class_id = 1
group by s.student_id, s.student_name
order by total_score desc;