（一）选择题（含单项选择和多项选择题） 每题2分，合计20分

1聚合函数max（）括号里面可以填写的类型有    ABCD

A：number  B：char    C：Varchar2    D：date

2下列没有去重功能的指令有    BC

A：union     B：order by   C：union all  D：group by

3关于空值，下面哪些说法是对的  AB

A：空值跟任何值进行算术运算，得到的结果都为空值

B：空值跟任何值进行关系运算，得到的结果都为不成立

C：空值参与聚合运算会报错。

D：排序的时候，空值永远是最小的

4下列命令属于DDL指令的有  AC

A；create     B：commit    C：drop   D：delete

5 以下不是属于索引类型的是 B

A：位图索引 B：列表索引  C：唯一索引  D：范围索引

6 以下属于外键特性的是  CD

A 外键是唯一非空的

B 外键约束依赖于另外一个表的列，该列可以不是主键

C 外键删除数据必须先删除主表数据

D 外键可以保证数据的完整性

7 以下对于查询结构说明不正确的是   A

A where和having都是过滤，一个是分组前，一个是分组后

B 关键字的执行顺序为 from->where ->group by ->having->order by->select

C group by和order by 后面接的列可以是select 后面列的别名。

D 使用聚合函数必须使用group by

8 以下关于finereport说法正确的是    D

A finereport参数设置数据集参数用${}表示

B finereport设置模板参数可以不用再过滤

C finereport超链接设置需要先设置参数才有效果

D finereport可以进行权限设置

9 以下对于数据仓库描述正确的是   AC

A 数据仓库主要是面向分析的

B 数据仓库是个软件，数据不能存在oracle

C 数据仓库的架构可以为ods-dw-dm

D 解决数据同步的方法有拉链表

10 以下对于模型说法正确的是  CD

A 雪花模型的优点是没有冗余

B 星型模型和雪花模型比E-R模型好

C 星型模型是指所有维度表都跟事实表直接关联在一起的

D 雪花模型是指存在一个或者多个维度表跟事实表间接连接在一起

（二）问答题  

1   写出查询语句的语法结构、对查询关键词进行排序、对关键词进行解释。  3分

 

5 Select  列    查询列

1 From 表   获取表数据

2 Where  条件   分组前过滤表数据

3 Group by 列  分组

4 Having  分组后过滤表数据

6 Order by 列 根据列进行排序

 

2   写出TRUNCATE和DELETE和drop的区别。 3分

Drop 是删除表语句会将表结构一起删除

Truncate 是删除表数据的，先删除表，在重建表结构，无法回滚，是DDL语言，不受触发器和外键干扰

Delete  是删除表数据的DML语言，可以通过条件部分删除，可以回滚，受外键和触发器干扰，不会重置高水位

 

 

3   在emp表中查出入职时间在1981年1月1日到1983年1月1日的员工信息。 3分

Select * from emp where 时间

select *
from emp
where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-01-03','yyyy-mm-dd');

 

 

4   用2种或者2种以上的方法（sql语句）找出EMP中的ename是A开头的。    3分

Where ename like ‘A%’

Where substr(ename,1,1)=’A’

Where instr(ename,’A’,1)=1

 

5   求出字符串dasdsw,sssdsw,dszzsad,csdwd,sdaswd 第二个到第四个,中的内容。 3分(写sql)

--方法一
select substr(a,instr(a,',',1,2)+1,instr(a,',',1,4)-instr(a,',',1,2)-1)
from (
         select 'dasdsw,sssdsw,dszzsad,csdwd,sdaswd' a
         from dual
     );
--方法二
select regexp_substr(a,'[^,]+',1,3)||','||regexp_substr(a,'[^,]+',1,4) a1
from (
         select 'dasdsw,sssdsw,dszzsad,csdwd,sdaswd' a
         from dual
     );

 

 

6   查询出emp表中的第3行和6-12行。 3分

select * from (
                  Select rownum r, emp.*
                  from emp
              )
where r=3 or r>=6 and r <=12;

 

7   利用rowid对emp表中empno和deptno值一样的行进行去重。3分

delete from EMP
where rowid not in (
    select min(rowid) from EMP
    group by EMPNO,DEPTNO
    );

 

8   求出前两个月的当月的第二天；3分

select last_day(add_months(sysdate,-3))+2  from dual;
select trunc(add_months(sysdate,-2),'mm')+1  from dual;

 

 

9   在学生表(student_1)里更改一条记录,将学号(s001)的学生性别改为男然后删除掉这条信息 （2条语句）。3分

