软件123-冯晓红-1208060348-实验四
- 格式：doc
- 大小：1.36 MB
- 文档页数：12
贵州大学计算机科学与信息学院软件工程系上机实验报告
(2)求学生的学号、姓名、选修的课程号及成绩；
Select student.sno,sname,cname,grade
From student,SC,course
Where student.sno=SC.sno and o=o;
(3)求选修课程号为1且成绩在90以上的学生学号、姓名和成绩；Select student.sno,sname,grade
From SC,student
Where student.sno=SC.sno and
o=’1’
And SC.grade>’90’;
(4)求每一门课程的间接先行课（即先行课的先行课）；Select o,second.cpno
From course first,course second
Where first.cpno=o;
(5)求选修了数学课的学生的学号和姓名；
Select sno,sname
From student
Where sno in
(select sno
From SC
Where cno in
(select cno
From course
Where cname=’数学’));
(6)求学生李勇选修的总学分；
Select sum(ccredit)
From SC,course,student
Where sname=’李勇’ AND o=o;
(7)求各学生选修的总学分；
Select sum(ccredit)
From SC,course
Where o=o;
(8)求课程“数据库”的平均成绩；
Select avg(grade)
From course,SC
Where cname=’数据库’ and o=o;
(9)求计算机系学生的选课情况；Select sno,cno
From SC,student
Where sno IN
(select sno
From student
Where sdept=’CS’ )；
(10)求和钟灵在同一个系的老师姓名；Select tname
From teacher
Where tdept in
(select tdept
From teacher
Where tname=’钟灵’)；
(11)求吴娅所在系的教师人数；Select count(tno)
From teacher
Where tdept =
(Select tdept
From teacher
Where tname=’吴娅’)；
(12)求和王小峰同一职称的教师姓名和所在系；Select tname,tdept
From teacher
Where ttitles =
(Select ttitles
From teacher
Where tname=’王晓峰’ )；
(13)求和邹佳羽工资相同的教师人数；
Select count(tno)
From teacher
Where twage =
( select twage
From teacher
Where tname=’邹佳羽’)；
(14)求汪明所在系教师的平均工资和最高工资；Select avg(twage),max(twage)
From teacher
Where tdept =
(select tdept
From teacher
Where tname=’汪明’)；
(15)求至少选修了8个学分的学生学号。
Select sno
From SC,course
Group by sno
Having count(ccredit) >=’8’;
三、实验步骤
将查询需求用SQL语言表示：在SQL Server查询分析器的输入区中输入SQL查询语句；编译和发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。
（注：可编辑下载，若有不当之处，请指正，谢谢!）。
