数据库系统概论 实01 交互式SQL.docVIP

（一）数据定义 CREATE TABLE Student (SNO INT PRIMARY KEY, SNAME CHAR(8) NOT NULL, SEX CHAR(2), DEPTNO INT); CREATE TABLE Course (CNO INT, CNAME CHAR(20) NOT NULL, 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) NOT NULL, DEPTNO INT); CREATE TABLE Dept (DEPTNO INT PRIMARY KEY, DNAME CHAR(20) NOT NULL); ALTER TABLE Student ADD SAGE INT; ALTER TABLE Student ALTER COLUMN SAGE SMALLINT; DROP TABLE Student; DROP TABLE Course; DROP TABLE SC; DROP TABLE Teacher; DROP TABLE Dept; CREATE UNIQUE INDEX Stusno ON Student(SNO); CREATE UNIQUE INDEX Coucno ON Course(CNO ASC); DROP INDEX Stusno; DROP INDEX Coucno; CREATE VIEW CS_Student AS SELECT SNO,SNAME,SEX,DEPTNO,SAGE FROM Student WHERE DEPTNO=CS; DROP VIEW CS_Student; （二）数据操作 INSERT INTO Student (SNO,SNAME,SEX,DEPTNO,SAGE) VALUES(1001,张成,男,10,20); INSERT INTO Student (SNO,SNAME,SEX,DEPTNO,SAGE) VALUES(1002,李兰,女,10,21); INSERT INTO Student (SNO,SNAME,SEX,DEPTNO,SAGE) VALUES(1003,陈铭,男,10,21); INSERT INTO Student (SNO,SNAME,SEX,DEPTNO,SAGE) VALUES(1004,刘茜,女,20,21); INSERT INTO Student (SNO,SNAME,SEX,DEPTNO,SAGE) 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