The art of advanced queries using “subselects” or nested queries, using three examples.
We’ll use the bookworm database as in Lab 4 and Lab 5. Here, again, is the schema of this database:
Connect to the database:
$ psql bookworm“Find the titles of books that are below average in price.”
We could try
bookworm=> select btitle, bprice
bookworm-> from book
bookworm-> where bprice < avg(bprice);
ERROR: aggregates not allowed in WHERE clause
LINE 3: where bprice < avg(bprice);
^Ah, no.
Then, let’s find the average price:
bookworm=> select avg(bprice)
bookworm-> from book;
avg
---------------------
12.4189655172413793
(1 row)We can round that to the upper cent and plug in the number:
bookworm=> select btitle, bprice
from book
where bprice < 12.42;
btitle | bprice
-------------------------------------------+--------
The Karamazov Brothers | 10.00
Anna Karenina | 10.00
The Count of Monte Cristo | 4.95
The Three Musketeers | 5.95
The Phantom of the Opera | 2.50
Tess of the d'Urbervilles | 8.00
Jude the Obscure | 7.25
Far from the Madding Crowd | 7.95
The Return of the Native | 5.95
The Pickwick Papers | 10.00
Nicholas Nickleby | 7.95
David Copperfield | 7.95
Our Mutual Friend | 7.50
A Tale of Two Cities | 4.95
Oliver Twist | 4.95
A Christmas Carol | 2.95
Great Expectations | 4.95
The Good Earth | 5.50
Jane Eyre | 4.95
Wuthering Heights | 4.95
An Enquiry Concerning Human Understanding | 10.00
(21 rows)That works today. But if the mix of book prices changes, then the average price will also change, so each time we wanted to find the below average cost books, we’d have to find the average price again.
Instead of plugging in the result of the query, 12.42, let’s plug in the query itself. We have to enclose it in parentheses:
bookworm=> select btitle, bprice
from book
where bprice < (select avg(bprice) from book);
btitle | bprice
-------------------------------------------+--------
The Karamazov Brothers | 10.00
Anna Karenina | 10.00
The Count of Monte Cristo | 4.95
The Three Musketeers | 5.95
The Phantom of the Opera | 2.50
Tess of the d'Urbervilles | 8.00
Jude the Obscure | 7.25
Far from the Madding Crowd | 7.95
The Return of the Native | 5.95
The Pickwick Papers | 10.00
Nicholas Nickleby | 7.95
David Copperfield | 7.95
Our Mutual Friend | 7.50
A Tale of Two Cities | 4.95
Oliver Twist | 4.95
A Christmas Carol | 2.95
Great Expectations | 4.95
The Good Earth | 5.50
Jane Eyre | 4.95
Wuthering Heights | 4.95
An Enquiry Concerning Human Understanding | 10.00
(21 rows)This will work reliably, even if the average price changes.
The “query within a query”, enclosed in parentheses, is called a subselect. You could also call it a “nested query” or a “subquery.”
“Which books have more than one author? List the titles of these books.”
As a first step, we might find the book id’s (bid’s) of books with multiple authors:
bookworm=> select bid, count(aid)
bookworm-> from author_book
bookworm-> group by bid
bookworm-> having count(aid) > 1;
bid | count
------+-------
ilpr | 2
rein | 2
favs | 2
(3 rows)Then we could plug in the set of bid’s from that query into one that would show us the titles:
bookworm=> select bid, btitle
bookworm-> from book
bookworm-> where bid in ('ilpr', 'rein', 'favs');
bid | btitle
------+-----------------------------------------
rein | Reinforcement Learning: An Introduction
ilpr | Inductive Logic Programming
favs | Favorite Adventure Stories
(3 rows)
That seems to give the right results. So then we could try combining the two queries, using the first one as a subselect in the second:
bookworm=> select bid, btitle
from book
where bid in (select bid, count(aid)
bookworm(> from author_book
bookworm(> group by bid
bookworm(> having count(aid) > 1);
ERROR: subquery has too many columns
LINE 3: where bid in (select bid, count(aid)
^Oops! That didn’t quite work. Our subselect query returns two columns, bid and count(aid). It doesn’t make sense to say “bid in” something that has two columns, since bid is only one column. So we should limit columns from the subselect by using only select bid instead of select bid, count(aid). That gives
bookworm=> select bid, btitle
from book
where bid in (select bid
from author_book
group by bid
having count(aid) > 1);
bid | btitle
------+-----------------------------------------
rein | Reinforcement Learning: An Introduction
ilpr | Inductive Logic Programming
favs | Favorite Adventure Stories
(3 rows)“Delete books having a price higher than $35 from the book and author_book tables.”
We have to delete books from two tables, and there is a foreign key constraint, where author_book.bid references book.bid. So we must delete the tuples first from author_book, then from book.
Since you have read-only access to the bookworm database, you’ll need to do this with your personal copy, which you can create as directed in Lab 6. I’ll use mine:
$ psql gdweber_bookwormSome of you might be tempted to do it this way:
gdweber_bookworm=> start transaction;
START TRANSACTION
gdweber_bookworm=> delete from book natural join author_book
gdweber_bookworm-> where bprice > 35;
ERROR: syntax error at or near "natural"
LINE 1: delete from book natural join author_book
^
gdweber_bookworm=> rollback;
ROLLBACKThat doesn’t work. The natural join of two tables is not something that is stored in the database, so it’s not something that you can delete rows from. Some screwball databases like MySQL might let you do this, but it’s non-standard SQL, and PostgreSQL does not let you get away with it.
So we need to delete from two tables, but one table at a time.
It’s easy to find the books priced over $35:
gdweber_bookworm=> select bid, btitle, bprice
from book
where bprice > 35;
bid | btitle | bprice
------+-----------------------------------------+--------
cltl | Common Lisp: The Language | 39.00
rein | Reinforcement Learning: An Introduction | 40.00
ilpr | Inductive Logic Programming | 37.50
(3 rows)So, one way to do this would be to use the bid’s returned from this query:
gdweber_bookworm=> start transaction;
START TRANSACTION
gdweber_bookworm=> delete from author_book
gdweber_bookworm-> where bid in ('cltl', 'rein', 'ilpr');
DELETE 5
gdweber_bookworm=> delete from book
gdweber_bookworm-> where bprice > 35;
DELETE 3
gdweber_bookworm=> rollback;
ROLLBACKThat would have worked, but I rolled back the transaction because that’s not the way I want to do it. I want to do it in a way that’s repeatable and reliable; in short, a way that does not depend on those particular bid values.
Again, the solution is to replace the set of bid values ('cltl', 'rein', 'ilpr') with the query that resulted in them (and of course, I’m doing to delete btitle and bprice from this query when I use it as a subselect, since we’re only interested in bid):
gdweber_bookworm=> start transaction;
START TRANSACTION
gdweber_bookworm=> delete from author_book
gdweber_bookworm-> where bid in
gdweber_bookworm-> (select bid from book
gdweber_bookworm(> where bprice > 35);
DELETE 5
gdweber_bookworm=> delete from book
gdweber_bookworm-> where bprice > 35;
DELETE 3
gdweber_bookworm=> select *
gdweber_bookworm-> from book natural join author_book
gdweber_bookworm-> where bprice > 35;
bid | btitle | pid | bdate | bpages | bprice | aid
-----+--------+-----+-------+--------+--------+-----
(0 rows)
gdweber_bookworm=> commit;
COMMIT
gdweber_bookworm=> select *
from book natural join author_book
where bprice > 35;
bid | btitle | pid | bdate | bpages | bprice | aid
-----+--------+-----+-------+--------+--------+-----
(0 rows)This time I committed the transaction, since I was happy with both the result and the process, and then verified the result a second time. But you probably shouldn’t, because then you would be starting from the wrong data for Lab 6. So, if you committed your transaction, recreate your personal bookworm database copy before beginning Lab 6.