题目一：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 ))