Question

关于数据库的几道题目？求答案！

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
2、查询平均成绩大于60分的同学的学号和平均成绩；
3、查询所有同学的学号、姓名、选课数、总成绩；
4、查询姓“李”的老师的个数；
5、查询没学过“叶平”老师课的同学的学号、姓名；
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
9、查询所有课程成绩小于60分的同学的学号、姓名；
10、查询没有学全所有课的同学的学号、姓名；
求各题答
写好sql语句就可以

Answer 1

SELECT T2.S#--, T2.COURSE_001, T2.COURSE_002
FROM
(
 SELECT T1.S#, SUM(T1.COURSE_001) COURSE_001, SUM(T1.COURSE_002) COURSE_002
  FROM
  (
    SELECT S#,
           CASE WHEN C# = '001'THEN SCORE
                ELSE 0
                END COURSE_001,
           CASE WHEN C# = '002'THEN SCORE
                ELSE 0
                END COURSE_002
    FROM SC
  ) T1
  GROUP BY S#
) T2
WHERE T2.COURSE_001 > T2.COURSE_002
2.
select s.S#,avg(sc.score) from Student s,SC sc
    where avg(sc.score) > 60
3.SELECT T1.S#, T1.SNAME, T2.CNT, T2.TOTAL_SCORE
FROM STUDENT T1,
(
SELECT S#, COUNT(1) CNT, SUM(SCORE) TOTAL_SCORE
FROM SC
GROUP BY S#
) T2
WHERE T1.S# = T2.S#
4. select count(1) from Teacher where Tname like '李%'
5.select s.S#,s.Sname from Student s,SC sc where s.S# = sc.S# and sc.C# not in(select C# from Teacher t,Course c 
  where t.C# = c.C# and Tname ='叶平')
6. select * from  Course where
7.SELECT T.S#, T.SNAME
  FROM STUDENT T
WHERE T.S# IN (SELECT S#
                  FROM SC
                 WHERE C# IN (SELECT C#
                                FROM TEACHER T1, COURSE T2
                               WHERE T1.T# = T2.T#
                                 AND TNAME = '叶平'));
                                 8.
                                 SELECT T3.S#, T3.SNAME--, T2.COURSE_001, T2.COURSE_002

FROM

(

  SELECT T1.S#, SUM(T1.COURSE_001) COURSE_001, SUM(T1.COURSE_002) COURSE_002

  FROM

  (

    SELECT S#,

           CASE WHEN C# = '001'THEN SCORE

                ELSE 0

                END COURSE_001,

           CASE WHEN C# = '002'THEN SCORE

                ELSE 0

                END COURSE_002

    FROM SC

  ) T1

  GROUP BY S#

) T2,

STUDENT T3

WHERE T2.S# = T3.S#

AND   T2.COURSE_001 < T2.COURSE_002
9.SELECT T.S#, T.SNAME FROM STUDENT T

WHERE EXISTS (SELECT DISTINCT T1.S#

          FROM SC T1

         WHERE NOT EXISTS (SELECT 1

                  FROM SC T2

                 WHERE T2.SCORE > 60

                   AND T2.S# = T1.S#)

           AND T1.S# = T.S#)
10.
SELECT T1.S#, T1.SNAME FROM STUDENT T1

WHERE NOT EXISTS (SELECT S#

          FROM (SELECT S#, COUNT(1) CNT FROM SC GROUP BY S#) T

         WHERE CNT = (SELECT COUNT(1) FROM COURSE)

           AND T.S# = T1.S#)

ORDER BY T1.S#

Answer 2

1. select score1.s# from sc score1
inner join sc score2 on score1.s#=score2.s#
where score1.c#='001' and score2.c#='002' and score1.score>score2.score;
2. select s#,avg(score) from sc group by s# having avg(score)>60;
3. select a.s#,s.sname,a.ct,a,sm from ( select s#,count(s#) ct ,sum(score) sm from sc group by s#) a
left join student s on a.s#=s.s#;
4. select count(*) from teacher where tname like '李%';
5. select s#,sname from student where s# not in (
select distinct sco.s# from sc sco left join course c on sco.c#=c.c# where c.cname='叶平' )
6. select s.s#,s.sname from sc score1
inner join sc score2 on score1.s#=score2.s#
left join student s where score1.s#=s.s#
where score1.c#='001' and score2.c#='002';
7. select s.s#,s.sname from sc sco
left join course c on sco.c#=c.c#
left join teacher t on c.t#=t.t#
left join student s on s.s#=sco.s#
where t.tname='叶平'
group by sco.s#
having count(distinct c.c#)=(
select count(c2.c#) from course c2 left join teacher t2 where t2.tname='叶平' )
8. select score1.s# from sc score1
inner join sc score2 on score1.s#=score2.s#
where score1.c#='001' and score2.c#='002' and score1.score>score2.score;
9. select s#,sname from student where s# not in (
select distinct s# from sc where score >=60)
10.select s.s#,s.sname from sc sco
left join course c on sco.c#=c.c#
left join teacher t on c.t#=t.t#
left join student s on s.s#=sco.s#
group by sco.s#
having count(distinct c.c#) !=( select count(c2.c#) from course c2 )