数据库上机作业6

发布于 2018-06-13  11 次阅读


1. 定义视图,统计每门课程不同分数段的人数、平均成绩、方差、最高成绩、最低成绩。分数段为:100,95~99,90~94,85~89,80~84,75~79,70~74,65~69,60~64,60以下。字段为:课号,课名,分数段,平均成绩,方差,最高成绩,最低成绩。(20)

CREATE VIEW GRADE_LEVEL(CNO,CNAME,INTERVAL,AVGS,STDS,MAXS,MINS)
AS SELECT SCORE.CNO,CNAME,
CASE WHEN SCORE=100 THEN '100'
WHEN SCORE BETWEEN 95 AND 99 THEN '95-99'
WHEN SCORE BETWEEN 90 AND 95 THEN '90-94'
WHEN SCORE BETWEEN 85 AND 89 THEN '85-89'
WHEN SCORE BETWEEN 80 AND 84 THEN '80-84'
WHEN SCORE BETWEEN 75 AND 79 THEN '75-79'
WHEN SCORE BETWEEN 70 AND 74 THEN '70-74'
WHEN SCORE BETWEEN 65 AND 69 THEN '65-69'
WHEN SCORE BETWEEN 60 AND 64 THEN '60-64'
WHEN SCORE BETWEEN 0 AND 59 THEN '0-59'
END INTERVAL,
AVG(SCORE) AVGS,STDDEV(SCORE) STDS,MAX(SCORE) MAXS,MIN(SCORE) MINS
FROM SCORE,COURSE
WHERE SCORE.CNO=COURSE.CNO
GROUP BY SCORE.CNO,CNAME,
CASE WHEN SCORE=100 THEN '100'
WHEN SCORE BETWEEN 95 AND 99 THEN '95-99'
WHEN SCORE BETWEEN 90 AND 95 THEN '90-94'
WHEN SCORE BETWEEN 85 AND 89 THEN '85-89'
WHEN SCORE BETWEEN 80 AND 84 THEN '80-84'
WHEN SCORE BETWEEN 75 AND 79 THEN '75-79'
WHEN SCORE BETWEEN 70 AND 74 THEN '70-74'
WHEN SCORE BETWEEN 65 AND 69 THEN '65-69'
WHEN SCORE BETWEEN 60 AND 64 THEN '60-64'
WHEN SCORE BETWEEN 0 AND 59 THEN '0-59'
END;

(2)记录选课表的更新、删除和插入元组的时间和用户及相应的值,使用触发器实现(20)

CREATE TABLE SCORE_MODIFY
(
ACTION CHAR(10),
SNO CHAR(10),
CNO CHAR(6),
SCORE NUMERIC(3),,
M_DATE CHAR(20)
);
CREATE TRIGGER TR_SC_MODIFY
AFTER INSERT OR UPDATE OR DELETE ON SCORE
REFERENCING OLD AS OLDTUPLE NEW AS NEWTUPLE
FOR EACH ROW
BEGIN 
IF INSERTING THEN
INSERT INTO SCORE_MODIFY(ACTION,SNO,CNO,SCORE,M_DATE)
VALUES('INSERT',:NEWTUPLE.SNO,:NEWTUPLE.CNO,:NEWTUPLE.SCORE,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
ELSIF UPDATING THEN
INSERT INTO SCORE_MODIFY(ACTION,SNO,CNO,SCORE,M_DATE)
VALUES('UPDATE',:NEWTUPLE.SNO,:NEWTUPLE.CNO,:NEWTUPLE.SCORE,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
ELSIF DELETING THEN
INSERT INTO SCORE_MODIFY(ACTION,SNO,CNO,SCORE,M_DATE)
VALUES('DELETE',:OLDTUPLE.SNO,:OLDTUPLE.CNO,:OLDTUPLE.SCORE,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END IF;
END;

(3)定义视图,显示学生基本信息,字段为:学号,姓名,年龄。(5)

CREATE VIEW INFO_STUDENT
AS
SELECT SNO,SNAME,TRUNC((TO_CHAR(SYSDATE, 'YYYYMMDD') - TO_CHAR(SBIRTHDAY, 'YYYYMMDD')) /10000) AS AGE
FROM STUDENT
WITH CHECK OPTION;

(4)在第3题的定义的视图上定义触发器,实现可以通过此视图进行学生基本信息的录入(15)

CREATE TRIGGER STUDENT_INFO_TRIGGER 
INSTEAD OF INSERT ON INFO_STUDENT
FOR EACH ROW
BEGIN
  INSERT INTO STUDENT(SNO,SNAME,SBIRTHDAY) 
  VALUES(:NEW.SNO,:NEW.SNAME,ADD_MONTHS(SYSDATE, -12*:NEW.AGE) );
END;

(5)查询选修了课程的学生人数;(5)

SELECT COUNT(DISTINCT SNO) FROM SCORE;

(6)在所有课程中查询最高分的学生学号和成绩;(5)

SELECT SNO,SCORE FROM SCORE WHERE SCORE IN (SELECT MAX(SCORE) FROM SCORE);

(7) 查询每一门课程的间接先行课(即先行课的先行课);(5)

SELECT FIRST.CNO,SECOND.CPNO
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.CPNO=SECOND.CNO;

(8) 自然连接student和score表;(5)

SELECT STUDENT.SNO,SNAME,SBIRTHDAY,SSEX,SCLASS,SREMARK,ADDRESS,ZIPCODE,
PHONE,EMAIL,CNO,SCORE
FROM STUDENT,SCORE
WHERE STUDENT.SNO=SCORE.SNO;

(9)计算各个课程号与相应的选课人数;(5)

SELECT CNO,COUNT(SNO)
FROM SCORE
GROUP BY CNO;

(10) 查询选修了全部课程的学生;(5)

SELECT SNO
FROM SCORE 
GROUP BY SNO
HAVING COUNT(CNO)IN (SELECT COUNT(CNO)
FROM COURSE);

(11) 查询“会计系0102”班的学生及年龄不大于27岁(现有年龄)

的学生;(5)

SELECT SNO, SNAME 
FROM STUDENT
WHERE TRUNC((TO_CHAR(SYSDATE, 'YYYYMMDD') - TO_CHAR(SBIRTHDAY, 'YYYYMMDD')) /10000)
<27 AND SCLASS='会计系0102';

(12) 查询至少选修全部学分数为4个学分的课程的学生的学号、姓名;(5)

SELECT SNO,SNAME
FROM STUDENT
WHERE SNO IN(
SELECT STUDENT.SNO
FROM STUDENT,SCORE,COURSE
WHERE STUDENT.SNO=SCORE.SNO AND SCORE.CNO=COURSE.CNO
GROUP BY STUDENT.SNO
HAVING SUM(CTIME)>=4
);