mysql闯关练习

1、表关系


班级表：class				学生表：student
cid	caption	grade_id		sid	sname	gender	class_id
1	一年一班	1		1	乔丹	女	1
2	二年一班	2		2	艾弗森	女	1
3	三年二班	3		3	科比	男	2

老师表：teacher				课程表：course
tid	tname			cid	cname	teacher_id
1	张三			1	生物	1
2	李四			2	体育	1
3	王五			3	物理	2

成绩表：score					年级表：class_grade
sid	student_id	course_id	score		gid	gname
1	1	1	60		1	一年级
2	1	2	59		2	二年级
3	2	2	99		3	三年级

班级任职表：teach2cls
tcid	tid	cid
1	1	1
2	1	2
3	2	1
4	3	2

二、题目要求

# 题目：
        1、自行创建测试数据；
        2、查询学生总人数；
        3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
        4、查询每个年级的班级数，取出班级数最多的前三个年级；
        5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
        6、查询每个年级的学生人数；
        7、查询每位学生的学号，姓名，选课数，平均成绩；
        8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
        9、查询姓“李”的老师的个数和所带班级数；
        10、查询班级数小于5的年级id和年级名；
        11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；
            班级id   班级名称   年级 年级级别
            1  一年一班   一年级    低
        12、查询学过“张三”老师2门课以上的同学的学号、姓名；
        13、查询教授课程超过2门的老师的id和姓名；
        14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
        15、查询没有带过高年级的老师id和姓名；
        16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
        17、查询带过超过2个班级的老师的id和姓名；
        18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
        19、查询所带班级数最多的老师id和姓名；
        20、查询有课程成绩小于60分的同学的学号、姓名；
        21、查询没有学全所有课的同学的学号、姓名；
        22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
        23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
        24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；
        25、删除学习“张三”老师课的score表记录；
        26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
        27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
        28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
        29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
        30、课程平均分从高到低显示（显示任课老师）；
        31、查询各科成绩前三名的记录(不考虑成绩并列情况)
        32、查询每门课程被选修的学生数；
        33、查询选修了2门以上课程的全部学生的学号和姓名；
        34、查询男生、女生的人数，按倒序排列；
        35、查询姓“张”的学生名单；
        36、查询同名同姓学生名单，并统计同名人数；
        37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
        38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
        39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
        40、求选修了课程的学生人数
        41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
        42、查询各个课程及相应的选修人数；
        43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
        44、查询每门课程成绩最好的前两名学生id和姓名；
        45、检索至少选修两门课程的学生学号；
        46、查询没有学生选修的课程的课程号和课程名；
        47、查询没带过任何班级的老师id和姓名；
        48、查询有两门以上课程超过80分的学生id及其平均成绩；
        49、检索“3”课程分数小于60，按分数降序排列的同学学号；
        50、删除编号为“2”的同学的“1”课程的成绩；
        51、查询同时选修了物理课和生物课的学生id和姓名；

View Code

三、解答

1、表与表之间的关系

学生表：student 多对一班级表：class

班级表：class 多对一年级表：class_grade

课程表：course 多对一老师表：teacher

成绩表：score　　多对一　学生表：student

成绩表：score　　多对一课程表：course

teacher2cls：

老师表：teacher　多对多班级表：class

2、建立表格

一、建立表格
先建立被关联的表，再建立关联表

0、班级表：class        多对一     年级表：class_grade

建立年级表--------class_grade----------

create table class_grade(
gid int primary key auto_increment,
gname varchar(20) not null unique
);

1、学生表：student    多对一     班级表：class

建立班级表-----------class------------

create table class(
cid int primary key auto_increment,
caption varchar(20) not null,
grade_id int not null,
foreign key(grade_id) references class_grade(gid) on delete cascade on update cascade
);


建立学生表-------student-------------

create table student(
sid int primary key auto_increment,
sname varchar(20) not null,
gender enum('male','female') not null default 'male',
class_id int not null,
foreign key (class_id) references class(cid) on delete cascade on update cascade
);

