mysql周末测试试题与答案

SELECT* FROM student WHERE class IN('计算机','数学','信息');
（13）查既不是计算机系、又不是数学系、又不是信息系的学生姓名性别
SELECT* FROM student WHERE class NOT IN(பைடு நூலகம்计算机','数学','信息');
（14）查所有姓“刘”的学生的姓名、学号和性别。
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<>
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)
(SELECT cno FROM course WHERE cname = '汽车概论'));
（7）检索学习全部课程的学生姓名；
SELECT sname FROM student WHERE sno=(
SELECT sno FROM elective WHERE cno = ALL(
SELECT cno FROM course))
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,
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 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);
（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
SELECT DISTINCT sno FROM scores WHERE score<60)
（11）查所年龄在20至23岁之间的学生姓名、系别及年龄。
SELECT sname , class,age FROM student WHERE age BETWEEN 20 AND 23;
（12）查计算机系、数学系、信息系的学生姓名、性别。
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);
SELECT* FROM student WHERE sname LIKE '刘%';
（3）检索选修课程名为“广告设计”的学生学号与姓名；
SELECT sname , sno
FROM student
WHERE sno= ANY(SELECT sno FROM elective WHERE cno = (SELECT cno FROM course WHERE cname='广告设计'));
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);
（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='汽车概论'));
INSERT INTO student VALUES(NULL,1010,'刘1','女','数学',18);
INSERT INTO student VALUES(NULL,1011,'刘2','男','计算机',31);
INSERT INTO course VALUES (NULL,9001,'汽车概论');
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(
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,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);
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,
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);
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);