MYSQL数据库系统第4次实验 单表查询

基于之前实验数据库teaching中的6张数据表的数据，完成以下操作：
1.查询student表中在9月份之后出生的学生的学号、姓名、电话和年龄，并将查询结果字段重命名为学号、姓名、电话和年龄；

mysql> select studentno as '学号',sname as '姓名',phone as '电话',
    -> year(now())-year(birthdate) as '年龄'
    -> from student
    -> where month(birthdate)>9;
    +-------------+--------+-------------+------+
| 学号        | 姓名   | 电话        | 年龄 |
+-------------+--------+-------------+------+
| 18122210009 | 许东山 | 13623456778 |   22 |
| 18122221324 | 何白露 | 13178978999 |   21 |
| 18135222201 | 凌浩风 | 15978945645 |   20 |
+-------------+--------+-------------+------+
3 rows in set (0.00 sec)

2.查询teacher表中包含的不同学院信息；

mysql> select distinct department
    -> from teacher;
+------------+
| department |
+------------+
| 计算机学院 |
| 管理学院   |
| 机械学院   |
| 材料学院   |
+------------+
4 rows in set (0.00 sec)

3.查询student表中，“19”级的男同学的人数；

+------------------+
| 19级男生同学人数 |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

4.查询score表中，“18”级的学生对应每一门课程的平时成绩的最高分、最低分，以及期末成绩的平均分；

mysql> select courseno,max(daily) as max_daily,
    -> min(daily) as min_daily,avg(final) as avg_final
    -> from score
    -> where substring(studentno,1,2)='18'
    -> group by courseno;
+----------+-----------+-----------+-----------+
| courseno | max_daily | min_daily | avg_final |
+----------+-----------+-----------+-----------+
| c05103   |      88.0 |      74.0 |  78.33333 |
| c05109   |      99.0 |      77.0 |  80.50000 |
| c08106   |      79.0 |      78.0 |  97.00000 |
| c08123   |      85.0 |      78.0 |  90.50000 |
| c08171   |      95.0 |      77.0 |  90.66667 |
+----------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

5.查询course表中，总学时top5的课程的所有信息；

mysql> select *
    -> from course
    -> order by period desc
    -> limit 5;
+----------+----------+------+--------+-----+------+
| courseno | cname    | type | period | exp | term |
+----------+----------+------+--------+-----+------+
| c05103   | 电子技术 | 必修 |     64 |  16 |    2 |
| c05127   | 数据结构 | 必修 |     64 |  16 |    2 |
| c06127   | 机械设计 | 必修 |     64 |   8 |    3 |
| c06108   | 机械制图 | 必修 |     60 |   8 |    2 |
| c05109   | C语言    | 必修 |     48 |  16 |    2 |
+----------+----------+------+--------+-----+------+
5 rows in set (0.00 sec)

6.查询score表中，“19”级的学生的期末平均成绩大于80的课程，结果显示课程编号、课程对应的平时成绩平均值、期末成绩平均值，并按课程编号排序;

mysql> select courseno,avg(daily) as avg_daily,avg(final) as avg_final
    -> from score
    -> where substring(studentno,1,2)='19'
    -> group by courseno
    -> having avg(final)>80
    -> order by courseno;
+----------+-----------+-----------+
| courseno | avg_daily | avg_final |
+----------+-----------+-----------+
| c05103   |  77.33333 |  81.33333 |
| c05108   |  88.00000 |  89.00000 |
| c05109   |  83.00000 |  86.33333 |
| c06108   |  84.00000 |  91.33333 |
| c06127   |  85.33333 |  84.66667 |
+----------+-----------+-----------+
5 rows in set (0.00 sec)

7.查询score表中，每一门课的期末成绩的期末成绩平均值和所有成绩的平均值;

mysql> select courseno,
    -> avg(final) as avg_final
    -> from score
    -> group by courseno with rollup;