有3个表S,C,SC
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号成绩)
问题:
1,找出没选过“黎明”老师的所有学生姓名。
2,列出2门以上(含2门)不及格学生姓名及平均成绩。
3,即学过1号课程有学过2号课所有学生的姓名。
--1. 找出没选过“李”老师的所有学生姓名。
SELECT s.Sname FROM Student s WHERE s.SNO NOT IN
(SELECT sc.SNO FROM Stu_Cau sc LEFT JOIN Cause c ON sc.CNO = c.CNO
WHERE c.CTEACHAR = '李' )
--或
SELECT DISTINCT s.Sname FROM Student s JOIN Stu_Cau sc ON (s.SNO = sc.SNO)
JOIN Cause c ON (c.CNO = sc.CNO) WHERE c.CTEACHAR <> '李'
--2. 列出2门以上(含2门)不及格学生姓名及平均成绩。
SELECT t.av,s.SNAME FROM (
SELECT sc.SNO,AVG(sc.SCGRADE) AS av FROM Stu_Cau sc
WHERE sc.SNO IN
(SELECT SNO FROM Stu_Cau WHERE SCGRADE<60 GROUP BY SNO HAVING COUNT(SNO) >= 2)
GROUP BY sc.SNO ) AS t
LEFT JOIN Student s ON s.SNO = t.SNO
--3. 既学过1号课程又学过2号课所有学生的姓名。
SELECT s.SNAME FROM Student s WHERE s.SNO IN
(
SELECT a.SNO FROM Stu_Cau a CROSS JOIN Stu_Cau b
WHERE a.SNO = b.SNO AND a.CNO = 1 AND b.CNO = 2
)
简洁的
1.
select sname froms
join sc on (s.sno = sc.sno)
join c (c.cno = sc.cno)
where c.cteacher <> 'liming';
2.
select sname
where sno in (
select sno from sc where scgrade < 60 group by sno having count(*) >= 2);
3.
select sname
where sno in (
select sno from sc
where cno = 1 and sno in
(select sno fromsc where cno = 2)
);