数据库设计实例（2）多表联合查询，数据库实例

数据库设计实例（2）多表联合查询，数据库实例

在oracle10g测试通过：

一、题目要求

书表(books)

book_id     book_name         creatdate        Lastmodifydate    decription
001         三个人的世界     2005-02-02         2005-07-07        NULL

作者表(authors)
A_id    A_name
01        王纷
02        李尚
03        泰和

部门表(depts)
d_id    d_name
001        编辑一部
002        编辑二部
003        编辑三部

书和作者关联表(bookmap)
book_id        A_id
001            01
001            02
001            03

部门和作者关联表(depmap)
d_id        a_id
001        01
002        02
003        03

找出每个部门的所写的总书量,比如,一本书有3 个人写,如果三个人在不同的部门,
则每个部门的总数量就是1.最后结果如下:
部门        书量
编辑一部    1
编辑二部    1

编辑三部    1

1.建表语句与插入数据的语句:

--书表(books)
create table books
(
	book_id varchar(10) primary key,
	book_name varchar(30),
	creatdate date,
	Lastmodifydate date,
	decription varchar(500)
);
insert into books (book_id,book_name,creatdate,Lastmodifydate) values('001','一个人的天空','1-4月-2005','29-7月-2005');
insert into books (book_id,book_name,creatdate,Lastmodifydate) values('002','两个人的小窝','1-12月-2001','11-4月-2002');
insert into books (book_id,book_name,creatdate,Lastmodifydate) values('003','三个人的世界','1-4月-2005','29-7月-2005');
insert into books (book_id,book_name,creatdate,Lastmodifydate) values('004','四个人的大地','1-12月-2001','11-4月-2002');
insert into books (book_id,book_name,creatdate,Lastmodifydate) values('005','五个人的未来','1-4月-2005','29-7月-2005');
--作者表(authors)
create table authors
(
	A_id varchar(4) primary key,
	A_name varchar(20)
);
insert into authors values('01','张三');
insert into authors values('02','李四');
insert into authors values('03','王五');
insert into authors values('04','马六');
--部门表(depts)
create table depts
(
	d_id varchar(4) primary key,
	d_name varchar(20)
);
insert into depts values('01','编辑一部');
insert into depts values('02','编辑二部');
insert into depts values('03','编辑三部');
insert into depts values('04','编辑四部');
--书和作者关联表(bookmap)
create table bookmap
(
book_id	varchar(10),	
A_id varchar(4)
);
insert into bookmap values('001','01');
insert into bookmap values('002','01');
insert into bookmap values('003','01');
insert into bookmap values('004','01');
insert into bookmap values('004','02');
insert into bookmap values('005','03');
--部门和作者关联表(depmap)
create table depmap
(
d_id varchar(4),	
A_id varchar(4)
);
insert into depmap values('01','01');
insert into depmap values('02','02');
insert into depmap values('02','04');
insert into depmap values('03','03');
commit;

2.找出每个部门的所写的总书量

--第一步找到部门编号和出书总量的对应关系，需要用到(bookmap)以及(depmap)
select d_id,count(book_id) from bookmap,depmap where depmap.A_id=bookmap.A_id group by d_id;
--第二步,再结合部门表查出部门名
select
d_name,nvl(books.cou,0)
from
	(select d_id inid,count(book_id) as cou 
	from 
	bookmap,depmap 
	where 
	depmap.A_id=bookmap.A_id 
	group 
	by d_id) 
    books 
	right outer join 
	depts
on
depts.d_id=inid;

查询结果如下：