3、课程表：course     多对一     老师表：teacher

建立老师表---------teacher-----------

create table teacher(
tid int not null primary key auto_increment,
tname varchar(20) not null
);

建立课程表---------course------------

create table course(
cid int primary key auto_increment,
cname varchar(20) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid) on delete cascade on update cascade
);

4、成绩表：score　　多对一　 学生表：student
5、成绩表：score　　多对一     课程表：course
学生表：student   课程表：course

建立成绩表--------score-----------

create table score(
sid int primary key auto_increment,
student_id int not null,
foreign key(student_id) references student(sid) on delete cascade on update cascade,
course_id int not null,
foreign key(course_id) references course(cid) on delete cascade on update cascade,
unique(student_id,course_id),
score int not null
);

6、teacher2cls：

老师表：teacher　多对多      班级表：class

建立班级任职表------teacher2cls-------

create table teach2cls(
tcid int primary key auto_increment,
tid int not null,
cid int not null,
unique(tid,cid),
foreign key(tid) references student(sid) on delete cascade on update cascade,
foreign key(cid) references class(cid) on delete cascade on update cascade
);

View Code

3、准备数据

# ------------------------插入数据
# ----------------------------设定4个 年级 -------------------------
insert into class_grade(gname) values
('一年级'),
('二年级'),
('三年级'),
('四年级');
# ---------------------设定每个年级的 班级 个数------------------------
共10个班级
1年级1个班；2年级2个班；3年级3个班；4年级4个班

insert into class(caption,grade_id) values
('一年级一班',1),


('二年级一班',2),
('二年级二班',2),


('三年级一班',3),
('三年级二班',3),
('三年级三班',3),

('四年级一班',4),
('四年级二班',4),
('四年级三班',4),
('四年级四班',4);


# ------------------设定  学生表  每个班名学生-------------------------
1 2 4 4 4 其余3--------------共30名学生
('一年级一班') 1个学生
('二年级一班') 2个学生
('二年级二班') 3个学生
('三年级一班') 4个学生
('三年级二班') 4个学生
('三年级三班') 4个学生
('四年级一班') 3个学生
('四年级二班') 3个学生
('四年级三班') 3个学生
('四年级四班') 3个学生

insert into student(sname,gender,class_id) values
('乔丹','male',1),


('alex','male',2),
('egon','female',2),


('乔丹2','male',3),
('爱丽丝2','female',3),
('布莱恩2','male',3),

('乔丹','male',4),
('alex2','male',4),
('egon2','female',4),
('victor2','male',4),

('alex3','male',5),
('乔丹3','male',5),
('爱丽丝3','female',5),
('布莱恩3','male',5),

('victor2','male',6),
('alex3','male',6),
('egon3','female',6),
('张胜利','male',6),

('乔丹4','male',7),
('爱丽丝4','female',7),
('布莱恩4','male',7),

('alex4','male',8),
('egon4','female',8),
('victor4','male',8),

('乔丹5','male',9),
('爱丽丝5','female',9),
('布莱恩5','male',9),

('alex5','male',10),
('egon5','female',10),
('victor5','male',10);



# ---------------老师表----------------------
# 设置10名老师  ('吉泽')----没授课----

insert into teacher(tname) values
('张三'),
('李四'),
('王五'),
('曹操'),
('刘备'),
('孙权'),
('许褚'),
('赵云'),
('黄盖'),
('吉泽');

# ----------------课程表-------------------
# ---13门课程  ('艺术',10)---没人选-----
insert into course(cname,teacher_id) values
('语文',1),
('数学',2),
('英语',3),
('物理',4),
('化学',5),
('生物',6),

('体育',7),
('音乐',8),
('美术',9),
('计算机',3),
('政治',3),
('历史',6),
('艺术',10);

# --------------------成绩表------------------------
# 30个学生选课数为
每个学生 选课数为 ，1 ，2 ，3  ，4 ，5 ，6 ，7 ，其余全部2

