数据库上机试验报告dk

（6）

1．创建视图

CREATE VIEW S_AG(S#, AVG) AS SELECT S#, AVG(GRADE) FROM JSJ86_SC140 GROUP BY S# 2．查看视图 SELECT * FROM S_AG

3.执行查询

SELECT S_AG.S#, SNAME, AVG FROM JSJ86_S140, S_AG

WHERE JSJ86_S140.S# = S_AG.S# AND AVG > (SELECT AVG FROM S_AG WHERE (S# IN

(SELECT DISTINCT S# FROM JSJ86_S140

WHERE (SNAME = '王涛')))) ORDER BY S_AG.S# DESC (7)

1.创建视图

CREATE VIEW C_NUM(S#, C_NUM) AS SELECT S#, COUNT(*) FROM JSJ86_SC140 GROUP BY S# 2.查看视图

3.执行查询

SELECT MAX(AVG) FROM S_AG WHERE S# IN

(SELECT S# FROM C_NUM

WHERE (C_NUM >= 3))

2、分别在JSJ86_S140和JSJ86_C140表中加入记录‘(01032005’，‘刘静’，‘男’，

‘1983-12-10’，1.75，‘西14舍312’)及(‘CS03’，“离散数学”，64，4，‘陈建明’)。

解：执行第一条插入语句：

INSERT INTO JSJ86_S140

(S#, SNAME, SEX, BDATE, HEIGHT, DORM)

VALUES ('01032005', '刘静', '男', '1983-12-10', 1.75, '西14舍312')

语句执行后JSJ86_S140表中数据：

执行第二条插入语句

INSERT INTO JSJ86_C140

(C#, CNAME, PERIOD, CREDIT, TEACHER) VALUES ('CS-03', '离散数学', 64, 4, '陈建明')