mysql周末测试试题与答案

一、设定当前是教学管理系统中的选修课功能表：
学生表（主键，姓名，学号，性别，年龄，所属系别。。。。。。）
课程表
学生选修课关系表--成绩表（成绩分数列的值为null时表示缺考）

要求每个表都有主键并且自动递增，其余字段自己考虑，合理定义列的名称、数据类型及约束。
完成以下题目：
CREATE TABLE student(
Sid INT PRIMARY KEY AUTO_INCREMENT,
Sno INT ,
sname VARCHAR(10),
gender CHAR(2),
class VARCHAR(10)
)

CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cno INT UNIQUE,
cname VARCHAR(20)
)

CREATE TABLE elective(
Eid INT PRIMARY KEY AUTO_INCREMENT,
Sno INT ,
Cno INT
)

CREATE TABLE scores(
Sc_id INT PRIMARY KEY AUTO_INCREMENT,
Sno INT ,
Cno INT ,
Score INT
)

INSERT INTO student VALUES(NULL,1001,'张1','男','计算机',19);
INSERT INTO student VALUES(NULL,1002,'张2','男','计算机',18);
INSERT INTO student VALUES(NULL,1003,'张3','女','计算机',18);
INSERT INTO student VALUES(NULL,1004,'张4','女','数学',19);
INSERT INTO student VALUES(NULL,1005,'张5','女','数学',24);
INSERT INTO student VALUES(NULL,1006,'张6','男','数学',24);
INSERT INTO student VALUES(NULL,1007,'上官1','男','数学',28);
INSERT INTO student VALUES(NULL,1008,'上官2','女','数学',18);
INSERT INTO student VALUES(NULL,1009,'上官3','女','计算机',18);
INSERT INTO student VALUES(NULL,1010,'刘1','女','数学',18);
INSERT INTO student VALUES(NULL,1011,'刘2','男','计算机',31);

INSERT INTO course VALUES (NULL,9001,'汽车概论');
INSERT INTO course VALUES (NULL,9002,'广告设计');
INSERT INTO course VALUES (NULL,9003,'市场营销');
INSERT INTO course VALUES (NULL,9004,'ui设计');
INSERT INTO course VALUES (NULL,9005,'数据库');

INSERT INTO elective VALUES (NULL,1001,9001);
INSERT INTO elective VALUES (NULL,1001,9002);
INSERT INTO elective VALUES (NULL,1001,9003);
INSERT INTO elective VALUES (NULL,1001,9004);
INSERT INTO elective VALUES (NULL,1002,9001);
INSERT INTO elective VALUES (NULL,1002,9005);
INSERT INTO elective VALUES (NULL,1003,9004);
INSERT INTO elective VALUES (NULL,1003,9003);
INSERT INTO elective VALUES (NULL,1003,9002);
INSERT INTO elective VALUES (NULL,1004,9002);
INSERT INTO elective VALUES (NULL,1004,9004);
INSERT INTO elective VALUES (NULL,1005,9001);
INSERT INTO elective VALUES (NULL,1006,9003);
INSERT INTO elective VALUES (NULL,1007,9004);
INSERT INTO elective VALUES (NULL,1008,9001);
INSERT INTO elective VALUES (NULL,1009,9002);
INSERT INTO elective VALUES (NULL,1010,9003);
INSERT INTO elective VALUES (NULL,1010,9004);
INSERT INTO elective VALUES (NULL,1011,9001);
INSERT INTO elective VALUES (NULL,1011,9002);

