Oracle练习及答案_新

练习一：

✧创建4张表：

学生表student(sid,sname)

教师表teacher(tid,tname)

课程表course(cid,cname,ctype)

选课表choose_course(ccid,sid,tid,cid)

✧在4张表中插入如下数据：

学生表(1,’小明’)

学生表(2,’小花’)

学生表(3,’小红’)

教师表(1,’陈红’)

教师表(2,’陈白’)

课程表(1,’语文’,’文科’)

课程表(2,’数学’,’理科’)

选课表(1,1,1,1)

选课表(2,1,1,2)

选课表(3,2,1,2)

选课表(4,2,1,1)

选课表(5,3,2,1)

准备

create user test identified by test

default tablespace users；

grant connect,resource to test;

grant create view to test;

rem set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK rem sqlplus /nolog

rem conn test/test@orcl

drop table student;

drop table teacher;

drop table course;

drop table choose_course;

drop view v_course;

create table student(

sid number(2),

sname varchar2(20)

)

tablespace USERS;

create table teacher(

tid number(2),

tname varchar2(20)

)

tablespace USERS;

create table course(

cid number(2),

cname varchar2(20),

ctype varchar2(20)

)

tablespace USERS;

create table choose_course(

ccid number(2),

sid number(2),

tid number(2),

cid number(2)

)

tablespace USERS;

insert into student values(1,'小明'); insert into student values(2,'小花'); insert into student values(3,‘小红'); insert into teacher values(1,'陈红'); insert into teacher values(2,'陈白'); insert into course values(1,'语文','文科'); insert into course 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(4,1,2,1); insert into choose_course values(5,2,1,1); insert into choose_course values(6,3,2,1);

create view v_course as select t.tname,s.sname,ame,c.ctype from student s, choose_course cc, teacher t, course c where s.sid=cc.sid and t.tid=cc.tid and c.cid=cc.cid ;

题目

1.查找陈红老师教的学生是哪些？

2.找学生小明所有的文科老师

3.找出没有选修陈红老师的学生

4.教的学生最少的老师

答案

1、select distinct sname

from student s,choose_course cc,teacher t

where s.sid=cc.sid and t.tid=cc.tid and t.tname=‘陈红’；

2、select t.tname

from teacher t, choose_course cc

where t.tid=cc.tid and sid=(select sid from student where sname=‘小明’)

and cc.cid=(select cid from course where ctype=‘文科’);

3、select distinct s.sname from teacher t, student s

where s.sid not in(

select s.sid

from teacher t, student s, choose_course cc

where t.tid=cc.tid and s.sid=cc.sid and t.tname=‘陈红’）

4、select tname from teacher where tid=(select tid from(select cc.tid from teacher t,choose_course cc where t.tid=cc.tid group by cc.tid order by count(sid)) where ROWNUM=1)