Question

关于Oracle数据库编程题的解答。谢谢各位！

在数据库成绩管理中有三张表：
学生表：Student(Sno,Sname,Sage,Ssex)
课程表：Course(Cno,Cname,Tno)
成绩表：SC(Sno,Cno,score)
教师表：Teacher(Tno,Tname)
用T-SQL命令完成以下题目。

1、查询姓“李”的老师的个数；

2、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

3、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

4、查询“c001”课程比“c002”课程成绩高的所有学生的学号

5、查询平均成绩大于60分的同学的学号和平均成绩；

6、查询所有同学的学号、姓名、选课数、总成绩；

7、查询没有学全所有课的同学的学号、姓名；
各位大虾，请指教指教啦！小弟在此感激不尽啊！！

Answer 1

1、查询姓“李”的老师的个数；
SELECT COUNT(Tno) FROM Teacher WHERE Tname LIKE '李%'

2、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

SELECT Sno, Sname
FROM Student
WHERE sno IN(SELECT sno
FROM (SELECT Sno FROM SC WHERE Cno = 'c001') t1
(SELECT Sno FROM SC WHERE Cno = 'c002') t2
WHERE t1.sno = t2.sno)

3、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT Sno, Sname
FROM student
WHERE Sno IN(SELECT SC
FROM SC
WHERE CNO IN (SELECT CNO FROM Course WHERE Cname = '叶平')
GROUP BY SC HAVING COUNT(CNO) = (SELECT COUNT(CNO) FROM Course WHERE Cname = '叶平'))

4、查询“c001”课程比“c002”课程成绩高的所有学生的学号

SELECT C1.SC
FROM (SELECT SC, Score FROM SC WHERE Cno = 'c001') c1
(SELECT SC, Score FROM SC WHERE Cno = 'c002') c2
WHERE c1.SC = c2.SC
AND c1.Score > c2.Score
如果只有C1,而没有C2成绩，用这个好一点
SELECT SC
FROM (SELECT SC, Score FROM SC WHERE Cno = 'c001') c1
left join (SELECT SC, Score FROM SC WHERE Cno = 'c002') c2
ON c1.SC = c2.SC AND c1.Score > c2.Score

5、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT Sno, AVG(score)
FROM SC GROUP BY Sno Having AVG(score) > 60

6、查询所有同学的学号、姓名、选课数、总成绩；
SELECT stu.Sno, stu.Sname, COUNT(Cno), SUM(score)
FROM Student stu, SC
WHERE stu.Sno = Sc.sno
GROUP BY stu.sno, stu.Sname

7、查询没有学全所有课的同学的学号、姓名；
SELECT Sno, Sname
FROM SC
WHERE Sno NOT IN (SELECT Sno
FROM SC
GROUP BY SC
HAVING COUNT(CNO) < (SELECT COUNT(DISTINCT CNO) FROM SC))

Answer 2

1.select count(tno) from teacher where tname like '李%';

2.SELECT stu.SID, stu.name
FROM SC t, student stu
WHERE cid ='001'
AND EXITS (SELECT 1 FROM SC WHERE cid = '002' AND sid = t.sid)
and t.sid = stu.sid;

3.select sno,sname from (select sno fromstudent sc,(select cno from teacher,course where tname='叶平') a where a.cno=sc.cno) b,student stu where b.sno=stu.sno;

4.select sno,sname from sc a,student stu where cno='c001' and exits(select 1 from sc where cno='c002' and a.score>sc.score) and a.sno=stu.sno;
5.select sno,aver(score) from sc group by sno having aver(score)>60;

6.select sno,sname,a.ccount,b.sscore from student stu left join (select sno,count(*) as ccount from sc group by sno ) a on a.sno=sut.sno left join (select sno,sum(score) as sscore from sc group by sno) b on b.sno=stu.sno;

7.select sno,sname from student,(select sno from sc group by sno having count(cno)<(select count(*) from course)) a where a.sno=student.sno;