05_oracel题集

1、已建立两张学生基本信息表，表的结构如下

Test

No	Name	Sex	Age	Department	Place
2002001	张三	男	20	计算机系	北京
2002002	李四	女	20	数学系	山东
2002003	王五	男	21	计算机系	北京
2002004	小红	女	21	数学系	河北
2002005	小李	男	20	数学系	辽宁
2002006	小王	男	22	计算机系	浙江

Test1

No	Grade	Courses
2002001	87	英语
2002002	81	数学
2002003	75	操作系统
2002004	91	网络
2002005	70	数据库
2002006	85	C语言

1、 找出是计算机系并籍贯是北京的学生的所有记录

2、 把计算机系的学生的Department改为信息学院

3、 连接查询找出成绩大于等于75分的学生的NO,Nname，Grade,Courses字段的记录

1.select t.*,t1.grade,t1.course from test t,test1 t1 where t.no=t1.no and t.department='计算机系' and t.place='北京';

2.update test set department='信息学院' where department='计算机系';

3.select t.no,t.name,t1.grade,t1.courses from test t,test1 t1 where t.no=t1.no and t1.grade>75;

2、表的结构如下

Student(s#,sname,sage,ssex)学生表

Course(c#,cname,t#)课程表

Sc(s#,c#,score)成绩表

Teacher(t#,tname)教师表

查询成绩小于60分的学生姓名和课程名

查询平均成绩并排序

查询学过“李华”老师所教的所有课程的同学的学号和姓名

修改“王小二”学生的“历史”课成绩，修改为85

删除成绩表

1.select s.sname, c.cname from student s,course c,sc where s.s#=sc.s# and c.c#=sc.c# and sc.score<60;

2.select avg(score) avgscore from sc group by s# order by avgscore;

3. select s.s#,s.sname from student s,

(select s.s#,count(sc.c#) count2 from student s,sc,

(select c.c# c1,count(c.c#) count1 from course c,teacher t

where c.c#=t.t# and t.tname='李华' --1--李华老师教的课程) a1

where s.s#=sc.s# and sc.c#=a1.c1 and count1=count2 --2) a2

where s.s#=a2.s#; --3

4.update 表名 set 列=值 where 条件

update sc set score=85 where s#=(select s# from student where sname='王小二')

and c#=(select c# from course where cname='历史')

5.drop table sc

3、表的结构如下

S(sno,sname) 学生关系。Sno为学号，sname为姓名

C(cno,cname,cteacher)课程关系。Cno为课程号，cname为课程名，cteacher为任课老师

Sc(sno，cno,scgrade)选课关系，scgrade为成绩

(1) 找出没有选修过“李明”老师讲授课程的所有学生姓名

select s.sname from s,c,sc where s.sno=sc.sno and c.cno=sc.cno and c.cteacher !='李明';

(2) 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

select s.sname ,a1.avggrade from s,

(select sno,avg(scgrade) avggrade from sc where scgrade<60 group by sno having count(*)>=2) a1

where s.sno=a1.sno;

(3) 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

select sname from s where sno in(

select sno from sc where cno=1

intersect

select sno from sc where cno=2);

3、在学生选课系统的后台数据库中，主要有3个表：

学生基本信息表stdinfo(学号SN，姓名Name，性别Sex,专业Speciality,出生年月Bady),

选课表Mycourse(学号SN、课程号CourseNum、分数Credithour),

课程表Coursetab(课程号CourseNum,课程名Coursename,所属专业Speciality, 分数Credithour)

要求：写出SQL语句。

1) 查询所有SQL课程的学生学号，姓名和专业。

2) 查询所有选计算机这门课程的学生学号，姓名和专业，并按照学号降序排序

3) 删除所有选择数学的同学的选课记录

4) 查询有哪些课程有30个以上的同学报选

5) 查询有哪些课程没有被任何同学报选

1.select s.sn,s.name,c.speciality from stdinfo s,coursetab c where s.specaility=c.specaility and c.courename='SQL';

2.select s.sn,s.name,c.speciality from stdinfo s,coursetab c where s.specaility=c.specaility and c.courename='计算机' order by s.sn desc;

3.delete from mycourse where courseNum=(select coursenum from coursetab where coursename='数学')

4.select coursename from coursetab where coursenum in (select coursenum from mycourse group by coursenum having count(*)>30);

5. select coursename from coursetab where coursenum in

(select coursenum from coursetab

except

select distinct coursenum from mycourse)

4、以下有两个表

表一 AAA

Mc种类	S1库存总量
A	997
B	1234

表二 BBB

Mc种类	S1出库数量
A	105
A	213
B	116
B	211
B	303

1) 分别写出AAA的建表语句；

2) 用一条SQL语句求出A出库的数量是多少？

3) 用一条SQL语句求出A,B各剩下多少？

2.select sum(s1出库数量) from bbb where mc种类='A'

3. select a1.s1库存总量-a2.outs1 from aaa a1,( select mc种类,sum(s1出库数量) outs1 from bb group by mc种类) a2 where a1.mc种类=a2.mc种类

5、表结构如下：

学生表：Student(S#,Sname,Sage,Ssex) ，学号：S#，学生姓名：Sname，学生年龄：Sage，学生性别：Ssex

课程表：Course(C#,Cname,T#)，课程编号：C#，课程名字：Cname，教师编号：T#

成绩表：SC(S#,C#,score)， 学号：S#，课程编号：C#，成绩：score

教师表：Teacher(T#,Tname)，教师编号：T#，教师名字：Tname

问题：

1、查询"001"课程比"002"课程成绩高的所有学生的学号；

select a1.s# from

(select s#,score from sc where c#=001) a1,

(select s#,score from sc where c#=002) a2

where a1.s#=a2.s# and a1.score>a2.score;

2、删除学习“叶平”老师课的SC表记录；

delete from sc where c# in(select t.c# from course c1, teacher t where c1.t#=t.t# and t.tname='叶平')

3、向SC表插入一笔数据(S#:200806505; C#:201101, score:85)；

insert into sc values(20080605,201101,85);

4、把"SC"表中“叶平”老师教的课的成绩都更改为此课程的平均成绩。

update sc set score=(select avg(score) from sc where c# =(select t.c# from course c1, teacher t where c1.t#=t.t# and t.tname='叶平'))

where c# =(select t.c# from course c1, teacher t where c1.t#=t.t# and t.tname='叶平'))