SQL查询练习1
假设学校学生会组织了多个俱乐部,比如足球俱乐部、文学俱乐部、篮球俱乐部等,同学们可以自由报名参加,一个同学也可参加多个俱乐部。ERD图如下:
俱乐部
PK俱乐部编号
俱乐部名称
成立日期FK1俱乐部主席
学生
PK学号
姓名
性别
入学年龄
所在系学生入会登记
PK,FK2学号
PK,FK1俱乐部编号
入会日期
提示,学生入会登记表是个关联实体,它将学生与俱乐部之间的多对多联系分解成了两个一对多联系。在学生入会登记表中如果一个学生参加了3个俱乐部,就会有3 条记录与之有关,在3 条记录中分别对应着3个不同的俱乐部编号。
1.显示所有俱乐部的编号、名称及成立的年数。
Select 俱乐部编号,俱乐部名称,成立日期
From 俱乐部
2.查找07级的会员记录,显示他们的俱乐部编号、俱乐部名称、学号、姓名、所在系,结果按
俱乐部编号、学号排序。(假设学号以“2007”打头表示是2007级的学生)
Select俱乐部.俱乐部编号,俱乐部名称,学生.学号,姓名,所在系
From俱乐部,学生,学生入会登记
Where学生.学号like'07%'and学生入会登记.学号=学生.学号and学生入会登记.俱乐部编号=俱乐部.俱乐部编号
order by俱乐部.俱乐部编号,学生.学号;
3.求所有俱乐部会员的总人次(即,一个人参加3个俱乐部算3个人次)。
思考:求总人数如何做?即,一个人参加3个俱乐部只算一个人,怎样去除重复的?Select count(*)总人次
From学生入会登记
Select count(distinct学号)总人数
From学生入会登记
4.求各系学生的平均入学年龄,列出所在系、人数。
Select 所在系,avg(入学年龄)平均入学年龄
From 学生
5.求计算机系的女生的最大入学年龄。
6.查询人数多于100 人的俱乐部的俱乐部名称、人数。
Select 俱乐部名称,人数
From 学生入会登记
Where 人数=count(学号) and 人数>100
7.列出所有同学的学号、姓名、俱乐部编号,非会员的同学只列出学号姓名、俱乐部编号一列为
空。提示:可使用学生表与学生入会登记表的左外连接。
Select 学号,姓名,俱乐部编号
From 学生入会登记,学生
Where学生入会登记.学号=学生.学号
8.用两种方法(连接/嵌套……)查询足球俱乐部的会员名单。
9.建立足球俱乐部的查询视图(属性至少包括学号、姓名、入会日期等)。
10.查询与“李丽”在同一个系的学生记录。
11.查询至少参加了学号为“070201007”的同学所参加的所有俱乐部的学生学号。
12.查询计算机系与数学系同名的同学。
13.将查询学生入会登记表的权限授予所有用户。
14.插入单条记录。在学生表中插入一条新入学的学生记录,学号“070401011”,姓名“王冰冰”,
男,16岁,计算机系。
15.插入一批记录(子查询)。假设外语系的学生同时加入网络俱乐部,请将他们的记录插入到学
生入会登记表中,入会日期填写当日系统日期。提示:注意子查询的字段列表应与学生入会登记表的字段顺序、数量、类型吻合。
16.修改单个记录的数据。将学号为“050401004”的同学姓名更改为“李小炎”。
17.修改表结构,再批量修改记录的数据。先使用命令ALTER TABLE在“俱乐部”表中增加“会
费”一列,然后用UPDATE命令将该表中所有俱乐部会费置为10.00(表示10元)。
18.删除单个记录。删除名为“王冰冰”的学生记录。
19.删除一批记录。将所有02级的已毕业学生从学生入会登记表中删除。