数据应用程序题.docx

四、编写程序1. 假设数据库factory中有3个表，各表逻辑结构设计如下： 部门表depart，其结构为：部门号（整数），部门名（定长字符串，10）。主键：部门号。职工表worker，其结构为：职工号（整数），姓名：（定长字符串，8），性别：（定长字符串，2，默认值“男”），出生日期（日期型），党员否（逻辑型），参加工作（日期型），部门号（整数）。主键：职工号；外键：部门号。职工工资表salary，其结构为：职工号（整数），日期（日期型），工资（数字型，(6,1)，取值12003) 建立视图view1，查询所有职工的职工号、姓名和平均工资。答：CREATE VIEW view1(职工号，姓名，平均工资) AS SELECT worker.职工号，姓名，AVG(工资) FROM worker,salary WHERE worker.职工号=salary.职工号 GROUP BY worker.职工号，姓名4) 显示所有职工的年龄。答：SELECT 姓名，YEAR(GETDATE()-YEAR(出生日期) As 年龄FROM worker5) 求出各部门的党员人数。答：SELECT depart部门名，COUNT（*） AS党员人数FROM depart，workerWHERE depart.部门号=worker.部门号 AND 党员否=1GROUP BY depart部门名6) 显示所有职工的职工号、姓名、部门名和 2004年2月份工资，并按部门名顺序排列。答：SELECT worker职工号，worker姓名，depart部门名，salary.工资 As 2004年2月工资FROM worker,depart,salaryWHERE worker部门号=depart部门号 AND worker职工号=salary职工号 AND YEAR(salary日期)=2004 AND MONTH(salary日期)=2ORDER BY worker部门号7) 显示各部门名和该部门的所有职工平均工资。答：SELECT depart部门名，AVG(salary工资） As 平均工资FROM worker,depart,salaryWHERE worker部门号=depart部门号 AND worker职工号=salary职工号GROUP BY depart部门名8) 显示所有职工的职工号、姓名和部门类型，其中财务处和人事处属管理部门，市场部属市场部门。答：USE factoryGOSELECT 职工号，姓名，部门类型=CASE depart.部门名 WHEN 财务处THEN 管理部门 WHEN 人事处THEN 管理部门 WHEN 市场部THEN 市场部门 ENDFROM worker,departWHERE worker.部门号=depart.部门号GO9) 若存在职工号为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)+工作ENDELSEPRINT 不存在该职工号的记录GO2. 假设数据库std_dbs中有3个表，各表逻辑结构设计如下： 学生表s，其结构为：学号（定长字符串，12），姓名（变长字符串，20，非空），性别（定长字符串，2，默认值“男”），年龄（整数，非空）。主键：学号课程表c，其结构为：课程号（定长字符串，3）,课程名称（变长字符串，20）。主键：课程号成绩表sc，其结构为：学号（定长字符串，12）,课程号（定长字符串，3）,成绩（整数，取值0100）。主键：（学号，课程号）。外键：学号，其值取自表“学生”主键“学号”的值；课程号，其值取自表“课程”主键“课程号”的值。请用SQL语句完成下列操作：（1）计算各个学生的总成绩，显示学生的学号、姓名和总成绩，并将成绩从大到小进行排序。答：SELECT s.学号，姓名，SUM（成绩）AS 总成绩 FROM s，sc WHERE s.学号=sc.学号 GROUP BY s.学号，姓名 ORDER BY 总成绩 DESC（2）创建一个视图v1，其结果返回3门90分以上的学生的学号、姓名。答：CREATE VIEW V1 AS SELECT 学号，姓名 FROM s WHERE 学号 IN （SELECT 学号 FROM sc WHERE 成绩=90 GROUP BY 学号 HAVING COUNT（学号）=3）（3）查询与“刘晨”年龄一样的学生。答：SELECT * FROM student WHERE 年龄=（SELECT 年龄 FROM student WHERER 姓名=“刘晨”）（4）查询选修了全部课程的学生姓名。答：SELECT 姓名 FROM student WHERER NOT EXISTS（SELECT * FROM course WHERE NOT EXISTS（SELECT * FROM grade WHERE student.学号=grade.学号 AND course.课程号=grade.课程号）3. 设有如下关系模式： 学生：S(SNO，SNAME，AGE，DEPT）课程：C（CNO，CNAME）教师：T（TNO，TNAME，TITLE，AGE）联系关系：SC(SNO，CNO,SCORE）TC(TNO,CNO)其中，TITLE为该教师的职称。请用SQL语句完成下列操作：（1）查找给学生“刘芳”上“操作系统”课程的教师姓名。答：SELECT TNAME FROM T WHERE TNO IN （SELECT TNO FROM TC WHERE CNO IN（SELECT CNO FROM C WHERE CNAME=操作系统） AND CNO IN (SLECT CNO FROM SC WHERE SNO IN (SELECT SNO FROM S WHERE SNAME=刘芳）（2）查找职称为副教授不讲授英语课的教师号。答：SELECT TNO FROM TC WHERE TNO IN（SELECT TNO FROM T WHERE TITLE=副教授）AND CNO NOT IN （SELECT CNO FROM C WHERE CNAME=英语）（3）查找职称为教授的教师的全部数据。答：SELECT * FROM TEACHER WHERE TITLE=教授（4）查找由3位以上任课教师数的课程名称。答：SELECT CNAME FROM COURSE WHERE TNUM=34. 编写一个存储过程能根据用户提供WPH(物品号)、RCKS（入出库数）进行出库处理。KCB(WPH CHAR(3) NOT NULL,KCS INT)是库存表，其中KCS为库存数；RCKMXB(WPH CHAR(3) NOT NULL,RCKS INT，RCKBZ BIT,RQ DATETIME)是入出库明细表，其中RCKBZ为入出库标志，1为入库，0为出库。答：CREATE PROCEDURE ST_CKCL WPH CHAR(3),RCKS INTASIF CKS=(SELECT KCS FROM KCB WHERE WPH=WPH) BEGIN BEGIN TRANSACTION UPDATE KCB SET KCS=KCS-CKS WHERE WPH=WPH INSERT RCKMXB VALUES (WPH,RCKS,0,GETDATE() COMMIT ENDELSE RAISERROR(库存数不足!,16,1)GO5. 设有图书表TSB和借书表JSB其结构如下：图书表TSB字段名数据类型约束主键外键说明TSHCHAR(10)主键图书号TSMVARCHAR(50)图书名TSCBSVARCHAR(50)图书出版社TSZZVARCHAR(20)图书作者TSSLTINYINT图书数量借书表JSB字段名数据类型约束主键外键说明DZHCHAR(10)主键读者号TSHCHAR(10)主键图书号JSRQSMALLTIME借书日期HSRQSMALLTIME还书日期请编写一个还书处理的存储过程USP_HSCL。答：CREATE PROCEDURE USP_JHSCL DZH CHAR(10)，TSH CHAR(10) AS BEGINBEGIN TRANSACTIONUPDATE TSB SET TSSL=TSSL+1 WHERE TSH=TSHUPDATE JSB SET HSRQ=GETDATE() WHERE DZH=DZH AND TSH=TSHCOMMIT ENDGO6. 编写一个存储过程其功能是向职工表worker插入一条记录，职工表worker其结构为：职工号（整数），姓名：（定长字符串，8），性别：（定长字符串，2，默认值“男”），出生日期（日期型），党