insert into score(student_id,course_id,score) values
(1,1,70),(2,3,50),(2,4,60),(3,5,80),(3,6,90),(3,7,91),(4,7,100),(4,8,50),(4,9,60),(4,10,70),(5,9,60),(5,10,70),(5,11,80),(5,12,90),(5,1,92),
(6,11,80),(6,12,90),(6,1,93),(6,2,94),(6,3,95),(6,4,96),(7,1,97),(7,2,50),(7,3,60),(7,4,66),(7,5,70),(7,6,80),(7,7,90),
(8,3,60),(8,4,70),(9,5,80),(9,6,90),(10,7,99),(10,8,50),
(11,9,60),(11,10,75),(12,11,80),(12,12,90),(13,1,92),(13,2,50),(14,3,60),(14,4,70),(15,5,80),(15,6,90),
(16,7,99),(16,8,50),(17,9,61),(17,10,74),(18,11,80),(18,12,85),(19,1,77),(19,2,91),(20,3,100),(20,4,55),(21,5,60),(21,6,74),
(22,7,80),(22,8,90),(23,9,88),(23,10,50),(24,11,60),(24,12,70),(25,1,80),(25,2,90),(26,3,85),(26,4,50),
(27,5,61),(27,6,75),(28,7,80),(28,8,90),(29,9,100),(29,10,50),(30,11,61),(30,12,78);

# -----------------班级任职表：teach2cls-------------

1 345 其余 2

cid：4个4 3个3 5个5
count(tid)--count(cid)       cid---------count(cid)
    1------------2             1---------------1
    2------------2             2---------------2
    3------------2             3---------------2
    4------------2             4---------------2
    5------------2             5---------------4
    6------------1             6---------------2
    7------------3             7---------------1
    8------------4             8---------------1
    9------------4             9---------------2
                               10--------------1

insert into teach2cls(tid,cid) values
(1,5),
(1,2),
(2,3),
(2,5),
(3,5),
(3,6),
(4,7),
(4,8),
(5,9),
(5,10),

(6,1),
(7,2),
(7,3),
(7,4),

(8,4),
(8,9),
(8,5),
(8,6),

(9,3),
(9,5),
(9,1),
(9,4),
(9,8);

View Code

4、查询语句

# -----------------查询语句-------------------------
1、自行创建测试数据；

2、查询学生总人数；
select count(sid) as 学生总人数 from student;

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
select sid,sname from student where sid in
(select student_id from score where course_id in
(select cid from course where course.cname in('物理','生物')) and score>=60
group by student_id having count(course_id)=2
);

4、查询每个年级的班级数，取出班级数最多的前三个年级；
select
    grade_id 年级编号,count(caption) as class_number
from
    class group by grade_id order by class_number desc limit 3;

5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；-------------------------------------------
select student.sid,student.sname,t1.avg_score from student,
(
select student_id ,avg(score) as avg_score from score where student_id in
(
(select student_id from score group by student_id  order by avg(score) desc limit 1),
(select student_id from score group by student_id  order by avg(score) asc limit 1)
) group by student_id
) as t1 where student.sid = t1.student_id;

6、查询每个年级的学生人数；

select gname,t2.count_student from class_grade inner join
(
select t1.grade_id ,count(t1.sid)as count_student from
(select student.sid,class.grade_id from student,class where student.class_id=class.cid)
as t1 group by t1.grade_id
) as t2 on class_grade.gid=t2.grade_id order by t2.count_student asc;


7、查询每位学生的学号，姓名，选课数，平均成绩；
select sid,sname,t1.count_course,t1.avg_score from student inner join
(select student_id,count(course_id) as count_course,avg(score) as avg_score from score group by student_id)
as t1 on student.sid=t1.student_id;


8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
select student.sname,course.cname,t1.score from
(
select student_id,course_id,score from score where student_id=2
and score in (
(select max(score) from score where student_id=2),
(select min(score) from score where student_id=2)
)
)as t1
inner join student  on t1.student_id=student.sid
inner join course  on t1.course_id=course.cid
;

