SQL数据库期中测试及答案

数据库及应用期中测试
班级__________学号__________姓名__________成绩
一、
设有一个顾客商品关系数据库，有三个基本表，表结构如下：
商品表：Article (商品号，商品名，单价，库存量)
客户表：Customer (顾客号，顾客名，性别，年龄，电话)
订单表：OrderItem (顾客号，商品号，数量，购买价，日期)
注意：回答时要将答案写到规定的地方；给出的答案要求字迹必须清楚；不允许改变程序结构和随意添加子查询。

1.请用SQL语言找出女顾客购买的商品号、商品名和数量合计。

（6分）
SELECT OrderItem.商品号AS商品号, OrderItem.商品名AS商品名,SUM(OrderItem.数量) AS 数量合计
FROM OrderItem, Artcle, Customer,
WHERE Artcle.商品号= OrderItem.商品号AND OrderItem.顾客号= Customer.顾客号AND Customer. 性别='女'
GROUP BY OrderItem.商品号，商品名；
二、现有关系数据库如下：
学生（学号，姓名，性别，专业，奖学金）；课程（课程号，名称，学分）；
学习（学号，课程号，分数）
用关系代数表达式实现下列4小题：（每小题5分，共20分）
（1）检索“英语”专业学生所学课程的信息，包括学号、姓名、课程名和分数。

（2）检索“数据库原理”课程成绩高于90分的所有学生的学号、姓名专业和分数。

（3）检索不学课程号为“C135”课程的学生信息，包括学号、姓名和专业。

（4）检索没有任何一门课程成绩不及格的所有学生的信息，包括学号、姓名和专业
1.Π学号，姓名，课程名，分数（σ专业=‘英语’（学生∞学习∞课程））（3
分）
2.Π学号，姓名，专业，分数（σ分数）90∧名称=‘数据库系统原理’（学生∞学习∞课程））
（3分）
3.Π学号，姓名，专业（学生）-Π学号，姓名，专业（σ课程号=‘C135’（学生∞学习））
（3分）
4.Π学号，姓名，专业（学生）-Π学号，姓名，专业（σ分数《60（学生∞学习））（3
分）
三、设关系模式 SCG （ S#, C#, grade ）中 S# 为学生号， C ＃为课程号， grade 为某学生学某号课程的考试成绩。

今要查询平均分数超过 80 分的课程的平均成绩，且要求查询的结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。

写出 SQL 查询。

（ 10 分）
Select c#,avg(grade) avggrade
From SCG
Group by c#
Having avggrade>80
Order by avggrade asc,c# dece
四、有雇员表emp(empno,ename,age,sal,tel,deptno),
其中：empno-----编号，name------姓名，age -------年齡，sal-----工资，tel-----电话
deptno-----部门号。

请按下列要求分别写出相应SQL语句。

（每小题5分，共25分）
（1）查询家有电话的职工信息。

（2）查询工资在500至800元之间的雇员信息
（3）按年龄递增顺序显示雇员编号、姓名、年龄、工资
（4）求部门号为D_01的平均工资
（5）找部门号为D_01的40岁以上而工资在400元以下的雇员名单。

1.SELECT * FROM emp WHERE tel is NOT NULL; （5分）
2.SELECT * FROM emp WHERE BETWEEN 500 AND 800; （5分）
3.SELECT empno,ename,age,sal FROM emp ORDER BY age ASC; （5
分）
4.SELECT A VG(sal) FROM emp WHERE deptno='D_01'; （5分）
5.SELECT ename FROM emp WHERE deptno='D_01' AND age>40 AND
Sal<400; （5分）
五、设有四个关系
S（SNO，SNAME， ADDRESS，TEL）
SNO：供应商代码 SNAME：姓名 ADDRESS：地址 TEL：电话
J（JNO，JNAME，LEADER，BG）
JNO：工程代码 JNAME：工程名 LEADER：负责人 BG：预算
P（PNO，PNAME，SPEC，CITY，COLOR）
PNO：零件代码 PNAME：零件名 SPEC：规格 CITY：产地 COLOR：颜色
SPJ（SNO，JNO，PNO，QTY）
SNO：供应商代码 JNO：工程代码 PNO：零件代码 QTY：数量
用关系代数表达式实现下列1-4小题：（12分）
（1）找出所有供应商的姓名和地址、电话。

（2）找出所有零件的名称、规格、产地。

（3）找出使用供应商代码为S1供应零件的工程号。

（4）找出工程代码为J2的工程使用的所有零件名称、数量。

用SQL语言实现下列5-10小题：（18分）
（5）找出所有供应商的姓名和地址、电话。

（6）找出所有零件的名称、规格、产地。

（7）找出使用供应商代码为S1供应零件的工程号。

（8）找出工程代码为J2的工程使用的所有零件名称、数量。

（9）找出产地为上海的所有零件代码和规格。

（10）找出使用上海产的零件的工程名称。

1、Πsname,address,tel(s)
2、Πpname,spec,city(p)
3、Πjno(σpno=’S1’(spj))
4、Πpname,qty（σjno=’J2’（p∞spj））
5、SELECT sname,address,tel FROM S;
6、SELECT pname,spec,city FROM p;
7、SELECT DISTINCT jno FROM spj WHERE pno=‘S1’; 8、SELECT pname, qty FROM p,spj
WHERE p.pno=spj.pno AND jno=‘J2’；
9、SELECT pno,spec FROM p
WHERE city=‘上海’；
10、SELECT a.jno,b.jname from spj a,j b,p c
WHERE a.jno=b.jno AND a.pno=c.pno and c.city=‘上海’
学生信息表（表名为STUDENT
）
选课信息表（表名为SKB）
写出下列语句执行后的结果
1、SELECT 课号，MAX(成绩) AS 最高成绩FROM SKB GROUP BY 课号。

（4分）
2、SELECT 籍贯，MAX（年龄）AS 最大年龄FROM STUDENT GROUP BY 籍贯HAVING 最大年龄>22。

（5分）。