day35作业

1. 查询所有大于60分的学生的姓名和学号  (DISTINCT: 去重)

-- 2.查询每个老师教授的课程数量 和 老师信息

-- 3. 查询学生的信息以及学生所在的班级信息

-- 4、学生中男生的个数和女生的个数

-- 5、获取所有学习'生物'的学生的学号和成绩；姓名

-- 6、查询平均成绩大于60分的同学的学号和平均成绩； 

-- 7、查询姓“李”的老师的个数；

-- 8、查询课程成绩小于60分的同学的学号、姓名；

-- 9. 删除学习“叶平”老师课的SC表记录

-- 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

-- 11.查询每门课程被选修的学生数

-- 12.查询姓“张”的学生名单；

-- 13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

-- 14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

-- 15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；

-- 16.查询各个课程及相应的选修人数

-- 17.查询“4”课程分数小于60，按分数降序排列的同学学号

-- 18.删除学号为“2”的同学的“1”课程的成绩

准备：

--班级表

create table class(

	cid int auto_increment primary key,

	caption char(25) not null default ''

)charset utf8;

insert into class (caption) values('三年二班'),('一年三班'),('三年一班');

--学生表

create table student(

	sid int auto_increment primary key,

	sname char(25) not null default '',

	gender enum('男','女') not null,

	class_id int not null default 1,

	constraint fk_class foreign key(class_id) references class(cid)

)charset utf8;

insert into student (sname,gender,class_id) values ('张三',1,1),('李四',2,1),('王五',2,2);

--老师表

create table teacher(

	tid int auto_increment primary key,

	tname char(25) not null default ''

)charset utf8;

insert into teacher (tname) values ('张磊老师'),('李平老师'),('李海燕老师');

--课程表

create table course(

	cid int auto_increment primary key,

	cname char(25) not null default '',

	teacher_id int not null default 1,

	constraint fk_course foreign key(teacher_id) references teacher(tid)

)charset utf8;

insert into course (cname,teacher_id) values ('生物',1),('体育',1),('物理',2);

--分数表

create table score(

	sid int auto_increment primary key,

	student_id int not null default 1,

	course_id int not null default 1,

	number int not null default 1,

	constraint fk_score foreign key(student_id) references student(sid),

	constraint fk_score2 foreign key(course_id) references course(cid)

)charset utf8;

insert into score (student_id,course_id,number) values (1,1,60),(1,2,59),(2,2,100);

答案：

--3. 查询学生的信息以及学生所在的班级信息

select student.*,class.caption from student left join class on student.class_id=class.cid;--1. 查询所有大于60分的学生的姓名和学号

select student.sid,student.sname from student left join score on student.sid=score.student_id where score.number>60;

+------+-------+

| sid  | sname |

+------+-------+

|    2 | 李四  |

+------+-------+

--2.查询每个老师教授的课程数量 和 老师信息

select teacher.tname,count(course.cname) as '课程数量' from teacher left join course on teacher.tid=course.teacher_id group by teacher.tid;

+------------+----------+

| tname      | 课程数量 |

+------------+----------+

| 张磊老师   |        2 |

| 李平老师   |        1 |

| 李海燕老师 |        0 |

+------------+----------+

--3. 查询学生的信息以及学生所在的班级信息

select student.*,class.caption from student left join class on student.class_id=class.cid;

+-----+-------+--------+----------+----------+

| sid | sname | gender | class_id | caption  |

+-----+-------+--------+----------+----------+

|   1 | 张三  | 男     |        1 | 三年二班 |

|   2 | 李四  | 女     |        1 | 三年二班 |

|   3 | 王五  | 女     |        2 | 一年三班 |

+-----+-------+--------+----------+----------+

--4. 学生中男生的个数和女生的个数

select gender,count(*) from student group by gender;

+--------+----------+

| gender | count(*) |

+--------+----------+

| 男     |        1 |

| 女     |        2 |

+--------+----------+

--5、获取所有学习'生物'的学生的学号和成绩；姓名

select student.sid,student.sname,score.number from score left join student on score.student_id=student.sid left join course on score.course_id=course.cid where course.cname='生物';

+------+-------+--------+

| sid  | sname | number |

+------+-------+--------+

|    1 | 张三  |     60 |

+------+-------+--------+

-- 6、查询平均成绩大于60分的同学的学号和平均成绩

select score.student_id,avg(number) from score group by student_id having avg(number)>60;

+------------+-------------+

| student_id | avg(number) |

+------------+-------------+

|          2 |    100.0000 |

+------------+-------------+

-- 7、查询姓“李”的老师的个数

select count(tname) from teacher where tname like '李%';

+--------------+

| count(tname) |

+--------------+

|            2 |

+--------------+

--8、查询课程成绩小于60分的同学的学号、姓名

select sid,sname from student where sid in (select distinct student_id from score where number<60);

+-----+-------+

| sid | sname |

+-----+-------+

|   1 | 张三  |

+-----+-------+

--9. 删除学习“叶平”老师课的SC表记录

delete from score where course_id in (select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = '叶平');

-- 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select course_id as '课程ID', max(number) as '最高分', min(number) as '最低分' from score group by course_id;

+--------+--------+--------+

| 课程ID | 最高分 | 最低分 |

+--------+--------+--------+

|      1 |     60 |     60 |

|      2 |    100 |     59 |

+--------+--------+--------+

-- 11.查询每门课程被选修的学生数

select course.cname,count(*) from score left join course on score.course_id=course.cid group by course_id;

+-------+----------+

| cname | count(*) |

+-------+----------+

| 生物  |        1 |

| 体育  |        2 |

+-------+----------+

-- 12.查询姓“张”的学生名单

select sname from student where sname like '张%';

+-------+

| sname |

+-------+

| 张三  |

+-------+

-- 13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select score.course_id,course.cname,avg(score.number) as average from score,course where score.course_id=course.cid group by score.course_id order by avg(score.number) asc,avg(course.cid) desc;

+-----------+-------+---------+

| course_id | cname | average |

+-----------+-------+---------+

|         1 | 生物  | 60.0000 |

|         2 | 体育  | 79.5000 |

+-----------+-------+---------+

--14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select student.sid,student.sname,avg(score.number) from student left join score on score.student_id=student.sid group by student.sid having avg(score.number)>85;

+-----+-------+-------------------+

| sid | sname | avg(score.number) |

+-----+-------+-------------------+

|   2 | 李四  |          100.0000 |

+-----+-------+-------------------+

-- 15.查询课程编号为2且课程成绩在80分以上的学生的学号和姓名；

select student.sid,student.sname from student left join score on score.student_id=student.sid where score.course_id=2;

+-----+-------+

| sid | sname |

+-----+-------+

|   1 | 张三  |

|   2 | 李四  |

+-----+-------

-- 16.查询各个课程及相应的选修人数

select course.cname,count(*) from score left join course on score.course_id = course.cid group by course_id;

+-------+----------+

| cname | count(*) |

+-------+----------+

| 生物  |        1 |

| 体育  |        2 |

+-------+----------+

-- 17.查询“2”课程分数小于60，按分数降序排列的同学学号

select student_id from score where course_id=2 and number<60 order by number desc;

+------------+

| student_id |

+------------+

|          1 |

+------------+

-- 18.删除学号为“2”的同学的“1”课程的成绩

delete from score where course_id=1 and student_id=2;

巴特西

day35作业

最新文章

热门文章