Student(stuId,stuName,stuAge,stuSex) 学生表
stuId:学号;stuName:学生姓名;stuAge:学生年龄;stuSex:学生性别
Course(courseId,courseName,teacherId) 课程表
courseId,课程编号;courseName:课程名字;teacherId:教师编号
Scores(stuId,courseId,score) 成绩表
stuId:学号;courseId,课程编号;score:成绩
Teacher(teacherId,teacherName) 教师表
teacherId:教师编号; teacherName:教师名字
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.stuId from (select stuId,score from Scores where courseId='001') a,(select stuId,score
from Scores where courseId='002') b
where a.score>b.score and a.stuId=b.stuId;
2、查询平均成绩大于60分的同学的学号和平均成绩;
select stuId,avg(score)
from Scores
group by stuId having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select Student.stuId,Student.stuName,count(Scores.courseId),sum(score)
from Student left Outer join Scores on Student.stuId=Scores.stuId
group by Student.stuId,stuName
4、查询姓“李”的老师的个数;
select count(distinct(teacherName))
from Teacher
where teacherName like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
select Student.stuId,Student.stuName
from Student
where stuId not in (select distinct( Scores.stuId) from Scores,Course,Teacher where Scores.courseId=Course.courseId and Teacher.teacherId=Course.teacherId and Teacher.teacherName=' 叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
select Student.stuId,Student.stuName from Student,Scores where Student.stuId=Scores.stuId and Scores.courseId='001'and exists( Select * from Scores as Scores_2 where Scores_2.stuId=Scores.stuId and Scores_2.courseId='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select stuId,stuName
from Student
where stuId in (select stuId from Scores ,Course ,Teacher where Scores.courseId=Course.courseId and Teacher.teacherId=Course.teacherId and Teacher.teacherName=' 叶平' group by stuId having count(Scores.courseId)= (select count(courseId) from Course,Teacher where Teacher.teacherId=Course.teacherId and teacherName=' 叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
Select stuId,stuName from (select Student.stuId,Student.stuName,score ,(select score from Scores Scores_2 where Scores_2.stuId=Student.stuId and Scores_2.courseId='002') score2
from Student,Scores where Student.stuId=Scores.stuId and courseId='001') S_2 where score2 <score;
9、查询所有课程成绩小于60分的同学的学号、姓名;
select stuId,stuName
from Student
where stuId not in (select Student.stuId from Student,Scores where S.stuId=Scores.stuId and score>60);
10、查询没有学全所有课的同学的学号、姓名;
select Student.stuId,Student.stuName
from Student,Scores
where Student.stuId=Scores.stuId group by Student.stuId,Student.stuName having count(courseId) <(select count(courseId) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select stuId,stuName from Student,Scores where Stud