update student_1 set sex='男' where stuno='s001';
delete from student_1 where stuno='s001';

 

（三）解答题

1 给你一份2018年8月8日的用户访问App各页面的日志流水数据（记为t_sala_log），

有userid(用户唯一标识),pageid(页面唯一标识)，time(访问时间)三个字段如下;

userid	pageid	time
10017	5001	2018-08-08 12:02:13
10101	0072	2018-08-08 15:02:13

 

问题1：如何计算当天的UV（UV是访问页面的用户数量）？（写sql） 2分

Select count(1) from (Select userid  from  t_sala_log group by userid)

 

问题2：若userid+pageid组合形成一个有效页面访问，如何计算出当天APP有多少个不重复的有效页面访问？（写sql） 3分

Select count(1)
from (
    Select userid  
    from  t_sala_log
    group by userid,pageid
    );

 

问题3：如何查询出每个人当天最后一次访问了APP哪个页面？（写sql） 5分

Select userid,pageid
From(
Select t.*,row_number  (partition by userid oder by time desc) r
From t_sala_log t
)
Where R=1;

 

2 创建存储过程，输入部门编号，输出emp表中相对应职工的员工编号、姓名、工作岗位以及部门人数；5分

create procedure sp_emp(
v_deptno number
)
as
    cursor a is
        select emp.*,count(1) over() 人数 from emp where DEPTNO=v_deptno;
begin
    for i in a loop
        DBMS_OUTPUT.PUT_LINE(i.empno||','||i.JOB||','||i.ename||','||i.人数);
        end loop;
end;
call sp_emp(10);

 

 

 

 

3 创建函数，根据给定的部门编号(提示: 利用&)计算该部门所有职工的平均工资。5分

create function fun_get_avg_sal(
v_deptno number
)return number
as
    v_avg_sal number;
begin
    select avg(sal) into v_avg_sal from emp where DEPTNO=v_deptno;
    return v_avg_sal;
end;
select empno,sal,DEPTNO,fun_get_avg_sal(DEPTNO) from emp;

 

4 求出200-900之间的全部素数，按照大小排列出来；5分

 

declare
    r number :=0;
begin
    for i  in  200..9000 loop
        for j in 2..sqrt(i) loop
            if mod(i,j)=0 then
                r:=1;
                exit;
            end if;
            end loop;
        if r=0 then DBMS_OUTPUT.PUT_LINE(i); end if;
        r:=0;
        end loop;
end;

 

 

 

 

 

 

 

 

 

 

 

 

 

（四）解答题

1  请简述索引的种类以及失效的原因；5分

种类：

按功能：位图索引，唯一索引，组合索引，函数索引

按实现：B树索引，反向键索引，哈希索引

失效的原因

1. 全表扫描 使用了！，not等等

2. 隐式转换，等号两边的类型不一致

3. 列使用了函数

4. 列进行了计算

5. 组合索引没有带上第一列

 

 

2  请说出排序函数以及之间的区别； 4分

Row_number  1 2 3 4 5

Rank        1 2 2 4 5

Dense_rank  1 2 2 3 4

 

 

3  请说出分析方式与聚合函数的区别；4分

1. 分析函数是用partition by分组的 聚合函数是group by分组

2. 聚合函数 配合开窗函数 可以当做分析函数使用，加order by可以实现累计效果

3. 分析函数 返回 原有的行数 ，聚合函数 返回聚合后的行数 

 

 

 

6  请简单描述下E-R模型和数仓模型。以及他们的区别； 4分

①E-R模型 是业务数据库设计的数据模型，通过实体进行关系映射，从而设置表结构。通常按照三范式的规则设置。数据冗余度会特别小。

②数仓模型包含了星型模型，雪花模型等，主要面向分析

其中星型模型是指所有事实表和维度表都直接关联在一起的类型，性能比较好。但是冗余比较大，扩展性也会比较差一点。设计比较简单。

其中雪花模型是指在星型的基础上，存在一个或者多个维度表跟事实表间接关联在一起。冗余比较少，但是查询性能比较慢一点。

 

7  请简述一般数据清洗，处理什么内容，以及处理的步骤 4分

答：一般对数据清洗的话，主要还是看业务需求。其中业务的异常数据，例如会员卡号不符合规则这样的数据。还有常规的异常数据，例如年龄为负的。还有一些null处理，拆分字段，合并字段，统一格式等等。通常我们会先观察数据，确定我们需要清洗的数据，然后再将数据读入到数据流中，在通过清洗处理，输出到我们 的目标表中。