(作业与答案)课件.ppt

第三章 作业;;;;请写出下列查询语句并给出结果;请写出下列查询语句并给出结果;;;1、列出student表中所有记录的sname、sex和class列。 Select name, sex, class from studnt; 2、显示教师所有的单位即不重复的depart列。 Select distinct depart from teacher; 3、显示学生表的所有记录。 Select sno as 学号, name as 姓名, sex as 性别 , birthday as 出生日期 from student; 4、显示score表中成绩在60到80之间的所有记录。 Select * from score where degree between 60 and 80; 5、显示score表中成绩为85，86或88的记录。 Select * from score where degree in (85, 86, 88); ;6、显示student表中“95031”班或性别为“女”的同学记 Select * from student where class=98031 or sex=‘女’; 7、以class降序显示student表的所有记录。 Select * from student order by class desc; 8、以cno升序、degree降序显示score表的所有记录。 Select * from score order by cno, degree desc; 9、显示“98031”班的学生人数。 Select count(*) from student where class=95031; 10、显示score表中的最高分的学生学号和课程号。 select sno,cno,degree as 最高分 from score where degree= (select max(degree) from score) SNO CNO 最高分 103 3-105 92 ;11、显示“3-105”号课程的平均分。 Select avg(degree) as 课程平均分 from score where cno=‘3-105’ 12、显示score表中至少有5名学生选修的并以3开头的课程号的平均分数。 Select cno,avg(degree) from score where cno like ‘3%’ Group by cno having count(*) =5; 13、显示最低分大于70，最高分小于90 的sno列。 Select sno from score group by sno Having min(degree)70 and max(degree)90; 14、显示所有学生的 sname、 cno和degree列。 select sname,cno,degree from score,student where student.sno=score.sno;;15、显示所有学生的 sname、 cname和degree列。 Select sname, cname, degree from course , student,score Where student.sno=score.sno and course.cno=score.cno; 16、列出“95033”班所选课程的平均分。 Select cno,avg(degree) from student , score where student.sno=score.sno and student.class=‘95033’ group by cno; ;16另解 SQL select cno,avg(degree) from score 2 where sno in(select sno from student where 3 class=95033) group by cno; CNO AVG(DEGREE) ------ ----------- 3-105 77.6666667 6-166 81.6666667 16另解 select avg(degree) from score