笔试_oracle（8）

一、 综合题（每小题10分，共200分）

1. 查询所有从事"CLERK"工作的雇员姓名及其部门名称、部门人数。

参考答案：

select ename,dname,count(ename) from emp e ,dept d
        where lower(job)='clerk' and e.deptno=d.deptno
        group by ename,dname;

解析：

先分析主干：查询员工姓名、部门名称、部门人数 select ename,dname,count(ename) from ...再根据限制语句编写 where语句和分组语句。

2. 查询在部门"sales"（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

参考答案：

select ename from emp
            where deptno= (select deptno from dept where lower(dname)='sales' );

解析：

先查询出销售部的部门编号，再根据部门编号查询员工姓名。

3. 查询出king所在部门的工作年限最大的员工名字。

参考答案：

select  ename, hiredate from emp

           where  hiredate in

              (

                select min(hiredate) from (select hiredate from emp where deptno in

                             (select deptno from emp where ename='KING') )

              )

解析：

子查询中可以再次嵌套子查询。

4. 查询出没有员工的那个部门的部门编号和部门名称。

参考答案：

方法一：

select  a.DEPTNO as 部门编号, DNAME as部门名称

from  Dept a

          left join Emp b

          on a.DEPTNO=b.DEPTNO

where  b.EMPNO is null；

 

方法二：

select deptno as 部门编号,DNAME as部门名称

from  Dept a

where not exists (

          select 1 from Emp b

          where a.deptno=b.deptno

           )；

解析：

连接查询有时候可以写为等价的子查询。

5. 查询雇员的领导信息，要求领导的薪水要超过3000。

参考答案：

select distinct m.* from emp e,emp m
           where m.sal >3000 and e.mgr=m.empno;

解析：

通过自连接在同一张表间进行“多表查询”。

6. 显示雇员工资的最大差额。

参考答案：

elect distinct max(sal)-min(sal) as 最大差额 from emp;

解析：

在做题的时候，如果发现查询结果可能有重复，就要考虑使用distinct。

7. 显示所有雇员名及其全年收入(工资+补助)，并指定列别名“年收入”。

参考答案：

select emp.ename as 雇员名,nvl2(comm,comm+sal,sal)*12 as年收入 from emp;

解析：

null的运算结果仍然为null，需要通过nvl()或nvl2()先对null进行运算。

8. 显示工资不在1500到2850之间的所有雇员名及工资。

参考答案：

select emp.ename as 雇员名,emp.sal as 工资 from emp
         where emp.sal not between 1500 and 2850;

解析：

not可以对where条件进行取反操作。

9.有一个学生成绩单，要建立数据表把数据存储此表中：需要包括的字段为：

1.写出创建表的语法，表名自定  

2.查询出生时间在1990-01-01后的学生所有课程成绩  

3.查询学生总数  

4.查询该班学生Java基础（课程编号为000101）的平均成绩 

参考答案：

1.CREATE TABLE STUDENT (stuName VARCHAR2(60),stuID VARCHAR(100),stuBirthday DATE,classID NUMBER(8),

        stuScore NUMBER(5,2),stuRemark VARCHAR2(400));

2.SELECT stuName,stuScore FROM STUDENT WHERE stuBirthday>TO_DATE('1990-01-01','yyyy-mm-dd');

3.SELECT count(DISTINCT(STUNAME)) "学生总数" FROM STUDENT;

4.SELECT AVG(stuScore) FROM STUDENT WHERE classID=000101；

解析：

1. 此处需要注意，oracle数据库里字符的概念是对英文来说的，但是一个汉字占两个字符，所以题目中要求插入n个汉字，在创建表时，字符的数量要写2n。

2. 这里一定要注意字符到日期的转换，我们输入的‘1990-01-01’在系统看来是字符，当然字符跟日期是没有办法比较的（数据类型不同嘛），所以在比较之前一定要先将字符类型的‘1990-01-01’转换成日期类型。

3. 在学生成绩表中查询出所有学生，然后去除重复再计数，即得出学生总数

4. 此题看似简单，实则暗藏杀机，首先要想到一点，有些学生可能缺考，此时他的Java基础的分数就为null，但是系统对null会自动忽略，然后人数就会减少（少了Java基础得分为null的同学），显然这种情况得出的平均分是不正确的（不信的话，假如系统不处理null的情况，老师为了好看的平均分会让成绩差的同学缺考~~）。扯远了，处理null可以用NVL(stuScore,0)；假如stuScore为null，就当成0来计算。

10. 根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点(最终结果保留两位小数，并进行四舍五入操作)。

参考答案：

update  emp
             set  sal= round(sal * (1+(sysdate - hiredate)/365/12/100),2);

解析：

(sysdate - hiredate)/365/12可以算出工作了多少个整月。

11. 查询在每个部门工作的员工数量、平均工资和平均服务年限 。要求对个位数字四舍五入。

参考答案：

select count(ename),round(avg(sal)),round(avg((sysdate-hiredate)/365)) from emp where deptno in (select deptno from emp group by deptno) ;

解析：

可以嵌套组函数，如round(avg(...))。

12. 显示所有雇员名、雇员工资及工资级别。

参考答案：

select emp.ename, emp.sal, salgrade.grade
from emp,salgrade
where emp.sal>salgrade.losal and emp.sal<=salgrade.hisal;

解析：

对于scott方案中提供的四张表结构，读者必须非常熟悉。

