oracle 练习题（一）

题目一：1)找出忘记填写性别的员工；

create table emp1(
id number(2) primary key,
name varchar2(2)
)
select * from emp1;
insert into emp1(id,name)
values (1,‘a’);
insert into emp1(id,name)
values (2,‘b’);
insert into emp1(id,name)
values (3,‘c’);
insert into emp1(id,name)
values (4,‘d’);

create table sex(
id number(2) primary key,
sex varchar2(2)
)
select * from sex;
insert into sex(id,sex)
values (1,‘男’);
insert into sex(id,sex)
values (4,‘女’);
insert into sex(id,sex)
values (5,‘男’);

select name from emp1
where id in (select id from emp1 minus select id from sex);

---

题目二：用一条 SQL 语句算出商品 A,B 目前还剩多少？

create table AAA
(
mc varchar2(1) not null,
sl number(3)
)
select * from AAA;
insert into AAA(mc,sl)
values (‘A’,100);
insert into AAA(mc,sl)
values (‘B’,120);

create table BBB
(
mc varchar2(1) not null,
sl number(3)
)
select * from BBB;
insert into BBB(mc,sl)
values (‘A’,10);
insert into BBB(mc,sl)
values (‘A’,20);
insert into BBB(mc,sl)
values (‘B’,10);
insert into BBB(mc,sl)
values (‘B’,20);
insert into BBB(mc,sl)
values (‘B’,30);

select AAA.mc,AAA.sl-sum(BBB.sl) 剩余 from BBB,AAA
where AAA.mc=BBB.mc
group by AAA.mc,AAA.sl;

select a.mc,a.sl-(select sum(b.sl)
from BBB b
where b.mc=a.mc) rs
from AAA a;

---

题目三：人员情况表（employee）中字段包括，员工号（ID），姓名（name），年龄（age），
文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段
查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。
请写出对应的sql语句。（需要写出相应的建表语句，插入模拟数据语句等）

create table employee
(
id number(2) primary key,
name varchar2(20),
age number(2),
wh varchar2(20)
)

以下是对employee表插入数据，数据可以自己按照需求插入：
select * from employee;
insert into employee(id,name,age,wh)
values (1,‘a’,20,‘本科以上’);
insert into employee(id,name,age,wh)
values (2,‘b’,20,‘本科以上’);
insert into employee(id,name,age,wh)
values (3,‘c’,21,‘本科以上’);
insert into employee(id,name,age,wh)
values (4,‘d’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (5,‘e’,20,‘初中以下’);
insert into employee(id,name,age,wh)
values (6,‘f’,20,‘初中以下’);
insert into employee(id,name,age,wh)
values (7,‘g’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (8,‘h’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (9,‘i’,20,‘大专’);
insert into employee(id,name,age,wh)
values (10,‘j’,20,‘大专’);
insert into employee(id,name,age,wh)
values (11,‘k’,21,‘大专’);
insert into employee(id,name,age,wh)
values (12,‘l’,21,‘高中’);
insert into employee(id,name,age,wh)
values (13,‘m’,20,‘高中’);
insert into employee(id,name,age,wh)
values (14,‘n’,20,‘高中’);
insert into employee(id,name,age,wh)
values (15,‘o’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (16,‘p’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (17,‘q’,21,‘大专’);
insert into employee(id,name,age,wh)
values (18,‘r’,21,‘高中’);
insert into employee(id,name,age,wh)
values (19,‘s’,20,‘高中’);
insert into employee(id,name,age,wh)
values (20,‘t’,20,‘高中’);
insert into employee(id,name,age,wh)
values (21,‘u’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (22,‘v’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (23,‘w’,20,‘高中’);
insert into employee(id,name,age,wh)
values (24,‘x’,21,‘初中以下’);
insert into employee(id,name,age,wh)
values (25,‘y’,21,‘大专’);
insert into employee(id,name,age,wh)
values (26,‘z’,21,‘大专’);

select wh 学历,age 年龄,count() 人数,trunc(count()/(select count(*) from employee)*100) 百分比
from employee
group by wh,age
order by age;

---

题目四：四张表：
学生表 student（sid,sname)， 教师表 teacher(tid,tname)，
课程表 course(cid,cname，ctype)， 选课表 choose_course(ccid,sid,tid,cid)

create table student
(
sid number(2) primary key,
sname varchar2(20)
)
create table teacher
(
tid number(2) primary key,
tname varchar2(20)
)
create table course
(
cid number(2) primary key,
cname varchar2(20),
ctype varchar2(20)
)
create table choose_course
(
ccid number(2),
sid number(2),
tid number(2),
cid number(2)
)

select * from student;
insert into student(sid,sname)
values(1, ‘小明’);
insert into student(sid,sname)
values(2, ‘小花’);

select * from teacher;
insert into teacher(tid,tname)
values(1, ‘陈红’);
insert into teacher(tid,tname)
values(2, ‘陈白’);

select * from course;
insert into course(cid,cname,ctype)
values(1, ‘语文’ ,‘文科’);
insert into course(cid,cname,ctype)
values(2, ‘数学’,‘理科’);

–小明选了陈红老师的语文
insert into choose_course values(1,1,1,1);
–小明选了陈红老师的数学
insert into choose_course values(2,1,1,2);
–小花选了陈红老师的数学
insert into choose_course values(3,2,1,2);
–小明选了陈白老师的语文
insert into choose_course values(1,1,2,1);
–小花选了陈红老师的语文
insert into choose_course values(4,2,1,1);

1.查找陈红老师教的学生是哪些？
select student.sname from student,teacher,choose_course
where student.sid=choose_course.sid and teacher.tid=choose_course.tid and teacher.tname=‘陈红’;

2.找学生小明所有的文科老师？
Select tname
from student,choose_course, teacher, course
where student.sid=choose_course.sid and choose_course.tid=teacher.tid and choose_course.cid=course.cid and student.sname=‘小明’ and course.ctype=‘文科’;

3.找出没有选修陈红老师的学生？
select student.sname
from student
minus
select student.sname
from student,teacher,choose_course
where student.sid=choose_course.sid and teacher.tid=choose_course.tid and teacher.tname=‘陈红’;

4.教的学生最少的老师？

select *
from teacher
where tid in (select tid
from choose_course
group by tid
having count() = (select min(count())
from choose_course
group by tid
))