上海大学数据库上机作业上机练习2作业.doc

上大学数据库上机作业
《数据库系统与应用》上机习题*************************************************************************************************
第二部分、SQL查询━━单表查询
二、使用购进凭证数据库 。（由老师提供复制，内含“商品信息表”和“购进凭证表”）
⒈只显示购进凭证表中凭证号、单价、数量；
并输出一个计算字段“金额”，计算金额的公式是：单价×数量。（注：不是增加字段）
USE 购进凭证
SELECT 凭证号,单价,数量,(单价*数量)AS '金额'
FROM 购进凭证表
⒉加入筛选条件：只输出“饼干”大类商品。
USE 购进凭证
SELECT 商品编号,品名,大类编号,大类名
FROM 商品信息表
WHERE 大类名='饼干'
⒊除了“饼干”，再同时输出“酒类”商品；再同时输出“饮料”、“糖果”商品。
USE 购进凭证
SELECT *
FROM 商品信息表
WHERE 大类名 in('饼干','酒类','饮料','糖果')
ORDER BY 大类编号 ASC
⒋按大类名排序、同一大类的单价从大到小排序。
USE 购进凭证
SELECT 凭证号,商品编号,单价,数量,大类名,部门名称
FROM 购进凭证表
ORDER BY 大类名,单价 DESC
⒌清除原筛选条件，重设条件：只输出单价不在10至30元之间的酒类商品。
USE 购进凭证
SELECT 凭证号,商品编号,单价,,量,大类名,部门名称
FROM 购进凭证表
WHERE 大类名='酒类', 单价 not between 10 and 30
⒍统计所有商品的总数量、总金额。
USE 购进凭证
SELECT SUM(数量)AS'总数量',SUM(单价*数量)AS 总金额
FROM 购进凭证表
⒎统计饼干大类的总数量、总金额。
USE 购进凭证
SELECT 大类名,SUM(数量)AS'总数量',SUM(单价*数量)AS 总金额
FROM 购进凭证表
WHERE 大类名='饼干'
GROUP BY 大类名
⒏统计饼干、糖果大类的总数量、总金额。
USE 购进凭证
SELECT SUM(数量)AS'总数量',SUM(单价*数量)AS '总金额 '
FROM 购进凭证表
WHERE 大类名 ='饼干' OR 大类名 ='糖果'
⒐统计饼干大类和糖果大类各自的总数量、总金额。
USE 购进凭证
SELECT 大类名,SUM(数量)AS'总数量',SUM(单价*数量)AS '总金额 '
FROM 购进凭证表
WHERE 大类名 ='饼干' OR 大类名 ='糖果'
GROUP BY 大类名
⒑统计各大类各自的总数量、总金额。
USE 购进凭证
SELECT 大类名,SUM(数量)AS'总数量',SUM(单价*数量)AS '总金额 '
FROM 购进凭证表
GROUP BY 大类名
⒒统计购进凭证表中的凭证张数。
USE 购进凭证
SELECT COUNT(*)AS '凭证张数'
FROM 购进凭证表
⒓统计各大类的凭证张数。
USE 购进凭证
SELECT 大类名,COUNT (*)AS '凭证张数'
FROM 购进凭证表
GROUP BY 大类名
三、利用上次上机的学生_课程数据库
1. 求计算机学院学生的学号和姓名
USE 学生课程
SELECT 学号,姓名,单位
FROM 学生
WHERE 单位='计算机学院'

2. 求选修了课程的学生学号 （利用select命令中DISTINCT选项）
USE 学生课程
SELECT DISTINCT 学号
FROM 选课
3. 求选修了C1课程的学生学号和成绩，并对查询结果按成绩降序排列，如果成绩相同则按学号升序排列.
USE 学生课程
SELECT 学号,课程号,成绩
FROM 选课
WHERE 课程号='C1'
ORDER BY 成绩 DESC
4. 在选课表中添加一条记录（S7,C1,89）,再运行上小题的查询，看结果是否正确
USE 学生课程
INSERT INTO 选课 VALUES('S7','C1','89')
5. 求选修课程C1且成绩在80-90分之间的学生学号和成绩
USE 学生课程
SELECT 学号,课程号,成绩
FROM 选课
WHERE 课程号='C1'AND 成绩 BETWEEN 80 AND 90
6. 求选修课程C1且成绩在80-90分之间的学生学号和成绩，将成绩乘以系数0.8输出
USE 学生课程
SELECT 学号,课程号,(成绩*0.8)AS 成绩
FROM 选课
WHERE 课程号='C1'AND 成绩 BETWEEN 80 AND 90
7. 求计算机学院的所有学生以及自动化学院姓张的学生信息
USE 学生课程
SELECT *
FROM 学生
WHERE 姓名 LIKE '张%' AND 单位='自动化学院' OR 单位='计算机学院'
8. 求缺少了成绩的学生的学号和课程号
USE 学生课程
SELECT 学号,课程号
FROM 选课
WHERE 成绩 IS NULL

2