logo
Xenon
banner
NEWS LETTER

数据管理技术-week5

Scroll down

SQL测验题

一、

现有关系模式如下：

学生（学号，姓名，性别）；课程（课程号，课程名，教师姓名）；

选课表（课程号，学号，成绩）

1． 检索年龄大于20岁的男生的学号和姓名。

1
2
3
select 学号, 姓名 
from 学生
where 年龄 > 20;

2． 检索选修了姓刘的老师所教授的课程的女学生的姓名。

1
2
3
4
select 姓名
from 学生, 课程, 选课表
where 学生.学号 = 选课表.学号 and 课程.课程号 = 选课表.课程号
and 教师姓名 like '刘%' and 性别 = '女';

3． 检索李想同学不学的课程的课程号和课程名。

1
2
3
4
5
6
7
8
select 课程号, 课程名
from 课程
where not exists(
    select * 
    from 学生, 选课表
    where 学生.学号 = 选课表.学号 课程.课程号 = 选课表.课程号 and
    学生.姓名 = '李想'
);

4． 检索至少选修了两门课程的学生的学号。

1
2
3
select 学号
from 选课表 as first, 选课表 as second
where first.学号 = second.学号 and first.课程号 != second.学号;

5． 求刘老师所教授课程的每门课的平均成绩。

1
2
3
4
select 课程.课程号, avg(选课表.成绩)
from 课程, 选课表
where 课程.课程号 = 选课表.课程号 and 课程.教师姓名 like '刘%'
group by 课程.课程号;

