–（1）查询“001”课程比“002”课程成绩低的所有学生的学号、001学科成绩、002学科成绩

select s1.studentno,s1.score,s2.score from score s1 ,score s2
where s1.courseno=1 and s2.courseno = 2 and s1.studentno = s2.studentno and s1.score <s2.score
 

–（2）查询平均成绩大于60分的同学的学号和平均成绩

SELECT [StudentNo]
      ,avg([score]) as [avg]
  FROM [LearnSQL].[dbo].[score] group by [StudentNo] having AVG([score]) > 60
GO

–（3）查询所有同学的学号、姓名、选课数、总成绩

select stu.name,stu.studentno,COUNT(sco.CourseNo),SUM(sco.score) from student stu , score sco where  stu.studentno = sco.StudentNo group by stu.StudentNo,stu.name
 

–（4）查询姓“李”的老师的个数

select COUNT(1) as total from teacher where name like '叶%'

–（5）查询没学过“叶平”老师课的同学的学号、姓名
– 报了叶平老师的学号

select s.name,s.studentno from student s where s.studentNo in
(
    select distinct StudentNo from score  sc, course c , teacher tea 
    where  sc.CourseNo = c.courseNo and c.teacherNo = tea.teacherNo and tea.name = '叶平'
)

– （6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select stu.studentno,stu.name,COUNT(sc.CourseNo)  from student stu,score sc
where stu.studentno = sc.StudentNo
and sc.CourseNo in (1,2)
group by stu.studentno,stu.name 
having COUNT(sc.CourseNo) >1
 

– （7）查询学过“叶平”老师所教的所有课的同学的学号、姓名
–首先是求出叶平老师的课程数量

select * from student where studentno in (
select sco.StudentNo from course c,score sco,teacher te where c.teacherNo = te.teacherno and c.CourseNo = sco.CourseNo and te.name='叶平' group by sco.StudentNo having COUNT(1) =(
select COUNT(1) from course where teacherno in (select tea.teacherno from teacher tea  where tea.name = '叶平')))
 

– （8）查询有课程成绩小于60分的同学的学号、姓名

SELECT DISTINCT
  s1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.studentNo
  AND
  s1.score < 60
   

— （9）查询没有学全所有课的同学的学号、姓名

SELECT 
  s1.StudentNo,
  stu1.name
FROM
  score s1,
  student stu1
WHERE
  s1.StudentNo = stu1.StudentNo
GROUP BY s1.StudentNo,stu1.name
HAVING COUNT(1) < 
(
SELECT
  COUNT(*)
FROM
  course c1
)

大家创造
– （11）查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
– （12）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名（和11题撞脸，排除1号同学就可以了）