Some SQL queries
Here are some queries for the books/authors database. Load up the database on stearns and try these out. Then try to make sense of how they do what they do.
Reminder: loading the database
- Login to stearns with VS Code.
- Update your copy of my cs257-2025-spring repository.
- Change directories to my repo's
postgresqldirectory. - Load the books database:
psql < books.sql.
Sample queries
SELECT * FROM books;
SELECT * FROM books LIMIT 5;
SELECT * FROM books ORDER BY publication_year;
SELECT title FROM books ORDER BY publication_year;
SELECT * FROM books ORDER BY publication_year DESC;
SELECT books.title, books.publication_year
FROM books, authors, books_authors
WHERE authors.surname = 'Willis'
AND authors.id = books_authors.author_id
AND books.id = books_authors.book_id;
SELECT authors.given_name, authors.surname, books.title, books.publication_year
FROM books, authors, books_authors
WHERE books.id = books_authors.book_id
AND authors.id = books_authors.author_id;
SELECT authors.given_name, authors.surname, books.title, books.publication_year
FROM books, authors, books_authors
WHERE books.id = books_authors.book_id
AND authors.id = books_authors.author_id
AND authors.surname LIKE 'B%'
ORDER BY authors.surname, authors.given_name;
SELECT authors.given_name || ' ' || authors.surname, COUNT(*) AS book_count
FROM books, authors, books_authors
WHERE books.id = books_authors.book_id
AND authors.id = books_authors.author_id
GROUP BY authors.surname, authors.given_name
ORDER BY book_count DESC;
SELECT authors.given_name, authors.surname, COUNT(*) AS book_count
FROM authors, books_authors
WHERE authors.id = books_authors.author_id
GROUP BY authors.surname, authors.given_name
ORDER BY book_count DESC;