表数据：
CREATE TABLE J_TEACHER (
tno int NOT NULL PRIMARY KEY,
tname varchar(20) NOT NULL
);
CREATE TABLE J_STUDENT(
sno int NOT NULL PRIMARY KEY,
sname varchar(20) NOT NULL,
sage datetime NOT NULL,
ssex char(2) NOT NULL
);
CREATE TABLE J_COURSE(
cno int NOT NULL PRIMARY KEY,
cname varchar(20) NOT NULL,
tno int NOT NULL
);
CREATE TABLE J_SCORE(
sno int NOT NULL,
cno int NOT NULL,
score int NOT NULL
);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_SCORE_course FOREIGN KEY(cno)
REFERENCES J_COURSE (cno);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_score_student FOREIGN KEY(sno)
REFERENCES J_STUDENT (sno);
练习：
1、查询课程1的成绩比课程2的成绩高的所有学生的学号。
select s1.sno from
j_score s1 join j_score s2
on s1.sno = s2.sno
where s1.cno = 1 and s2.cno = 2 and s1.score > s2.score;2、查询平均成绩大于60分的同学的学号和平均成绩。
select sno,avg(score) from j_score group by sno having avg(score) > 603、查询所有同学的学号、姓名、选课数、总成绩。
select s1.sno,s1.sname,s2.a,s2.b from
j_student s1
join
(select sno,count(*) a ,sum(score) b from j_score group by sno) s2
on s1.sno = s2.sno;4、查询姓“李”的学生的个数。
select count(sno) from j_student where sname like '李%';5、查询没学过“叶平”老师课的同学的学号、姓名。
select sno,sname from j_student where sno not in(
select sno from j_score where cno in(
select cno from j_course where tno in(
select tno from j_teacher where tname = '叶平'
)
)
);6、查询同时学过课程1和课程2的同学的学号、姓名。
select sno,sname from j_student where sno in(
select s1.sno from
(select sno from j_score where cno = 1) s1
join
(select sno from j_score where cno = 2) s2
on s1.sno = s2.sno
)7、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名。
select sno,sname from j_student where sno in (
select sno from
j_score
join
j_course
on
j_score.cno = j_course.cno
join
j_teacher
on j_course.tno = j_teacher.tno
where j_teacher.tname = '叶平'
group by sno
having count(distinct j_course.cno) = (select count(cno) from j_course join j_teacher on j_course.tno = j_teacher.tno where j_teacher.tname = '叶平')
) 8、查询 课程编号1的成绩 比 课程编号2的成绩 高的所有同学的学号、姓名。
select sno,sname from j_student where sno in (
select s1.sno from
(select sno,score from j_score where cno = 1) s1
join
(select sno,score from j_score where cno = 2) s2
on s1.sno = s2.sno
where s1.score > s2.score
);9、查询所有课程成绩小于60分的同学的学号、姓名。
select sno,sname from j_student where sno in(
select s1.sno from
(select sno,count(*) a from j_score where score < 60 group by sno) s1
join
(select sno,count(*) b from j_score group by sno ) s2
on s1.a = s2.b
); 10、查询所有课程成绩大于60分的同学的学号、姓名。
select sno,sname from j_student where sno in(
select s1.sno from
(select sno,count(*) a from j_score where score > 60 group by sno) s1
join
(select sno,count(*) b from j_score group by sno ) s2
on s1.a = s2.b
);11、查询没有学全所有课的同学的学号、姓名
select sno,sname from j_student where sno in(
select sno from j_score
group by sno
having count(cno) < (select count(distinct cno) from j_course)
);12、查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名
select sno,sname from j_student where sno in(
select distinct sno from j_score where cno in(
select cno from j_score where sno=1
)
);13、查询和2号同学学习的课程完全相同的其他同学学号和姓名。
select sno,sname from j_student where sno in (
select s1.sno from
(select * from j_score where sno!=2 ) s1
join
(select cno from j_score where sno= 2) s2
on s1.cno=s2.cno
group by sno
having count(*) = (select count(cno) from j_score where sno = 2)
)14、查询各科成绩最高分和最低分。以如下形式显示：课程号，最高分，最低分
select cno,max(score),min(score) from j_score group by cno;15、查询每门课程被选修的学生数。
select count(distinct sno) from j_score group by cno;16、查询出只选修了一门课程的全部学生的学号和姓名。
select sno,sname from j_student where sno in (
select s1.sno from (select sno,count(cno) a from j_score group by sno) s1
where s1.a = 1
);17、查询同名同性学生名单，并统计同名人数。
select count(*) from j_student s1,j_student s2 where s1.sname = s2.sname and s1.ssex = s2.ssex and s1.sno != s2.sno group by s1.sname;
select sname,count(*) from j_student group by sname having count(*)>1;18、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩。
select s1.sname,s2.score from
j_student s1
join (
select sno,score from j_score where score in (
select max(score) from j_score where cno in (
select cno from j_course where tno in (
select tno from j_teacher where tname = '叶平'
)
)group by cno
)) s2
on s1.sno = s2.sno;19、查询不同课程成绩相同的学生的学号、课程号、学生成绩。
select s1.sno,s1.cno,s1.score from
j_score s1,j_score s2
where s1.score = s2.score
and s1.cno != s2.cno
and s1.sno = s2.sno;20、查询每门课程成绩最好的学生学号
select sno,cno from j_score s1
where s1.score = (select max(score) from j_score s2 where s1.cno = s2.cno)21、查询至少选修了5门课程的学生学号。
select sno from j_score group by sno having count(*) >= 5;22、查询没学过“叶平”老师讲授的任一门课程的学生姓名。
select sname from j_student where sno not in (
select sno from j_score where cno in (
select cno from j_course where tno = (
select tno from j_teacher where tname = '叶平'
)
)
);23、查询两门以上不及格课程的同学的学号及其平均成绩。
select sno,avg(score) from j_score
where score < 60 group by sno having count(*) >= 2;24、查询最受欢迎的老师(选修学生最多的老师)。
select tno,tname from j_teacher where tno in(
select tno from j_course where cno in (
select cno from j_score group by cno having count(*) =(
select max(b.a) from (
select cno,count(sno) a from j_score group by cno
) b
)
)
);
