Database Design Example (2) Multi-table Union query

In the oracle10g test pass:

First, the topic requirements

Book list (books)

book_id book_name creatdate lastmodifydate decription
13 Human World 2005-02-02 2005-07-07 NULL

Author's table (authors)
A_ID A_name
01 Wang Fen
02 Li Shang
03 Taihe

Department Table (depts)
d_id D_name
001 Editing a
002 edition of Two
003 edition of Three

Book and Author Association Table (BOOKMAP)
book_id a_id
001 01
001 02
001 03

Department and Author Association tables (DEPMAP)
d_id a_id
001 01
002 02
003 03

Find out the total amount of books written by each department, for example, a book that has 3 people written, if three people in different departments,
The total number of each department is 1. The final result is as follows:
Department Book Volume
Edit a 1
Editing Section two 1

Editing Section three 1

1. Build the statement with the statements that insert the data:

--Book table (books) Create TABLE books (book_id varchar) primary key,book_name varchar (+), creatdate date,lastmodifydate Date , decription varchar); INSERT into books (book_id,book_name,creatdate,lastmodifydate) VALUES (' 001 ', ' A man's Sky ', ' January-April-2005 ', ' 2 September-July -2005 '); insert into books (book_id,book_name,creatdate,lastmodifydate) VALUES (' 002 ', ' two-person's Den ', ' January-December-2001 ', ' November-April -2002 '); insert into books (book_id,book_name,creatdate,lastmodifydate) VALUES (' 003 ', ' Three Human worlds ', ' January-April-2005 ', ' 2 September-July -2005 '); insert into books (book_id,book_name,creatdate,lastmodifydate) VALUES (' 004 ', ' Four Human earth ', ' January-December-2001 ', ' November-April -2002 '); insert into books (book_id,book_name,creatdate,lastmodifydate) VALUES (' 005 ', ' Five person's future ', ' January-April-2005 ', ' 2 September-July-2005 ');--Author's Table (authors) CREATE TABLE authors (a_id varchar (4) primary key,a_name varchar); insert Into authors values (' 01 ', ' Zhang San '), insert into authors values (' 02 ', ' John Doe '), insert into authors values (' 03 ', ' Harry '); INSERT into Authors values (' 04 ', ' Caifan ');--Department table (depts) CREATE TABLE depts (d_id varchar (4) Primary Key,d_name varchar); INSERT into depts values (' 01 ', ' Edit a '), insert into depts values (' 02 ', ' Edit two '), insert into depts values (' 03 ' , ' edit three '); INSERT into depts values (' 04 ', ' Edit Four ');--Book and Author Association Table (BOOKMAP) CREATE TABLE Bookmap (Book_idvarchar), a_id varchar (4)) insert into BOOKMAP values (' 001 ', ' n '), insert into bookmap values (' 002 ', ' n '), insert into bookmap values (' 003 ', ' n '), insert into bookmap values (' 004 ', ' n '), insert into bookmap values (' 004 ', ' "."), insert into bookmap values (' 005 ', ' 03 ');--Department and Author Association table (DEPMAP) CREATE TABLE Depmap (d_id varchar (4), a_id varchar (4)); INSERT into depmap values (' 01 ', ' 01 ' INSERT into Depmap values (' ', ' n ' '), insert into depmap values (' n ' ', ' "), insert into depmap values (' n ', '"); commit ;

2. Find out the total number of books written by each department

--The first step is to find the correspondence between the department number and the total amount of the book, which needs to be used (BOOKMAP) and (depmap) Select D_id,count (book_id) from Bookmap,depmap where Depmap. A_id=bookmap. A_ID GROUP BY d_id;--Second step, combined with departmental table to identify department name 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;

The query results are as follows:

Database Design Example (2) Multi-table Union query