INSERT INTO scores VALUES (NULL,1001,9001,80);
INSERT INTO scores VALUES (NULL,1001,9002,90);
INSERT INTO scores VALUES (NULL,1001,9003,70);
INSERT INTO scores VALUES (NULL,1001,9004,50);
INSERT INTO scores VALUES (

NULL,1002,9001,90);
INSERT INTO scores VALUES (NULL,1002,9005,NULL);
INSERT INTO scores VALUES (NULL,1003,9004,NULL);
INSERT INTO scores VALUES (NULL,1003,9003,90);
INSERT INTO scores VALUES (NULL,1003,9002,70);
INSERT INTO scores VALUES (NULL,1004,9002,59);
INSERT INTO scores VALUES (NULL,1004,9004,80);
INSERT INTO scores VALUES (NULL,1005,9001,58)
INSERT INTO scores VALUES (NULL,1006,9003,93);
INSERT INTO scores VALUES (NULL,1007,9004,58);
INSERT INTO scores VALUES (NULL,1008,9001,NULL);
INSERT INTO scores VALUES (NULL,1009,9002,90);
INSERT INTO scores VALUES (NULL,1010,9003,59);
INSERT INTO scores VALUES (NULL,1010,9004,59);
INSERT INTO scores VALUES (NULL,1011,9001,NULL);
INSERT INTO scores VALUES (NULL,1011,9002,70);
（1）检索计算机系的全体学生的学号，姓名和性别；
SELECT * FROM student WHERE class='计算机';
（2）检索学习汽车概论
课程的学生学号与姓名；

SELECT sname , sno
FROM student
WHERE sno= ANY(SELECT sno FROM elective WHERE cno = (SELECT cno FROM course WHERE cname='汽车概论'));

（3）检索选修课程名为“广告设计”的学生学号与姓名；
SELECT sname , sno
FROM student
WHERE sno= ANY(SELECT sno FROM elective WHERE cno = (SELECT cno FROM course WHERE cname='广告设计'));

（4）检索选修课程号为9002或9004的学生信息；
SELECT a.sname , b.sno FROM
(SELECT sname , sno
FROM student
WHERE sno= ANY(SELECT sno FROM elective WHERE cno IN(9002,9004)))a
JOIN
(SELECT sno, COUNT(*) FROM elective GROUP BY sno HAVING COUNT(*)=2)b
ON a.sno=b.sno;
（5）检索至少选修课程号为9002和9004的学生信息；
SELECT sname , sno
FROM student
WHERE sno= ANY(SELECT sno FROM elective WHERE cno IN(9002,9004))
（6）检索不学汽车概论课的学生姓名和年龄；SELECT sname ,age FROM student
WHERE sno IN
(SELECT sno FROM elective WHERE cno<>
(SELECT cno FROM course WHERE cname = '汽车概论'));
（7）检索学习全部课程的学生姓名；
SELECT sname FROM student WHERE sno=(
SELECT sno FROM elective WHERE cno = ALL(
SELECT cno FROM course))
（8）查询所学课程包含学生张3所学课程的学生信息
SELECT sname , sno FROM student WHERE sno IN(
SELECT DISTINCT sno FROM elective WHERE cno IN(
SELECT cno FROM elective WHERE sno=(
SELECT sno FROM student WHERE sname = '张3')))AND sname<>'张3';
（9）查所有年龄在20岁以下的学生姓名及年龄。
SELECT * FROM student WHERE age <20;

（10）查考试成绩有不及格的学生信息
SELECT * FROM student WHERE sno IN(
SELECT DISTINCT sno FROM scores WHERE score<60)
（11）查所年龄在20至23岁之间的学生姓名、系别及年龄。
SELECT sname , class,age FROM student WHERE age BETWEEN 20 AND 23;
（12）查计算机系、数学系、信息系的学生姓名、性别。
SELECT* FROM student WHERE class IN('计算机','数学','信息');
（13）查既不是计算机系、又不是数学系

