資料庫設計執行個體（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_idvarchar(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;--第二步,再結合部門表查出部門名selectd_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 deptsondepts.d_id=inid;

查詢結果如下：