专题1_数据库笔试模拟-answer.doc

CORE-E-001的综合练习：
1.用sqlplus连接数据库时，为什么会出Oracle not available错误？
oracle server（即通常所说的数据库）是否启动，ORACLE_SID是否正确设置。

2.找出员工的姓中（last_name）第三个字母是a的员工名字
select last_name from s_emp where last_name like '__a%';

3.找出员工名字中含有a和e的
select first_name from s_emp where first_name like '%a%' and first_name like '%e%';
比较：
select first_name from s_emp where first_name like '%a%e%';

4.找出所有有提成的员工，列出名字、工资、提出，显示结果按工资从小到大，提成从小到大
select first_name , salary , commission_pct from s_emp where commission_pct is not null order by salary , commission_pct;

5.42部门有哪些职位
select distinct title from s_emp where dept_id = 42

6.除了Sales部门还有哪些部门
select id , name ,region_id from s_dept where name <> 'Sales'

7.显示工资不在1000到1550之间的员工信息：名字、工资，按工资从大到小排序。
select first_name , salary from s_emp where salary not between 1000 and 1550 order by salary desc

8.显示职位为Stock Clerk和Sales Representative，年薪在14400和17400之间的员工的信息：名字、职位、年薪。
select first_name , title , salary*12 ann_sal from s_emp where title in ('Stock Clerk', 'Sales Representative' ) and salary between 1200 and 1450

9.解释select id ，commission_pct from s_emp where commission_pct is null和select id ， commission_pct from s_emp where commission_pct = null的输出结果。
is null判断是否为空，=null判断某个值是否等于null，null = null和null <> null都为null。

10.select语句的输出结果为
select * from s_dept;
select * from s_emp;
select * from s_region;
select * from s_customer;
……
当前用户有多少张表，结果集有多少条记录。
select 'select * from '||table_name||';' from user_tables;
11.判断select first_name , dept_id from s_emp where salary > '1450'是否抱错，为什么？
隐式数据类型转换

CORE-E-002的综合练习：
1.改变NLS_LANG的值，让select to_char(salary*12,’L99,999.99’) from s_emp 输出结果的货币单位是￥和$
setenv NLS_LANG ' SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
setenv NLS_LANG ' AMERICAN_AMERICA.US7ASCII'

2.列出每个员工的名字，工资、涨薪后工资（涨幅为8%），元为单位进行四舍五入
select first_name , salary , round(salary*1.08) from s_emp;

3.找出谁是最高领导，将名字按大写形式显示
select upper(first_name) from s_emp where manager_id is null;

4.Ben的领导是谁（Ben向谁报告）。
select e1.first_name from s_emp e1 , s_emp e2 where e2.manager_id = e1.id and e2.first_name = 'Ben';
select e1.first_name from s_emp e1 join s_emp e2 on e2.manager_id = e1.id and e2.first_name = 'Ben';

5.Ben领导谁。（谁向Ben报告）。
select e1.first_name from s_emp e1 , s_emp e2 where e1.manager_id = e2.id and e2.first_name = 'Ben';
select e1.first_name from s_emp e1 join s_emp e2 on e1.manager_id = e2.id and e2.first_name = 'Ben'

6.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资
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;
select e.first_name , e.salary , m.first_name , m.salary from s_emp e join s_emp m on e.manager_id = m.id and e.salary > m.salary

7.哪些员工和Biri(last_name)同部门
select e1.first_name from s_emp e1 , s_emp e2 where e1.dept_id = e2.dept_id and e2.last_name = 'Biri' and e1.last_name <> 'Biri';
select e1.first_name from s_emp e1 join s_emp e2 on e1.dept_id = e2.dept_id and e2.last_name = 'Biri' where e1.last_name <> 'Biri'
select e1.first_name from s_emp e1 join s_emp e2 on e1.dept_id = e2.dept_id and e2.last_name = 'Biri' and e1.last_name <> 'Biri'

