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;
- 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);
- 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'
- 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='叶平'))
