数据库实验1：建数据库,实现简单查询

广东海洋大学学生实验报告书

(1) 修改学生表，添加一个新的属性列‘籍贯’。

(2) 修改课程表，删除学分列上的取值0-5的约束。

(3) 修改选修表，将成绩列的数据类型修改了浮点型。(修改类型前先删除此列上的约束)

(4) 修改课程表，给课程名添加一个取值唯一的约束。

/* 新建Student_Course数据库*/

CREATE DATABASE Student_Course     

ON PRIMARY

(

    NAME='student_course.mdf',

    FILENAME='E:\\Document\\SQL_DATA\\student_course.mdf',

    SIZE=3mb,

    MAXSIZE=20mb,

    FILEGROWTH=10%

)

/* 创建学生表Student */

CREATE TABLE Student(

    cStudentNo CHAR(12) PRIMARY KEY,

    vStudentName VARCHAR(6) NOT NULL,

    iSage INT CHECK(iSage > '0' AND isage < '100'),

    nSgender NCHAR(5) CHECK(nSgender='男' OR nSgender='女'),

    cClass CHAR(10),

    vDepartment VARCHAR(20) DEFAULT '计算机系'

)

/* 创建课程表Course */

CREATE TABLE Course(

    cCourseNo CHAR(8) PRIMARY KEY,

    vCourseName VARCHAR(10),

    vTeacher VARCHAR(10),

    iCredit FLOAT CHECK(iCredit >= 0 AND iCredit <= 5)

)

/* 创建选修表SC */

CREATE TABLE SC(

    cStudentNo CHAR(12) NOT NULL,

    cCourseNo CHAR(8) NOT NULL,

    iGrade INT CHECK(iGrade >=0 AND iGrade<=100),

    PRIMARY KEY(cStudentNo,cCourseNo),

    FOREIGN KEY (cStudentNo) REFERENCES Student(cStudentNo),

    FOREIGN KEY (cCourseNo) REFERENCES Course(cCourseNo)

)

/* 添加籍贯（BirthPlace）列 */

ALTER TABLE Student ADD BirthPlace VARCHAR(50)

/* 修改课程表，删除学分列上的取值0-5的约束 */

ALTER TABLE Course DROP CK__Course__iCredit__023D5A04

/* 修改选修表，将成绩列的数据类型修改了浮点型 */

ALTER TABLE SC DROP CK__SC__iGrade__0519C6AF  /* 删除iGrade列的约束 */

ALTER TABLE SC ALTER COLUMN iGrade FLOAT

/* 修改课程表，给课程名添加一个取值唯一的约束 */

2.执行脚本，生成Recruitment和Globaltoyz数据库，并完成下列查询。

注：首先开启数据库服务，启动Microsoft SQL SERVER Management Studio；

解压缩SQL SCRIPT文件到SQL SCRIPT文件夹，进入‘Guided Practice’目录，点击打开CREATERECRUITMENTDB文件，将该脚本文件在SQL SERVER查询编辑器中打开，点击工具栏上的‘执行’按钮执行该脚本。执行成功后检查刷新服务，观察是否创建了一个名为‘Recuitment’的数据库。接下来，同样点击打开该目录下的另一文件‘CREATERECRUITMENTOBJECTS’，按同样的方法执行脚本，执行成功后会生成Recuitment数据库中的一系列基本表。

（GlobalToyz数据库的安装脚本在‘UnGuided Practice’目录下，安装过程和Recuitment类似，安装的脚本文件为‘CREATEGLOBALTOYZDB’和‘CREATEGLOBALTOYZOBJECTS’，按先后次序安装）

安装后请熟悉一下两个数据库的基本表，了解一下该数据库主要描述的数据应用环境。

安装后写出在GlobalToyz中的查询语句，（只要求写出SELECT 语句，不需要写出查询结果）

(1)查询日期为2001年5月22日的订单详情。

select * from orderdetail

where corderno=(select corderno from orders 

where dorderdate=2001-5-22) 

(2)查询订单总额超过75的订单详情。

select * from orderdetail

where corderno in (select corderno from orders

where mtotalcost > 75) 

(3) 订单延误将按照总价值mTotalCost的5倍赔偿，查询每份订单的订单号和需要赔偿的金额

select cOrderNo, 5*mToyCost  from OrderDetail

(4) 查询以‘I’开头，国家名称由五个字母构成的国家ID和名称。

select ccountryid,ccountry from country 

where ccountry like 'I____'

(5) 查询Shipment表中dActualDeliveryDate为空的记录。

select * from shipment 

where dactualdeliverydate is null

(6) 对于PickofMonth表，查询月销售量最高的玩具ID和销售的月份和年份。

select ctoyid,simonth,iyear from pickofmonth

where itotalsold in (select max(itotalsold) from pickofmonth)

(7) 对于PickofMonth表，统计2000年销售的玩具的总数量。

select cout（ctoyid） from pickofmonth 

where iyear = '2000'

(8) 对于Toyz表，查询玩具的最高售价，最低售价，和平均售价。

select max(mtoyrate) highest,min(mtoyrate) lowest,avg(mtoyrate) average from toys

(9) 对于Shopper表，统计‘California’州的购买者人数。

select count(*) from shopper 

where ccity = 'california'

(10) 对于PickofMonth表，查询2001的销售记录，要求按照月销售额按照从小到大的顺序显示。

select * from pickofmonth 

where iyear = '2001'

order by itotalsold

(11) 对于ShippingRate表，计算每个国家的每磅的平均运费。

select avg(mrateperpound) 

from shippingrate