SELECT cno,grade
FROM SC
WHERE sno IN(SELECT sno
from Student
WHERE sname ='李勇');
2. SQL命令查询选修“数据库”的学生学号、姓名;(嵌套查询实现)
SELECT Sno,Sname
FROM Student
WHERE Sno IN(SELECT Sno
FROM SC
WHERE Cno IN(SELECT Cno
from course
WHERE cname ='数据库'));
3. SQL命令查询有不及格课程的学生学号、姓名;(嵌套查询实现)
SELECT Sno,Sname
FROM Student
WHERE Sno IN(SELECTdistinct Sno
FROM SC
WHERE grade<60)
4. SQL命令查询没有不及格课程的学生学号;(嵌套查询、集合查询实现,)
第一种方法:
SELECTdistinct Sno
FROM SC
WHERE Sno notIN(SELECTdistinct Sno
FROM SC
WHERE grade<60)
第二种方法:
SELECTdistinct Sno
FROM SC
exceptSELECTdistinct Sno
FROM SC
WHERE grade<60
5. SQL命令查询选修学生“1001”修过的全部课程的学生姓名。
SELECT Sno,sname
FROM Student
WHERENOTEXISTS(SELECT*FROM SC SCY
WHERE SCY.Sno =' 1001'ANDNOTEXISTS(SELECT*FROM SC SCZ
WHERE SCZ.Sno=Student.Sno AND SCZ.Cno=SCY.Cno))