华师大数据库往年上机考

上机测试一

现有医院数据库，包含以下表：

1、医生表：doctor

2、病人表：Patient

3、病历表：Patient_Chart

主键为（病人编号，医生编号，看病时间），而病人编号为外键，医生编号为外键

表中的数据

1、医生表：doctor

2、病人表：Patient

3、病历表：Patient_Chart

要求完成以下操作：

1、先创建书医院数据库，在该数据库中按照上述的描述创建3张表，除非特别

说明，否则字段值都不允许为空

2、将所有的数据录入到相应表中

3、完成以下对数据的操作：

（1）将有医疗保险的病人的病人编号、姓名、性别、年龄、联系电话、联系地址等信息查询出来。

（2）统计每个医生所看的病人数。

（3）将…李明?医生所看的病人的姓名、性别、联系电话、看病时间以及病历描述等信息查询出来

（4）将2009年12月在…内科?看过病的病人编号、病人姓名、看病时间以及病历描述查询出来。

上机测试二

现有书店管理数据库，包含以下表：

表中的数据：1

5

要求完成以下操作：

4、先创建书店管理数据库，在该数据库中按照上述的描述创建5张表，除非特

别说明，否则字段值都不允许为空

5、将所有的数据录入到相应表中

6、创建一个订单查询视图，该视图包括订单编号，每一张订单订购的书籍总数

量以及总价等信息。

7、完成以下对数据的操作：

a) 将类别为“计算机类”的书的编号，名称，作者，出版社，单价等信息查

询出来。

b) 利用订单查询视图，将“2009-12-01”日前生成的，并且已经发货的订单

的订单编号、订单的用户编号、订购的书籍总数量以及总价，订单生成

时间，发货时间等信息查询出来

c) 将订单编号为“2009001189”的用户姓名，联系方式，联系地址，所订购

的书籍编号，书籍名称，出版社，单价、订购的数量等信息查询出来。

参考答案一

--1、先创建书医院数据库，在该数据库中按照上述的描述创建3张表，

除非特别说明，否则字段值都不允许为空

create table doctor

(

doctor_id int primary key,

name char(10) not null,

sex char(2) check(sex in('男','女')) not null,

birthday datetime not null,

professional char(10) check(professional in('住院医师','主治医师','副主任医师','主任医师')) not null,

department char(20) not null

)

create table patient

(

patient_id int primary key,

name char(10) not null,

sex char(2) check(sex in('男','女')) not null,

age int not null,

insurance int not null,

telephone varchar(15) not null,

address varchar(50) not null

)

create table patient_chart

(

patient_id int,

doctor_id int,

see_time datetime,

descriptions varchar(100) not null,

primary key(patient_id,doctor_id,see_time),

foreign key(patient_id) references patient(patient_id),

foreign key(doctor_id) references doctor(doctor_id)

)

--2、将所有的数据录入到相应表中