9、查询姓“李”的老师的个数和所带班级数；
select teacher.tid,teacher.tname,t1.count_class from teacher inner join
(
select tid ,count(cid) as count_class from teach2cls where tid in
(select tid from teacher where tname like '李%')
group by tid
) as t1 where teacher.tid=t1.tid;

10、查询班级数小于5的年级id和年级名；
select gid,gname,t2.count_class from class_grade inner join
(select t1.grade_id,t1.count_class from
(select grade_id ,count(cid) as count_class from class  group by grade_id)
as t1  where t1.count_class<5) as t2
on t2.grade_id=class_grade.gid;

# 第二种更简洁
select gid ,gname from class_grade where gid in
(select grade_id from class group by grade_id
having count(class.grade_id)<5);


11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果
如下；
 班级id    班级名称        年级    年级级别
  1        一年一班      一年级       低

select
    class.cid as 班级id,class.caption 班级名称,class_grade.gname as 年级,
    case when class_grade.gid between 1 and 2 then '低'
         when class_grade.gid between 3 and 4 then '中'
         when class_grade.gid between 5 and 6 then '高'
    else 0 end as '年级级别'

from
    class,class_grade
where
    class.grade_id=class_grade.gid;

12、查询学过“张三”老师2门课以上的同学的学号、姓名；

select student.sid ,student.sname from student,
(select student_id from score
where course_id in (
select course.cid from teacher, course
where teacher.tid = course.teacher_id and teacher.tname = '张三')
group by student_id
having count(course_id) > 2) as t1 where student.sid=t1.student_id;

13、查询教授课程超过2门的老师的id和姓名；
select teacher.tid,teacher.tname from teacher,
(select teacher_id,count(cid) from course group by teacher_id having count(cid)>2) as t1
where teacher.tid=t1.teacher_id;

# 方法二更简洁
select tid,tname from teacher where tid in
(select teacher_id from course group by teacher_id having count(cid)>2);



14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
select sid,sname from student where sid in
(select  student_id from score where course_id in (1,2));

15、查询没有带过高年级的老师id和姓名；

select tid,tname from teacher where tid not in
(select teach2cls.tid from teach2cls,class where teach2cls.cid=class.cid
and class.grade_id between 5 and 6 );

16、查询学过“张三”老师所教的所有课的同学的学号、姓名；

select student.sid,student.sname from student,
(select score.student_id,score.course_id from score,
  (select cid ,teacher_id from course where teacher_id  in
      (select tid from teacher where tname='张三')
  ) as t1  where score.course_id=t1.cid
) as t2
where student.sid=t2.student_id;

# 第二种更简洁
select sid,sname from student where sid in
(select student_id from score where course_id in
  (select cid from course where teacher_id  in
       (select tid from teacher where tname='张三')
  )
);

17、查询带过超过2个班级的老师的id和姓名；
select tid,tname from teacher where tid in
(select tid from teach2cls group by tid having count(cid)>2);

18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;
select sid,sname from student where sid in
(select t1.student_id from
  (select student_id,score from score where course_id =2) as t1,
  (select student_id,score from score where course_id =1) as t2
where t1.score<t2.score and t1.student_id=t2.student_id
);

19、查询所带班级数最多的老师id和姓名；
select tid,tname from teacher where tid in
(select tid from teach2cls group by tid having count(cid) =
  (select count(tid) from teach2cls  group by tid order by count(cid) desc limit 1)
);

20、查询有课程成绩小于60分的同学的学号、姓名；
select sid,sname from student where sid in
(select distinct student_id from score where score<60 );

21、查询没有学全所有课的同学的学号、姓名；
select sid,sname from student where sid not in
(select student_id from score
    group by student_id having count(course_id)=(select count(cid) from course)
);

22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select sid ,sname from student where sid in
(select student_id from score
    where  course_id in
    (select course_id from score where student_id=1)
group by student_id
);

