实验二交互式SQL.docx
- 文档编号:2781188
- 上传时间:2023-05-04
- 格式:DOCX
- 页数:41
- 大小:25.79KB
实验二交互式SQL.docx
《实验二交互式SQL.docx》由会员分享,可在线阅读,更多相关《实验二交互式SQL.docx(41页珍藏版)》请在冰点文库上搜索。
实验二交互式SQL
交互式SQL语言
题目:
实验二
(1)交互式SQL语言
姓名Vivian日期2005-1-1
实验内容及完成情况:
(写出每一种类型的SQL测试用例)
(一)数据定义
一、基本表操作
1.建立基本表
1)创建学生表Student,由以下属性组成:
学号SNO(INT型,主码),姓名SNAME(CHAR
型,长度为8,非空唯一),性别SEX(CHAR型,长度为2),所在系DEPTNO(INT型)。
CREATETABLEStudent(
SNO
INT
PRIMARYKEY,
SNAMECHAR(8)NOTNULLUNIQUE,
SEX
CHAR
(2),
DEPTNOINT);
2)创建课程表Course,由以下属性组成:
课程号CNO(INT型),课程名CNAME(CHAR
型,长度为20,非空),授课教师编号TNO(INT型),学分CREDIT(INT型)。
其中(CNO,
TNO)为主码。
CREATETABLECourse(
CNO
INT,
CNAMECHAR(20)
NOTNULL,
TNO
INT,
CREDITINT,
PRIMARYKEY(CNO,TNO));
3)创建学生选课表SC,由以下属性组成:
学号SNO,课程CNO,成绩GRADE。
所有属性
均为INT型,其中(SNO,CNO)为主码。
CREATETABLESC(
SNO
INT,
CNO
INT,
GRADEINT,
PRIMARYKEY(SNO,CNO));
4)创建教师表Teacher,由以下属性组成:
教师编号TNO(INT型,主码),教师姓名
TNAME(CHAR型,长度为8,非空),所在系DEPTNO(INT型)。
CREATETABLETeacher(
TNO
INT
PRIMARYKEY,
TNAMECHAR(8)NOTNULL,
DEPTNOINT);
5)创建系表Dept,由以下属性:
系号DEPTNO(INT型,主码),系名DNAME(CHAR型,
长度为20,非空)。
CREATETABLEDept(
DEPTNOINT
PRIMARYKEY,
DNAMECHAR(20)
NOTNULL);
2.修改基本表
1)在Student表中加入属性AGE(INT型)。
ALTERTABLEStudentADDAGEINT;
2)将Student表中的属性SAGE类型改为SMALLINT型。
ALTERTABLEStudentALTERCOLUMNSAGESMALLINT;
3.删除基本表
1)在所有操作结束后删除STUDENT表。
DROPTABLEStudent;
2)在所有操作结束后删除COURSE表。
DROPTABLECourse;
3)在所有操作结束后删除SC表。
DROPTABLESC;
4)在所有操作结束后删除TEACHER表。
DROPTABLETeacher;
5)在所有操作结束后删除DEPT表。
DROPTABLEDept;
二、索引操作
1.建立索引
1)在Student表上建立关于SNO的唯一索引。
CREATEUNIQUEINDEXstusnoONStudent(SNO);
2)在Course表上建立关于CNO升序的唯一索引。
CREATEUNIQUEINDEXcoucnoONCourse(CNO);
2.删除索引
1)删除Student表上的索引stusno。
DROPINDEXstusno;
2)删除Course表上的索引coucno。
DROPINDEXcoucno;
三、视图操作
1.建立视图
在插入数据的Student基本表上为计算机科学与技术系的学生记录建立一个视图
CS_STUDENT。
CREATEVIEWCS_STUDENTAS
SELECT*
FROMStudent
WHEREDEPTNO=
(SELECTDEPTNO
FROMDept
WHEREDNAME='计算机科学与技术')
WITHCHECKOPTION;
2.删除视图
在操作结束后删除视图CS_STUDENT。
DROPVIEWCS_STUDENT;
(二)数据操作
一、更新操作
1.插入数据
1)向STUDENT表插入下列数据:
1001,张天,男,10,20
1002,李兰,女,10,21
1003,陈铭,男,10,21
1004,刘茜,女,20,21
1005,马朝阳,男,20,22
INSERTINTOStudentVALUES(1001,'张天','男',10,20);
INSERTINTOStudentVALUES(1002,'李兰','女',10,21);
INSERTINTOStudentVALUES(1003,'陈铭','男',10,21);
INSERTINTOStudentVALUES(1004,'刘茜','女',20,21);
INSERTINTOStudentVALUES(1005,'马朝阳','男',20,22);
2)向COURSE表插入下列数据:
1,数据结构,101,4
2,数据库,102,4
3,离散数学,103,4
4,C语言程序设计,101,2
INSERTINTOCourseVALUES(1,'数据结构',101,4);
INSERTINTOCourseVALUES(2,'数据库',102,4);
INSERTINTOCourseVALUES(3,'离散数学',103,4);
INSERTINTOCourseVALUES(4,'C语言程序设计',101,2);
3)向SC表插入下列数据:
1001,1,80
1001,2,85
1001,3,78
1002,1,78
1002,2,82
1002,3,86
1003,1,92
1003,3,90
1004,1,87
1004,4,90
1005,1,85
1005,4,92
INSERTINTOSCVALUES(1001,1,80);
INSERTINTOSCVALUES(1001,2,85);
INSERTINTOSCVALUES(1001,3,78);
INSERTINTOSCVALUES(1002,1,78);
INSERTINTOSCVALUES(1002,2,82);
INSERTINTOSCVALUES(1002,3,86);
INSERTINTOSCVALUES(1003,1,92);
INSERTINTOSCVALUES(1003,3,90);
INSERTINTOSCVALUES(1004,1,87);
INSERTINTOSCVALUES(1004,4,90);
INSERTINTOSCVALUES(1005,1,85);
INSERTINTOSCVALUES(1005,4,92);
4)向TEACHER表插入下列数据:
101,张星,10
102,李珊,10
103,赵天应,10
104,刘田,20
INSERTINTOTeacherVALUES(101,'张星',10);
INSERTINTOTeacherVALUES(102,'李珊',10);
INSERTINTOTeacherVALUES(103,'赵天应',10);
INSERTINTOTeacherVALUES(104,'刘田',20);
5)向DEPT表插入下列数据:
10,计算机科学与技术
20,信息
INSERTINTODeptVALUES(10,'计算机科学与技术');
INSERTINTODeptVALUES(20,'信息');
2.修改数据
将张星老师数据结构课的学生成绩全部加2分
UPDATESC
SETGRADE=GRADE+2
WHERECNOIN
(SELECTCNO
FROMCourse,Teacher
WHERECourse.TNO=Teacher.TNO
ANDTeacher.TNAME='张星');
3.删除数据
删除马朝阳同学的所有选课记录
DELETEFROMSC
WHERESNOIN
(SELECTSNO
FROMStudent
WHERESNAME='马朝阳');
二、查询操作
1.单表查询
1)查询所有学生的信息。
SELECT*
FROMStudent;
结果:
1001张天
男1020
1002李兰
女1021
1003陈铭
男1021
1004刘茜
女2021
1005马朝阳
男2022
2)查询所有女生的姓名。
SELECTSNAME
FROMStudent
WHERESEX='女';
结果:
李兰
刘茜
3)查询成绩在80到89之间的所有学生选课记录,查询结果按成绩的降序排列。
SELECT*
FROMSC
WHEREGRADE>=80
ANDGRADE<=89
ORDERBYGRADEDESC;
结果:
10041
87
10023
86
10012
85
10051
85
10022
82
10011
80
4)查询各个系的学生人数。
SELECTDEPTNO,count(SNO)
FROMStudent
GROUPBYDEPTNO;
结果:
10
3
20
2
2.连接查询
查询信息系年龄在21岁以下(含21岁)的女生姓名及其年龄。
SELECTSNAME,AGE
FROMStudent,Dept
WHEREStudent.DEPTNO=Dept.DEPTNO
ANDDept.DNAME='信息'
ANDAGE<=21
ANDSEX='女';
结果:
刘茜
21
3.嵌套查询
1)查询修课总学分在10学分以下的学生姓名。
SELECTSNAME
FROMStudent
WHERESNOIN
(SELECTSNO
FROMSC,Course
WHERESC.CNO=Course.CNO
GROUPBYSNO
HAVINGSUM(CREDIT)<10);
结果:
陈铭
刘茜
马朝阳
2)查询各门课程取得最高成绩的学生姓名及其成绩。
SELECTCNO,SNAME,GRADE
FROMStudent,SCSCX
WHEREStudent.SNO=SCX.SNOANDSCX.GRADEIN
(SELECTMAX(GRADE)
FROMSCSCY
WHERESCX.CNO=SCY.CNO
GROUPBYCNO);
结果:
2张天
85
3陈铭
92
1陈铭
90
4马朝阳
92
3)查询选修了1001学生选修的全部课程的学生学号。
SELECTSNO
FROMStudent
WHERENOTEXISTS
(SELECT*
FROMSCSCX
WHERESCX.SNO=1001ANDNOTEXISTS
(SELECT*
FROMSCSCY
WHERESCY.SNO=Student.SNOANDSCY.cno=SCX.cno));
结果:
1001
1002
4)查询选修了张星老师开设的全部课程的学生姓名。
SELECTSNAME
FROMStudent
WHERENOTEXISTS
(SELECT*
FROMCourse
WHERETNOIN
(SELECTTNO
FROMTeacher
WHERETNAME='张星')ANDNOTEXISTS
(SELECT*
FROMSC
WHERESC.SNO=Student.SNOANDSC.CNO=Course.CNO));
结果:
刘茜
马朝阳
出现的问题及解决方案:
1.在创建基本表时是否可以缺省主码?
可以。
在定义基本表时可以定义主码也可以先不定义主码。
2.对基本表进行修改,执行ALTERTABLEStudentMODIFYSageSMALLINT显示执行失败。
当基本表中没有数据时,KingbaseES允许对基本表的属性类型进行修改,如上述修改
可以写成ALTERTABLEStudentALTERCOLUMNSAGESMALLINT,执行成功。
但如果基本表中已经存有数据时,系统则会给出数据将丢失的警告,不允许进行属性类型的修改。
3.在SQLServer中没有提供删除属性列的语句,KingbaseES则支持删除属性列。
如果要在基本表Student上删除属性列SNAME,可以执行ALTERTABLEStudentDROPSNAME。
题目:
实验二
(2)交互式SQL语言
姓名
Vivian
日期2005-1-1
实验内容及完成情况:
(写出每一种类型的SQL测试用例)
(一)数据定义
一、基本表操作
1.建立基本表
1)创建供应商表S,由以下属性组成:
供应商号SNO(CHAR型),供应商姓名SNAME(CHAR
型),供应商状态STATUS(CHAR型),供应商所在城市CITY(CHAR型)。
CREATETABLES(
SNO
CHAR(3),
SNAMECHAR(10),
STATUSCHAR
(2),
CITY
CHAR(10));
2)创建零件表P,由以下属性组成:
零件代码PNO(CHAR型),零件名PNAME(CHAR型),
颜色COLOR(CHAR型),重量WEIGHT(INT型)。
CREATETABLEP(
PNO
CHAR(3),
PNAMECHAR(10),
COLORCHAR(4),
WEIGHTINT);
3)创建工程项目表J,由以下属性组成:
工程项目代码JNO(CHAR型),工程项目名JNAME
(CHAR型),工程项目所在城市CITY(CHAR型)。
CREATETABLEJ(
JNO
CHAR(3),
JNAMECHAR(10),
CITY
CHAR(10));
4)创建供应情况表SPJ,由以下属性组成:
供应商代码SNO(CHAR型),零件代码PNO
(CHAR型),工程项目代码JNO(CHAR型),供应数量QTY(INT型)。
CREATETABLESPJ(
SNO
CHAR(3),
PNO
CHAR(3),
JNO
CHAR(3),
QTY
INT);
2.修改基本表
1)在P表中加入属性零件产地CITY(CHAR型)。
ALTERTABLEPADDCITYCHAR(10);
2)将P表中的属性WEIGHT类型改为SMALLINT型。
ALTERTABLEPALTERCOLUMNWEIGHTSMALLINT;
3)删除刚才在P表中加入的零件产地CITY属性。
ALTERTABLEPDROPCITY;
3.删除基本表
1)在所有操作结束后删除S表。
DROPTABLES;
2)在所有操作结束后删除P表。
DROPTABLEP;
3)在所有操作结束后删除J表。
DROPTABLEJ;
4)在所有操作结束后删除SPJ表。
DROPTABLESPJ;
二、索引操作
1.建立索引
1)在S表上建立关于SNO的唯一索引。
CREATEUNIQUEINDEXsupply_snoONS(SNO);
2)在J表上建立关于JNO升序的唯一索引。
CREATEUNIQUEINDEXproject_jnoONJ(JNO);
2.删除索引
1)删除S表上的索引supply_sno。
DROPINDEXsupply_sno;
2)删除J表上的索引project_jno。
DROPINDEXproject_jno;
三、视图操作
1.建立视图
在插入数据的S,P,J,SPJ基本表上为三建工程项目记录建立一个视图V_SPJ,包括供应
商代码SNO,零件代码PNO,供应数量QTY。
CREATEVIESV_SPJAS
SELECTSNO,PNO,QTY
FROMSPJ
WHEREJNO=(
SELECTJNO
FROMJ
WHEREJNAME='三建');
2.根据视图完成查询
1)查询三建工程项目中供应商S1的供应情况。
SELECTPNO,QTY
FROMV_SPJ
WHERESNO=‘S1’;
查询结果为:
P1
200
2)查询三建工程项目使用的各种零件代码及其数量。
SELECTPNO,QTY
FROMV_SPJ;
查询结果为:
P1
200
P3
400
P5
400
P1
200
P3
200
P5
100
P3
200
3.删除视图
在操作结束后删除视图V_SPJ。
DROPVIEWV_SPJ;
(二)数据操作
一、更新操作
1.插入数据
1)向S表插入下列数据:
S1,精益,20,天津
S2,盛锡,10,北京
S3,东方红,30,北京
S4,丰泰盛,20,天津
S5,为民,30,上海
INSERTINTOSVALUES('S1','精益','20','天津');
INSERTINTOSVALUES('S2','盛锡','10','北京');
INSERTINTOSVALUES('S3','东方红','30','北京');
INSERTINTOSVALUES('S4','丰泰盛','20','天津');
INSERTINTOSVALUES('S5','为民','30','上海');
2)向P表插入下列数据:
P1,螺母,红,12
P2,螺栓,绿,17
P3,螺丝刀,蓝,14
P4,螺丝刀,红,14
P5,凸轮,蓝,40
P6,齿轮,红,30
INSERTINTOPVALUES('P1','螺母','红',12);
INSERTINTOPVALUES('P2','螺栓','绿',17);
INSERTINTOPVALUES('P3','螺丝刀','蓝',14);
INSERTINTOPVALUES('P4','螺丝刀','红',14);
INSERTINTOPVALUES('P5','凸轮','蓝',40);
INSERTINTOPVALUES('P6','齿轮','红',30);
3)向J表插入下列数据:
J1,三建,北京
J2,一汽,长春
J3,弹簧厂,天津
J4,造船厂,天津
J5,机车厂,唐山
J6,无线电厂,常州
J7,半导体厂,南京
INSERTINTOJVALUES('J1','三建','北京');
INSERTINTOJVALUES('J2','一汽','长春');
INSERTINTOJVALUES('J3','弹簧厂','天津');
INSERTINTOJVALUES('J4','造船厂','天津');
INSERTINTOJVALUES('J5','机车厂','唐山');
INSERTINTOJVALUES('J6','无线电厂','常州');
INSERTINTOJVALUES('J7','半导体厂','南京');
4)向SPJ表插入下列数据:
S1,P1,J1,200
S1,P1,J3,100
S1,P1,J4,700
S2,P3,J1,400
S1,P2,J2,100
S2,P3,J2,200
S2,P3,J4,500
S2,P3,J5,400
S2,P5,J1,400
S2,P5,J2,100
S3,P1,J1,200
S3,P3,J1,200
S4,P5,J1,100
S4,P6,J3,300
S4,P6,J4,200
S5,P2,J4,100
S5,P3,J1,200
S5,P6,J2,200
S5,P6,J4,500
INSERTINTOSPJVALUES('S1','P1','J1',200);
INSERTINTOSPJVALUES('S1','P1','J3',100);
INSERTINTOSPJVALUES('S1','P1','J4',700);
INSERTINTOSPJVALUES('S1','P2','J2',100);
INSERTINTOSPJVALUES('S2','P3','J1',400);
INSERTINTOSPJVALUES('S2','P3','J2',200);
INSERTINTOSPJVALUES('S2','P3','J4',500);
INSERTINTOSPJVALUES('S2','P3','J5',400);
INSERTINTOSPJVALUES('S2','P5','J1',400);
INSERTINTOSPJVALUES('S2','P5','J2',100);
INSERTINTOSPJVALUES('S3','P1','J1',200);
INSERTINTOSPJVALUES('S3','P3','J1',200);
INSERTINTOSPJVALUES('S4','P5','J1',100);
INSERTINTOSPJVALUES('S4','P6','J3',300);
INSERTINTOSPJVALUES('S4','P6','J4',200);
INSERTINTOSPJVALUES('S5','P2','J4',100);
INSERTINTOSPJVALUES('S5','P3','J1',200);
INSERTINTOSPJVALUES('S5','P6','J2',200);
INSERTINTOSPJVALUES('S5','P6','J4',500);
2.修改数据
1)将全部红色零件的颜色改成蓝色。
UPDATEP
SETCOLOR=‘蓝’
WHERECOLOR=‘红’;
2)将由S5供给J4的零件P6改成由S3供应。
UPDATESPJ
SETSNO=‘S3’
WHERESNO=‘S5’
ANDJNO=‘J4’
ANDPNO=‘P6’;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验 交互式 SQL