数据库综合实验.docx
- 文档编号:5395445
- 上传时间:2022-12-15
- 格式:DOCX
- 页数:26
- 大小:845.44KB
数据库综合实验.docx
《数据库综合实验.docx》由会员分享,可在线阅读,更多相关《数据库综合实验.docx(26页珍藏版)》请在冰豆网上搜索。
数据库综合实验
实验六SQL语句综合训练
一、实验目的
本次实验是一个综合练习,通过训练,使学生全面掌握SQLSERVER2008的基本操作语句和功能。
二、实验内容
综合练习一
设学校环境如下:
一个系有若干个专业,每个专业一年只招一个班,每个班有若干个学生。
现要建立关于系、学生、班级的数据库,关系模式为:
班CLASS(班号,专业名,系号,入学年份,人数)
学生STUDENT(学号,姓名,年龄,班号)
系DEPARTMENT(系号,系名)
试用SQL语言完成以下功能:
1.建立数据库,数据库名称为C_S_D
CREATEDATABASEC_D_S
2.建立数据表,在定义中要求声明:
(1)每个表的主、外码。
(2)每个班级的人数不能超过30人。
(3)学生的年龄介于15到40岁之间。
(4)学生姓名不能为空。
CREATETABLEDEPARTMENT
(系号varchar(20)PRIMARYKEY,
系名varchar(20)
);
CREATETABLECLASS
(班号varchar(6)PRIMARYKEY,
专业名varchar(20),
系号varchar(20),
入学年份int,
人数intCHECK(人数<=30),
FOREIGNKEY(系号)REFERENCESDEPARTMENT(系号)
);
CREATETABLESTUDENT
(学号varchar(6)PRIMARYKEY,
姓名varchar(20)NOTNULL,
年龄smallintCHECK(年龄>=15AND年龄<=40),
班号varchar(6),
FOREIGNKEY(班号)REFERENCESCLASS(班号)
);
3.插入如下数据
INSERTINTODEPARTMENT(系号,系名)
VALUES
('001','数学'),
('002','计算机'),
('003','化学'),
('004','中文'),
('005','经济')
INSERTINTOCLASS(班号,专业名,系号,入学年份,人数)
VALUES
('101','软件工程','002','1995',20),
('102','微电子','002','1996',30),
('112','高分子化学','003','1996',25),
('131','现代语言','004','1996',20),
('142','国际金融','005','1996',14),
('151','物理化学','003','1995',29),
('111','无机化学','003','1995',29),
('121','离散数学','001','1995',20),
('141','国际贸易','005','1997',30),
('143','数字电路','002','1997',30)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)
VALUES
('8101','张三',18,'101'),
('8102','钱四',16,'121'),
('8103','王玲',17,'131'),
('8105','李飞',19,'102'),
('8109','赵四',18,'141'),
('8110','李可',20,'142'),
('8201','张飞',18,'111'),
('8302','周瑜',16,'112'),
('8203','王亮',17,'111'),
('8305','董庆',19,'102'),
('8409','赵龙',18,'101'),
('8510','李丽',20,'142')
4.完成以下查询功能
(1)找出所有姓李的学生。
SELECT*
FROMSTUDENT
WHERE姓名LIKE'李%'
(2)列出所有开设超过两个专业的系的名字。
SELECT系名
FROMDEPARTMENT
WHERE系号IN(
SELECT系号
FROMCLASS
GROUPBY系号
HAVINGCOUNT(DISTINCT专业名)>=2)
(3)列出人数大于等于60的系的编号和名字。
SELECT*
FROMDEPARTMENT
WHERE系名in(
SELECT系名
FROMCLASS
GROUPBY系号
HAVINGSUM(人数)>=60)
5.学校又新增加了一个物理系,编号为006。
INSERTINTODEPARTMENT
VALUES('006','物理')
6.学生张三退学,请更新相关的表。
更新前
UPDATECLASS
SET人数=人数-1
WHERE班号in(
SELECT班号
FROMSTUDENT
WHERE姓名='张三')
7.新建一个登录名user1,在C_S_D数据库中为user1映射一个数据库用户,使此用户对表
CLASS,STUDENT,DEPARTMENT有查询和修改权限,没有插入和删除权限。
GRANTSELECT,UPDATEONCLASSTOUSER1;
GRANTSELECT,UPDATEONDEPARTMENTTOUSER1;
GRANTSELECT,UPDATEONSTUDENTTOUSER1;
综合练习二
有某个学生运动会比赛信息的数据库,保存了如下的表:
运动员(运动员编号,运动员姓名,运动员性别,所属系名)
项目(项目编号,项目名称,项目比赛地点)
成绩(运动员编号,项目编号,积分)
请用SQL语句完成如下功能:
1.建立数据库,数据库名称为y_x_c
CREATEDATABASEY_X_C
2.建立数据表,并满足如下条件:
(1)定义各个表的主码、外码约束
(2)运动员的姓名和所属系别不能为空值。
(3)积分要么为空值,要么为6,4,2,0,分别代表第一,二,三名和其他名次的积分。
CREATETABLEATHLETE
(Anovarchar(10)PRIMARYKEY,
Anamevarchar(20)NOTNULL,
Asexvarchar(6),
Adepvarchar(10)NOTNULL
);
CREATETABLEITEM
(Inovarchar(10)PRIMARYKEY,
Inamevarchar(20),
Ilocationvarchar(10)
);
CREATETABLESCORE
(Anovarchar(10),
Inovarchar(10),
ScoreintCHECK(Score=6orScore=4orScore=2orScore=2),
PRIMARYKEY(Ano,Ino),
FOREIGNKEY(Ano)REFERENCESATHLETE(Ano),
FOREIGNKEY(Ino)REFERENCESITEM(Ino)
)
3.往表中插入数据:
INSERTINTOATHLETE
VALUES
('1001','李明','男','计算机系'),
('1002','张三','男','数学系'),
('1003','李四','男','计算机系'),
('1004','王二','男','物理系'),
('1005','李娜','女','心理系'),
('1006','孙丽','女','数学系')
INSERTINTOITEM
VALUES
('x001','男子五千米','1操场'),
('x002','男子标枪','1操场'),
('x003','男子跳远','2操场'),
('x004','女子跳高','2操场'),
('x005','女子三千米','3操场')
INSERTINTOSCORE
VALUES
('1001','x001',6),
('1002','x001',4),
('1003','x001',2),
('1004','x001',0),
('1001','x003',4),
('1002','x003',6),
('1004','x003',2),
('1005','x004',6),
('1006','x004',4)
4.完成如下查询
(1)求出目前总积分最高的系名,及其积分。
SELECTAdep,SUM(Score)
FROMATHLETE,SCORE
WHEREATHLETE.Ano=SCORE.Ano
GROUPBYAdep
HAVINGSUM(Score)>=ALL
(SELECTSUM(Score)
FROMATHLETE,SCORE
WHEREATHLETE.Ano=SCORE.Ano
GROUPBYAdep
)
(2)找出在1操场进行比赛的各项目名称及其冠军的姓名。
SELECTIname,Aname
FROMATHLETE,ITEM,SCORE
WHEREIlocation='1操场'andATHLETE.Ano=SCORE.AnoandITEM.Ino=SCORE.Inoand
Score>=(SELECTMAX(Score)
FROMSCORE,ITEM
WHEREIlocation='1操场'andSCORE.Ino=ITEM.Ino)
(3)找出参加了张三所参加的所有项目的其他同学的姓名。
SELECTAname
FROMATHLETE
WHEREAnoIN
(SELECTAno
FROMSCORE
WHEREInoIN
(SELECTIno
FROMATHLETE,SCORE
WHEREathlete.ano=SCORE.anoANDaname='张三'))ANDaname<>'张三'
5.建立视图文件,查询每个项目的项目名称、运动员姓名和所属系名。
CREATEVIEWI_A(Iname,Aname,Adep)
AS
SELECTIname,Aname,Adep
fromATHLETE,ITEM,SCORE
whereATHLETE.Ano=SCORE.AnoandITEM.Ino=SCORE.Ino;
6.经查张三因为使用了违禁药品,其成绩都记0分,请在数据库中做出相应修改。
UPDATESCORE
SETScore=0
WHEREAnoin
(SELECTAno
FROMATHLETE
WHEREAname='张三');
7.经组委会协商,需要删除女子跳高比赛项目。
DELETEFROMSCORE
WHEREInoIN
(SELECTIno
FROMITEM
WHEREIname='女子跳高')
DELETEFROMITEM
WHEREIname='女子跳高';
8.
新建一个登录名user2,在y_x_c数据库中为user2映射一个数据库用户,使此用户对运动员表有查询和修改权限,没有插入和删除权限,对项目表有查询权限,没有插入、修改、删除的权限,对成绩表有查询、插入、修改、删除权限。
CREATELOGINuser2
WITHPASSWORD='123';
CREATEUSERhubing
FORLOGINuser2;
GRANTCONNECTTOhubing;
GRANTSELECT
ONATHLETE
TOhubing;
GRANTUPDATE
ONATHLETE
TOhubing;
GRANTSELECT
ONITEM
TOhubing;
GRANTSELECT
ONSCORE
TOhubing;
GRANTINSERT
ONSCORE
TOhubing;
GRANTUPDATE
ONSCORE
TOhubing;
GRANTDELETE
ONSCORE
TOhubing;
综合练习三
现有一个商店的数据库,记录顾客及其购物情况,由下面三个表组成:
商品(商品号,商品名,单价,商品类别,供应商);
顾客(顾客号,姓名,住址);
购买(顾客号,商品号,购买数量);
试用SQL语言完成下列功能:
1.建立数据库,数据库名称自定
CREATEDATABASEC_C_P
2.建立数据表,在定义中要求声明:
(1)每个表的主外码;
(2)顾客的姓名和商品名不能为空值;
(3)单价必须大于0,购买数量必须再0到20之间;
CREATETABLECOMMODITY(
CnoVARCHAR(10)PRIMARYKEY,
Cnamevarchar(20)NOTNULL,
PriceFLOATCHECK(Price>0),
CategoryVARCHAR(20),
Supplychar(20)
);
CREATETABLECUSTOMER(
CunoVARCHAR(10)PRIMARYKEY,
CnameVARCHAR(20)NOTNULL,
AddressVARCHAR(40)
);
CREATETABLEPURCHASE(
CunoVARCHAR(10),
CnoVARCHAR(10),
QUANTITYINTCHECK(QUANTITY>=0ANDQUANTITY<=20),
PRIMARYKEY(Cuno,Cno),
FOREIGNKEY(Cuno)REFERENCESCUSTOMER(Cuno),
FOREIGNKEY(Cno)REFERENCESCOMMODITY(Cno),
);
3.往表中插入数据
INSERTINTOCOMMODITY
VALUES
('M01','佳洁士','8.00','牙膏','宝洁'),
('M02','高露洁','6.50','牙膏','高露洁'),
('M03','洁诺','5.00','牙膏','联合利华'),
('M04','舒肤佳','3.00','香皂','宝洁'),
('M05','夏士莲','5.00','香皂','联合利华'),
('M06','雕牌','2.50','洗衣粉','纳爱斯'),
('M07','中华','3.50','牙膏','联合利华'),
('M08','汰渍','3.00','洗衣粉','宝洁'),
('M09','碧浪','4.00','洗衣粉','宝洁');
INSERTINTOCUSTOMER
VALUES
('C01','Dennis','海淀'),
('C02','John','朝阳'),
('C03','Tom','东城'),
('C04','Jenny','东城'),
('C05','Rick','西城')
INSERTINTOPURCHASE
VALUES
('C01','M01','3'),
('C01','M05','2'),
('C01','M08','2'),
('C02','M02','5'),
('C02','M06','4'),
('C03','M01','1'),
('C03','M05','1'),
('C03','M06','3'),
('C03','M08','1'),
('C04','M03','7'),
('C04','M04','3'),
('C05','M06','2'),
('C05','M07','8');
4.用SQL语句完成下列查询:
(1)求购买了供应商“宝洁”产品的所有顾客。
SELECTCuno,Cuname
FROMCUSTOMER
whereCunoin
(SELECTCuno
FROMPURCHASE
WHERECnoin
(SELECTCno
FROMCOMMODITY
WHEREsupply='宝洁'
)
);
(2)求购买的商品包括了顾客“Dennis”所购买的任意商品的顾客姓名。
SELECTDISTINCTCuname
FROMCUSTOMER,PURCHASE
WHERECUSTOMER.Cuno=PURCHASE.CunoANDCnoIN
(SELECTCno
FROMPURCHASE,CUSTOMER
WHERECUSTOMER.Cuno=PURCHASE.CunoANDCuname='Dennis'
);
(3)求牙膏卖出数量最多的供应商名。
SELECTsupply
FROMCOMMODITY,PURCHASE
WHERECOMMODITY.Cno=PURCHASE.CnoANDQUANTITY>=ALL
(SELECTSUM(QUANTITY)
FROMPURCHASE
GROUPBYCno
HAVINGCnoIN
(SELECTCno
FROMCOMMODITY
WHEREcategory='牙膏'
)
);
5.建立视图文件,查询每种商品的商品名称和购买数量。
CREATEVIEWMENU
AS
SELECTCname,QUANTITY
FROMCOMMODITY,PURCHASE
WHERECOMMODITY.Cno=PURCHASE.Cno;
6.将所有的牙膏商品单价增加10%。
UPDATECOMMODITY
SETprice=price*1.1
WHEREcategory='牙膏';
7.删除从未被购买的商品记录。
DELETE
FROMCOMMODITY
WHERECnoNOTIN
(SELECTCno
FROMPURCHASE
);
8.新建一个登录名user3,在数据库中为user3映射一个数据库用户,使数据库用户拥有对表商品、顾客、购买的插入和删除权限,没有查询和修改权限。
CREATELOGINuser3
WITHPASSWORD='456';
CREATEUSERHUBING1
FORLOGINuser3;
GRANTINSERT,DELETE
ONCOMMODITY
TOHUBING1;
DENYSELECT,UPDATE
ONCOMMODITY
TOHUBING1;
GRANTINSERT,DELETE
ONCUSTOMER
TOHUBING1;
DENYSELECT,UPDATE
ONCUSTOMER
TOHUBING1
GRANTINSERT,DELETE
ONPURCHASE
TOHUBING1;
DENYSELECT,UPDATE
ONPURCHASE
toHUBING1
3、实验总结
通过此次总和实验,能够更加熟练的使用SQL语言,对于数据库中完整性约束有了更深的认识。
总之,希望在以后的学习中能学会有实验中发现的问题,让自己更好的掌握数据库知识。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 综合 实验
![提示](https://static.bdocx.com/images/bang_tan.gif)