8.哪些员工跟Smith(last_name)做一样职位
select e1.first_name from s_emp e1 , s_emp e2 where e1.title = e2.title and e2.last_name = 'Smith' and e1.last_name <> 'Smith';
select e1.first_name from s_emp e1 join s_emp e2 on e1.title = e2.title and e2.last_name = 'Smith' where e1.last_name <> 'Smith';
select e1.first_name from s_emp e1 join s_emp e2 on e1.title = e2.title and e2.last_name = 'Smith' and e1.last_name <> 'Smith';

9.哪些员工跟Biri(last_name)不在同一个部门
select e1.first_name from s_emp e1 , s_emp e2 where e1.dept_id = e2.dept_id(+) and e2.last_name(+) = 'Biri' and e2.last_name is null;
select e1.first_name from s_emp e1 left outer join s_emp e2 on e1.dept_id = e2.dept_id and e2.last_name = 'Biri' where e2.id is null

10.哪些员工跟Smith(last_name)做不一样的职位
select e1.first_name , e1.title from s_emp e1 , s_emp e2 where e1.title = e2.title(+) and e2.last_name(+) = 'Smith' and e2.id is null;
select e1.first_name,e1.title from s_emp e1 left outer join s_emp e2 on e1.title = e2.title and e2.last_name = 'Smith' where e2.id is null

11.显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称
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;
select d.name dname , r.name rname , first_name , commission_pct from s_emp e join s_dept d on e.dept_id = d.id and mission_pct is not null join s_region r on d.region_id = r.id

12.显示Operations部门有哪些职位
select distinct e.title from s_emp e , s_dept d where e.dept_id = d.id and d.name = 'Operations';
select distinct e.title from s_emp e join s_dept d on e.dept_id = d.id and d.name = 'Operations'

13.整个公司中，最高工资和最低工资相差多少
select max(salary) – min(salary) from s_emp;

14.提成大于0的人数
select count(*) from s_emp where commission_pct > 0;

15.显示整个公司的最高工资、最低工资、工资总和、平均工资，保留到整数位。
select max(salary) , min(salary) , sum(salary) , round(avg(salary)) from s_emp;

16.整个公司有多少个领导
select count(distinct manager_id) from s_emp;

17.列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期
select distinct 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;
select distinct e1.first_name , e1.start_date , e1.salary from s_emp e1 join s_emp e2 on e1.dept_id = e2.dept_id and e1.start_date > e2.start_date and e1.salary > e2.salary

CORE-E-003的综合练习：
1.各个部门平均、最大、最小工资、人数，按照部门号升序排列
select dept_id , avg(salary) asal , max(salary) asal , min(salary) isal , count(*) cnt from s_emp group by dept_id order by dept_id;

2.各个部门中工资大于1500的员工人数
select dept_id , count(*) cnt from s_emp where salary > 1500 group by dept_id;

3.各个部门平均工资和人数，按照部门名字升序排列
select max(d.name) dname , max(r.name) rname , avg(e.salary) avgsal，count(*) cnt 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;
select max(d.name) dname , max(r.name) rname , avg(e.salary) avgsal , count(*) cnt from s_emp e join s_dept d on e.dept_id = d.id join s_region r on d.region_id = r.id group by e.dept_id order by dname,rname

4.列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数
select dept_id,salary,count(*) cnt from s_emp group by dept_id,salary having count(*) >= 2

5.该部门中工资高于1000的员工数量超过2人，列出符合条件的部门：显示部门名字、地区名称
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 > 1000 group by dept_id having count(*) > 2;
select max(d.name) dname , max(r.name) rname , avg(e.salary) avgsal , count(*) cnt from s_emp e join s_dept d on e.dept_id = d.id and e.salary > 1000 join s_region r on d.region_id = r.id group by e.dept_id having count(*) > 2

6.哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
select first_name , salary from s_emp where salary > (select avg(salary) from s_emp) order by salary desc;

7.哪些员工的工资，介于32和33部门（33高些）平均工资之间
select first_name , salary from s_emp where salary between (select avg(salary) from s_emp where dept_id = 32) and (select avg(salary) from s_emp where dept_id = 33);

8.所在部门平均工资高于1500的员工名字
select first_name , salary from s_emp where dept_id in (select dept_id from s_emp group by dpet_id having avg(salary) > 1500);

9.列出各个部门中工资最高的员工的信息：名字、部门号、工资
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);

