willcool4ever • MYSQL两道试题

有表结构如下：
CREATE TABLE students
(name varchar(20),
age int,
sex varchar(10),
classid int);

CREATE TABLE classes
(classid int,/*班级ID，与students表中的classid对应*/
name varchar(20),/*班级名称*/
teacher varchar(20));/*班级老师*/

INSERT INTO students (name, age, sex, classid) VALUES ('刘华', 25, '男', 1);
INSERT INTO students (name, age, sex, classid) VALUES ('张惠', 30, '女', 1);
INSERT INTO students (name, age, sex, classid) VALUES ('马艳丽', 24, '女', 2);
INSERT INTO students (name, age, sex, classid) VALUES ('苍海', 26, '女', 2);
INSERT INTO students (name, age, sex, classid) VALUES ('张敬', 21, '男', 1);
INSERT INTO students (name, age, sex, classid) VALUES ('志祥', 22, '男', 3);
INSERT INTO students (name, age, sex, classid) VALUES ('饭岛', 23, '女', 3);
INSERT INTO students (name, age, sex, classid) VALUES ('周发', 25, '男', 3);
INSERT INTO students (name, age, sex, classid) VALUES ('刘怡', 20, '女', 2);

INSERT INTO classes (classid, name, teacher) VALUES (1, 'C++班', '王老师');
INSERT INTO classes (classid, name, teacher) VALUES (2, 'IOS班', '李老师');
INSERT INTO classes (classid, name, teacher) VALUES (3, 'PHP班', '张老师');

1、用SELECT语句，查询出大于平均年龄的男同学的姓名，年龄，所在班级编号，老师名称。
select  st.name,st.age,st.classid,cl.teacher    from students st,classes cl
where st.classid = cl.classid and st.sex = '男' and st.age > (select avg(age) from students);

2、用SELECT语句，查询人数最多班的所有女同学的姓名，年龄，所在班级编号，老师名称。
select  st.name,st.age,st.classid,cl.teacher    from students st,classes cl where st.classid = cl.classid and st.sex = '女' and st.classid =
( select  b.classid from  (select count(*)  Count, a.classid from students  a group by a.classid order by  Count desc ) b limit 0,1 );