insert into doctor values(10001,'张哓军','男','1966-9-10','副主任医师','

内科')

insert into doctor values(10002,'赵芳','女','1970-3-13','主治医师','外科') insert into doctor values(10003,'李明','男','1967-2-23','副主任医师','眼科')

insert into doctor values(10004,'吴永辉','男','1974-12-1','住院医师','口腔科')

insert into doctor values(10005,'周红','女','1950-1-2','主任医师','内科')

insert into Patient values(900001,'李芳','女',12,1,'62314512','通江路230号')

insert into Patient values(900002,'王元伟','男',10,1,'61111111','中山北路3663号')

insert into Patient values(900003,'李济刚','男',20,0,'62221111','东川路1000号')

insert into Patient values(900004,'陆西娅','女',24,1,'62221212','怒江路2300号')

insert into Patient values(900005,'赵杰','男',31,0,'68900000','金沙江路1000号')

insert into patient_chart values(900003,10001,'2009-9-10','上呼吸道感染')

insert into patient_chart values(900001,10003,'2009-10-2','结膜炎') insert into patient_chart values(900004,10003,'2009-11-23','结膜炎') insert into patient_chart values(900002,10002,'2009-11-25','左手手腕骨折')

insert into patient_chart values(900005,10004,'2009-12-1','口腔发炎') insert into patient_chart values(900001,10002,'2009-12-10','右腿脚腕扭伤')

insert into patient_chart values(900002,10003,'2009-12-11','结膜炎')

insert into patient_chart values(900003,10001,'2009-12-20','咽喉炎') insert into patient_chart values(900004,10004,'2009-12-24','上呼吸道感染')

--3、完成以下对数据的操作：

--（1）将有医疗保险的病人的病人编号、姓名、性别、年龄、联系电话、联系地址等信息查询出来。

select * from patient

--（2）统计每个医生所看的病人数。

select https://www.360docs.net/doc/8014610904.html,,count(patient_chart.patient_id)

from doctor,patient_chart

where doctor.doctor_id = patient_chart.doctor_id

group by https://www.360docs.net/doc/8014610904.html,

--（3）将‘李明’医生所看的病人的姓名、性别、联系电话、看病时间以及病历描述等信息查询出来

select

patient.patient_id,https://www.360docs.net/doc/8014610904.html,,patient.sex,patient.age,telephone,patie nt.address,see_time,descriptions

from patient,doctor,patient_chart

where https://www.360docs.net/doc/8014610904.html,='李明' and doctor.doctor_id=patient_chart.doctor_id and patient.patient_id=patient_chart.patient_id

--（4）将2009年12月在‘内科’看过病的病人编号、病人姓名、看病时间以及病历描述查询出来。

select

patient.patient_id,https://www.360docs.net/doc/8014610904.html,,patient_chart.see_time,descriptions from doctor,patient,patient_chart

where doctor.doctor_id=patient_chart.doctor_id and patient.patient_id=patient_chart.patient_id

and patient_chart.see_time between '2009-12-01' and '2010-12-31' and department='内科'

参考答案二

--1、先创建书店管理数据库，在该数据库中按照上述的描述创建5张表，除非特别说明，否则字段值都不允许为空

create table booktype

(

type_id int primary key,

typename Varchar(20) unique not null

)

create table book

(

Book_id int primary key,

Book_name Varchar(40) unique not null,

Type_id int not null,

author char(10),

Price Decimal(5,1) not null,

publisher Varchar(30) not null,

page int,

Publish_date datetime not null,

foreign key(Type_id) references booktype(type_id)

)

create table users

(

User_id i nt primary key,

password char(8) not null,

name char(10) not null,

email char(20) not null,

telephone char(15) not null,

address Varchar(50) not null

)

create table book_order

(

Order_id int primary key,

User_id i nt,

ordertime datetime,

sendtime datetime,

consignment tinyint,

foreign key(User_id) references users(User_id),

constraint chk_sendtime check((sendtime is null and consignment=0) or sendtime is not null)

)

create table order_detail

(

Book_id int,

Book_number int,

Order_id int,

primary key(Book_id,Order_id),

foreign key(Book_id) references book(Book_id),

foreign key(Order_id) references book_order(Order_id)

)

--2.将所有的数据录入到相应表中

insert into booktype values(1,'计算机类')

insert into booktype values(2,'经济类')

insert into booktype values(3,'管理类')

insert into booktype values(4,'外语类')

insert into book values(100001,'计算机基础',1,'李肖',17,'清华大学出版社',250,2008-9)

insert into book values(100002,'程序设计方法学',1,'吴永辉',25,'北大出版社',450,2009-9)

insert into book values(200003,'经济学',2,'王林',16,'社会科学出版社',300,2003-4)

insert into book values(100114,'数据库系统',1,'夏以琳',12,'复旦大学出版社',450,2002-7)

insert into book values(300021,'企业管理',3,'许戈福',21,'江西出版社',500,2007-12)

insert into book values(300006,'管理模式',3,'张千',14,'社会科学出版社',350,2008-7)

insert into book values(400001,'大学英语',4,'肖红',35,'上海外语出版社',550,2006-5)

insert into book values(400002,'英语翻译技巧',4,'周阳',16,'华东师范大学出版社',350,2008-9)

insert into users values(230001,'12345678','张丹','acbd@https://www.360docs.net/doc/8014610904.html,','61234901','通江路230号')

insert into users values(367009,'12345678','刘红','Ssss@https://www.360docs.net/doc/8014610904.html,','62235678','中山北路3663号')

insert into users values(128901,'12345678','赵明','qwer@https://www.360docs.net/doc/8014610904.html,','61111111','东川路1000号')

insert into users values(330002,'12345678','李芳','aaaaa@https://www.360docs.net/doc/8014610904.html,','62222222','怒江路2300号')

insert into book_order values(2009001189,230001,2009-11-20,2009-11-24,1)

insert into book_order values(2009001190,330002,2009-11-25,2009-11-28,1)

insert into book_order values(2009001191,128901,2009-11-30,null,0) insert into book_order values(2009001192,367009,2009-12-5,null,0)

insert into order_detail values(100001,2,2009001189)

insert into order_detail values(100002,1,2009001189)

insert into order_detail values(400001,2,2009001189)

insert into order_detail values(200003,1,2009001190)

insert into order_detail values(300021,2,2009001190)

insert into order_detail values(400002,1,2009001191)

insert into order_detail values(300006,2,2009001191)

insert into order_detail values(100114,1,2009001192)

--3、创建一个订单查询视图，该视图包括订单编号，每一张订单订购的书籍总数量以及总价等信息。

drop view order_search

create view order_search as

select order_id ,sum(price*book_number) as total_price,

sum(book_number) as total_num

from

order_detail od inner join book b

on od.book_id=b.book_id

group by order_id

--4.完成以下对数据的操作：

--a)将类别为“计算机类”的书的编号，名称，作者，出版社，单价等信

息查询出来。

select book_id,book_name,author,publisher,price from book

where type_id in

(select type_id from booktype where typename='计算机类')

--b)利用订单查询视图，将“2009-12-01”日前生成的，并且已经发货的订单的订单编号、订单的用户编号、订购的书籍总数量以及总价，

--订单生成时间，发货时间等信息查询出来

select

book_order.order_id,user_id,total_num,total_price,ordertime,sendtime from order_search,book_order

where ordertime < '2009-12-01' and consignment=1 and order_search.order_id = book_order.order_id

--c)将订单编号为“2009001189”的用户姓名，联系方式，联系地址，所订购的书籍编号，书籍名称，出版社，单价、订购的数量等信息查询出来。

select

name,email,telephone,address,book.book_id,book_name,publisher,pri ce,book_number

from users,book,book_order,order_detail

where order_detail.order_id=2009001189 and order_detail.book_id=book.book_id and order_detail.order_id =book_order.order_id

and book_https://www.360docs.net/doc/8014610904.html,er_id = https://www.360docs.net/doc/8014610904.html,er_id