、又不是信息系的学生姓名性别
SELECT* FROM student WHERE class NOT IN('计算机','数学','信息');
（14）查所有姓“刘”的学生的姓名、学号和性别。
SELECT* FROM student WHERE sname LIKE '刘%';
`（15）查姓“上官”且全名为3个字的学生姓名。
SELECT sname FROM student WHERE sname LIKE '上官_';
（16）查所有不姓“张”的学生的姓名。
SELECT* FROM student WHERE sname NOT LIKE '张%';
（17）查汽车概论课程的课程号。
SELECT cno FROM course WHERE cname='汽车概论';
（18）查缺考的学生的学号和课程号。
SELECT sno, cno FROM scores WHERE score IS null
（19）查年龄为空值的学生的学号和姓名。
SELECT sno , sname FROM student WHERE age IS NULL;
（20）查计算机系20岁以下的学生的学号和姓名。
SELECT sno ,sname FROM student WHERE age < 20 AND class = '计算机';
（22）查询选修了“广告设计”课程的学生的学号和成绩，其结果按分数的降序排列。
SELECT sno , score FROM scores WHERE cno =(
SELECT cno FROM course WHERE cname ='广告设计') ORDER BY score DESC;

（23）查询全体学生的情况，同一个系的学生排在一起，对同一系中的学生按年龄降序排列。
SELECT * FROM student ORDER BY class ,age DESC;
（24）查询学生总人数。
SELECT COUNT(*) FROM student;
（25）查询选修了课程的学生人数。
SELECT COUNT(DISTINCT sno) FROM elective;
（26）计算选修了“市场营销”课程的学生平均成绩。
SELECT AVG(score)FROM scores WHERE cno=(
SELECT cno FROM course WHERE cname = '市场营销')
（27）查询学习“广告设计”课程的学生最高分数。
SELECT MAX(score)FROM scores WHERE cno=(
SELECT cno FROM course WHERE cname = '广告设计')

（28）查询各个课程名称与相应的选课人数。
SELECT cname,人数 FROM
(SELECT cname ,cno FROM course )a
JOIN
(SELECT cno , COUNT(sno) 人数 FROM elective GROUP BY cno)b
ON o=o;
（29）查询计算机系选修了3门以上课程的学生的学号和姓名。
SELECT a.sno,b.sname FROM
(SELECT sno , COUNT(cno)FROM elective GROUP BY sno HAVING COUNT(cno)>3)a
JOIN
(SELECT sname , class , sno FROM student WHERE class='计算机')b
ON a.sno = b.sno;
（30）查询学生年龄及对应的学生人数。要求查询结果按人数升序排列，人数相同按年龄降序排列。
SELECT age , COUNT(*)人数 FROM student GROUP BY age ORDER BY 人数 DESC;
（31）查询每个学生及其选修课程的情况。
SELECT * FROM
student JOIN elective JOIN course JOIN scores
ON student.sno=elective.sno AND o=o AND o=o AND scores.sno=student.sno

（32）查询选修了“汽车概论”课程且成绩在90分及以上的所有学生。
SELECT * FROM student WHERE sno=(
SELECT sno FROM scores WHERE score >=90 AND cno=
(SELECT cno FROM course WHERE cname = '汽车概论'));

（33）查询每个学生选修的课程名及其成绩。
SELECT student.sname , ame, scores.score FROM
student JOIN elective JOIN course JOIN scores
ON student.sno=elective.sno AND o=o AND o=o AND scores.sno=student.sno

`（34）查询选修了“UI设计”课程的学生姓名。
SELECT sname FROM student WHERE sno IN(
SELECT sno FROM elective WHERE cno=(
SELECT cno FROM course WHERE cname ='ui设计'));
`（35）查询所有未选修“UI设计”课程的学生姓名。
SELECT sname FROM student WHERE sno NOT IN(
SELECT sno FROM elective WHERE cno =(
SELECT cno FROM course WHERE cname ='ui设计'));
`（36）查询与“张三”在同一个系学习的学生学号、姓名和系别。
SELECT sno,sname , class FROM student WHERE class=(
SELECT class FROM student WHERE sname='张3');
（37）查询选修课程名为“数据库”的学生学号和姓名。
SELECT sno , sname FROM student WHERE sno=(
SELECT sno FROM elective WHERE cno=(
SELECT cno FROM course WHERE cname = '数据库'));
（38）查询选修了全部课程的学生姓名。
SELECT sname FROM student WHERE sno IN(
SELECT sno FROM elective GROUP BY sno HAVING COUNT(cno)=(
SELECT COUNT(*) FROM course))