I took a lab exam today.
However, though the exam is over I'm still thinking about
one of the questions.
I had a schema for a bookstore database defined like this:
Book:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ISBN | varchar(30) | NO | PRI | NULL | |
| Title | varchar(50) | NO | | NULL | |
| PubDate | year(4) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
Author:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Aname | varchar(50) | NO | PRI | NULL | |
| DOB | date | NO | | NULL | |
| Sex | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
Wrote:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Aname | varchar(50) | NO | MUL | NULL | |
| ISBN | varchar(30) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
Plus some other tables about the publishers, not relevant to
this question.
A book can have several authors who wrote the book together.
The question was:
"Find the book(s) with the largest number of authors."
After a while I came up with the following query which
seemed to work.
select Title
from Book
where ISBN=
(
select ISBN
from
(
select W.ISBN,
count(W.ISBN) as cnt
from Wrote W
group by W.ISBN
) as A
where A.cnt=
(
select max(B.cnt)
from
(
select count(W.ISBN) as cnt
from Wrote W
group by W.ISBN
) as B
)
);
But I find the query to by somewhat ugly so I was wondering
if there is a more elegant/simple way of achieving the same
result without defining views which was not allowed.
In particular it is bothering me that I count the number of
authors per book twice. If possible I would have liked to
limit the nesting depth as well.
Mikkel.
