SQL实验及部分答案.doc

实验一 使用SQL语句创建和删除数据库一. 实验目的：1、 了解SQL Server 2005数据库的逻辑结构和物理结构。2、 掌握使用SQL 语句创建和删除数据库。二. 实验准备1 明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。2 了解用SQL语句创建和删除数据库的基本语法。三. 实验要求3、 熟练使用查询分析器进行数据库的创建和删除操作4、 完成用sql语句建立和删除数据库的实验报告四. 实验内容 1以下是创建数据库userdb1的SQL语句，以下是创建数据库userdb1的SQL语句， CREATE DATABASE userdb1 -创建名为userdb1的数据库on(NAME=userdb2,-数据文件的逻辑名称为userdb2FILENAME= d:testuserdb1.mdf, -物理路径为d:testSIZE=5mb,-数据初始长度为5MMAXSIZE=10mb, -最大长度为10MFILEGROWTH=1mb -数据文件每次增长1M)运行上诉语句建立数据库userdb1.2.用SQL语句删除步骤一建立的数据库userdb1。drop database userdb1 -删除数据库userdb1实验二 分别用向导和SQL语句创建和删除表一用SQL语句创建表在SQL Server 2005 的查询分析器中，用sql语句创建student表。 表2.1 student表（学生信息表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号snamechar8NOT NULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系spnochar8NULL专业代码（外键）classnochar4NULL班级号entimesmalldate4NULL学生入校时间homevarchar40NULL学生家庭住址telvarchar40NULL学生联系电话CREATE TABLE student(sno char(8) not null primary key, -学号(主键)sname char(8) not null,-姓名sex char(2) null,-性别native char(20) null,-籍贯birthday datetime null,-出生日期dno char(6) null,-所在院系spno char(8) null,-专业代码classno char(4) null,-班级号entime datetime null,-入校时间home varchar(40) null,-家庭住址tel varchar(40) null,-联系电话)表2.2 course表（课程信息表）字段名称类 型宽 度允许空值主 键说 明cnochar10NOT NULL是课程编号spnochar8NULL专业代码（外键）cnamechar20NOT NULL课程名称ctnotinyint1NULL课程类型编号（外键）experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分 CREATE TABLE course(cno char(10) not null primary key, spno char(8) null,cname char(20) not null,ctno tinyint null,experiment tinyint null,lecture tinyint null,semester tinyint null,credit tinyint null)实验三 修改数据库基本表的定义一 实验目的1了解表的结构特点。3学会使用SQL语句修改表的定义。二 实验准备1. 了解常用的修改表定义语句。三 实验要求1. 完成在已经创建成功的表上修改表定义的操作四 实验内容1 修改列属性（1） 用SQL语句将Student表中的属性sno char(8)改成varchar(20)类型。alter table studentalter column sno varchar(20)2 添加列（1） 用SQL语句在Course表中添加一列year ，类型为varchar（4），默认置为空。alter table courseadd year varchar(20) null（2） 用SQL语句在year字段添加约束，year 的属性值在2004-2008之间。 alter table courseadd constraint year_constraint check(year=2004 and year=2008) 3 删除列 （1）用SQL语句将Course表中的year字段删除。 alter table coursedrop constraint year_constraintalter table coursedrop column year实验四 创建和删除索引一 实验目的1 了解索引的类型和应用。2 学会用SQL语句对表创建和删除索引二 实验准备2. 了解创建和删除索引的方法。三 实验要求1. 了解索引类型并比较各类索引的不同之处2. 完成索引的创建和删除，并提交实验报告。四 实验内容 1. 分别建立以下索引(如果不能成功建立,请分析原因)(1) 在student表的sname列上建立普通降序索引。CREATE INDEX sname ON Student(Sname deSC)(2) 在course表的cname列上建立唯一索引。CREATE unique INDEX cname ON course(cname)(3) 在student_course表的sno列上建立聚集索引。create CLUSTERED INDEX student_sno on student(sno)无法对表student 创建多个聚集索引。在创建新聚集索引前应该删除已有的聚集索引PK_student。2. 删除索引 将student_course表的sno列上的聚集索引删掉。alter table student drop constraint PK_student实验五 SELECT语句一 实验目的1观察查询结果, 体会SELECT语句实际应用；3. 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二 实验要求 1.完成简单查询和连接查询操作，并验收实验结果提交实验报告三 实验内容所有的查询全部用Transact-SQL语句实现1. 简单查询操作首先通过insert语句向student和course表插入5条数据，sql语句及代码如下：insert into student values(20131701110,张福新,女,山东,19901211,计算机,17,2,2013,山东德州insert into student values(20131702111,杨文婷,女,山西,19880309,计算机,17,2,2013,山西太原insert into student values(20131703111,张琦,女,山西,19890316,计算机,17,2,2013,山西太原insert into student values(20130802044,冯艺,女,山东,19900809,经济,08,1,2013,山东德州insert into student values(201308702040,王仲,男,山东,19930623,工商,05,1,2014,山东德州insert into course values(1,001,数据库,001,9,18,1,2) insert into course values(2,001,网络工程,002,9,18,1,2) insert into course values(3,001,信息系统,003,9,18,1,2) insert into course values(4,001,ERP应用,004,9,18,1,2) insert into course values(5,001,移动商务,005,9,18,1,2) 对数据库实现以下查询： 计算机系的学生学号和姓名；select sno,sname from student where dno=计算机 选修了课程的学生学号；ALTER TABLE student ADD CONSTRAINT spno_cons FOREIGN KEY (spno) REFERENCES course ON DELETE SET NULL 选修某一课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列； 求选修某一课程且成绩在8090 之间的学生学号和成绩，并将成绩乘以系数0.75 输出； 求某两个系的姓某姓的学生的信息； 求缺少了成绩的学生的学号和课程号。 将2000以后的成绩大于90分的学生成绩存入永久成绩表；将2000年以前的成绩存入临时成绩表中。2. 连接查询操作对数据库实现以下查询： 查询每个学生的情况以及他（她）所选修的课程； 求学生的学号、姓名、选修的课程名及成绩； 查询每一门课的间接先行课。实验六 子查询一 实验目的1 掌握子查询的表示。2 进一步掌握SQL Server 查询分析器的使用方法，加深对SQL 语言的嵌套查询语句的理解二 实验准备1 掌握SQL语句的基本语法，并熟悉查询分析器的工作环境。2 完成了实验四，创建了数据库及各个基本表。3 了解子查询的表示方法，熟悉IN 比较符、ANY、EXISTS操作符的用法。三 实验内容 1.建立“工程-零件”数据库及如下4个表，并输入实验数据，用SQL语句实现如下三个查询：1) 供应项目j4红色零件的供应商号及名称2) 没有上海供应商生成的零件的项目号3)至少使用了供应商S5所供应全部零件的项目号。表结构如下：供应商（S）：列名类型长度是否可为空供应商代码Char5姓名Char50所在城市Char20联系电话Char20工程（J）：列名类型长度是否可为空工程代码Char5工程名Char50负责人Char10预算Char8零件（P）：列名类型长度是否可为空零件代码Char5零件名Char50规格Char10产地Char20颜色Char10供应零件（SPJ）：列名类型长度是否可为空供应商代码Char5工程代码Char5零件代码Char5数量Int4要求：供应商表：供应商代码为主码工程表：工程代码为主码零件表：零件代码为主码供应零件表：供应商代码、工程代码、零件代码为联合主码；供应商代码为外码，参照表、列是供应商表的供应商代码列；工程代码为外码，参照表、列是工程表的工程代码列；零件代码为外码，参照表、列是零件表的零件代码列实验数据如下：供应商： 供应商代码姓名所在城市联系电话S1北京供应商北京0108888888S2天津供应商天津0228888888S3重庆供应商重庆0238888888S4上海供应商1上海0218888888S5广州供应商广州0208888888S6上海供应商2上海0216666666工程：工程代码工程名负责人预算J1工程1丁一200000J2工程2赵二60000J3工程3张三70000J4工程4李四80000J5工程5王五150000零件：零件代码零件名规格产地颜色P1螺丝中济南红色P2主板集成深圳绿色P3显卡独立香港蓝色P4声卡集成天津红色P5网卡100M上海黑色P6鼠标无线上海黑色供应零件：供应商代码工程代码零件代码数量S1J2P450S1J3P5100S2J2P6500S4J1P3150S4J5P1200S5J4P6100S6J4P290create table 供应商(供应商代码char(5),姓名char(50),所在城市varchar(20),联系电话char(20),)create table 工程(工程代码char(5),工程名char(50),负责人varchar(10),预算char(8),)create table 零件(零件代码char(5),零件名char(50),规格varchar(10),产地char(20),颜色char(10),)create table 供应零件(供应商代码char(5),工程代码char(5),零件代码char(5),数量int,)alter table 供应零件add constraint Sforeign key (供应商代码)references 供应商(供应商代码)alter table 供应零件add constraint Jforeign key (工程代码)references 工程(工程代码)alter table 供应零件add constraint Pforeign key (零件代码)references 零件(零件代码)1、use 工程零件select 供应商代码,姓名from 供应商where 供应商代码in(select 供应商代码from 供应零件where零件代码IN (SELECT 零件代码FROM 零件 WHERE 颜色=黑色)and工程代码IN (SELECT 工程代码FROM 工程WHERE 工程代码=J4)2、select 工程代码from 供应零件where 供应商代码in(select 供应商代码from 供应商where 姓名not like 上海% )3、select 工程代码from 供应零件where 供应商代码=S5实验七 使用子句的SELECT语句一 实验目的1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。2. 进一步掌握SQL Server 查询分析器的使用，加深对SQL 语言的嵌套查询语句的理解。二 实验准备1. 了解SELECT语句的GROUP BY和ORDER BY子句的作用。2. 了解统计函数和分组统计函数的使用方法。3. 熟悉查询分析器的运行环境。三 实验内容 所有查询都全部用SQL语句实现1. 建立“图书_读者”数据库及如下3个表，并输入实验数据，用SQL语句实现如下五个查询：1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书的平均定价的2 倍。2）求机械工业出版社出版的各类图书的平均定价，分别用GROUP BY 和GROUP BY ALL表示。3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册书和总价格。5）查询计算机类和机械工业出版社出版的图书。表结构如下：图书：列名类型长度是否可为空书号char10类别char12是出版社char50作者char20是书名char50定价money8是读者：列名类型长度是否可为空编号char10姓名char8单位char50是性别char2是电话char15是借阅：列名类型长度是否可为空串号char10书号char10读者编号char10借阅日期datetime8要求：图书表：书号为主码读者表：编号为主码；性别只能是“男”或“女”的CHECK 约束借阅表：串号为主码；书号为外码，参照表、列是图书表的书号列；读者编号为外码，参照表、列是读者表的编号列；书号和读者编号的联合UNIQUE 约束实验数据：图书：书号类别出版社作者书名定价1001计算机机械工业出版社王民数据结构801002计算机机械工业出版社张建平计算机应用201003计算机电子工业出版社王敏数据库技术151004计算机电子工业出版社谭浩强C 语言251005英语中国人民大学出版社张锦芯应用文写作251006管理高等教育出版社Robison管理学151007管理机械工业出版社Fayol工业管理701008数学机械工业出版社李平线性代数501009管理机械工业出版社Durark公司的概念141010数学机械工业出版社徐新国统计学15读者：编号姓名单位性别电话1001丁一数学院男812345671002赵二经济学院男822345671003张三管理学院女832345671004李四文学院男842345671005王五历史文化学院女852345671006孙六物理学院男862345671007周七生命科学院女872345671008徐八化学院男882345671009宋九信息学院女892345671010刘十计算机学院女80234567借阅：串号书号读