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

上大学数据库上机作业
《数据库系统与应用》上机习题*************************************************************************************************
第四部分、SQL查询━━嵌套和组合统计查询
要求掌握：利用SQL查询语言表达嵌套查询语句以及数据查询中的统计计算和组合操作。
一、 做书上第九章余下的例题，并完成书上练习题9中第11、12、13、14题
11. if exists(SELECT * FROM sys.objects WHERE name=student)
12.
二、利用图书_读者数据库
1. 求机械工业出版社出版的各类图书的平均价。
USE 图书读者
SELECT 类别,AVG(定价)AS 平均价
FROM 图书
WHERE 出版社='机械工业出版社'
GROUP BY 类别
2. 求各类图书的最高价、最低价、图书的数量。
USE 图书读者
SELECT 类别,MAX(定价)AS 最高价,MIN(定价)AS 最低价,COUNT(*)AS 数量
FROM 图书
GROUP BY 类别
3. 查找图书类别，要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的1.5倍。
USE 图书读者
SELECT 类别
FROM 图书
WHERE 定价= ALL
(SELECT MAX(定价)
FROM 图书
WHERE 定价<=ALL
(SELECT AVG(定价)*1.5
FROM 图书) )
4.计算机类和机械工业出版社出版的图书。
USE 图书读者
SELECT *
FROM 图书
WHERE 出版社='机械工业出版社' AND 类别='计算机'
5.查询所有读者借阅过的书，要求按读者姓名、书名来排序。
USE 图书读者
SELECT 读者.编号,借阅.读者编号,姓名,书名
FROM 图书,读者,借阅
WHERE 读者.编号=借阅.读者编号 AND 借阅.书号=图书.书号
ORDER BY 姓名
6. 查询所有在2008.11.15日以后被借阅过的图书名及借阅者。
USE 图书读者
SELECT 读者.编号,借阅.读者编号,书名,姓名,借阅日期
FROM 图书,读者,借阅
WHERE 读者.编号=借阅.读者编号 AND 借阅.书号=图书.书号
AND 借阅日期>'2008-11-15'
ORDER BY 姓名
三、利用上次上机的学生_课程数据库
1. 查询每个学生的情况以及他所选修的课程。
USE 学生课程
SELECT 学生.学号,姓名,选课.课程号,课程名,单位
FROM 学生,选课,课程
WHERE 学生.学号=选课.学号 AND 选课.课程号=课程.课程号
ORDER BY 学号
2. 求学生的学号、姓名、选修的课程名及成绩。
USE 学生课程
SELECT 学生.学号,姓名,选课.课程号,课程名,成绩
FROM 学生,选课,课程
WHERE 学生.学号=选课.学号 AND 选课.课程号=课程.课程号
ORDER BY 学号
3. 求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。
USE 学生课程
SELECT 学生.学号,姓名,选课.课程号,课程名,成绩
FROM 学生,选课,课程
WHERE 学生.学号=选课.学号 AND 选课.课程号=课程.课程号AND 选课.课程号=’C1’ AND 成绩>90
ORDER BY 学生.学号
4. 查询每门课程的间接先行课（就是先行课的先行课）。
USE 学生课程
SELECT 选课.课程号,课程名,先行课
FROM 选课,课程
WHERE 选课.课程号=课程.课程号
四、 完成书上P158页上机实验题4
1. 显示所有职工的年龄，并按职工号递增排序。
USE factory
SELECT 职工号,DATEDIFF(YY,GETDATE(),出生日期) AS 年龄
FROM worker
ORDER BY 职工号
2. 求出各部门的党员人数。
USE factory
SELECT 部门号,SUM(cast(党员否 as bigint))AS 党员人数
FROM worker
WHERE 党员否='True'
GROUP BY 部门号
3. 显示所有职工的姓名和2004年1月份的工资数。
USE factory
SELECT 姓名,工资
FROM salary
WHERE 日期='2004-01-04'
4. 显示所有职工的职工号、姓名和平均工资。
USE factory
SELECT 职工号,姓名,AVG(工资)AS 平均工资
FROM salary
GROUP BY 职工号,姓名
5. 显示所有职工的职工号、姓名、部门名和2004年2月份的工资，并按部门名顺序排序。
USE factory
SELECT salary.职工号,salary.姓名,部门名,工资
FROM salary,depart,worker
WHERE worker.部门号=depart.部门号 AND 日期='2004-02-03' AND worker.职工号=salary.职工号
ORDER BY 部门名
6. 显示个部门名和该部门的所有职工平均工资。
USE factory
SELECT depart.部门名,AVG(工资)AS 平均工资
FROM salary,depart,worker
WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号
GROUP BY depart.部门名
7. 显示所有平均工资高于1200的部门名和对应的平均工资。
USE factory
SELECT depart.部门名,AVG(工资)AS 平均工资
FROM salary,depart,worker
WHERE worker.部门号=depart.部门号
AND worker.职工号=salary.职工号
GROUP BY depart.部门名
HAVING AVG(工资)>1200
8. 显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属管理部门，市场部属市场部门。
USE factory
SELECT worker.职工号,worker.姓名,
CASE rtrim(部门名)
WHEN '财务处'THEN'管理部门'
WHEN '人事处'THEN'管理部门'
WHEN '市场部'THEN'市场部门'
ELSE '其他部门'
END AS 部门类型
FROM worker inner join depart On worker.部门号=depart.部门号
9. 若存在职工号为10的职工，则显示其工作部门名称，否则显示相应的提示信息。
USE factory
GO
DECLARE @no int,@dep char(10)
SET @no=10
IF EXISTS(SELECT * FROM worker WHERE 职工号=@no)
BEGIN
SELECT @dep=depart.部门名
FROM worker,depart
WHERE worker.职工号=@no AND worker.部门号 =depart.部门号
PRINT '职工号为'+CAST(@no AS CHAR(2))+'的职工在'+
CAST(@dep AS char(6))+'工作'
END
ELSEqw
PRINT '不存在该员工的记录'
GO

第二种：
USE factory
DECLARE @cno int
SET @cno=10
IF EXISTI (SELECT * FROM worker WHERE 职工号=@cno)
SELECT 部门名 FROM depart WHERE 部门号 IN(SELECT 部门号 FROM worker WHERE 职工号=@cno)
ELSE
SELECT ‘没有该工号’
10. 求出男女职工的平均工资，若男职工平均工资高于女职工平均工资50%，怎显示“南‘男职工比女职工的工资高多了’，若男职工平均工资高于女职工平均工资比率在1.5-0.8之间，则显示男职工平均工资高于女职工平均工资差不多“”的信息，否则“女职工平均工资高于男职工平均工资”
USE factory
GO
DECLARE @avg1 float,@avg2 float,@ratio float
--计算男职工平均工资
SELECT @avg1=AVG(工资)
FROM worker,salary
WHERE worker.职工号=salary.职工号 AND worker.性别='男'
--计算女职工平均工资
SELECT @avg2=AVG(工资)
FROM worker,salary
WHERE worker.职工号=salary.职工号 AND worker.性别='女'
SET @ratio=@avg1/@avg2
IF @ratio>1.5
PRINT '男职工比女职工的工资高多了'
ELSE
IF @ratio>=0.8
PRINT '男职工跟女职工的工资差不多'
ELSE
PRINT '女职工比男职工的工资高多了'
GO
6