Skip to main content

Joins

Overview​

In this lab we will practice using joins to retrieve data from multiple tables

EXERCISES​

  1. Inner join authors with titleauthor based on au_id.

  2. Inner join titles with publishers and show only titles, price, and publisher name.

  3. Inner join titles with publishers, and show the average price of books for each publisher.

  4. Inner join authors with titles and show only authors' names and titles' names. Hint: use your query solution from question 1 to get started.

  5. Retrieve the title_id of any titles with multiple authors.

  6. Duplicate and edit the previous query to find the names of any titles with multiple authors.

Click here to view the sample solution

SELECT * FROM authors JOIN titleauthor ON authors.au_id = titleauthor.au_id;

SELECT titles.title, titles.price, publishers.pub_name FROM titles JOIN publishers ON titles.pub_id = publishers.pub_id;

SELECT publishers.pub_name, AVG(titles.price) FROM titles JOIN publishers ON titles.pub_id = publishers.pub_id GROUP BY publishers.pub_id;

SELECT authors.au_fname, authors.au_lname, titles.title FROM authors JOIN titleauthor ON authors.au_id = titleauthor.au_id JOIN titles ON titleauthor.title_id = titles.title_id;

SELECT COUNT(authors.au_id) AS num_authors, titleauthor.title_id FROM authors JOIN titleauthor ON authors.au_id = titleauthor.au_id GROUP BY titleauthor.title_id;

SELECT COUNT(authors.au_id) AS num_authors, titles.title FROM authors JOIN titleauthor ON authors.au_id = titleauthor.au_id  JOIN titles ON titleauthor.title_id = titles.title_id GROUP BY titleauthor.title_id, titles.title HAVING COUNT(authors.au_id) > 1;

Two things differ from MySQL here: PostgreSQL requires every non-aggregated column in the SELECT list (titles.title) to also appear in GROUP BY, and it doesn't let HAVING refer back to a SELECT alias like num_authors - HAVING is evaluated before column aliases exist, so the aggregate expression has to be repeated.