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

上大学数据库上机作业 数据库系统与应用上机习 第四部分、 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 定价 2008-11-15ORDER 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 选课.课程号 = C1A ND 成绩90 ORDER BY 学生 .学号4. 查询每门课程的间接先行课(就是先行课的先行课) 。USE 学生课程SELECT 选课.课程号 ,课程名 ,先行课FROM 选课 ,课程WHERE 选课.课程号 =课程.课程号四、完成书上 P158 页上机实验题 41. 显示所有职工的年龄，并按职工号递增排序。USE factorySELECT 职工号 ,DATEDIFF (YY,GETDATE (),出生日期 )AS 年龄FROM workerORDER BY 职工号2. 求出各部门的党员人数。USE factorySELECT 部门号 ,SUM (cast(党员否 as bigint )AS 党员人数FROM workerWHERE 党员否 =TrueGROUP BY 部门号3. 显示所有职工的姓名和 2004 年 1 月份的工资数。USE factorySELECT 姓名 ,工资FROM salaryWHERE 日期 =2004-01-044. 显示所有职工的职工号、姓名和平均工资。USE factorySELECT 职工号 ,姓名 ,AVG (工资 )AS 平均工资FROM salaryGROUP BY 职工号 ,姓名5. 显示所有职工的职工号、姓名、部门名和 2004 年 2 月份的工资，并按部门名顺序排序。USE factorySELECT salary.职工号 ,salary.姓名 ,部门名 , 工资FROM salary,depart,workerWHERE worker .部门号 =depart.部门号 AND 日期=2004-02-03AND worker.职工号 =salary.职工号 ORDER BY 部门名6. 显示个部门名和该部门的所有职工平均工资。USE factorySELECT depart.部门名 ,AVG (工资 )AS 平均工资FROM salary,depart,workerWHERE worker .部门号 = depart.部门号 AND worker .职工号 =salary.职工号GROUP BY depart.部门名7. 显示所有平均工资高于 1200 的部门名和对应的平均工资。USE factorySELECT depart.部门名 ,AVG (工资 )AS 平均工资FROM salary,depart,workerWHERE worker .部门号 = depart.部门号AND worker .职工号 = salary.职工号GROUP BY depart.部门名HA VING AVG (工资 ) 12008. 显示所有职工的职工号、姓名和部门类型，其中财务部和人事部属管理部门，市场部 属市场部门。USE factorySELECT worker.职工号 ,worker .姓名 ,CASE rtrim ( 部门名 )WHEN 财务处 THEN 管理部门 WHEN 人事处 THEN 管理部门 WHEN 市场部 THEN 市场部门 ELSE 其他部门 END AS 部门类型FROM worker inner join depart On worker.部门号 =depart.部门号9. 若存在职工号为 10 的职工，则显示其工作部门名称，否则显示相应的提示信息。 USE factoryGODECLARE no int ,dep char(10)SET no =10IF EXISTS( SELECT * FROM worker WHERE 职工号 =no)BEGINSELECT dep=depart.部门名FROM worker ,departWHERE worker .职工号 =no AND worker .部门号 = depart.部门号PRINT 职工号为 +CAST (no AS CHAR (2)+ 的职工在 +CAST (dep AS char(6)+ 工作ENDELSEqwPRINT 不存在该员工的记录 GO第二种：USE factoryDECLARE cno intSET cno=10IF EXISTI (SELECT * FROM worker WHERE职工号 =cno)SELECT 部门名 FROM depart WHERE 部门号 IN（SELECT 部门号 FROM worker WHERE 职工号=cno）ELSESELECT 没有该工号 10. 求出男女职工的平均工资， 若男职工平均工资高于女职工平均工资 50% ，怎显示“南 男职工比女职工的工资高多了 ，若男职工平均工资高于女职工平均工资比率在 1.5-0.8 之间，则显示男职工平均工资高于女职工平均工资差不多“”的信息，否则“女 职工平均工资高于男职工平均工资”USE factoryGODECLARE avg1 float ,avg2 float ,ratio float-计算男职工平均工资SELECT avg1=AVG（工资 ）FROM worker ,salaryWHERE worker .职工号 = salary.职工号 AND worker .性别 =男-计算女职工平均工资SELECT avg2=AVG（工资 ）FROM worker ,salaryWHERE worker .职工号 = salary.职工号 AND worker .性别 =女SET ratio = avg1/avg2IF ratio 1.5PRINT 男职工比女职工的工资高多了ELSEIF ratio = 0.8PRINT 男职工跟女职工的工资差不多 ELSEPRINT 女职工比男职工的工资高多了 GO