练习sql

题目来源：

https://www.cnblogs.com/zqm1/p/6559757.html

一、 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

1、建表语句&插入数据语句：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

  `name` varchar(20) DEFAULT NULL,

  `kecheng` varchar(20) DEFAULT NULL,

  `fenshu` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('张三', '语文', '81');

INSERT INTO `student` VALUES ('张三', '数学', '75');

INSERT INTO `student` VALUES ('李四', '语文', '76');

INSERT INTO `student` VALUES ('李四', '数学', '90');

INSERT INTO `student` VALUES ('王五', '语文', '81');

INSERT INTO `student` VALUES ('王五', '数学', '90');

INSERT INTO `student` VALUES ('王五', '英语', '100');

2、参考答案

select name from student GROUP BY kecheng HAVING min(fenshu)>80

select DISTINCT name from student where name not in (select DISTINCT name from student where fenshu <= 80)

二、删除除了自动编号不同, 其他都相同的学生冗余信息

1、建表语句&插入数据语句：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

  `id` varchar(20) NOT NULL,

  `name` varchar(20) DEFAULT NULL,

  `kecheng` varchar(20) DEFAULT NULL,

  `fenshu` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('25001', '张三', '语文', '96');

INSERT INTO `student` VALUES ('25002', '张三', '语文', '96');

INSERT INTO `student` VALUES ('25003', '李四', '数学', '95');

2、参考答案

DELETE FROM student WHERE id NOT IN ( SELECT id FROM (SELECT id FROM student s GROUP BY s. NAME, s.kecheng, s.fenshu) a)

三、 面试题：怎么把这样一个表儿

year month amount

1991 1 1.1

1991 2 1.2

1991 3 1.3

1991 4 1.4

1992 1 2.1

1992 2 2.2

1992 3 2.3

1992 4 2.4

查成这样一个结果

year m1 m2 m3 m4

1991 1.1 1.2 1.3 1.4

1992 2.1 2.2 2.3 2.4

1、建表语句&插入数据语句：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for timetable

-- ----------------------------

DROP TABLE IF EXISTS `timetable`;

CREATE TABLE `timetable` (

  `year` varchar(255) DEFAULT NULL,

  `month` int(11) DEFAULT NULL,

  `amount` double DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of timetable

-- ----------------------------

INSERT INTO `timetable` VALUES ('1991', '1', '1.1');

INSERT INTO `timetable` VALUES ('1991', '2', '1.2');

INSERT INTO `timetable` VALUES ('1991', '3', '1.3');

INSERT INTO `timetable` VALUES ('1991', '4', '1.4');

INSERT INTO `timetable` VALUES ('1992', '1', '2.1');

INSERT INTO `timetable` VALUES ('1992', '2', '2.2');

INSERT INTO `timetable` VALUES ('1992', '3', '2.3');

INSERT INTO `timetable` VALUES ('1992', '4', '2.4');

2、参考答案

SELECT

year,

(select amount from timetable a where a.`year` = timetable.`year` and a.`month`=1) m1,

(select amount from timetable a where a.`year` = timetable.`year` and a.`month`=2) m2,

(select amount from timetable a where a.`year` = timetable.`year` and a.`month`=3) m3,

(select amount from timetable a where a.`year` = timetable.`year` and a.`month`=4) m4

FROM

timetable

GROUP BY

YEAR