sql查询面试题

有表结构如下:

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





posted @ 2015-06-11 23:45 外禅内定,程序人生 阅读(...) 评论(...) 编辑 收藏