10.最高的部门平均工资值的是多少
select max(avg(salary)) from s_emp group by dept_id;

11.哪个部门的平均工资是最高的，列出部门号、平均工资
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary) = (select max(avg(salary)) from s_emp);

12.哪些部门的人数比32号部门的人数多
select dept_id , count(*) cnt from s_emp group by dept_id having count(*) > (select count(*) from s_emp where dept_id = 32);

13.Ben的领导是谁（非关联子查询）
select first_name from s_emp where id in (select manager_id from s_emp where first_name = 'Ben');

14.Ben领导谁（非关联子查询）
select first_name from s_emp where manager_id = (select id from s_emp where first_name = 'Ben');

15.Ben的领导是谁（关联子查询）
select first_name from s_emp o where exists (select 1 from s_emp i where first_name = 'Ben' and i.manager_id = o.id);

16.Ben领导谁（关联子查询）
select first_name from s_emp o where exists (select 1 from s_emp i where first_name = 'Ben' and i.id = o.manager_id);

17.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期（关联子查询）
select first_name from s_emp o where exists (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);

18.哪些员工跟Biri(last_name)不在同一个部门（非关联子查询）
select first_name from s_emp where dept_id not in (select dept_id from s_emp where last_name = 'Biri');

19.哪些员工跟Biri(last_name)不在同一个部门（关联子查询）
select last_name from s_emp o where not exists (select 1 from s_emp i where i.dept_id = o.dept_id and i.last_name = 'Biri');

20.Operations部门有哪些职位（非关联子查询）
select distinct title from s_emp where dept_id in (select id from s_dept where name = 'Operations');

