发布时间 : 星期三 文章上海大学数据库上机作业上机练习4作业更新完毕开始阅读
上大学数据库上机作业
《数据库系统与应用》上机习题*************************************************************************************************
第四部分、SQL查询━━嵌套和组合统计查询
要求掌握：利用SQL查询语言表达嵌套查询语句以及数据查询中的统计计算和组合操作。
一、做书上第九章余下的例题，并完成书上练习题9中第11、12、13、14题
11.ifexists(SELECT*FROMsys.objectsWHEREname=student) 12.
二、利用图书_读者数据库
1. 求机械工业出版社出版的各类图书的平均价。
USE图书读者
SELECT类别,AVG(定价)AS平均价 FROM图书
WHERE出版社='机械工业出版社' GROUPBY类别
2. 求各类图书的最高价、最低价、图书的数量。
USE图书读者
SELECT类别,MAX(定价)AS最高价,MIN(定价)AS最低价,COUNT(*)AS数量 FROM图书
GROUPBY类别
3. 查找图书类别，要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的1.5倍。
USE图书读者 SELECT类别 FROM图书
WHERE定价=ALL (SELECTMAX(定价) FROM图书
WHERE定价<=ALL
(SELECTAVG(定价)*1.5 FROM图书))
4.计算机类和机械工业出版社出版的图书。
USE图书读者 SELECT* FROM图书
WHERE出版社='机械工业出版社'AND类别='计算机'
5.查询所有读者借阅过的书，要求按读者姓名、书名来排序。
USE图书读者
SELECT读者.编号,借阅.读者编号,姓名,书名 FROM图书,读者,借阅
WHERE读者.编号=借阅.读者编号AND借阅.书号=图书.书号 ORDERBY姓名
6. 查询所有在2008.11.15日以后被借阅过的图书名及借阅者。
USE图书读者
SELECT读者.编号,借阅.读者编号,书名,姓名,借阅日期 FROM图书,读者,借阅
1
WHERE读者.编号=借阅.读者编号AND借阅.书号=图书.书号 AND借阅日期>'2008-11-15' ORDERBY姓名
三、利用上次上机的学生_课程数据库 1. 查询每个学生的情况以及他所选修的课程。
USE学生课程
SELECT学生.学号,姓名,选课.课程号,课程名,单位 FROM学生,选课,课程
WHERE学生.学号=选课.学号AND选课.课程号=课程.课程号 ORDERBY学号
2. 求学生的学号、姓名、选修的课程名及成绩。
USE学生课程
SELECT学生.学号,姓名,选课.课程号,课程名,成绩 FROM学生,选课,课程
WHERE学生.学号=选课.学号AND选课.课程号=课程.课程号 ORDERBY学号
3. 求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。
USE学生课程
SELECT学生.学号,姓名,选课.课程号,课程名,成绩 FROM学生,选课,课程
WHERE学生.学号=选课.学号AND选课.课程号=课程.课程号AND选课.课程号=’C1’AND成绩>90 ORDERBY学生.学号
4. 查询每门课程的间接先行课（就是先行课的先行课）。
USE学生课程
SELECT选课.课程号,课程名,先行课 FROM选课,课程
WHERE选课.课程号=课程.课程号
四、完成书上P158页上机实验题4
1.显示所有职工的年龄，并按职工号递增排序。
USEfactory
SELECT职工号,DATEDIFF(YY,GETDATE(),出生日期)AS年龄 FROMworker ORDERBY职工号
2.求出各部门的党员人数。
USEfactory
SELECT部门号,SUM(cast(党员否asbigint))AS党员人数 FROMworker
WHERE党员否='True' GROUPBY部门号
3.显示所有职工的姓名和2004年1月份的工资数。
USEfactory
SELECT姓名,工资 FROMsalary
WHERE日期='2004-01-04'
4.显示所有职工的职工号、姓名和平均工资。
USEfactory
SELECT职工号,姓名,AVG(工资)AS平均工资 FROMsalary
GROUPBY职工号,姓名
5.显示所有职工的职工号、姓名、部门名和2004年2月份的工资，并按部门名顺序排
2
序。
USEfactory
SELECTsalary.职工号,salary.姓名,部门名,工资 FROMsalary,depart,worker
WHEREworker.部门号=depart.部门号AND日期='2004-02-03'ANDworker.职工号=salary.职工号 ORDERBY部门名
6.显示个部门名和该部门的所有职工平均工资。
USEfactory
SELECTdepart.部门名,AVG(工资)AS平均工资 FROMsalary,depart,worker
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号 GROUPBYdepart.部门名
7.显示所有平均工资高于1200的部门名和对应的平均工资。
USEfactory
SELECTdepart.部门名,AVG(工资)AS平均工资 FROMsalary,depart,worker
WHEREworker.部门号=depart.部门号 ANDworker.职工号=salary.职工号 GROUPBYdepart.部门名 HAVINGAVG(工资)>1200
8.显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属管理部门，市场部
属市场部门。
USEfactory
SELECTworker.职工号,worker.姓名, CASErtrim(部门名)
WHEN'财务处'THEN'管理部门' WHEN'人事处'THEN'管理部门' WHEN'市场部'THEN'市场部门' ELSE'其他部门' ENDAS部门类型
FROMworkerinnerjoindepartOnworker.部门号=depart.部门号
9.若存在职工号为10的职工，则显示其工作部门名称，否则显示相应的提示信息。 USEfactory GO
DECLARE@noint,@depchar(10) SET@no=10
IFEXISTS(SELECT*FROMworkerWHERE职工号=@no) BEGIN
SELECT@dep=depart.部门名 FROMworker,depart
WHEREworker.职工号=@noANDworker.部门号=depart.部门号 PRINT'职工号为'+CAST(@noASCHAR(2))+'的职工在'+ CAST(@depASchar(6))+'工作' END ELSEqw
PRINT'不存在该员工的记录' GO
第二种： USE factory
DECLARE@cnoint SET@cno=10
IF EXISTI (SELECT * FROM worker WHERE 职工号=@cno)
3
SELECT 部门名 FROM depart WHERE 部门号 IN(SELECT 部门号 FROM worker WHERE 职工号=@cno) ELSE
SELECT ‘没有该工号’
10.求出男女职工的平均工资，若男职工平均工资高于女职工平均工资50%，怎显示?南
‘男职工比女职工的工资高多了’，若男职工平均工资高于女职工平均工资比率在1.5-0.8之间，则显示男职工平均工资高于女职工平均工资差不多??的信息，否则?女职工平均工资高于男职工平均工资?
USEfactory GO
DECLARE@avg1float,@avg2float,@ratiofloat --计算男职工平均工资
SELECT@avg1=AVG(工资) FROMworker,salary
WHEREworker.职工号=salary.职工号ANDworker.性别='男' --计算女职工平均工资
SELECT@avg2=AVG(工资) FROMworker,salary
WHEREworker.职工号=salary.职工号ANDworker.性别='女' SET@ratio=@avg1/@avg2 IF@ratio>1.5
PRINT'男职工比女职工的工资高多了' ELSE
IF@ratio>=0.8
PRINT'男职工跟女职工的工资差不多' ELSE
PRINT'女职工比男职工的工资高多了' GO
4