某一学生选课系统中有下列三张表:用SQL语言完成下列功能:
找出学过“林红”老师讲授课程的所有学生的学号、姓名、成绩
找出学全了“林红”老师讲授课程的所有学生的学号、姓名
找出没学过“林红”老师讲授课程的所有学生的基本信息
找出没学全“林红”老师讲授课程的所有学生的基本信息
1.找出学过“林红”老师讲授课程的所有学生的学号、姓名、成绩
SELECT S$.Sno,Sname,SC$.Cno,SC$.Grade
FROM S$,SC$
WHERE S$.Sno=SC$.Sno AND S$.Sno IN (SELECT SNO
FROM SC$,C$
WHERE SC$.Cno=C$.Cno AND C$.Cteacher='林红')
结果
2. 找出学全了“林红”老师讲授课程的所有学生的学号、姓名
SELECT Sno,Sname
FROM S$
WHERE Sno IN(SELECT S$.Sno
FROM S$,SC$,C$
WHERE S$.Sno=SC$.Sno
AND SC$.Cno=C$.Cno AND C$.Cteacher='林红'
GROUP BY S$.Sno
HAVING COUNT(*)=(SELECT COUNT(*)
FROM C$
WHERE Cteacher='林红'))
结果
3. 找出没学过“林红”老师讲授课程的所有学生的基本信息
SELECT *
FROM S$
WHERE Sno NOT IN (SELECT SNO
FROM SC$,C$
WHERE SC$.Cno=C$.Cno AND C$.Cteacher='林红')
结果
4. 找出没学全“林红”老师讲授课程的所有学生的基本信息
SELECT *
FROM S$
WHERE Sno IN(SELECT S$.Sno
FROM S$,SC$,C$
WHERE S$.Sno=SC$.Sno AND SC$.Cno=C$.Cno AND C$.Cteacher='林红'
GROUP BY S$.Sno
HAVING COUNT(*)>0 AND COUNT(*)<(SELECT COUNT(*)
FROM C$
WHERE Cteacher='林红'))