MySQL数据库练习

题目

1.求数学系学生的学号和姓名

SELECT Sno,Sname FROM student WHERE sdept='数学';

2.求选修了C1课程的学生学号

SELECT Sno FROM sc WHERE Cno = 'C1';

3.求选修C1课程的学生学号和成绩,结果按成绩降序排列,如成绩同按学号升序排列

SELECT Sno,Grade FROM sc WHERE Cno = 'C1' ORDER BY Grade DESC;

4.求选修课程C1成绩在80~90之间的学生学号和成绩,并将成绩乘以0.8输出

SELECT Sno,Grade*0.8 FROM (SELECT Sno,Grade FROM sc WHERE Cno = 'C1' AND Grade BETWEEN 80 AND 90 ) AS tmp ORDER BY Grade DESC;

5.求数学或计算机系姓张的学生的信息

SELECT * FROM (SELECT * FROM student WHERE sdept IN ('数学','计算机')) AS tmp WHERE Sname REGEXP '^张';

6.求缺少了成绩的学生的学号和课程号

(SELECT sc.Sno,course.Cno FROM course JOIN sc WHERE course.Cno!=sc.Cno) UNION (SELECT Sno,Cno FROM (SELECT DISTINCT Sno FROM (SELECT * FROM student except SELECT DISTINCT student.Sno,Sname,sex,age,sdept FROM student INNER JOIN sc ON student.Sno=sc.Sno)AS tmp)AS temp JOIN course) ORDER BY Sno;

7.查询每个学生的情况以及他(她)所选修的课程

SELECT * FROM (SELECT Sno,Sname,sex,age,sdept,Cno,Grade FROM student INNER JOIN sc USING(Sno))AS tmp INNER JOIN course USING(Cno);

8.求学生的学号、姓名、选修的课程及成绩

SELECT Sno,Sname,Cno,Cname,Grade FROM (SELECT Sno,Sname,sex,age,sdept,Cno,Grade FROM student INNER JOIN sc USING(Sno))AS tmp INNER JOIN course USING(Cno);

9.求选修课程C1且成绩在？平均分？分以上的学生学号、姓名及成绩

SELECT Sno,Sname,Cno,Grade FROM student INNER JOIN sc USING(Sno) WHERE Cno='C1' AND Grade>(SELECT AVG(Grade)AS avg_val FROM sc WHERE Cno='C1');

10.统计有学生选修的课程门数。

SELECT COUNT(DISTINCT Cno)AS `计数` FROM sc ;

11.求选修C4课程的学生的平均年龄

SELECT *,AVG(age)AS `平均年龄` FROM student JOIN sc USING(Sno) WHERE Cno='C4';

12.求学分为’3’的每门课程的学生平均成绩

SELECT Cno,AVG(Grade) FROM sc JOIN (SELECT Cno FROM course WHERE ct='3')tmp USING(Cno) GROUP BY Cno;

13.统计每门课程的学生选修人数，超过3人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT Sno,COUNT(*)AS COUNT FROM sc GROUP BY Sno HAVING COUNT>=3 ORDER BY COUNT ASC;

14.检索学号比王林同学大，而年龄比他小的学生姓名

SELECT Sname FROM student WHERE Sno>(SELECT Sno FROM student WHERE Sname='王林') AND age<(SELECT age FROM student WHERE Sname='王林');

15.检索姓名以王打头的所有学生的姓名和年龄

SELECT Sname,age FROM student WHERE Sname REGEXP '^王';

16.在SC中检索成绩为空值的学生学号和课程号

SELECT Sno,Cno FROM sc WHERE Grade IS NULL ;

17.求年龄大于女同学平均年龄的男学生姓名和年龄

SELECT Sname,age FROM student WHERE sex='男' HAVING age>(SELECT AVG(age)AS avg_age FROM student WHERE sex ='女');

18.求年龄大于所有女同学年龄的男学生姓名和年龄

SELECT Sname,age FROM student WHERE sex='男' HAVING age> (SELECT MAX(age)女生最大年龄 FROM student WHERE sex='女');

19.检索所有比王林年龄大的学生姓名、年龄和性别

