sql查询面试题

有表结构如下：

1. CREATE TABLE students
2. (name varchar(20),/*姓名*/
3. age int,/*年龄*/
4. sex varchar(10),/*性别*/
5. classid int);/*与classes表的classid对应*/
7. CREATE TABLE classes
8. (classid int,/*班级ID，与students表中的classid对应*/
9. name varchar(20),/*班级名称*/
10. teacher varchar(20));/*班级老师*/
12. INSERT INTO students (name, age, sex, classid) VALUES ('刘德华', 25, '男', 1);
13. INSERT INTO students (name, age, sex, classid) VALUES ('张惠妹', 30, '女', 1);
14. INSERT INTO students (name, age, sex, classid) VALUES ('马艳丽', 24, '女', 2);
15. INSERT INTO students (name, age, sex, classid) VALUES ('苍井空', 26, '女', 2);
16. INSERT INTO students (name, age, sex, classid) VALUES ('萧敬腾', 21, '男', 1);
17. INSERT INTO students (name, age, sex, classid) VALUES ('罗志祥', 22, '男', 3);
18. INSERT INTO students (name, age, sex, classid) VALUES ('饭岛爱', 23, '女', 3);
19. INSERT INTO students (name, age, sex, classid) VALUES ('周润发', 25, '男', 3);
20. INSERT INTO students (name, age, sex, classid) VALUES ('章子怡', 20, '女', 2);
21. INSERT INTO students (name, age, sex, classid) VALUES ('陈冠希', 22, '男', 1);
23. INSERT INTO classes (classid, name, teacher) VALUES (1, 'C++班', '王老师');
24. INSERT INTO classes (classid, name, teacher) VALUES (2, 'IOS班', '李老师');
25. INSERT INTO classes (classid, name, teacher) VALUES (3, 'PHP班', '张老师');

1、用SELECT语句，查询出大于平均年龄的男同学的姓名，年龄，所在班级编号，老师名称。

1. select a.name, a.age, a.classid, b.teacher from students a, classes b
2. where a.classid = b.classid and a.sex = '男' and a.age > (select avg(age) from students);

2、用SELECT语句，查询人数最多班的所有女同学的姓名，年龄，所在班级编号，老师名称。

1. select a.name, a.age, a.classid, b.teacher from students a, classes b
2. where a.classid = b.classid and a.sex = '女' and a.classid = (select classid from
3. (select count(*) count, classid from students group by classid) c order by count desc limit 0,1);

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/ZhangJinkun/p/4570554.html