子查询的练习及答案

创建一个学生表（编号，姓名，年龄，生日，性别，住址，专业,班级编号）
设置编号为主键自增，
设置姓名可以为null并且拥有默认值‘无名’
性别默认为‘女’
住址设置唯一约束
专业，生日，年龄设置not null
班级编号设置为外键

CREATE TABLE STU(
num INT PRIMARY KEY,
NAME VARCHAR(40) NULL DEFAULT ‘无名’,
age INT NOT NULL,
birthday VARCHAR(40) NOT NULL,
sex VARCHAR(20) CHECK(sex IN(‘男’,‘女’)),
address VARCHAR(40) UNIQUE,
major VARCHAR(40) NOT NULL,
classnum INT,
CONSTRAINT classnum FOREIGN KEY(classnum) REFERENCES class(classnum)
);

INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (1, ‘q’, 15, ‘01-01-1991’, ‘男’, ‘北大街’, ‘信息技术与技术’, 1);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (2, ‘w’, 17, ‘05-05-1993’, ‘女’, ‘东大街’, ‘法学’, 1);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (3, ‘e’, 20, ‘13-04-1992’, ‘男’, ‘西大街’, ‘信息技术与技术’, 1);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (4, ‘r’, 21, ‘05-09-1990’, ‘女’, ‘北大小街’, ‘管理学’, 1);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (5, ‘t’, 22, ‘05-08-1996’, ‘男’, ‘南大街’, ‘金融学’, 1);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (6, ‘y三’, 19, ‘21-10-1994’, ‘男’, ‘前大街’, ‘贸易经济学’, 1);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (7, ‘i’, 20, ‘17-08-1995’, ‘男’, ‘后大街’, ‘会计’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (8, ‘u’, 17, ‘10-05-1997’, ‘女’, ‘下大街’, ‘计算机技术’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (9, ‘o’, 18, ‘26-04-2001’, ‘男’, ‘上大街’, ‘java工程师’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (10, ‘a’, 22, ‘04-03-1993’, ‘女’, ‘右大街’, ‘数据库’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (11, ‘p’, 24, ‘29-04-1998’, ‘女’, ‘左大街’, ‘体育’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (12, ‘s’, 18, ‘06-02-1993’, ‘女’, ‘西大小街’, ‘贸易经济学’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (13, ‘无名’, 20, ‘05-02-1994’, ‘男’, ‘东大小街’, ‘管理学’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (14, ‘f’, 23, ‘04-03-1997’, ‘男’, ‘南大小街’, ‘金融学’, 2);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (15, ‘g’, 21, ‘07-03-1998’, ‘女’, ‘上大小街’, ‘信息技术与技术’, 3);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (16, ‘j’, 19, ‘09-08-1999’, ‘男’, ‘夏大街’, ‘法学’, 3);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (17, ‘h’, 37, ‘05-07-2000’, ‘女’, ‘龙大街’, ‘数据库’, 3);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (18, ‘k’, 16, ‘05-03-2001’, ‘女’, ‘田大街’, ‘体育’, 3);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (19, ‘l’, 21, ‘03-01-2000’, ‘女’, ‘新大街’, ‘计算机技术’, 3);
INSERT INTO STU (num, NAME, age, birthday, sex, address, major, classnum)
VALUES (20, ‘z’, 20, ‘06-03-1994’, ‘男’, ‘旧大街’, ‘会计’, 3);
COMMIT;

创建一个班级表（班级编号，班级名称）
设置班级编号为主键自增
班级名称设置为not null ，唯一约束

CREATE TABLE CLASS(
classnum INT PRIMARY KEY,
classname VARCHAR(40) NOT NULL UNIQUE
);

1.找出蜗牛班中的哪些学生的生日在1990年到1995年之间

SELECT NAME FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘蜗牛班’) AND (birthday>‘1990’ AND birthday<‘1995’);

2. 使用子查询查询出在火箭班的学生；

SELECT NAME FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘火箭班’ );

3. 使用子查询找出蜗牛班的学生中的男女各有多少人

SELECT sex,COUNT(*) FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘蜗牛班’) GROUP BY sex;

4. 查询男学生中的最高年龄的学生在哪个班级

SELECT classname FROM class WHERE classnum=(SELECT classnum FROM stu WHERE age=(SELECT MAX(age) FROM stu WHERE sex=‘男’) AND sex=‘男’)

5. 查询女学生在普通班的有多少人

SELECT COUNT(num) FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘蜗牛班’) AND sex=‘女’;

6. 把年龄是21岁的女学生转到普通班

UPDATE stu SET classnum=(SELECT classnum FROM class WHERE classname=‘蜗牛班’) WHERE age=21 AND sex=‘女’;

7. 查询在蜗牛班的男学生的平均年龄

SELECT AVG(age) FROM stu WHERE sex=‘男’ AND classnum=(SELECT classnum FROM class WHERE classname=‘蜗牛班’);

8. 给住在北大街的普通班学生年龄增加一岁

UPDATE stu SET age=age+1 WHERE address=‘北大街’ AND classnum=(SELECT classnum FROM class WHERE classname=‘普通班’);

9. 蜗牛班的有多少人

SELECT COUNT(num) FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘蜗牛班’);

10.找出火箭班生日在1991-1-1在1993-12-12的女学生有多少人

SELECT COUNT(num) FROM stu WHERE sex=‘女’ AND classnum=(SELECT classnum FROM class WHERE classname=‘火箭班’);

11.查询学生在电子信息科学与技术的女学生有多少人

SELECT COUNT(num) FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘电子信息科学与技术’) AND sex=‘女’;

12.查询年龄小于20，或大于30还是在蜗牛班的男女学生各有多少人

SELECT sex,COUNT(num) FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘蜗牛班’) AND (age<20 OR age>30) GROUP BY sex;