SELECT Sname,age,sex FROM student HAVING age> (SELECT age FROM student WHERE Sname='王林');

20.检索选修课程C2的学生中成绩最高的学生的学号

SELECT Sno FROM sc WHERE Cno='C2' ORDER BY Grade DESC LIMIT 1;

21.检索学生姓名及其所选修课程的课程号和成绩

SELECT Sname,Cno,Grade FROM student JOIN sc USING(Sno);

22.检索选修3门以上课程的学生总成绩(不统计不及格的课程)，并要求按总成绩的降序排列出来

SELECT Sno,Sname,SUM(Grade)平均成绩 FROM student JOIN sc USING(Sno) WHERE Grade>=60 GROUP BY Sno HAVING COUNT(*)>=3 ORDER BY 平均成绩 DESC;

建库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `Cno` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cdept` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Tname` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ct` int NULL DEFAULT NULL,
  `CPNO` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('C1', 'C语言', '计算机', '汪小寒', 4, '计算机基础');
INSERT INTO `course` VALUES ('C2', 'R软件', '自动化', '周在莹', 3, '线性代数');
INSERT INTO `course` VALUES ('C3', '精算学', '统计', '刘晓', 3, '金融数学');
INSERT INTO `course` VALUES ('C4', '计算机算法', '计算机', '李杰', 2, 'C语言');
INSERT INTO `course` VALUES ('C5', '数据库应用', '自动化', '周有顺', 4, 'R软件');
INSERT INTO `course` VALUES ('C6', '金融数学', '统计', '黄旭东', 3, '数学分析');
INSERT INTO `course` VALUES ('C7', '时间序列', '统计', '何道江', 4, '精算学');
INSERT INTO `course` VALUES ('C8', '数学分析', '数学', '周文', 4, '高等数学');
INSERT INTO `course` VALUES ('C9', '线性代数', '数学', '储茂权', 2, '高等数学');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `Sno` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Cno` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `GRADE` int NULL DEFAULT NULL,
  PRIMARY KEY (`Sno`, `Cno`) USING BTREE,
  INDEX `Cno`(`Cno`) USING BTREE,
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('S1', 'C1', 90);
INSERT INTO `sc` VALUES ('S1', 'C2', 85);
INSERT INTO `sc` VALUES ('S2', 'C1', 84);
INSERT INTO `sc` VALUES ('S2', 'C2', 94);
INSERT INTO `sc` VALUES ('S2', 'C3', 83);
INSERT INTO `sc` VALUES ('S3', 'C1', 73);
INSERT INTO `sc` VALUES ('S3', 'C4', 88);
INSERT INTO `sc` VALUES ('S3', 'C5', 85);
INSERT INTO `sc` VALUES ('S3', 'C7', 59);
INSERT INTO `sc` VALUES ('S4', 'C2', 65);
INSERT INTO `sc` VALUES ('S4', 'C5', 90);
INSERT INTO `sc` VALUES ('S4', 'C6', 79);
INSERT INTO `sc` VALUES ('S5', 'C1', 84);
INSERT INTO `sc` VALUES ('S5', 'C2', 89);
INSERT INTO `sc` VALUES ('S6', 'C1', 60);
INSERT INTO `sc` VALUES ('S6', 'C5', 55);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `Sno` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Sname` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '男',
  `age` int NULL DEFAULT NULL,
  `sdept` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('S1', '赵四', '男', 20, '计算机');
INSERT INTO `student` VALUES ('S2', '王林', '女', 18, '计算机');
INSERT INTO `student` VALUES ('S3', '陈高', '女', 15, '自动化');
INSERT INTO `student` VALUES ('S4', '张杰', '男', 17, '自动化');
INSERT INTO `student` VALUES ('S5', '吴小丽', '女', 19, '统计');
INSERT INTO `student` VALUES ('S6', '张敏敏', '女', 20, '计算机');
INSERT INTO `student` VALUES ('S7', '郑冬', '男', 19, '数学');
INSERT INTO `student` VALUES ('S8', '朱雨', '男', 20, '数学');

SET FOREIGN_KEY_CHECKS = 1;