发布时间 : 星期六 文章数据库上机试验报告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, '陈建明')