6． 假设不存在重修的情况，请统计每门课的选修人数(选课人数超过两人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

1
2
3
4
5
6
select 课程.课程号, count(选课表.学号)
from 课程, 选课表
where 课程.课程号 = 选课表.课程号
group by 课程.课程号
having count(*) > 2
order by count(*) desc, 课程.课程号;

7． 求年龄大于所有女生年龄的男生的姓名和年龄。

1
2
3
4
5
6
7
select 姓名, 年龄
from 学生
where 性别 = '男' and 年龄 < (
	select max(年龄)
    from 学生
    where 性别 = '女'
);

8． 假定不存在重修的情况，求选修了所有课程的学生的学号姓名。(可以不用相关子查询做)

1
2
3
4
5
6
7
8
select 学生.姓名, 学生.学号
from 学生, 选课表
where 学生.学号 = 选课表.学号
group by 学生.学号
having count(*) = (
select count(*)
    from 课程
);

9． 查询重修次数在2次以上的学生学号，课程号，重修次数

1
2
3
4
5
select 学生.姓名, 学生.学号, count(*) as 重修次数
from 学生, 选课表
where 学生.学号 = 选课表.学号
group by 学生.学号, 选课表.课程号
having count(*) > 2;

10． 查询重修学生人数最多的课程号，课程名，教师姓名

1
2
3
4
5
6
7
8
9
10
11
12
select 课程.课程号, 课程.课程名, 课程.教师姓名
from 课程, 选课表
where 课程.课程号 = 选课表.课程号
group by 课程.课程号
having count(*) = (
	select max(args)
    from (
    	select count(*) as args
        from 选课表
        group by 选课表.课程号
    ) tmp_table
);

二、

学生（学号，姓名，年龄，性别，班级）

课程（课程号，课程名，先修课程号，学分）注意：此表的主键是(课程号)

选课（学号，课程号，教师号，成绩）

教师（教师号，教师名称）

  1. 查找李力的所有不及格的课程名称和成绩，按成绩降序排列

    1
    2
    3
    4
    select 选课.课程名, 选课.成绩
    from 学生, 选课
    where 学生.学号 = 选课.学号 and 学生.姓名 = '李力' and 选课.成绩 < 60
    order by 选课.成绩 desc;

  2. 列出每门课的学分，选修的学生人数，及学生成绩的平均分

    1
    2
    3
    4
    select 课程.学分, count(学生.学号) as 学生人数, avg（选课.成绩）
    from 学生, 课程, 选课
    where 学生.学号 = 选课.学号 and 选课.课程号 = 课程.课程号
    group by 课程.课程号;

  3. 选出所修课程总学分在10分以下的学生（注：不及格的课程没有学分）。

    1
    2
    3
    4
    5
    select 学生.学号, 学生.姓名
    from 学生, 课程, 选课
    where 学生.学号 = 选课.学号 and 选课.课程号 = 课程.课程号 and 选课.成绩 >= 60
    group by 学生.学号
    having sum(课程.学分) < 10;

  4. 选出选课门数最多的学生学号及选课数量

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select 学生.学号, count(*) as 选课数量
    from 学生, 课程, 选课
    where 学生.学号 = 选课.学号 and 选课.课程号 = 课程.课程号
    group by 学生.学号
    having count(*) >= all(
        select count(*)
        from 学生, 课程, 选课
        where 学生.学号 = 选课.学号 and 选课.课程号 = 课程.课程号
        group by 学生.学号
    )

  5. 列出每门课的最高分及获得该分数的学生

    1
    2
    3
    4
    5
    select 选课.成绩, 学生.学号, 学生.姓名
    from 学生, 选课
    where 学生.学号 = 选课.学号
    group by 选课.课程号
    having 选课.成绩 = max(选课.成绩);

  6. 选出物理课得分比所有男学生的物理课平均分高的学生姓名

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select 学生.姓名
    from 学生, 课程, 选课
    where 学生.学号 = 选课.学号 and 选课.课程号 = 课程.课程号 and 课程名 = '物理'
    having 选课.成绩 > (
    	select avg(选课.成绩)
        from 学生, 课程, 选课
        where 学生.学号 = 选课.学号 and 选课.课程号 = 课程.课程号 and 课程名 = '物理'
        and 学生.性别 = '男'
    );

  7. 选出修习过物理课的直接先修课的学生

    1
    2
    3
    4
    5
    6
    7
    8
    select 学生.学号, 学生.姓名
    from 学生, 课程, 选课
    where 学生.学号 = 选课.学号 and 选课.课程号 = 课程.课程号 
    and 课程.课程号 = (
    	select 课程.先修课程号
        from 课程
        where 课程.课程名 = '物理'
    );

  8. 选出有两门以上先修课的课程（包括直接先修课、间接先修课）(用课程表)

1
2
3
4
5
6
7
select * 
from 课程 as c1
where exists(
	select * 
    from 课程 as c2, 课程 as c3
    where c1.先修课程号 = c2.课程号 and c2.先修课程号 = c3.课程号
);

课本习题, P130, 5

1.找出所有供应商的姓名和所在城市

1
2
select sname, city
from s;

2.找出所有零件的名称、颜色、重量

1
2
select pname, color, weight
from p;

3.找出使用供应商S1所供应零件的工程号码

1
2
3
select jno
from spj
where sno = 'J2';

4.找出工程项目J2使用的各种零件的名称及其数量

1
2
3
select p.pname, spj.qty
from p, spj
where p.pno = spj.pno and spj.pno = 'J2';

5.找出上海厂商供应的所有零件号码

1
2
3
4
5
6
7
select distinct pno
from spj
where sno in (
	select sno
    from s
    where sity = '上海'
);

6.找出使用上海产的零件的工程名称

1
2
3
select jname
from j, spj, s
where j.jno = spj.jno and spj.sno = s.sno and s.city = '上海';

7.找出没有使用天津产的零件的工程号码

1
2
3
4
5
6
7
select jno
from j
where not exists (
	select *
    from spj, s
    where j.jno = spj.jno and spj.sno = s.sno and s.city = '天津'
);
其他文章
cover
SoftwareEngineering
软工_Lab3
  • 23/03/25
  • 23:48
  • 41
  • 1
cover
DataBase
数据管理技术 Lab_2
  • 23/03/23
  • 17:10
  • 252
  • 1
© 2023- 2024 Xenon的小破站
由 Hexo 驱动 v6.3.0 主题 - Async v2.0.3