MySQL 练习题4

1.表结构如下:

 

#课程表
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');

#成绩表
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `num` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');

#学生表
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(50) DEFAULT NULL,
  `s_age` int(10) DEFAULT NULL,
  `s_sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');

#老师表
CREATE TABLE `teacher` (
  `t_id` int(10) NOT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');

2.查询:

1. 查询学习课程"python"比课程 "java" 成绩高的学生的学号;
2. 查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　　
3. 查询所有同学的姓名、选课数、总成绩；
4. 查询所有的课程的名称以及对应的任课老师姓名;
5. 查询没学过“alex”老师课的同学的姓名;
6. 查询学过'python'并且也学过编号'java'课程的同学的姓名;
7. 查询学过“alex”老师所教的全部课程的同学的姓名；
8. 查询挂科超过两门(包括两门)的学生姓名;
9. 查询有课程成绩小于60分的同学的姓名;
10. 查询选修了全部课程的学生姓名;
11. 查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；
12. 查询学过'貂蝉'同学全部课程 的其他同学姓名；
13. 查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；
14. 按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,课程数,平均分
15. 统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 
16. 查询每门课程被选修的次数
17. 查询出只选修了一门课程的学生的学号和姓名
18. 查询学生表中男生、女生各有多少人
19. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
20. 查询课程名称为“python”，且分数低于60的学生姓名和分数