13. 查询员工表，使查询结果的格式如下：
SMITH的年薪是9600

参考答案：

select concat(concat(ename,'的年薪是'),sal*12+nvl(comm,0))  年薪 from emp;或select ename|| '的年薪是'|| (sal*12 +nvl(comm,0)) from emp;0

解析：

可以通过concat()或||对列进行拼接；并注意常量需要使用单引号。

14. 观察下列表结构：学生表：


成绩表

完成下列需求：

1. 查询所有姓张的学生，并按年龄从小到大排列

2. 查询计算机科考成绩不及格的学生

3. 向Student表中增加一条学生记录(学号：97005 姓名：赵六 年龄：20)

4. 李五的年龄记录错了，应该是21，请写SQL，根据主键进行更新

5. 删除Student中没有考试成绩的学生记录，请使用not  in条件

参考答案

1.SELECT * FROM Student WHERE stuName LIKE '张%' ORDER BY stuage;

 

2.SELECT s.STUID,s.STUNAME,r.CLASS,r.SCORE FROM STUDENT s 

    LEFT JOIN RESULT r ON (s.STUID=r.STUID) 

    WHERE r.SCORE<60;

3.INSERT INTO StuDENT (STUID,STUNAME,STUAGE) VALUES(97005,'赵六',20);

4.UPDATE Student SET STUAGE=21 WHERE STUNAME='李五'

5.DELETE FROM Student WHERE STUID NOT IN 

    (SELECT DISTINCT(STUID) FROM RESULT 

        WHERE SCORE IS NOT NULL);

解析：

5.首先在RESULT表中查询出成绩不为空学生的STUID，然后在student表中删除STUID不在上一步查询的结果集中的学生。

15. 为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%。

参考答案：

update  emp

             set  sal=decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18));

解析：

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 

16. 显示"BLAKE"同部门的所有雇员，但不显示"BLAKE"。

参考答案：

select * from emp

             where emp.deptno=(select emp.deptno from emp where emp.ename='BLAKE' )

                         and emp.ename<>'BLAKE' ;

解析：

子查询的查询结果，可以作为主查询的查询条件。

17. 根据下面创建表的语句完成SQL查询

Create table org_organization(

   Organization_id        NUMBER(10)        not null,--组织结构ID（主键）

   Parent_organization_id  NUMBER(10)        null,--从属的上级组织结构ID

   Organization_name     varchar(60)   not null,--组织机构名称

   Organization_desc      varchar(240)  null,--组织机构说明

   Organization_type      NUMBER(1)       not  null,--组织类型：1=智能型；2=业务型

   Primary key(organization_id)

);

1.查询组织结构ID大于“100”的所有组织机构的信息？
2.查询“组织机构名称”中包含“北京”的所有组织机构的信息？  
3.查询组织机构ID等于“1000”的组织机构的信息以及从属的上级组织机构的信息？  
4.统计“职能型”和“业务型”的组织机构，各有多少个？（注：用一条sql语句实现）

参考答案：

1.SELECT*FROM org_organization WHERE Organization_id>100;

2.SELECT*FROM org_organization WHERE Organization_name LIKE '%北京%';

3.SELECT ORG1.*,ORG2.* FROM org_organization org1 

    LEFT JOIN org_organization org2 

    ON ORG1.Parent_organization_id=ORG2.Organization_id

    WHERE Organization_id=1000;

4.SELECT Organization_type,count(Organization_id) FROM org_organization GROUP BY Organization_type

解析：

1. 略  

2. 模糊查询需要用like关键字，%是正则表达式的概念里的通配符，代表0或多个字符，_代表1个字符。正则表达式在各种程序设计技术里用到的都特别多，有兴趣、学有余力的同学可以到网上自学一下。 

3. 此题是简单的连接查询，由于组织机构和直接从属上级机构在同一张表里，所以需要将一张表以别名的形式当成两张表来连接查询，连接条件是ORG1.Parent_organization_id=ORG2.Organization_id，需要在结果里筛选出Organization_id=1000的组织机构。 

4. 很明显要使用分组查询，分组的依据是Organization_type，然后统计出每组里的Organization_id的数量即可。

18. 显示所有雇员的平均工资、总计工资、最高工资、最低工资。

参考答案：

select round(avg(emp.sal),2) as 平均工资,

          count(emp.sal) as 总计工资,

          max(emp.sal) as 最高工资,

          min(emp.sal) as 最低工资

                  from emp ;

解析：

组函数可以嵌套使用，如round(avg(...))；列名可以直接写，如select  sal from emp也可以通过 “别名.列名”使用，如：select e.sal from emp e;也可以通过“表名.列名” 使用，如：select emp.sal from emp;

19. 查询出KING所在部门的部门号\部门名称\部门人数。

参考答案：

select e.deptno as 部门号,d.dname as部门名称,

    (

         select count(*) from (select deptno from emp where deptno in (select deptno from emp where ename='KING') )

    ) as 部门人数

   from  emp e, dept d

   wheree.deptno = d.deptno and e.ename = 'KING';

解析：

子查询可以出现在where、from、select等后面。

20. 查询薪金高于30号部门的所有员工薪金的员工姓名、薪金及部门名称。

参考答案：

select ename,sal,dname from emp,dept
        where sal > (select max(sal) from emp where deptno=30)
                    and emp.deptno=dept.deptno;

解析：

最高薪金是max(sal)，比最高薪金高 就意味着比所有员工的薪金高。