oracle关联查询题目练习

数据库中有3个表 teacher 表，student表，tea_stu关系表。
teacher 表 teaID name age
student 表 stuID name age [size=medium][/size]
teacher_student表 teaID stuID

要求用一条sql查询出这样的结果

1.显示的字段要有老师name, age 每个老师所带的学生人数

2 只列出老师age为40以下学生age为12以上的记录

第一题的sql：

select teacher.name,teacher.age,t2.c from (select t.teaID,count(*) c from

(select tea_stu.* from tea_stu,teacher where tea_stu.teaID = teacher.teaID ) as t,

student where t.stuid=student.stuid

第二题的sql：

select teacher.name,t2.c from (select t.teaID,count(*) c from

(select tea_stu.* from tea_stu,teacher where tea_stu.teaID = teacher.teaID and

teacher.age<40) as t,student where t.stuid=student.stuid and student.age>12

group by t.teaID) as t2,teacher where teacher.teaID=t2.teaID;

也可以写成下面这样：

select teacher.teaID, teacher.name, t1.total from teacher,

(select teaID,count(tea_stu.stuID) total from tea_stu, student

where tea_stu.stuID = student.stuID and student.age>12

group by teaID ) as t1

where teacher.teaID = t1.teaID and teacher.age<40 ;