Oracle考试试题---达内科技.pdf

试题类型试题类型 单选题单选题 试题 1 第一题 根据需求说明找到对应的 SQL 完成 2 11 题 A select max salary min salary sum salary round avg salary from s emp B select max salary min salary sum salary avg salary from s emp C select e1 first name e2 first name from s emp e1 s emp e2 where e1 title e2 title and e2 last name Smith and e2 last name is null D select first name start date round months between sysdate start date 12 years from s emp where salary between 2000 and 3000 order by years desc E select e1 dept id e1 salary count cnt from s emp e1 s emp e2 where e1 salary e2 salary and e1 dept id e2 dept id and e1 id e2 id group by e1 id e1 salary F select d name dname r name rname first name commission pct from s emp e s dept d s region r where e dept id d id and d region id r id and mission pct is not null G select max d name dname max r name rname avg e salary avgsal from s emp e s dept d s region r where e dept id d id and d region id r id group by dname order by dept id rname H select d name r name avg e salary from s emp e s dept d s region r where e dept id d id and d region id r id and r name Europe group by d name r name I select d name dname r name rname first name commission pct from s emp e s dept d s region r where e dept id d id and d region id r id and mission pct is null J select dept id avg salary avgsal from s emp group by dept id having avg salary select avg salary from s emp where dept id 32 K select e1 first name e2 first name from s emp e1 s emp e2 where e1 title e2 title and e2 last name Smith and e2 last name is null L select e1 dept id e1 salary count cnt from s emp e1 s emp e2 where e1 salary e2 salary and e1 dept id e2 dept id and e1 id e2 id group by e1 dept id e1 salary M select dept id avg salary from s emp group by dept id where avg salary 2000 N select dept id avg salary from s emp group by dept id having avg salary 2000 O select max d name max r name avg e salary from s emp e s dept d s region r where e dept id d id and r name Europe group by e dept id P select dept id avg salary avgsal from select avg salary from s emp where dept id 32 Q select max d name dname max r name rname avg e salary avgsal from s emp e s dept d s region r where e dept id d id and d region id r id group by dept id order by dname rname R select dept id salary from s emp where dept id salary not in select dept id avg salary from s emp group by dept id S select dept id salary from s emp where dept id salary in select dept id avg salary from s emp group by dept id T select first name start date round between sysdate start date 12 years from s emp where salary between 2000 and 3000 order by years desc 注 第 2 11 题答案选项为试题 1 第一题 A T 中的某一项 请注意选择 试题 2 显示整个公司的最高工资 最低工资 工资总和 平均工资 保留到整数位 试题 3 列出每个部门中有同样工资的员工的统计信息 列出他们的部门号 工资 人数 试题 4 各个部门平均工资和人数 按照部门名字升序排列 试题 5 显示有提成的员工的信息 名字 提成 所在部门名称 所在地区的名称 试题 6 哪些员工跟 Smith last name 做不一样的职位 试题 7 平均工资大于 2000 元的各个部门的平均工资 部门号 平均工资 试题 8 Europe 地区的各个部门的平均工资 显示部门名称 地区名称 平均工资 试题 9 哪些部门的平均工资比 32 部门的平均工资高 试题 10 哪些员工的工资等于本部门的平均工资 试题 11 工资在 2000 至 3000 之间的员工 各自工作的年限 四舍五入 降序 试题 12 第二题 根据需求说明找到对应的 SQL 完成 13 22 题 A select dept id count cnt from s emp group by dept id having count select count from s emp where dept id 32 B select e ename e sal from emp e salgrade s where e sal between s losal and s hisal C select e1 dept id e1 salary count cnt from s emp e1 s emp e2 where e1 salary e2 salary and e1 dept id e2 dept id and e1 id e2 id group by e1 dept id e1 salary D select e first name e salary m first name m salary from s emp e s emp m where e manager id m id and e salary select from s emp where dept id 32 K select e1 first name e1 start date e1 salary from s emp e1 s emp e2 where e1 dept id e2 dept id and e1 start date e2 start date and e1 salary e2 salary L select d name r name avg e salary from s emp e s dept d s region r where e dept id d id and d region id r id group by d name r name M select first name salary dept id from s emp where dept id salary in select dept id max salary from s emp group by salary N select first name salary dept id from s emp where dept id salary in select dept id max salary from s emp group by dept id O select first name to char start date yyyy mm dd to char yyyy mm dd next day add months start date 12 25 FRIDAY from s emp P select first name from s emp o where select 1 from s emp i where i dept id o dept id and o start date i start date and o salary i salary Q select max d name dname max r name rname count cnt from s emp e s dept d s region r where e dept id d id and d region id r id and e salary 2000 group by dept id having count 3 R select max d name dname max r name rname count cnt from s emp e s dept d s region r having e dept id d id and d region id r id and e salary 2000 group by dept id where count 3 S select e1 dept id e1 salary count cnt from s emp e1 s emp e2 where e1 salary e2 salary and e1 dept id e2 dept id and e1 id e2 id group by e1 salary T select e first name e salary m first name m salary from s emp e s emp m where e manager id m id and e salary m salary 注 第 13 22 题答案选项为试题 12 第二题 A T 中的某一项 请注意选择 试题 13 列出每个部门中有同样工资的员工的统计信息 列出他们的部门号 工资 人数 试题 14 该部门中工资高于 2000 的员工数量超过 3 人 列出符合条件的部门 显示部门名字 地区 名称 试题 15 列出在同一部门