23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
select sid ,sname from student where sid in
(select student_id from score
    where student_id!=1 and course_id in
    (select course_id from score  where student_id=1)
group by student_id
);

24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；
select sid,sname from student where sid in
(select student_id from score
    where student_id !=2 and course_id in
    (select course_id from score where student_id=2 group by course_id)
     group by student_id
     having count(course_id) =
     (select count(course_id) as count_course from score where student_id=2 group by student_id)
);


25、删除学习“张三”老师课的score表记录；
delete from score where course_id in
(select course.cid from course,teacher where course.teacher_id = teacher.tid
  and teacher.tname='张三'
)

26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课
程的平均成绩；
insert into score(student_id,course_id,score)
select t1.sid,2,t2.avg_score from
(select sid from student where sid not in
  (select student_id from score where course_id=2)
) as t1,
(select avg(score) from score where course_id=2) as t2;

27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,
数学,英语,课程数和平均分；
select
sc.student_id,
(select
    score.score from score left join course on score.course_id=course.cid where course.cname='语文'
        and score.student_id = sc.student_id
)as Chinese,
(select
    score.score from score left join course on score.course_id = course.cid where course.cname='数学'
        and score.student_id=sc.student_id
)as math,
(select
    score.score from score left join course on score.course_id = course.cid where course.cname='英语'
        and score.student_id=sc.student_id
)as english,
    count(sc.course_id),
    avg(sc.score)
from score as sc
group by
    sc.student_id
order by
    avg(sc.score) asc;

28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course.cid,max(score.score) as 最高分 ,min(score.score) as 最低分
from course left join score on course.cid = score.course_id group by score.course_id;

29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
# 这题有点小问题

select course_id,avg(score.score) as avg_score,
(sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100) as percent
from score group by course_id order by avg_score asc, percent desc;

30、课程平均分从高到低显示（显示任课老师）；
select course.cid,tname,t1.avg_score from course,teacher,
( select course_id,avg(score) as avg_score from score group by course_id
 ) as t1
where course.cid=t1.course_id and teacher.tid=course.teacher_id order by t1.avg_score desc;

31、查询各科成绩前三名的记录(不考虑成绩并列情况) ；
select score.sid,score.student_id,score.course_id,score.score,t1.first_score,t1.second_score,t1.third_score
from score inner join(
select
    s1.sid,
    (select score from score as s2 where s1.course_id=s2.course_id order by score  desc limit 0,1) as first_score,
    (select score from score as s3 where s1.course_id=s3.course_id order by score  desc limit 1,1) as second_score,
    (select score from score as s4 where s1.course_id=s4.course_id order by score  desc limit 2,1) as third_score

from score as s1
) as t1 on score.sid = t1.sid
where score.score in(
    t1.first_score,
    t1.second_score,
    t1.third_score
);

32、查询每门课程被选修的学生数；
select course.cid as 课程编号,count(score.student_id) as stu_num from
score right join course on score.course_id = course.cid group by course_id;

# 此时 right join course 以course为主

33、查询选修了2门以上课程的全部学生的学号和姓名；
select sid,sname from student where sid in
(select student_id from score group by student_id having count(course_id)>2);

34、查询男生、女生的人数，按倒序排列；
select gender,count(sid) from student group by gender order by count(sid) desc;

35、查询姓“张”的学生名单；
select student.sid,student.sname,student.gender,class.caption from class, student
where student.sname like '张%' and student.class_id=class.cid;

36、查询同名同姓学生名单，并统计同名人数；
select sname,count(sid) as count_sname from student group by sname having count(sid)>=2;

37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
#先按照avg(score)升序排，如果avg(score)相同则按照id 降序排
select course_id,avg(score) from score group by course_id order by  avg(score) asc ,course_id desc;

38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

select student.sname,t1.score from student,
(select student_id ,score from score where course_id =
(select cid from course where cname='数学')
and score <60) as t1 where t1.student_id=student.sid;

39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；

select sid,sname from student  where sid in
(select student_id from score where course_id=3
and score>80);

