关联查询题

— 班级表
create table class(
cid int primary key,
caption varchar(10)
);
insert into class values (1,'三年二班');
insert into class values (2,'三年一班');
insert into class values (3,'一年二班');
insert into class values (4,'一年三班');
insert into class values (5,'二年二班');

— 学生表
create table student(
sid int primary key,
sname varchar(10),
gender enum('男','女'),
class_id int,
foreign key(class_id)references class(cid)
);
insert into student values(001,'张起灵','男',1);
insert into student values(002,'王老五','男',2);
insert into student values(003,'蔡徐坤','男',3);
insert into student values(004,'药水','女',4);
insert into student values(005,'呵呵','女',5);
insert into student values(006,'xx','女',5);

-- delete from student;

— 老师表
create table teacher(
tid int primary key,
tname varchar(10)
);
insert into teacher values(1,'李老师');
insert into teacher values(2,'叶平');
insert into teacher values(3,'李晨');
insert into teacher values(4,'王老师');
insert into teacher values(5,'唐老师');

-- delete from teacher;

— 课程表
create table course(
cid int,
cname varchar(10),
teacher_id int,
foreign key(teacher_id)references teacher(tid)
);
insert into course values(1,'语文',1);
insert into course values(2,'数学',2);
insert into course values(3,'生物',3);
insert into course values(4,'化学',4);
insert into course values(5,'英语',5);

— 成绩表

create table score(
sid int primary key,
student_id int,
foreign key(student_id)references student(sid),
course_id int,
foreign key(course_id)references course(cid),
number int
);
insert into score values(1,1,1,100);
insert into score values(6,1,2,90);
insert into score values(7,1,3,90);
insert into score values(8,1,4,90);
insert into score values(9,1,5,90);
insert into score values(2,2,2,60);
insert into score values(3,3,3,70);
insert into score values(4,4,4,40);
insert into score values(5,5,5,50);

select * from score
-- 
-- 1、查询平均成绩大于60分的同学的学号和平均成绩；
select student.sid,avg(number) from student,score where student.sid = score.student_id group by student.sid having avg(number) > 60;
-- 2、查询所有同学的学号、姓名、选课数、总成绩；
-- select student.sid,student.sname,count(course_id),sum(number) from student,score,course where student.sid = score.student_id and score.course_id = course.cid group by student.sid;
select student.sid,student.sname,count(course_id),ifnull(sum(number),0) from student left join score on student.sid = score.student_id group by student.sid;
-- 3、查询姓“李”的老师的个数；
select count(*) from teacher where tname like '李%';
-- 4、查询没学过“叶平”老师课的同学的学号、姓名；
select student.sid,student.sname from student  where student.sid not in (select student_id from score,course,teacher where score.course_id = course.cid and course.teacher_id = teacher.tid and tname = '叶平');
-- 5、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select student.sid,student.sname from student left join score on student.sid = score.student_id left join course on score.course_id=course.cid left join teacher on teacher.tid = course.teacher_id where tname = '叶平';
-- 6、查询没有学全所有课的同学的学号、姓名；
select student.sid,student.sname from student left join score on student.sid = score.student_id left join course on score.course_id = course.cid group by student.sid having count(*)<(select count(*) from course);
-- 7、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
select student.sid,student.sname from student left join score on student.sid = score.student_id left join course on score.course_id = course.cid where course_id in (select course_id from score where score.student_id = 1) and student.sid != 1;
-- 8、删除学习“叶平”老师课的score表记录
-- a.查出叶平老师带的课
select sid from teacher,course,score  where teacher.tid = course.teacher_id  and score.course_id = course.cid and tname = '叶平';
-- b.创建中间表
select * from (select sid from teacher,course,score  where teacher.tid = course.teacher_id  and score.course_id = course.cid and tname = '叶平') as b;
delete from score where score.sid in (select * from (select sid from teacher,course,score  where teacher.tid = course.teacher_id  and score.course_id = course.cid and tname = '叶平') as b);
-- 9、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id '课程ID',max(number) '最高分',min(number) '最低分' from score group by course_id; 
-- 10、查询出只选修了一门课程的全部学生的学号和姓名；
select student.sid,student.sname from student,score where student.sid = score.student_id group  by student_id having count(*) = 1;
-- 11、查询男生、女生的人数；
select gender,count(*) from student group by gender;
-- 12、查询姓“张”的学生名单；
select sname from student where sname like '张%';