SQL面试题

有3个表S，C，SC
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
3，即学过1号课程有学过2号课所有学生的姓名。
请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。

评论

10 楼 cuiyijing 2015-08-20  

[size=small]马士兵 26_homework_dml_and_transaction.avi 相当出名啊。。。[/size]

9 楼 utobe 2013-08-24  

兄弟 你真的出名了,尚学堂 oracle 马士兵的视频拿你的题讲解

8 楼 woshicaiqiang 2010-02-21  

你出名了啊

7 楼 zwllxs 2009-03-19  

大哥,我在看尚学堂的oracle视频时,马老师直接拿这个页面上的题目做例子,你出名了,哈哈

6 楼 liulyx 2009-03-11  

--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
)

5 楼 xiaohui6636 2009-03-11  

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)
);

4 楼 yzzh9 2009-02-14  

javacfish 写道

3.select sname from s where sno in &nbsp; (select sno from sc where cno = 1 and cno in (select distinct sno from sc where cno =2) );

红线部分应该为sno

3 楼 javacfish 2009-01-30  

3.select sname from s where sno in
  (select sno from sc where cno = 1 and cno in (select distinct sno

from sc where cno =2)
);

2 楼 javacfish 2009-01-30  

2.select sname where sno in(select sno from sc where scgrade <60 group

by sno having count(*) >=2);

1 楼 javacfish 2009-01-30  

1.select sanme from s join sc on(s.sno = sc.sno) join c (c.cno =

sc.cno) where c.cteacher <> 'liming';