40、求选修了课程的学生人数
select course_id,count(student_id) as count_student from score group by course_id;

41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

select student.sname,t2.course_id,t2.score,t2.max_score,t2.min_score from student
inner join (
select score.student_id,score.course_id,score.score,t1.max_score,t1.min_score from score,(
select course_id,max(score) as max_score ,min(score) as min_score from score
where course_id in (
select cid from course
inner join teacher on course.teacher_id = teacher.tid
where teacher.tname = '王五')
group by course_id) as t1
where score.course_id = t1.course_id and score.score in (max_score,min_score)
)as t2
on student.sid = t2.student_id;

42、查询各个课程及相应的选修人数；

select course.cid,course.cname,t1.count_course from course,
(select course.cid,count(score.student_id) as count_course from
score right join course on course.cid=score.course_id
group by score.course_id) as t1
where course.cid=t1.cid;

43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select distinct s1.student_id,s2.student_id,s1.course_id as s1_course_id,s2.course_id
as s2_course_id,s1.score,s2.score from score
as s1, score as s2
where s1.course_id != s2.course_id and s1.score = s2.score;

44、查询每门课程成绩最好的前两名学生id和姓名；
    select
                student.sid,
                student.sname,
                t2.course_id,
                t2.score,
                t2.first_score,
                t2.second_score
            from
                student
            inner join (
                select
                    score.student_id,
                    score.course_id,
                    score.score,
                    t1.first_score,
                    t1.second_score
                from
                    score
                inner join (
                    select
                        s1.sid,
                        (select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,
                        (select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score
                    from
                        score as s1
                ) as t1 on score.sid = t1.sid
                where
                    score.score in (
                        t1.first_score,
                        t1.second_score
                    )
            ) as t2 on student.sid = t2.student_id;
45、检索至少选修两门课程的学生学号；
select student_id from score group by student_id having count(course_id)>1;

46、查询没有学生选修的课程的课程号和课程名；
select cid,cname from course where  cid not in
(select course_id from score group by course_id);

47、查询没带过任何班级的老师id和姓名；
select tid,tname from teacher where tid not in
(select tid from teach2cls group by tid);

48、查询有两门以上课程超过80分的学生id及其平均成绩；
select student_id ,avg(score) from score where student_id in
(select student_id from score where score>80 group by student_id having count(course_id)>2);

49、检索“3”课程分数小于60，按分数降序排列的同学学号；
select student_id,score from score where course_id=3 and score<60 order by score desc;

50、删除编号为“2”的同学的“1”课程的成绩；
delete from score where sid=
(select t1.sid from (select sid from score where student_id =2 and course_id=1) as t1);

51、查询同时选修了物理课和生物课的学生id和姓名；

select sid,sname from student where sid in
(
select student_id from score where course_id in
(
select cid from course where course.cname in('物理','生物')
)
group by student_id having count(course_id)=2
);

View Code

重点关注的语法题

1(12为低年级，34为中年级，56为高年级)

11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果
如下；
 班级id    班级名称        年级    年级级别
  1        一年一班      一年级       低

select
    class.cid as 班级id,class.caption 班级名称,class_grade.gname as 年级,
    case when class_grade.gid between 1 and 2 then '低'
         when class_grade.gid between 3 and 4 then '中'
         when class_grade.gid between 5 and 6 then '高'
    else 0 end as '年级级别'

from
    class,class_grade
where
    class.grade_id=class_grade.gid;

2、删除学习“张三”老师课的score表记录；

delete from score where course_id in
(select course.cid from course,teacher where course.teacher_id = teacher.tid
  and teacher.tname='张三'
)

3、删除编号为“2”的同学的“1”课程的成绩；

delete from score where sid=
(select t1.sid from (select sid from score where student_id =2 and course_id=1) as t1);

4、truncate删除表中的所有数据，保留表的结构

posted on 2018-10-12 15:59 foremost 阅读(504) 评论(0) 编辑收藏举报