Database Design Example (2) Multi-table Union query

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.