Database design instance (2) Multi-table joint query, database instance

Database design instance (2) Multi-table joint query, database instance

Tested at oracle10g:

I. Question requirements

Books)

Book_id book_name creatdate Lastmodifydate decription
001 the world of three persons NULL

Author table (authors)
A_id A_name
01 Wang Fan
02 Li Shang
03 Taihe

Department table (depts)
D_id d_name
001 edit one
002 Editor 2
003 edit 3

Bookmap)
Book_id A_id
001 01
001 02
001 03

Department and author Association Table (depmap)
D_id a_id
001 01
002 02
003 03

Find out the total number of books written by each department. For example, if three people write a book in different departments,
The total number of each department is 1. The final result is as follows:
Department books
Edit Part 1
Edit Part 2 1

Edit Part 3 1

1. Table creation statements and data insertion statements:

-- Create table books (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', 'one person's sky', '1-January 1, April-2005 ', '29-March July-2005 '); insert into books (book_id, book_name, creatdate, Lastmodifydate) values ('002', 'two-person nest ', '1-December-2001 ', '11-April-2002'); insert into books (book_id, book_name, creatdate, Lastmodifydate) values ('003 ', 'Three worlds ', '1-April-2005', '29-July-2005 '); insert into books (book_id, book_name, creatdate, Lastmodifydate) values ('004 ', 'Land of the four people', '1-January 1, 2001-100', '11-January 1, 2002-'); insert into books (book_id, book_name, creatdate, lastmodifydate) values ('005 ', 'five people's futuristic', '1-April-2005 ', '29-July-2005'); -- author table (authors) create table authors (A_id varchar (4) primary key, A_name varchar (20); insert into authors values ('01', 'zhangsan '); insert into authors values ('02 ', 'Lee 4'); insert into authors values ('03', 'wang 5'); insert into authors values ('04 ', 'Ma 6'); -- department table (depts) create table depts (d_id varchar (4) primary key, d_name varchar (20); insert into depts values ('01 ', 'edit a Part'); insert into depts values ('02 ', 'edit two parts'); insert into depts values ('03', 'edit three parts '); insert into depts values ('04 ', 'edit fourth'); -- 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'); -- 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. Find the total number of books written by each department

-- The first step is to find the correspondence between the department number and the total number of books published. You need to use (bookmap) and (depmap) select d_id, count (book_id) from bookmap, depmap where depmap. a_id = bookmap. a_id group by d_id; -- Step 2: Check the 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 result is as follows: