实验八、高级查询.docVIP

思考与练习： 1.列出student表中所有记录的name、sex和class列。 select sname,sex,class from student 2.列出教师所有单位。 select tno,tname,sex,birthday,prof,depart from teacher 3.列出student表中的所有记录（以学号、姓名等） select sno,sname,sex,birthday,class,scode from student 4.输出成绩在60-80之间的所有记录。 select * from score where (degree between 60 and 80) 5.输出成绩为85，86和88的记录。 select * from score where (degree=85 or degree=86 or degree=88) 6.输出班级为95001或性别为‘女’的同学。 select distinct sname,class,sex from student where class=95001 or sex=女 7.以class降序输出student的所有记录。 select * from student order by class desc 8.以cno升序、degree降序输出score的所有记录。 select * from score order by cno asc ,degree desc 9.输出95001班级的学生人数。 select count(*) from student where class=95001 10.输出score中成绩最高的学号和课程号。 select top 1 Student.sno,cno from Student,Score where Student.sno=Score.sno 11.输出‘3-105’号课程的平均分。 select avg(degree)as 3-105平均分 from Score where cno=3-105 12.输出至少有5个同学选修的并以3开头的课程号的平均分。 select cno,avg(degree) as 平均分 from Score where cno like3% group by cno having count(*)=5 13.输出最低分大于70分最高分小于90分的学生学号。 select sno from Score where degree70 and degree90 14.输出所有学生的name、no和degree。 select sname,Student.sno,degree from Student,Score where Student.sno=Score.sno order by Student.sno asc 15.输出所有学生no、name、degree。 select cname,Student.sno,degree from Student,Score,Course where Student.sno=Score.sno and Score.cno=Course.cno 16.输出所有学生name、cname、degree。 select cname,sname,degree from Student,Score,Course where Student.sno=Score.sno and Score.cno=Course.cno 17.输出‘95001’班级所选课程的平均分。 select avg(degree) as 95001班级平均分 from Score,Student where Student.sno=Score.sno and class=95001 19.输出选修3-105课程的成绩高于0601005号同学成绩的所有同学。 select Student.sno,sname,degree from Student,Score where Student.sno=Score.sno and cno=3-105 and degree(select degree from Student where sno=0601005) 22.列出与0601004号同学同年出生的所有学生的no、name和birthday。 select sno,sname,birthday from Student where birthday=(select birthday from Student where sno=0601005 ) 2