sql上机实验.doc

1 select * from student where sex=男 2 select sno,grade from sc where cno=1 3 select sno,sname from student where year(csrq)=1990 and sdept=英语系 8 select * from student where (2010-year(csrq)21) and (sdept = 计算机系 or sdept = 信息系)9 select sno,cno,grade from sc where (cno = 3 or cno = 4)10 select cname,cpno,ccredit from course where cpno=0 and ccredit=21 SELECT sname,sdept, 2010-year(csrq)FROM studentWHERE 2010-year(csrq)=20 AND 2010-year(csrq) 85;6 SELECT *FROM studentWHERE sname like 李%;7 SELECT sdept,sexFROM studentWHERE sname like 张_;8 SELECT sname,snoFROM studentWHERE sname like _军;9 SELECT sname,snoFROM studentWHERE sname not like 刘% AND sdept=信息系;10 SELECT cname,ccreditFROM courseWHERE ccredit3 AND cname like 数据%;SELECT sname,cname,gradeFROM sc,course,studentWHERE sc.sno=student.sno AND o=o;2 SELECT sname,cno,gradeFROM sc,studentWHERE cno=1 AND grade80 AND sc.sno=student.sno;3 SELECT sname,cno,gradeFROM sc,studentWHERE cno 1 AND sex = 女 AND sc.sno=student.snoORDER BY grade DESC;4 SELECT sname,gradeFROM sc,course,studentWHERE cname=数据库 AND sex = 男 AND sc.sno=student.sno AND o=o;5 SELECT max(grade) as 最高成绩,min(grade) as 最低成绩,avg(grade) as 平均成绩 FROM sc,course WHERE o=o and cname=数据库;6 SELECT cno,sname,grade FROM student,sc WHERE student.sno=sc.sno and cno in (1,3);7 SELECT sum(grade) as 总成绩,avg(grade) as 平均成绩 FROM student,sc 1 select sno,avg(grade) as 平均成绩 from sc group by sno having avg(grade)852 select sc.sno from sc,student where sc.sno=student.sno and sdept=信息系 group by sc.sno having count(cno)=23 select sdept,count(*) as 女生人数 from student where sex=女 group by sdept4 select cno as 课程号,count(*)as所选人数 from sc group by cno5 select sc.sno,avg(grade) as 平均成绩 from sc,student where sc.sno=student.sno and sdept=计算机系 group by sc.sno6 select sc.sno, sum(ccredit) from sc,course where o=o group by sno having sum(ccredit)87 select sc.sno as 学号,avg(grade) as 平均成绩 from sc,student where student.sno=sc.sno and sname in(李勇,刘晨) group by sc.sno8 select sc.sno from sc,student where sc.sno=student.sno and sdept=信息系group by sc.sno having count(cno)=19 select o as 课程号,count(*) as 选课人数 from sc,student,course where sc.sno=student.sno and o=o and cname in (数据库,数学) group by o10 select sc.sno,avg(grade) as 平均成绩 from student,sc where sc.sno=student.sno group by sc.sno order by avg(grade) desc1 select sname,sdept from student where sno in(select sno from sc where sno in(select sno from sc where cno=3) and cno=1)2select 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=数学)4 select sname from student where sdept in (select sdept from student where sname=李勇) and year(csrq)(select year(csrq) from student where sname=李勇)5 select sname,2010-year(csrq)as 年龄 from student where 2010-year(csrq)all(select avg(2010-year(csrq) from student where sdept =计算机系) and sdept 计算机系6 select cno from course where cno not in (select cno from sc where sno= (select sno from student where sname=王名)8 select sname from student where sno in(select sno from sc,course where o=o group by sno having sum(ccredit)8)9 select sname from student where sno in (select sno from sc,course where o=o group by sno having sum(ccredit)=(select sum(ccredit) from sc,course,student where o=o and sc.sno=student.sno and sname=王名)10.select sname from student where sno in (select sno from sc group by sno having avg(grade)=19877004insert into student1 select * from student where sno not in (select sno from sc)7005update student1 set sname=魏玲玲 ,sdept=计算机系 where sname=魏玲铃 7006update student1 set sdept=计算机系 where sdept=信息系and sex=女7007update sc set grade=0 where cno=1 and sno in (select sno from student where sdept=计算机系 ) 7008delete * from student1 where sno=070101507009delete from student1 where sno not in(select distinct sno from sc )7010delete * from student1 where sno in (select sno from sc group by sno having avg(grade)80select o as 课程号,count(*) as 选课人数 from sc,course where o=o and cname in(数据库,数学) group by oselect sname,avg(grade) as 平均成绩from student,sc,coursewhere student.sno=sc.sno and o=o and sdept=管理系group by snameselect count(*)-(select count (*) from student where sdept =计算机系) from student where sdept=管理系select sno ,avg(grade) as 平均成绩 from sc group by sno order by avg(grade) descselect avg(2010-year(csrq) as 平均年龄,count(*) as 人数from studentgroup by sdeptselect sname,cno,grade from student,sc where student.sno=sc.sno and cno=1 and grade80select sno,sum(ccredit) from sc ,course where o=o group by sno having sum(ccredit)8select sdept ,count(sex) from student where sex=女 group by sdeptselect max(grade) as 最高成绩 ,min(grade) as 最低成绩 ,avg(grade) as 平均成绩 from student,sc,course where student.sno=sc.sno and o=o and cname=数据库 and student.sno like 09%select sum(grade) as 总成绩,avg(grade) as 平均成绩from student,sc,coursewhere sname=李勇 and student.sno=sc.sno and o=odelete *from student1where sdept=计算机系 and (2010-year(csrq)all (select avg(2010-year(csrq) from student1 where sdept=文传系)update student1set sdept=文传系 where student1.sno in (select sc.sno from sc,student1 where student1.sno=sc.sno and sdept=计算机系 group by sc.sno having avg(grade)80 )update sc1set grade=grade+10where sno in (select sc.sno from student,sc where sdept=管理系 and student.sno=sc.sno group by sc.sno having avg(grade) sdept=管理系)update sc1set grade=grade+10where sno in (select sc.sno from student,sc where sdept=英语系 and sname like _ )update sc1set grade=grade+3where sno in (select sc.sno from student,sc where sdept=计算机系 and cno=1)insert into student1select * from student where (2010-year(csrq)all (select avg(2010-year(csrq) from student group by sdept having sdept=文传系)insert into student1 select * from student where 2010-year(csrq)=1987 and sdept=计算机系update sc1 set grade=grade+5 where sno in (select sno from student where (2010-year(csrq)=all(select sum(ccredit) from student,sc,coursewhere o=o and student.sno=sc.sno group by sdept)select snamefrom studentwhere sno in (select sno from sc group by sno having count(cno)(select count(cno) from sc where sno in (select sno from student where sname=李明)select sdept,avg(grade)as 平均成绩from sc,studentwhere student.sno=sc.sno and sc.sno in(select sno from sc) group by sdept having avg(grade)80select sname,(2010-year(csrq) as 年龄,sdeptfrom studentwhere (2010-year(csrq)all(select (2010-year(csrq) from student where (2010-year(csrq)=all(select (2010-year(csrq) from student where sdept=计算机系) and sdept计算机系select sname,(2010-year(csrq)as 年龄,sdept from student where (2010-year(csrq) all (select min(2010-year(csrq) from student wheresdept=计算机系) and sdept 计算机系30查询没有选1号课的女生姓名，课程号及成绩,并将成绩按降序排序select sname,cno,grade from student,sc where student.sno=sc.sno and student.sno not in(select sno from sc where cno=1) and sex=女 order by grade desc31查询与李勇同在一个系，且年龄比他小的同学的姓名(提示用year(csrq)来进行比较）select sname from student where sdept in(select sdept from student where sname=李勇) and (2010-year(csrq)8035查询没有选过课人数最多的那个系的没选课同学的平均年龄select avg(2010-year(csrq) as 平均年龄 from student,scwhere student.sno=sc.sno and sc.sno not in (select cno from sc)group by sdepthaving count(*)=all(select count(*) from student,scwhere student.sno=sc.sno group by sdept)36计算机系选了数据库课程的所有密码不够8位的学生姓名及密码select sname,password1 from student,sc,course ,mm where student.sno=sc.sno and o=o and student.sno=right(mm.sno,8) and sdept =计算机系 and cname=数据库 and len(password1)=239在SC1表中求出每个同学的学号及选课数量（要求要滤掉重复选的课程）select sno,count(*) from (select distinct sno,cno from sc1) group by sno select count(*) from (select sc.sno from student,sc where student.sno=sc.sno and sdept=管理系 and sex=男 group by sc.sno having avg(grade)(select avg(grade) from student,sc where student.sno=sc.sno and sdept=文传系 and sex=男 )查询计算机系所有学生学的学分总数比文传系所有学生学的学分总数多多少select sum(ccredit) -(select sum(ccredit) from student,course,sc where sdept=文传系 and student.sno=sc.sno and o=o) from student,course,sc where student.sno=sc.sno and o=o and sdept=计算机系select sname,cname from student,course,sc where student.sno=sc.sno and o=o and sc.sno not in (select sno from sc where cno in (select cno from course where cname=数据库) group by sname,cnameselect sdept,avg(ccredit) as平均学分数 from student,course,sc where student.sno=sc.sno and o=o group by