1001 查询全体男同学信息情况
select * from student where sex='男'
1002 查询选修了1号课的学生的学号和成绩
select sno,grade from sc where cno='1'
1003 查询1989年以前出生的学生学号和姓名和出生日期（提示请用year(csrq)函数来取出生年号再与1989比较)
select sno,sname,csrq from student where year(csrq)<1989
1004 查询信息系所有女生的学号,姓名,及所在系
select sno,sname,sdept from student where sdept='信息系' and sex='女'
1005 查询课程名是数据库的课程号和学分
select cno,ccredit from course where cname='数据库'
1006 查询先行课号为5的课程号和课程名
select cno,cname from course where cpno='5'
1007 查询英语系90后的学生情况(注90年后出生的)
select * from student where sdept='英语系' and year(csrq)>=1990
1008 查询计算机系或信息系中年龄超过21岁的同学情况.(设当前年为2010年,提示用当前年减去出生年再与21岁比较)
select * from student where (sdept='计算机系' or sdept='信息系') and (2010-year(csrq))>21
1009 请将3号课及4号课学生的学号课程号及成绩显示出来
select sno,cno,grade from sc where cno='3' or cno='4'
1010 查询所有先行课程号为0且学分为2的课程名,先行课程号,及学分
select cname,cpno,ccredit from course where cpno='0' and ccredit=2
2010 查询学分为3以上(不含3),且课程名中包含着'数据'二字的所有课程名及学分 select cname ,ccredit from course where ccredit>3 and cname like '数据%'
2001 查询年龄在20-23岁之间（含20与23岁）的学生姓名，系别，年龄（请不要用between语句提交当前年2010
） select sname,sdept,(2010-year(csrq)) as age from student where (2010-year(csrq))>=20 and (2010-year(csrq))<=23
2002 查询选修过课程的所有学生的学号（不许重复）
select distinct sno from sc
2003 请将选修了课程的同学按课程号升序，成绩降序排序
select * from sc order by cno,grade desc
2004 请将选了1号课程的同学按成绩降序排序
select * from sc where cno='1' order by grade desc
2005 查询选修了1号课的成绩大于85分的学号和成绩
select sno,grade from sc where cno='1' and grade>85
2006 查询所有李姓同学情况
select * from student where sname like '李%'
2007 查询所有两字姓名的张姓同学的姓名，性别与系别
select sname,sex,sdept from student where sname like '张_'
2008 查询名字中第3个字为铃的学生的姓名和学号
select sname,sno from student where sname like '__铃'
2009 查询信息系所有不姓刘的同学的学号和姓名
select sno,sname fro
m student where sdept ='信息系' and sname not like '刘%'
3001 查询所有选修过课的学生的姓名，课程名及成绩
select sname,cname,grade from student,sc,course where student.sno=sc.sno and https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o
3002 查询选修了1号课且成绩大于80分的的同学的姓名，课程号及成绩
select sname,cno,grade from student,sc where student.sno=sc.sno and cno='1' and grade>80
3003 查询没有选1号课的女生姓名，课程号及成绩,并将成绩按降序排序
select sname,cno,grade from student,sc where student.sno=sc.sno and cno <> '1' and sex='女' order by grade desc
3004 查询选修了数据库课的所有男生的姓名及该课的成绩
select sname,grade from student,sc ,course where student.sno=sc.sno and https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o and cname='数据库' and sex='男'
3005 查询选修了数据库课的最高成绩.最低成绩和平均成绩（注用as 来表示最低，最高及平均成绩
select max(grade) as '最高成绩' ,min(grade) as '最低成绩' ,avg(grade) as '平均成绩' from sc,course where https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o and cname='数据库'
3006 查询选修了1号课或3号课的学生的学号,姓名,成绩(请用谓词的方法）请参考光盘课件简单查询例7
select sc.sno ,sname from student,sc where student.sno=sc.sno and cno in('1','3')
3007 查询李勇同学的所有选修过课程的总成绩及平均成绩并用“总成绩”及“平均成绩”表示出来
select sum(grade)as 总成绩,avg(grade) as 平均成绩 from student,sc where student.sno=sc.sno and sname='李勇'
3008 查询计算机系所有选了数据库成绩在80分以上的同学的姓名及成绩
select sname,grade from student,sc,course where student.sno=sc.sno and https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o and cname='数据库' and sdept='计算机系' and grade>80
3009 查询选修李勇同学所学课程的学分总和（注用as 总学分）
select sum(ccredit) as 总学分 from student,sc,course where student.sno=sc.sno and https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o and sname='李勇'
3010 查寻选了1号课，计算机系，女生的人数.
3011 "select count(*) as 人数 from student,sc where
student.sno=sc.sno and cno='1' and sdept='计算机系' and sex='女';"
4001 查询所有选修课平均成绩大于85分的同学的学号和平均成绩。
select sno,avg(grade) from sc group by sno having avg(grade)>85
4002 查询至少选修过2门课程的信息系学生学号
select sc.sno from sc,student where student.sno=sc.sno and sdept='信息系' group by sc.sno having count(cno)>=2
4003 查询各系女生人数，分别用系，女生人数表示出来。
select sdept ,count(sex) from student where sex='女' group by sdept
4004 查询各门课程所选的人数并用课程号及所选人数表示出
来
select cno ,count(*) as 所选人数 from sc group by cno
4005 查询计算机系所选课程中每个同学的平均成绩，并用学号和平均成绩表示出来 select sc.sno ,avg(grade) as 平均成绩 from student,sc where student.sno=sc.sno and sdept='计算机系' group by sc.sno
4006 查询选修的学分数超过8个学分的同学学号及学分数
"select sno,sum(ccredit) from sc ,course where https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o group by sno having sum(ccredit)>8
"
4007 查询李勇，刘晨两位同学的平均成绩，并用学号和平均成绩表示出来
select sc.sno,avg(grade) as 平均成绩 from sc ,student where sc.sno=student.sno and sname in('李勇','刘晨') group by sc.sno
4008 查询信息系仅选过一门课的同学学号
select sc.sno from student,sc where student.sno=sc.sno and sdept='信息系' group by sc.sno having count(*)=1
4009 查询数据库和数学这两门课的选课人数，并用课程号和选课人数表示出来 "select https://www.doczj.com/doc/4f14753610.html,o as 课程号,count(*) as 选课人数 from sc,course where
https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o and cname in('数据库','数学') group by https://www.doczj.com/doc/4f14753610.html,o"
4010 查询每位同学选修课的平均成绩，并按平均成绩从高到低排序，同时要用学号和平均成绩表示出来 select sno ,avg(grade) as 平均成绩
from sc group by sno order by avg(grade) desc
5001 查询同时选修了1号课和3号课的同学的姓名及系
5002 "select sname,sdept from student where sno in (select sno from sc where sno in(select sno from sc where cno='1') and cno='3')
"
5002 查询同时选修了数据库及数学的同学的学号
select sno from sc where sno in(select sno from sc where cno in (select cno from course where cname='数据库')) and cno in(select cno from course where cname='数学')
5003 查询选修了全部课程的同学的姓名
select sname from student where sno in(select sno from sc group by sno having count(*)=select count(*) from course
5004 查询与李勇同在一个系，且年龄比他小的同学的姓名(提示用year(csrq)来进行比较） select sname from student where sdept in(select sdept from student where sname='李勇') and year(csrq)>(select year(csrq) from student where sname='李勇')
5005 查询比计算机系平均年龄都大的其它系学生姓名，和年龄。（注意不含计算机系的学生）提示设当前年为2010,平均年龄可用avg(2010-year(csrq))表示
select sname,(2010-year(csrq))as 年龄 from student where (2010-year(csrq))>(select avg(2010-year(csrq)) from student where sdept='计算机系') and sdept<>'计算机系'
5006 查询王名同学没有选的课程号(提示：就是查询那课程表中所有的但是王名没有选过的课程）
"select cno from course where cno not in (select cno fro
m sc where sno in(select sno from student where sname='王名'))
"
5007 查询选修了1号课并且成绩比全班1号课平均成绩低的同学学号及成绩 "select sno，grade from sc where sno in(select sno from sc where cno='1') and grade<(select avg(grade) from sc where cno='1')
"
5008 查询选修的学分总数超过8个的学生的姓名 "select sname from student where sno in(select sno from sc,course where https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o group by sno having sum(ccredit)>8)
"
5009 查询选修了和王名一样学分数的同学姓名 "select sname from student where sno in(select sno from sc,course where https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o group by sno having sum(ccredit)=(select sum(ccredit) from sc,course where https://www.doczj.com/doc/4f14753610.html,o=https://www.doczj.com/doc/4f14753610.html,o and sno in(select sno from student where sname='王名') ))
"
5010 查询比平均成绩比李勇的平均成绩低的同学姓名 "select sname from student where sno in(select sno from sc group by sno having avg(grade)<(select avg(grade) from sc where sno in(select sno from student where sname='李勇')))
"
6001 用定义语句建立一个“学生”表student1，要求它的结构与student表的结构相同 create table student1(sno char(8),sname char(8),sex char(2),sdept char(20),csrq date);
6002 用定义语句创建选课表要sc1,要求和sc表一样 create table sc1(sno char(8) ,cno char(3) ,grade float)
6003 在学生表Student1中增加系（sage int)一列 alter table student1 add sage int ;
6004 将学生表Student1学生所属系（sdept）长度改为40 alter table student modify sdept char(40) ;(C++,modify 改为alter)
6005 创建一个名字为v_stu视图，视图字段为sno,sname,sex create view v_stu as select sno,sname,sex from student
6006 删除视图文件v_stu drop view v_stu
6007 请将学生表student1中的年龄sage字段删除 alter table student1 drop column sage
6008 以cno做主索引列（升序）,grade做第二索引列（降序），对sc1建索引文件，取名为idx_sc create index idx_sc on sc1(cno,grade desc)
6009 删除索引文件idx_sc "drop index idx_sc //on student (C++下)
drop index idx_sc on sc1"
6010 删除sc1表 drop table sc1
7002 将student表中所有文传系的女生信息插入到student1表中 "insert into student1 select * from student where
sdept='文传系'and sex='女' "
7003 将1987年后的所有学生记录从student表插入到student1中 insert into student1 select * from student where year(csrq)>=1987
7001 将一个新同学的记录(sno:07010150；sname:陈冬；sex：男；sdept：信息；csrq:1985-6-8)插入到STUDENT1表中。 Insert into student1 values('07010150','陈冬','男','信息',1985-6-8)
7005 请将student1表中魏玲铃同学的姓名改为魏玲玲，并将其系改为计算机系 update student1 set sname='魏
玲玲' ,sdept='计算机系' where sname='魏玲铃'
7006 将STUDENT1表中所有计算机系女生的系别改为信息系 update student1 set sdept='计算机系' where sdept='信息系'and sex='女'
7008 删除STUDENT1表中学号为07010150的学生记录 delete * from student1 where sno='07010150'
7009 将STUDENT1表中没有选课的学生删除 delete from student1 where sno not in(select distinct sno from sc )
7010 删除student1表中平均成绩低于80分的同学的记录 delete * from student1 where sno in (select sno from sc group by sno having avg(grade)<80)
7004 请将没有选过课的学生信息插入到student1表中 insert into student1 select * from student where sno not in (select sno from sc)
7007 将计算机系所有选了1号课程的同学1号课成绩清零 update sc set grade=0 where cno='1' and sno in (select sno from student where sdept='计算机系' )