数据库实验一：创建数据库并实现简单查询

[sql]代码库

1.创建学生Student数据库（包含学生、课程和选修三个表），写出创建三个基本表的CREATE语句，列名、数据类型和约束如下：
列名		描述	数据类型	约束
cStudentNo	学号	长度为12的定长字符串	主键
vSt
dentName	姓名	变长字符串，长度自定	非空
iSage		年龄	整型	年龄在0到100岁之间
nSgender	性别	Unicode编码方式的字符串型	性别只能取‘男’或‘女’
cClass		班级	定长字符串，长度10	形如‘2009117011’
vDepartment	院系	变长字符串，长度自定	取默认值‘计算机系’

列名		描述	数据类型	约束
cCourseNo	课程号	长度为8的定长字符串	主键
vCourseName	课程名	变长字符串，长度自定	无
vTeacher	教师名	变长字符串	无
 iCredit	学分	带小数点的数值型	0到5之间

列名		描述	数据类型	约束
cStudentNo	学号	和学生表学号一致	外键，引用学生表学号
cCourseNo	课程号	和课程表的课程号一致	外键，引用课程表课程号
iGrade		成绩	整型	0到100分之间

在Student数据库中，写出完成如下操作的T-SQL语句：
(1)	修改学生表，添加一个新的属性列‘籍贯’。
(2)	修改课程表，删除学分列上的取值0-5的约束。
(3)	修改选修表，将成绩列的数据类型修改了浮点型。(修改类型前先删除此列上的约束)
(4)	修改课程表，给课程名添加一个取值唯一的约束。	

	


1.创建表
CREATE TABLE student (
cStudentNo CHAR(12) PRIMARY KEY,
vStudentName VARCHAR(12) NOT NULL,
iSage INT CHECK(iSage>0 and iSage<100),
nSgender NCHAR CHECK(nSgender='男' OR nSgender='女'),
cClass CHAR(10),
vDepartment VARCHAR(12) DEFAULT '计算机系',
)

CREATE TABLE course (
cCourseNo CHAR(8) PRIMARY KEY,
vCourseName VARCHAR(12),
vTeacher VARCHAR,
iCredit FLOAT CHECK(iCredit>=0 and iCredit<=5),
)

CREATE TABLE selcou (
cStudentNo CHAR(12) PRIMARY KEY FOREIGN KEY(cStudentNo) REFERENCES student(cStudentNo),
cCourseNo CHAR(8) FOREIGN KEY(cCourseNo) REFERENCES course(cCourseNo),
iGrade INT CHECK(iGrade>=0 and iGrade<=100) ,
)

查询
(1).
alter table student
add cProvince char(6)

(2).
alter table course
drop constraint CK__course__iCredit__39AD8A7F

(3).
alter table selcou
drop constraint CK__selcou__iGrade__414EAC47
alter table selcou
alter column iGrade float    

(4).
alter table course
add unique(vCourseName)









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日的订单详情。
(2) 查询订单总额超过75的订单详情。
(3) 订单延误将按照总价值mTotalCost的5倍赔偿，查询每份订单的订单号和需要赔偿的金额
(4) 查询以‘I’开头，国家名称由五个字母构成的国家ID和名称。
(5) 查询Shipment表中dActualDeliveryDate为空的记录。
(6) 对于PickofMonth表，查询月销售量最高的玩具ID和销售的月份和年份。
(7) 对于PickofMonth表，统计2000年销售的玩具的总数量。
(8) 对于Toyz表，查询玩具的最高售价，最低售价，和平均售价。
(9) 对于Shopper表，统计‘California’州的购买者人数。
(10) 对于PickofMonth表，查询2001的销售记录，要求按照月销售额按照从小到大的顺序显示。
(11) 对于ShippingRate表，计算每个国家的每磅的平均运费。
	



(1) 查询日期为2001年5月22日的订单详情。
select *
from Orders
where dOrderDate='2001-05-22'

 

(2) 查询订单总额超过75的订单详情。
select *
from Orders
where mTotalCost > 75

 

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

select cOrderNo,mToyCost*5 '赔偿总额'
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=(select Max(iTotalSold)
from PickofMonth)

 

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

select Sum(iTotalSold) '2000年销售总额'
from PickofMonth
where iYear=2000

 

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

select Max(mToyRate) '最高售价', Min(mToyRate) '最低售价',
Avg(mToyRate) '平均售价'
from Toys

 

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

select Count(*) 'California州的购买者人数'
from Shopper
where cCity='California'

 

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

select *
from PickOfMonth
where iYear='2001'
order by siMonth asc

 

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

select Avg(mRatePerPound) '每磅平均运费'
from ShippingRate
group by cCountryID