13.法学专业的学生在哪些班级（按照升序排列）

SELECT classname FROM class WHERE classnum IN (SELECT classnum FROM stu WHERE major=‘法学’);

14.查询各班有多少人

SELECT classnum,COUNT(num) FROM stu GROUP BY classnum;

15.查询火箭班的平均年龄

SELECT AVG(age) FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘火箭班’);

16.查询在普通班的学生总年龄

SELECT SUM(age) FROM stu WHERE classnum=(SELECT classnum FROM class WHERE classname=‘普通班’);

17.找到贸易经济学中年龄最大的学生在哪个班级

SELECT classname FROM class WHERE classnum=(SELECT classnum FROM stu WHERE age=(SELECT MAX(age) FROM stu WHERE major=‘贸易经济学’) AND major=‘贸易经济学’);

18.查询生日在1991-1-1的那一天的有多少人

select count(num) from stu where birthday=‘1991-1-1’;

19.查询哪个班级的男生最多

SELECT classname FROM class WHERE classnum=(SELECT test.classnum FROM(
(SELECT stu.classnum,COUNT(num)AS s FROM stu WHERE sex=‘男’ GROUP BY classnum) test)
WHERE s=(SELECT MAX(s) FROM (
SELECT classnum,COUNT(num)AS s FROM stu WHERE sex=‘男’ GROUP BY classnum) test));

20.查询普通班的女学生的平均年龄

SELECT AVG(age) FROM stu WHERE sex=‘女’ AND classnum=( SELECT classnum FROM class WHERE classname=‘普通班’);

21.找出每个专业的学生班级

SELECT mc.major,class.classname FROM class INNER JOIN (SELECT major,classnum FROM stu GROUP BY major) AS mc ON mc.classnum=class.classnum;

22.查看一下哪个班级人数最多

SELECT classname FROM class WHERE classnum IN (SELECT cc.classnum FROM (SELECT COUNT(num) AS cnum,classnum FROM stu GROUP BY classnum) AS cc WHERE cc.cnum IN (SELECT MAX(cnum) FROM (SELECT COUNT(num) AS cnum,classnum FROM stu GROUP BY classnum) AS c));

23.如果年龄有未成年的人将其转至幼儿班

INSERT INTO class VALUES(4,‘幼儿班’);
UPDATE stu SET classnum=(SELECT classnum FROM class WHERE classname=‘幼儿班’) WHERE age<18;

25.将学生中性别为女的并且年龄大于28岁的人转至成人班

UPDATE stu SET classnum=(SELECT classnum FROM class WHERE classname=‘成人班’) WHERE age>28 AND sex=‘女’;

26.找出普通班中的哪些学生成年了

SELECT NAME FROM stu WHERE classnum =(SELECT classnum FROM class WHERE classname=‘普通班’);

28.找出每个专业的学生最大年纪（如果有相同年龄的同时找出来，并且按照编号倒叙排列）

SELECT major,MAX(age) FROM stu GROUP BY major ORDER BY num ;

29.如果火箭班学生年龄超过30岁则将其删除学籍

DELETE FROM stu WHERE age>30 AND classnum=(SELECT classnum FROM class WHERE classname=‘火箭班’);

30.将姓名中包含‘三’的学生或者叫‘无名’的学生转到‘未知班级’

INSERT INTO class VALUES(6,‘未知班级’);
UPDATE stu SET classnum=(SELECT classnum FROM class WHERE classname=‘未知班级’) WHERE NAME LIKE ‘%三%’ OR NAME=‘无名’;