实验2 数据定义与查询(1)

实验二数据定义和查询

一、实验目的

1、掌握使用SQL语句创建和删除数据表，创建各种完整性约束。

2、掌握使用SQL语句修改表的结构。

3、掌握查询语句的使用方法，重点掌握连接查询和嵌套查询。

二、实验内容

1．创建名为“Project”的数据库

2．在Project数据库下使用SQL语句建立4个关系，如下：

供应商表S （Sno, Sname, Ctiy）

零件表P（Pno, Pname, Color, Weights）

工程项目表J（Jno, Jname, City）

供应情况表SPJ（Sno, Pno, Jno, QTY）

其中：

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商所在城市（CITY）组成；

零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHTS）组成；

工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；

供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量组成（QTY），表示某供应商供应某种零件给某工程项目的数量为QTY。

数据如下：

S表

SNO SNAME CITY

S1 精益天津

S2 盛锡北京

S3 东方红北京

S4 丰泰盛天津

S5 为民上海

P表

PNO PNAME COLOR WEIGHTS P1 螺母红12

P2 螺栓绿17

P3 螺丝刀蓝14

P4 螺丝刀红14

P5 凸轮蓝40

P6 齿轮红30

J表

JNO JNAME CITY

J1 三建北京

J2 一汽长春

J3 弹簧厂天津

J4 造船厂天津

J5 机车厂唐山

J6 无线电厂常州

SPJ表

SNO PNO JNO QTY S1 P1 J1 200 S1 P1 J3 100 S1 P1 J4 700 S1 P2 J2 100 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J4 500 S2 P3 J5 400 S2 P5 J1 400 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

要求：

（1）创建每个关系的主键，有外键的创建外键。

（2）S表中的Sname属性列的取值唯一

（3）P表中weight属性列的取值范围在1-50之间

（4）J表中的Jname取值不能为空并且是唯一的

（5）SPJ表中QTY属性列的数据类型必须为int

2．用SQL语句完成以下操作：

（1）给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信箱。

（2）删除Jname属性列取值唯一的约束。

（3）将QTY属性列的数据类型修改为smallint型。

（4）删除S表中的属性列Semail

3．完成以下查询：

（1）查询所有供应商所在的城市。

（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色。

（3）查询工程项目的总个数。

（4）查询所有零件的平均重量。

（5）查询供应商S3供应的零件号。

（6）查询各个供应商号及其供应了多少类零件。

（7）查询供应了2类以上零件的供应商号。

（8）查询零件名以“螺”字开头的零件信息。

（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。

（10）查询给每个工程供应零件的供应商的个数。

（11）查询供应数量在1000—2000之间（包括1000和2000）的零件名称。4．对实验一创建的Student数据库进行还原，完成以下查询：

（1）查询计算机专业学生的姓名和年龄。

（2）查询成绩在70-80分（包括70和80分）之间的学生的学号、课程号和成绩。（3）查询C01号课程的最高成绩。

（4）统计每个专业的学生人数。

（5）统计每门课程的修课人数和考试最高分。

（6）统计每个学生的选课门数和考试总成绩，并按总成绩的递减顺序显示结果。（7）查询总成绩超过200分的学生，要求列出学号和总成绩。

（8）查询姓名为余丹妮的学生所学课程的课程名与学分。

（9）查询选修课程号为“C02”或“C07”的学生的学号。

（10）查询选修了课程号为“C02”和“C07”的学生的学号。

（11）查询学习全部课程的学生姓名。

（12）查询1992年1月1日以前出生的学生的姓名和专业。

（13）查询选修了“计算机网络”课程且成绩在90分以上的学生姓名。

（14）查询选修了5门以上课程的学生学号和姓名。

（15）查询未选修“离散数学”的学生情况。

（16）统计C01和C06号课程的选课人数及平均成绩。

（17）查询比所有计算机专业学生年龄都大的学生。

（18）将网络工程专业的学生按出生时间先后排序。

注：（1）以上用红色标注的题目写入实验报告，在实验报告中写出SQL语句，并且要有语句执行结果的截图，其他题目不用写入实验报告。

（2）将所有的SQL语句写完后保存到word文档，最终提交到202.117.179.110的系统中。