mysql查询男女平均年龄_mysql查询练习（三）

31、查询成绩比该课程平均成绩低的同学的成绩表

mysql> select * from score a where degree < (select avg(degree) from score b where  a.cno= b.cno);

+-----+-------+--------+

| sno | cno   | degree |

+-----+-------+--------+

| 101 | 3-106 |     70 |

| 103 | 3-105 |     59 |

| 104 | 3-107 |     48 |

| 105 | 3-105 |     82 |

| 106 | 3-108 |     10 |

| 107 | 3-106 |     68 |

+-----+-------+--------+

6 rows in set (0.00 sec)

32.查询所有任课教师的Tname和Depart

mysql> select tname,depart from teacher where tno in (select tno from course);

+-------+-----------+

| tname | depart    |

+-------+-----------+

| b1    | computer  |

| b2    | computer1 |

| b3    | computer1 |

| b4    | computer  |

+-------+-----------+

4 rows in set (0.00 sec)

33、查询至少有2名男生的班号

mysql> select * from student where ssex = 'boy' group by class having count(ssex) >=2;

+-----+-------+------+---------------------+-------+

| sno | sname | ssex | sbirthday           | class |

+-----+-------+------+---------------------+-------+

| 101 | a1    | boy  | 1977-10-01 00:00:00 | 95033 |

+-----