西安交大自动化数据库系统上机报告.docx

一题目要求1.从5(user=pwd=student)下载db2exc_971_WIN_x86.zip(DB2 UDBV9.7安装包), 解压后将DB2 UDB V9.7 安装到机器上。2.对DB2 UDB进行适当配置，并通过DB2控制中心或命令行处理器(CLP)建立到DB2 UDB数据库服务器(主机地址：3)的连接。3.以学号作为用户名和口令连接到课程实验数据库(Course)。二实验平台 IBM DB2 UDB V9.7 DB2是 IBM公司研制的一种关系型数据库管理系统。UDB是Universal Database，即通用数据库的简称。三 操作过程1. 在课程实验数据库中创建学生(S)、课程(C)、选课(SC)等3个基本表，各表包含以下属性：S（SNO(PK)，SNAME，SEX，BDATE，HEIGHT，DORM）C（CNO(PK)，CNAME, LHOUR，CREDIT，SEMESTER）SC（SNO(FK)，CNO(FK)，GRADE）其中SNO、CNO均为外键所创建上述基本表的DB2 SQL DDL语句分别为：课程表C:CONNECT TO TESTDB USER 10054130;CREATE TABLE 10054130.SZCL( SNO CHARACTER (8) NOT NULL , SNAME CHARACTER (30) NOT NULL , SEX CHARACTER (4) NOT NULL , BDATE DATE NOT NULL , HEIGHT DECIMAL (3, 2) NOT NULL , DORM VARGRAPHIC (30) NOT NULL , CONSTRAINT CC1383048888187 PRIMARY KEY ( SNO) ) ;学生S:CONNECT TO TESTDB USER 10054130;CREATE TABLE 10054130.CZCL ( CNO CHARACTER(5) NOT NULL , CNAME VARGRAPHIC (50) NOT NULL , LOHOUR INTEGER NOT NULL , CREDIT INTEGER NOT NULL , SEMESTER VARGRAPHIC(4) NOT NULL , CONSTRAINT CC1383048888187 PRIMARY KEY ( CNO) ) ;选课表SCZCL:CONNECT TO TESTDB USER 10054130;CREATE TABLE 10054130.SCZCL( SNO CHARACTER (8) NOT NULL , CNO CHARACTER (5) NOT NULL , GRADE DECIMAL (3, 1) NOT NULL , CONSTRAINT CC1383051688562 FOREIGN KEY (SNO) REFERENCES 10054130.S(SNO) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION , CONSTRAINT CC1383051698640 FOREIGN KEY (CNO) REFERENCES 10054130.C (CNO) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ) ; 2.将以下数据加入相应的表中：学生表S:SNOSNAMESEXBDATEHEIGHTDORM01032010李涛男1982-4-51.72西14舍22101032023孙文男1983-6-101.80西14舍22101032001张晓梅女1983-11-171.58西1舍31201032005刘静女1982-1-101.63西1舍31201032112董卫锋男1982-2-201.71西14舍22103031011王倩女1983-12-201.66西2舍10403031014赵思扬男1981-6-61.85西18舍42103031051周剑男1981-5-81.68西18舍42203031009田婷婷女1982-8-111.60西2舍10403031033蔡明亮男1982-3-121.75西18舍423课程表C:CNOCNAMELHOURCREDITSEMESTERCS101C语言程序设计603春CS102计算机原理804春CS103数据结构603秋CS104数字电路603秋EE101JAVA语言603春EE102程序设计方法1005秋选课表SC:SNOCNOGRADE01032010CS10182.001032010CS10291.001032010CS10383.501032001CS10177.501032001CS10285.001032001CS10383.001032005CS10162.001032005CS10277.001032005CS10382.001032023CS10155.001032023CS10281.001032023CS10376.001032112CS10188.001032112CS10291.501032112CS10386.003031033EE10193.003031033EE10289.003031009EE10188.003031009EE10278.503031011EE10191.003031011EE10286.003031051EE10178.003031051EE10258.003031014EE10179.003031014EE10271.0插入上述数据的DB2 SQL DML语句为：学生表S:INSERT INTO SZCL (SNO, SNAME, SEX,BDATE,HEIGHT,DORM)VALUES(01032010,李涛,男,1982-4-5,1.72,西14舍211),(01032023,孙文, 男,1983-6-10,1.80,西14舍221),(01032001,张晓梅,女,1983-11-17,1.58,西1舍312),(01032005,刘静, 女,1982-1-10,1.63,西1舍312),(01032112,董卫锋,男,1982-2-20,1.71,西14舍221),(03031011,王倩, 女,1983-12-20,1.66,西2舍104),(03031014,赵思扬,男,1981-6-6,1.85,西18舍421),(03031051,周剑,男,1981-5-8,1.68,西18舍422),(03031009,田婷婷,女,1982-8-11,1.60,西2舍104),(03031033,蔡明亮,男,1982-3-12,1.75,西18舍423),(10054130,左长乐,男,1991-9-16,170,东三舍415);课程表CZCL:INSERT INTO CZCL (CNO,CNAME,LOHOUR,CREDIT,SEMESTER)VALUES(CS101,C语言程序设计,60,3,春),(CS102,计算机原理,80,4,春),(CS103,数据结构,60,3,秋),(CS104,数字电路,60,3,秋),(EE101,JAVA语言,60,3,春),(EE102,程序设计方法,100,5,秋),(EE,自动控制原理,100,5,秋);选课表SCZCL:INSERT INTO SCZCL(SNO,CNO,GRADE)VALUES(01032010,CS101,82.0),(01032010,CS102,91.0),(01032010,CS103,83.5),(01032001,CS101,77.5),(01032001,CS102,85.0),(01032001,CS103,83.0),(01032005,CS101,62.0),(01032005,CS102,77.0),(01032005,CS103,82.0),(01032023,CS101,55.0),(01032023,CS102,81.0),(01032023,CS103,76.0),(01032112,CS101,88.0),(01032112,CS102,91.5),(01032112,CS103,86.0),(03031033,EE101,93.0),(03031033,EE102,89.0),(03031009,EE101,88.0),(03031009,EE102,78.5),(03031011,EE101,91.0),(03031011,EE102,86.0),(03031051,EE101,78.0),(03031051,EE102,58.0),(03031014,EE101,79.0),(03031014,EE102,71.0),(10054130,EE101,95.0),(10054130,EE102,98.0);3. 用SQL写出以下查询： 1)查询选修课程“CS102”的学生学号、成绩；SELECT SNO,GRADE FROM SCZCL WHERE CNO=CS102;2)查询选修课程“EE101”的女学生姓名；SELECT SNAME FROM S,SCZCL WHERE SZCL.SEX=女 AND CNO=EE101 AND SZCL.SNO=SCZCL.SNO;3)查询不选修课程“CS102”的学生姓名；SELECT SNAME FROM SZCL WHERE SZCL.SNO NOT IN(SELECT SNO FROM SCZCL WHERE CNO=CS102);4)查询所有比“李涛”年龄大的学生姓名、性别、年龄；SELECT SNAME,SEX,2013-year(BDATE) FROM SZCL WHERE BDATE80);8)查询所有未被选修的课程编号和名称；SELECT CNO,CNAME FROM SCZCL WHERE SCZCL.CNO NOT IN(SELECT CNO FROM SCZCL);9)查询选修三门以上课程（包括三门）的学生总成绩(不统计不及格的课程)，并要求按总成绩的降序排列出来；SELECT SNO,SUM(GRADE) AS GSUM FROM SCZCL X WHERE GRADE =60 GROUP BY SNO HAVING COUNT(*) =3 ORDER BY GSUM DESCZCL