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
升级会员 