实验一 交互式SQL(内附答案).docVIP

create table STUDENT (SNO int primary key, SNAME char(8) unique, SSEX char(2), DEPTNO int ); create table COURSE (CNO int , CNAME char(20) unique, TNO int, CREDIT int, primary key(CNO,TNO) ); create table SC (SNO int, CNO int, GRADE int, primary key(SNO,CNO) ); create table TEACHER (TNO int primary key, TNAME char(8) unique, DEPTNO int ); create table DEPT (DEPTNO int primary key, DEPTNAME char(20) unique ); alter table STUDENT add SAGE int; alter table STUDENT alter column SAGE smallint; drop table STUDENT ; drop table COURSE ; drop table DEPT ; drop table SC ; drop table TEACHER ; create unique index Stusno on STUDENT(SNO); create unique index Coucno ON COURSE(CNO); drop index Stusno; drop index Coucno; CREATE VIEW CS_STUDENT AS SELECT SNO,SNAME,SEX FROM STUDENT WHERE DEPTNAME = 计算机科学与技术; DROP VIEW CS__STUDENT; INSERT INTO STUDENT(SNO,SNAME,SSEX,DEPTNO,SAGE) VALUES (1001,张天,男,10,20); INSERT INTO Student VALUES (1002,李兰,女,10,21); INSERT INTO Student VALUES (1003,陈明,男,10,21); INSERT INTO Student VALUES (1004,刘茜,女,20,21); INSERT INTO Student VALUES (1005,马朝阳,男,20,22); INSERT INTO Course(CNO,CNAME,TNO,CREDIT) VALUES (1,数据结构,101,4); INSERT INTO Course(CNO,CNAME,TNO,CREDIT) VALUES (2,数据库,102,4); INSERT INTO Course(CNO,CNAME,TNO,CREDIT) VALUES (3,离散数学,103,4); INSERT INTO Course(CNO,CNAME,TNO,CREDIT) VALUES (4,C语言程序设计,101,2); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1001,1,80); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1001,2,85); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1001,3,78); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1002,1,78); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1002,2,82); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1002,3,86); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1003,1,92); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1003,3,90); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1004,1,87); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1004,4,90); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1005,1,85); INSERT INTO SC(SNO,CNO,GRADE) VALUES (1005,4,92); INSERT INTO Teacher VALUES (101,张星,10); I