21.Operations部门有哪些职位（关联子查询）
select distinct title from s_emp e where exists (select 1 from s_dept d where e.dept_id = d.id and name = 'Operations';

CORE-E-004综合练习
定义
CORE-F-012——CORE-F-017的综合练习：
1.工资在1500至3000之间的员工，各自工作的年限（四舍五入，降序）。
答：select first_name,start_date,round(months_between(sysdate,start_date)/12) years
from s_emp
where salary between 1500 and 3000
order by years desc

2.公司规定，每个员工在工作25年的第一个周五，可以申请退休，查询每个员工的这天显示格式：2010-01-01
答：select first_name,to_char(start_date,'yyyy-mm-dd'),to_char(next_day(add_months(start_date,12*25),'FRIDAY'),'yyyy-mm-dd') from s_emp

3.显示员工的名字、入职日期、周几入职（用英文全拼），显示顺序从周一至周日。
答： select first_name,start_date,to_char(start_date,'DAY')
from s_emp
order by to_char(start_date-1,'d')

select first_name,start_date,to_char(start_date,'DAY') ,
next_day(start_date, 'MONDAY')-start_date diff
from s_emp
order by diff desc

select first_name,start_date, to_char(start_date,'DAY'),
decode(to_char(start_date,'d'),1,8, to_char(start_date,'d')) d1
from s_emp
order by d1;

4.写一个sql脚本，实现多对多关系（暂时不实现约束）：学生表、课程表、学生选课表，学生表包含如下信息：学号、姓名、性别、出生日期、政治面貌，课程表包含如下信息：课程号、课程名称、学分、学时、学期，学生选课表包含如下信息：学号、课程号、成绩并插入数据。
答：create table student(
id char(10),
name varchar2(20),
gender char(2),
birth date,
party varchar(10));

create table course(
id char(10),
name varchar2(20),
credit number(2,1),
period number(3),
term number(1));

create table st_c(
sid char(10),
cid char(10),
grade number(3,1));

insert into student values('2006015001','Mary','F','15-MAY-1988','A');
insert into student values('2006015002','John','M','05-JAN-1988','A');
insert into student values('2006015003','Kitty','F','23-MAY-1987','B');
insert into student values('2006015004','Tony','M','18-MAY-1989','A');
insert into student values('2006015005','Elice','F','15-MAY-1989','B');

insert into course values('MUST200901','Data structure',4,72,2);
insert into course values('MUST200902','Operating Systems',2,54,1);
insert into course values('MUST200903','Design Pattern',4,72,2);
insert into course values('MUST200904','Core Java',5,108,1);

insert into st_c values('2006015001','MUST200901',85);
insert into st_c values('2006015001','MUST200902',90);
insert into st_c values('2006015001','MUST200903',85);
insert into st_c values('2006015002','MUST200902',92);
insert into st_c values('2006015002','MUST200903',78);
insert into st_c values('2006015004','MUST200901',96);
insert into st_c values('2006015004','MUST200902',86);
insert into st_c values('2006015004','MUST200903',98);
insert into st_c values('2006015004','MUST200904',70);
insert into st_c values('2006015003','MUST200904',67);

5.在建好表的基础上完成查询：某个指定的学生（给出学生名字）选了哪些课程。
select s.name student_name,c.name course_name from student s ,course c,st_c sc where s.id=sc.sid and c.id = sc.cid and s.name = 'Mary'
select s.name student_name,c.name course_name from student s join st_c sc on s.id = sc.sid and s.name = 'Mary' join course c on c.id = sc.cid

Mary选了哪些课程
STUDENT_NAME COURSE_NAME
-------------------- --------------------
Mary Data structure
Mary Operating Systems
Mary Design Pattern

6. 某个指定的学生（给出学号）没有选哪些课程。
select c.name from course c, stu_c sc where c.id = sc.cid(+) and sc.sid(+) = '2006015004' and sc.cid is null;
select c.name from course c left outer join stu_c sc on c.id = sc.cid and sc.sid = '2006015004' where sc.cid is null

（CORE-E-005）综合练习
定义
CORE-F-018——CORE-F-020的综合练习：
1.完善sql脚本中的建表语句，在三张表上增加约束：学生表、课程表、学生选课表，在数据处理上体现出事务的概念。
答：
//student_course.sql
drop table lm_student;
drop table lm_course;
drop table lm_sc;

create table lm_student(
id char(10) primary key,
name varchar2(20) not null,
gender char(2) default 'F' not null,
birth date not null ,
party varchar2(10));

create table lm_course(
id char(10) primary key,
name varchar2(20) not null,
credit number(2,1) ,
period number(3) ,
term number(1));

create table lm_sc(
sid char(10) references lm_student(id) on delete cascade,
cid char(10) references lm_course(id) on delete cascade,
grade number(3,1),
primary key (sid,cid)
);

insert into lm_student values('2006015001','Mary','F','15-MAY-1988','A');
insert into lm_student values('2006015002','John','M','05-JAN-1988','A');
insert into lm_student values('2006015003','Kitty','F','23-MAY-1987','B');
insert into lm_student values('2006015004','Tony','M','18-MAY-1989','A');
insert into lm_student values('2006015005','Elice','F','15-MAY-1989','B');

insert into lm_course values('MUST200901','Data structure',4,72,2);
insert into lm_course values('MUST200902','Operating Systems',2,54,1);
insert into lm_course values('MUST200903','Design Pattern',4,72,2);
insert into lm_course values('MUST200904','Core Java',5,108,1);

insert into lm_sc values('2006015001','MUST200901',85);
insert into lm_sc values('2006015001','MUST200902',55);
insert into lm_sc values('2006015001','MUST200903',45);
insert into lm_sc values('2006015002','MUST200902',92);
insert into lm_sc values('2006015002','MUST200903',78);
insert into lm_sc values('2006015004','MUST200901',96);
insert into lm_sc values('2006015004','MUST200902',86);
insert into lm_sc values('2006015004','MUST200903',98);
insert into lm_sc values('2006015004','MUST200904',70);
insert into lm_sc values('2006015003','MUST200904',67);
insert into lm_sc values('2006015003','MUST200901',57);
commit;

3.修改某个学生的出生日期。
答：update lm_student set birth=add_months(birth,2) where name='Tony';

4.修改某门课程的学时。
答：update lm_course set period=80 where id = 'MUST200901';

5.删除某名学生。
答：delete from lm_student where name='Tony';
lm_sc的sid
上的FK定义成了on delete cascade,oracle先删完成了 e
delete from lm_sc where sid = (select sid from lm_student where name = 'Tony');
所以