11393计算机信息管理课程实验3合1精品文档68页.docx
《11393计算机信息管理课程实验3合1精品文档68页.docx》由会员分享,可在线阅读,更多相关《11393计算机信息管理课程实验3合1精品文档68页.docx(56页珍藏版)》请在冰豆网上搜索。
11393计算机信息管理课程实验3合1精品文档68页
04735数据库系统原理:
1、设某工厂数据库中有2个基本表:
车间基本表:
DEPT(DNO,DNAME,MG
2、R_ENO)属性分别是车间编号、车间名、和车间主任职工号
职工基本表:
EMP(ENO,ENAME,AGE,SEX,SALRY,DNO)属性分别是职工号、姓名、年龄、性别、工资和所有车间编号
(1)试用关系代数表达式写出下列查询:
简述“金工车间”的男职工的职工号和姓名。
(2)建立一个有关车间的女职工号和姓名的视图,其结构如下:
VIEW6(ENO,ENAME).试写出创建视图VIEW6的SQL语句。
createviewVIEW6
as
selectENO,ENAMEfromEMP
whereSEX='女'
2、
3、
4、
5、
6、
7、关系模式为:
学生STUDENT(学号,姓名,年龄,班号)
班CLASS(班号,专业名,系名,入学年份)
系DEPARTMENT(系号,系名)
A.请使用SQL语言完成以下功能
(1)每个表的主外码
(2)每个班级的人数不能超过20人;
(3)学生的年龄介于16到30岁之间;
(4)学生姓名不能为空;
(5)只有班里没有学生的时候,才能删除该班级.
CREATEDATABASESCHOOL
USESCHOOL
createtableDEPARTMENT
(系号intPrimarykey,
系名varchar(20)unique)
createtableCLASS
(班号intprimarykey,
专业名varchar(20),
系名varchar(20),
FOREIGNkey(系名)referencesDEPARTMENT系(系名))
createtableSTUDENT
(学号intprimarykey,
姓名varchar(10)notnull,
年龄intcheck(年龄>=16and年龄<=30),
班号int,
foreignkey(班号)referencesCLASS(班号)ONDELETEnoactioncheck(20>=ALL(selectcount(学号)fromSTUDENTgroupby班号)))
B.插入如下数据:
CLASS(
101,软件,计算机,1995;
102,微电子,计算机,1996
STUDENT(
8101,张三,18,101;
8102,钱四,16,121;
DEPARTMENT(001,数学
INSERTINTODEPARTMENT(系号,系名)VALUES('001','数学')
INSERTINTODEPARTMENT(系号,系名)VALUES('002','计算机')
INSERTINTODEPARTMENT(系号,系名)VALUES('003','化学')
INSERTINTODEPARTMENT(系号,系名)VALUES('004','中文')
INSERTINTODEPARTMENT(系号,系名)VALUES('005','经济')
GO
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(101,'软件','计算机',1995)
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(102,'微电子','计算机',1996)
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(111,'无机化学','化学',1995)
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(112,'分子化学','化学',1996)
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(121,'统计数学','数学',1995)
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(131,'现代语言','中文',1996)
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(141,'国际贸易','经济',1997)
INSERTINTOCLASS(班号,专业名,系名,入学年份)VALUES(142,'国际金融','经济',1996)
GO
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8101,'张三',18,101)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8102,'钱四',16,121)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8103,'王玲',17,131)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8105,'李飞',19,102)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8109,'赵四',18,141)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8110,'李可',20,142)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8201,'张飞',18,111)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8302,'周瑜',16,112)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8203,'王亮',17,111)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8305,'董庆',19,102)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8409,'赵龙',18,101)
INSERTINTOSTUDENT(学号,姓名,年龄,班号)VALUES(8510,'李丽',20,142)
C.完成以下查询功能
(1)找出所有姓李的学生,并按其年龄由小到大排序;
SELECT*FROMSTUDENTWHERE姓名like'李%'ORDERBY年龄ASC
(2)列出所有开设超过两个专业的系的名字.
SELECT系名FROMCLASSGROUPBY系名HAVING(COUNT(*)>=2)
(3)列出学生人数大于等于2的系的编号和名称,
第一种:
SELECT*FROMDEPARTMENTWHERE系名in(SELECT系名FROMCLASSWHERE班号in(SELECT班号FROMSTUDENTGROUPBY班号HAVINGCOUNT(*)>=2))
第二种:
SELECT系号,系名FROMDEPARTMENTWHERE系名IN(SELECT系名FROMCLASSASC,STUDENTASSWHEREC.班号=S.班号GROUPBYC.系名HAVING(COUNT(*)>=2))
D.学生张三转到化学系111班,请更新相关的表
UPDATESTUDENTSET班号=111WHERE姓名='张三'
SELECT*FROMSTUDENTWHERE姓名='张三'
8、已知一个关系数据库的模式如下:
职工EMP(职工号,姓名,工资,所在部门)
部门DEPT(部门号,部门名,部门经理的职工号)
A.使用查询分析器创建上述表,要求声明:
(1)每个表的主外码.
(2)职工姓名和部门名不能为空;
(3)工资取800到5000之间整数;
CREATEDATABASECompany
USECompany
CREATETABLEDEPT
(部门号varchar(10)Primarykey,
部门名varchar(10)notnull,
部门经理的职工号varchar(10))
CREATETABLEEMP
(职工号varchar(10)Primarykey(这里去掉主键,因为重复的记录将不能增加)
姓名varchar(10)notnull,
工资intcheck(工资>=800and工资<=5000),
所在部门varchar(10)
FOREIGNKEY(部门号)REFERENCESDEPT(部门号)
B、插入如下数据
职工(E01,汤姆,4000,D02
E05,比尔,3500,D01)
部门(D01,人事,E04
D04,财务,E06)
INSERTINTODEPT(部门号,部门名,部门经理的职工号)VALUES('D01','人事','E04')
INSERTINTODEPT(部门号,部门名,部门经理的职工号)VALUES('D02','信息','E01')
INSERTINTODEPT(部门号,部门名,部门经理的职工号)VALUES('D03','销售','E05')
INSERTINTODEPT(部门号,部门名,部门经理的职工号)VALUES('D04','财务','E06')
INSERTINTOEMP(职工号,姓名,工资,所在部门)VALUES('E01','汤姆',4000,'D02')
INSERTINTOEMP(职工号,姓名,工资,所在部门)VALUES('E02','丹尼',3000,'D02')
INSERTINTOEMP(职工号,姓名,工资,所在部门)VALUES('E03','约翰',1000,'D01')
INSERTINTOEMP(职工号,姓名,工资,所在部门)VALUES('E04','迪克',5000,'D03')
INSERTINTOEMP(职工号,姓名,工资,所在部门)VALUES('E04','迪克',5000,'D01')
INSERTINTOEMP(职工号,姓名,工资,所在部门)VALUES('E05','比尔',3500,'D04')
C、用SQL表达以下查询:
找出那些工资高于其所在部门经理工资的职工的姓名及其工资.
SELECTS1.姓名,S1.工资
FROM
(SELECT*FROMEMP,DEPTWHEREEMP.所在部门=DEPT.部门号)ASS1,
(SELECT*FROMEMP,DEPTWHEREEMP.职工号=DEPT.部门经理的职工号)ASS2
WHERES1.工资>S2.工资ANDS1.所在部门=S2.部门号
D、用SQL完成:
将D01号部门经理的工资改为该部门所有职工的平均工资
UPDATEEMP
SET工资=(SELECTAVG(工资)
FROMEMPWHERE所在部门='D01')
WHERE职工号IN(SELECT部门经理的职工号
FROMDEPT
WHERE部门号='D01')
E、新增加一个广告部门,编号为D06
InsertintoDEPT((部门号,部门名,部门经理的职工号)values('D06','广告','')
select*fromDEPT
9、创建如下三个基本表,表结构如下:
BORROWER:
借书证号姓名系名班级
03001赵垒信管系03-1
03002刘诚信管系03-1
03101陈林计算机系03-2
LOANS:
借书证号图书登记号借书日期
03001T001012004.04.01
03002T001022004.05.01
04001T002012004.05.31
BOOKS
索书号书名作者图书登记号出版社价格
TP311.13数据结构李卫T00101科学19.00
CREATEDATABASELibrary
GO
USELibrary
GO
CREATETABLEBORROWER
借书证号varchar(10)Primarykey,主码
姓名varchar(10)notnull,
系名varchar(10),
班级varchar(10)
GO
CREATETABLEBOOKS
索书号varchar(10)notnull,
书名varchar(20)notnull,
作者varchar(10),
图书登记号varchar(10)Primarykey,主码
出版社varchar(10),
价格decimal(8,2)
GO
CREATETABLELOANS
借书证号varchar(10)FOREIGNKEYREFERENCESBORROWER(借书证号),外码
图书登记号varchar(10)FOREIGNKEYREFERENCESBOOKS(图书登记号),外码
借书日期datetime
GO
INSERTINTOBORROWER(借书证号,姓名,系名,班级)VALUES('03001','赵垒','信管系','03-1')
INSERTINTOBORROWER(借书证号,姓名,系名,班级)VALUES('03002','刘诚','信管系','03-1')
INSERTINTOBORROWER(借书证号,姓名,系名,班级)VALUES('03101','陈林','计算机系','03-2')
GO
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP311.13','数据结构','李卫','T00101','科学','19.00')
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP311.13','数据结构','李卫','T00102','科学','19.00')
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP.065','数据结构导论','李卫','T00201','北航','16.50')
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP.065','数据结构导论','李卫','T00202','北航','16.50')
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP.1599','数据通信','扬志','T00301','清华','28.50')
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP.1600','计算机网络','扬志','T00401','清华','38.50')
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP.1600','计算机网络','扬志','T00402','清华','38.50')
INSERTINTOBOOKS(索书号,书名,作者,图书登记号,出版社,价格)VALUES('TP.1610','软件工程','李新','T00501','清华','18.50')
GO
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03001','T00101','2004.04.01')
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03002','T00102','2004.04.01')
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03001','T00201','2004.04.01')
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03101','T00202','2004.05.31')
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03001','T00301','2004.04.01')
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03001','T00401','2004.04.01')
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03001','T00501','2004.04.01')
INSERTINTOLOANS(借书证号,图书登记号,借书日期)VALUES('03101','T00402','2004.05.31')
1)检索借了5本书以上的学生的借书证号\姓名\系名和借书数量.
SELECTB.借书证号,B.姓名,B.系名,COUNT(*)AS借书数量
FROMBORROWERASBINNERJOINLOANSASLONB.借书证号=L.借书证号
GROUPBYB.借书证号,B.姓名,B.系名
HAVING(COUNT(*)>=5)
2)检索借书和赵垒同学所借图书中任意一本相同的学生的姓名\系名\书名和借书日期
SELECTB.姓名,B.系名,K.书名,L.借书日期
FROMBORROWERASB,LOANSASL,BOOKSASK
WHEREB.借书证号=L.借书证号
ANDL.图书登记号=K.图书登记号
ANDK.索书号IN(SELECT索书号
FROMBORROWERASB,LOANSASL,BOOKSASK
WHEREB.借书证号=L.借书证号ANDL.图书登记号=K.图书登记号ANDB.姓名='赵垒')
ANDB.姓名<>'赵垒'
3)建立信管系学生借书的视图SB,该视图的属性列由借书证号\姓名\班级\图书登记号\书名\出版社和借书日期组成
CREATEVIEWSB
ASSELECTX.借书证号,X.姓名,X.班级,Y.图书登记号,Z.书名,Z.出版社,Y.借书日期
FROMBORROWERX
INNERJOINLOANSYONX.借书证号=Y.借书证号
INNERJOINBOOKSZONY.图书登记号=Z.图书登记号
WHERE(X.系名='信管系')
GO
SELECT*FROMSB
GO
样题四,现有一个学生选修课程的数据库,其中存放以下三个表:
学生(学号,姓名,性别,年龄,系别);
课程(课程号,课程名,任课教师);
选修(学号,课程号,分数)
请用SQL语言完成下列功能:
1.建表,在定义中要求声明:
(1)每个表的主外码;
(2)学生的年龄介于16到30之间;
(3)学生的姓名和课程名不能为空值;
(4)选课成绩要么为空值,要么取0~100之间的整数
CREATEDATABASEElective
GO
USEElective
GO
CREATETABLE学生
学号intprimarykey,主码
姓名varchar(10)NOTNULL,
性别char
(2),
年龄SmallintCHECK(年龄>=16AND年龄<=30),
系别varchar(10)
GO
CREATETABLE课程
课程号intprimarykey,主码
课程名varchar(20)NOTNULL,
任课老师varchar(10)
GO
CREATETABLE选修
学号intFOREIGNKEYREFERENCES学生(学号),外码
课程号intFOREIGNKEYREFERENCES课程(课程号),外码
分数intCHECK(分数ISNULLOR(分数>=0AND分数<=100))
INSERTINTO学生(学号,姓名,性别,年龄,系别)VALUES(101,'张三','男',18,'计算机')
INSERTINTO学生(学号,姓名,性别,年龄,系别)VALUES(102,'李四','男',16,'数学')
INSERTINTO学生(学号,姓名,性别,年龄,系别)VALUES(103,'王玲','女',17,'中文')
INSERTINTO学生(学号,姓名,性别,年龄,系别)VALUES(105,'李飞','男',19,'计算机')
INSERTINTO学生(学号,姓名,性别,年龄,系别)VALUES(109,'赵四','女',18,'历史')
INSERTINTO学生(学号,姓名,性别,年龄,系别)VALUES(110,'李平','男',20,'化学')
GO
INSERTINTO课程(课程号,课程名,任课老师)VALUES(203,'操作系统','程羽')
INSERTINTO课程(课程号,课程名,任课老师)VALUES(279,'高等数学','王备')
INSERTINTO课程(课程号,课程名,任课老师)VALUES(210,'现代文学','王丹')
INSERTINTO课程(课程号,课程名,任课老师)VALUES(243,'有机化学','沈同')
INSERTINTO课程(课程号,课程名,任课老师)VALUES(204,'数据结构','张青')
GO
INSERTINTO选修(学号,课程号,分数)VALUES(101,203,82)
INSERTINTO选修(学号,课程号,分数)VALUES(105,203,60)不及格在4小题会被删除,但5小题中需要用到
INSERTINTO选修(学号,课程号,分数)VALUES(102,203,59)另加
INSERTINTO选修(学号,课程号,分数)VALUES(102,279,90)
INSERTINTO选修(学号,课程号,分数)VALUES(101,279,88)
INSERTINTO选修(学号,课程号,分数)VALUES(105,279,82)
INSERTINTO选修(学号,课程号,分数)VALUES(110,279,68)
INSERTINTO选修(学号,课程号,分数)VALUES(109,210,72)
INSERTINTO选修(学号,课程号,分数)VALUES(103,210,90)
INSERTINTO选修(学号,课程号,分数)VALUES(110,243,92)
INSERTINTO选修(学号,课程号,分数)VALUES(101,204,85)
INSERTINTO选修(学号,课程号,分数)VALUES(105,204,91)
INSERTINTO选修(学号,课程号,分数)VALUES(10