sqlintermediate15 minutes

Design and Query a Library Management Data Model

Create a SQL data model for a library management system and write queries to retrieve meaningful information about books, authors, borrowers, and loans.

Challenge prompt

You are tasked with designing a simple relational database schema for a library management system. Your database must include tables for Books, Authors, Borrowers, and Loans. Each book can have multiple authors, and each borrower can have multiple loans. After creating the schema and inserting sample data, write a SQL query to find the names of all borrowers who currently have at least one book on loan that was authored by 'Jane Austen'. Include the book title, borrower name, and loan date in your results.

Guidance

• Design tables with appropriate primary keys and foreign keys to represent the relationships.
• Consider a junction table for the many-to-many relationship between Books and Authors.
• Write a query using JOINs to connect Borrowers, Loans, Books, and Authors.
• Filter loans to include only those not yet returned (assume a NULL return date).

Hints

• The many-to-many relationship between Books and Authors requires a separate linking table.
• Use INNER JOINs to combine the data across multiple tables.
• Filter on author name in the WHERE clause and check for NULL return_date to find current loans.

Starter code

CREATE TABLE Authors (
  author_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE Books (
  book_id INT PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);

CREATE TABLE BookAuthors (
  book_id INT,
  author_id INT,
  PRIMARY KEY (book_id, author_id),
  FOREIGN KEY (book_id) REFERENCES Books(book_id),
  FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE Borrowers (
  borrower_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE Loans (
  loan_id INT PRIMARY KEY,
  book_id INT,
  borrower_id INT,
  loan_date DATE,
  return_date DATE,
  FOREIGN KEY (book_id) REFERENCES Books(book_id),
  FOREIGN KEY (borrower_id) REFERENCES Borrowers(borrower_id)
);

Expected output

A result set containing columns: title, name, loan_date for all borrowers who have an active loan (return_date IS NULL) of a book authored by 'Jane Austen'. For example: | title | name | loan_date | |-----------------------|-----------------|------------| | Pride and Prejudice | Emily Clark | 2024-03-01 | | Sense and Sensibility | John Doe | 2024-04-15 |

Core concepts

SQL JOINsmany-to-many relationshipsforeign keysquery filtering

Challenge a Friend

Send this duel to someone else and see if they can solve it.