资源描述:
《数据库系统概论 实验01 交互式sql》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、(一)数据定义CREATETABLEStudent(SNOINTPRIMARYKEY,SNAMECHAR(8)NOTNULL,SEXCHAR(2),DEPTNOINT);CREATETABLECourse(CNOINT,CNAMECHAR(20)NOTNULL,TNOINT,CREDITINT,PRIMARYKEY(CNO,TNO));CREATETABLESC(SNOINT,CNOINT,GRADEINT,PRIMARYKEY(SNO,CNO));CREATETABLETeacher(TNOINTPRIM
2、ARYKEY,TNAMECHAR(8)NOTNULL,DEPTNOINT);CREATETABLEDept(DEPTNOINTPRIMARYKEY,DNAMECHAR(20)NOTNULL);ALTERTABLEStudentADDSAGEINT;ALTERTABLEStudentALTERCOLUMNSAGESMALLINT;DROPTABLEStudent;DROPTABLECourse;DROPTABLESC;DROPTABLETeacher;DROPTABLEDept;CREATEUNIQUEIND
3、EXStusnoONStudent(SNO);CREATEUNIQUEINDEXCoucnoONCourse(CNOASC);DROPINDEXStusno;DROPINDEXCoucno;CREATEVIEWCS_StudentASSELECTSNO,SNAME,SEX,DEPTNO,SAGEFROMStudentWHEREDEPTNO='CS';DROPVIEWCS_Student;(二)数据操作INSERTINTOStudent(SNO,SNAME,SEX,DEPTNO,SAGE)VALUES('10
4、01','张成','男',10,20);INSERTINTOStudent(SNO,SNAME,SEX,DEPTNO,SAGE)VALUES('1002','李兰','女',10,21);INSERTINTOStudent(SNO,SNAME,SEX,DEPTNO,SAGE)VALUES('1003','陈铭','男',10,21);INSERTINTOStudent(SNO,SNAME,SEX,DEPTNO,SAGE)VALUES('1004','刘茜','女',20,21);INSERTINTOStud
5、ent(SNO,SNAME,SEX,DEPTNO,SAGE)VALUES('1005','马朝阳','男',20,22);INSERTINTOCourse(CNO,CNAME,TNO,CREDIT)VALUES('1','数据结构','101',4);INSERTINTOCourse(CNO,CNAME,TNO,CREDIT)VALUES('2','数据库','102',4);INSERTINTOCourse(CNO,CNAME,TNO,CREDIT)VALUES('3','离散数学','103',4);I
6、NSERTINTOCourse(CNO,CNAME,TNO,CREDIT)VALUES('4','C语言程序设计','101',2);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1001','1',80);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1001','2',85);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1001','3',78);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1002','
7、1',78);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1002','2',82);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1002','3',86);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1003','1',92);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1003','3',90);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1004','1',87);INSE
8、RTINTOSC(SNO,CNO,GRADE)VALUES('1004','4',90);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1005','1',85);INSERTINTOSC(SNO,CNO,GRADE)VALUES('1005','4',92);INSERTINTOTeacher(TNO,TNAME,DEPTNO)VALUES('101','张星','10');INSERT