赞
踩
4.查询选修了“数据库”课程的学生的学号、姓名和成绩,查询结果按成绩降序排序;
- select Student.Sno,Sname,SC.Grade
- from Student,Course,SC
- where SC.Sno=Student.Sno
- and SC.Cno=Course.Cno
- and Course.Cname='数据库'
- order by SC.Grade DESC
5.查询每个学生的学号、姓名、选修课的课程名及成绩;
- select Student.Sno,Sname,SC.Grade,Course.Cname
- from Student,Course,SC
- where SC.Sno=Student.Sno
- and SC.Cno=Course.Cno
6.将所有学生所学课程成绩低于75分的课程名列出来。
- select Course.Cname
- from Student,Course,SC
- where SC.Sno=Student.Sno
- and SC.Cno=Course.Cno
- and SC.Grade<75
一、嵌套查询
1 带谓词IN的嵌套查询
【SY4-01】查询与“李勇”在同一个系学习的学生信息。
【方法一】内连接里挑出的,外连接的表里得有
- select Student.*
- from Student
- where Dno in
- (select Department.Dno
- from Student,Department
- where Student.Dno=Department.Dno
- and Student.Sname='李勇'
【SY4-02】查询选修了编号为“2”的课程的学生学号、姓名和系号。
- select Student.Sno,Sname,Dno
- from Student
- where Sno in
- (select SC.Sno
- from SC,Course
- where SC.Cno=Course.Cno
- and Course.Cno=2
- )
嵌套循环里可不可以只用sc里面的cno挑出来 自身链接?
【SY4-03】查询选修了课程名为“数据库”的学生学号(Sno)和姓名(Sname)。
(用两种方法)
方法一:
- select Student.Sno,Sname
- from Student
- where Sno in
- (select SC.Sno
- from SC,Course
- where SC.Cno=Course.Cno
- and Course.Cname='数据库'
- )
方法二:
- select Student.Sno,Sname
-
- from Student,SC,Course
-
- where SC.Cno=Course.Cno
-
- AND SC.Sno=Student.Sno
-
- and Course.Cname='数据库'
【SY4-04】查询没有选修“数学”课程的学生的学号和姓名。
- select Student.Sno,Sname
- from Student
- where Sno in
- (select SC.Sno
- from SC,Course
- where SC.Cno=Course.Cno
- and Course.Cname!='数学'
- )
2 带有比较运算符的嵌套查询
【SY4-05】将SY4-01改为带有比较运算符的嵌套查询。
- select Student.*
- from Student
- where Dno =
- (select Department.Dno
- from Student,Department
- where Student.Dno=Department.Dno
- and Student.Sname='李勇'
- )
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。