1、 sql实例1001 查询全体男同学信息情况select * from student where sex=男1002 查询选修了1号课的学生的学号和成绩select sno,grade from sc where cno=11003 查询1989年以前出生的学生学号和姓名和出生日期（提示请用year(csrq)函数来取出生年号再与1989比较)select sno,sname,csrq from student where year(csrq)19891004 查询信息系所有女生的学号,姓名,及所在系 select sno,sname,sdept from student where sde
2、pt=信息系 and sex=女1005 查询课程名是数据库的课程号和学分 select cno,ccredit from course where cname=数据库1006 查询先行课号为5的课程号和课程名 select cno,cname from course where cpno=51007 查询英语系90后的学生情况(注90年后出生的) select * from student where sdept=英语系 and year(csrq)=19901008 查询计算机系或信息系中年龄超过21岁的同学情况.(设当前年为2010年,提示用当前年减去出生年再与21岁比较) select
3、 * from student where (sdept=计算机系 or sdept=信息系) and (2010-year(csrq)211009 请将3号课及4号课学生的学号课程号及成绩显示出来select sno,cno,grade from sc where cno=3 or cno=41010 查询所有先行课程号为0且学分为2的课程名,先行课程号,及学分 select cname,cpno,ccredit from course where cpno=0 and ccredit=22010 查询学分为3以上(不含3),且课程名中包含着数据二字的所有课程名及学分 select cnam
4、e ,ccredit from course where ccredit3 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)=232002 查询选修过课程的所有学生的学号（不许重复）select distinct sno from sc2003 请将选修了课程的同学按课程号升序，成
5、绩降序排序 select * from sc order by cno,grade desc2004 请将选了1号课程的同学按成绩降序排序 select * from sc where cno=1 order by grade desc2005 查询选修了1号课的成绩大于85分的学号和成绩select sno,grade from sc where cno=1 and grade852006 查询所有李姓同学情况select * from student where sname like 李%2007 查询所有两字姓名的张姓同学的姓名，性别与系别select sname,sex,sdept f
6、rom student where sname like 张_2008 查询名字中第3个字为铃的学生的姓名和学号 select sname,sno from student where sname like _铃2009 查询信息系所有不姓刘的同学的学号和姓名 select sno,sname from student where sdept =信息系 and sname not like 刘%3001 查询所有选修过课的学生的姓名，课程名及成绩 select sname,cname,grade from student,sc,course where student.sno=sc.sno a
7、nd o=o and (cname=数据库 or cname=信息系统)3002 查询选修了1号课且成绩大于80分的的同学的姓名，课程号及成绩select sname,cno,grade from student,scwhere student.sno=sc.sno and cno=1 and grade803003 查询没有选1号课的女生姓名，课程号及成绩,并将成绩按降序排序 select sname,cno,grade from student,scwhere student.sno=sc.sno and not (cno=1) and sex=女 order by grade desc3
8、004 查询选修了数据库课的所有男生的姓名及该课的成绩 select sname,grade from student,sc,coursewhere student.sno=sc.sno and o=o and sex=男 and sdept=计算机系 and cname=数据库3005 查询选修了数据库课的最高成绩.最低成绩和平均成绩（注用as 来表示最低，最高及平均成绩 select max(grade) as 最高成绩 ,min(grade) as 最低成绩 ,avg(grade) as 平均成绩 from sc,course where o=o and cname=数据库3006 查询
9、选修了1号课或3号课的学生的学号,姓名,成绩(请用谓词的方法）请参 课 查询例7select sc.sno ,sname,grade 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分以上的同学的姓名及成绩s
10、elect sname,grade from student,sc,course where student.sno=sc.sno and o=o and cname=数据库 and sdept=计算机系 and grade803009 查询选修李 同学所学课程的学分 和（注用as 学分）select sum(ccredit) as 学分 from student,sc,course where student.sno=sc.sno and o=o and sname=李 3010 查 选了1号课，计算机系，女生的 数. 3011 select count(*) as 数 from stude
11、nt,sc wherestudent.sno=sc.sno and cno=1 and sdept=计算机系 and sex=女;4001 查询所有选修课平均成绩大于85分的同学的学号和平均成绩 select sno,avg(grade) from sc group by sno having avg(grade)854002 查询 选修过2 课程的信息系学生学号 select sc.sno from sc,student where student.sno=sc.sno and sdept=信息系group by sc.sno having count(cno)=24003 查询 系女生 数
12、，分别用系，女生 数表示出来 select sdept ,count(sex) from student where sex=女 group by sdept 4004 查询 课程所选的 数并用课程号及所选 数表示出来 select cno ,count(*) as 所选 数 from sc group by cno 4005 查询计算机系所选课程中 个同学的平均成绩，并用学号和平均成绩表示出来 selectsc.sno ,avg(grade) as 平均成绩 from student,sc where student.sno=sc.sno and sdept=计算机系 group by sc
13、.sno4006 查询选修的学分数超过8个学分的同学学号及学分数select sno,sum(ccredit) from sc ,course where o=o group by sno having sum(ccredit)84007 查询李 ，刘 两 同学的平均成绩，并用学号和平均成绩表示出来 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 fro
14、m student,sc where student.sno=sc.sno and sdept=信息系group by sc.sno having count(*)=1 4009 查询数据库和数学 两 课的选课 数，并用课程号和选课 数表示出来 select o as 课程号,count(*) as 选课 数 from sc,course where o=o and cname in(数据库,数学) group by o4010 查询 同学选修课的平均成绩，并按平均成绩 高 低排序，同 要用学号和平均成绩表示出来 select sno ,avg(grade) as 平均成绩from sc gr
15、oup by sno order by avg(grade) desc5001 查询同 选修了1号课和3号课的同学的姓名及系 5002 select sname,sdept from student where sno in (select snofrom 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
16、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 s
17、dept from student where sname=李 ) and year(csrq)(select year(csrq) from student where sname=李 ) 5005 查询比计算机系平均年龄 大的 系学生姓名，和年龄 （注不含计算机系的学生）提示设当前年为2010,平均年龄用avg(2010-year(csrq)表示select all sname,sdept,(2010-year(csrq)as 年龄 from student where (2010-year(csrq)any(select avg(2010-year(csrq) from student
18、where sdept=计算机系) and sdept计算机系5006 查询名同学没有选的课程号(提示是查询课程表中所有的是名没有选过的课程） select cno from course where cno not in (select cno from sc where sno in(select sno from student where sname=名) 5007 查询选修了1号课并且成绩比全currency11号课平均成绩低的同学学号及成绩 select sno,grade from sc where sno in(select sno from sc where cno=1) a
19、nd grade(select avg(grade) from sc where cno=1)5008 查询选修的学分 数超过8个的学生的姓名 select sname from student where sno in(select sno from sc,course where o=o group by sno having sum(ccredit)8)5009 查询选修了和名 学分数的同学姓名 select sname from student where sno in(select sno from sc,course where o=o group by sno having su
20、m(ccredit)=(select sum(ccredit) from sc,course where o=o and sno in(select sno from student where sname=名) )and 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 whe
21、re sname=李 )6001 用“语句 个 学生 表student1，要fi 的fl 与student表的fl 同,并且“名字不为,学号设为 create table student1(sno char(8) unique,sname char(8)not null,sex char(2),sdept char(20),csrq date);6002 用“语句选课表要sc1,要fi和sc表 ,学号与课程和不为 create table sc1(sno char(8) ,cno char(3) ,grade float)6003 在学生表Student1中系（sage int) ,”为20
22、alter table student1 add sage int default 20;6004 将学生表Student1学生所系（sdept） 为50, 为不为 alter table student alter sdept char(50) not null;(C+,modify 为alter)6005 个名字为v_stu，字为sno,sname,grade create view v_stu as select student.sno,sname,grade from student,sc where student.sno=sc.sno ;6006 v_stu drop view v
23、_stu6007 请将学生表student1中的年龄sage字 alter table student1 drop column sage6008 以cno （降序）,grade第二 （升序），sc ，取名为idx_sc create index idx_sc on sc1(cno desc,grade asc)6009 idx_sc drop index idx_sc /on student (C+)drop index idx_sc on sc16010 sc1表 drop table sc17002 将student表中所有 系的女生信息 student1表中 insert into s
24、tudent1 select * from student wheresdept= 系and sex=女 7003 将1987年后的所有学生 student表 student1中 insert into student1select * from student where year(csrq)=19877001 将 个 同学的 (sno:07010150 sname: sex男 sdept信息 csrq:1985-6-8) STUDENT1表中 Insert into student1 values(07010150, ,男,信息,1985-6-8)7005 请将student1表中 铃同学
25、的姓名 为 ，并将 系 为计算机系 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=070101507009 将STUDENT1表中没有选课的学生 delete from student1 where sno not in(select
26、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 wherecno=1 and sno in (select sno from student where sdept=计算机系)QW32K-48T2T-3D2PJ-DXBWY-C6WRJ
