sqlbeginner10 minutes

Create a Simple Library Database Schema with Tables and Queries

Build a basic SQL schema for a library system and write queries to retrieve information about books and authors.

Challenge prompt

Create two tables: 'Authors' and 'Books'. The 'Authors' table should include columns for 'AuthorID' (primary key) and 'AuthorName'. The 'Books' table should include columns for 'BookID' (primary key), 'Title', and 'AuthorID' (foreign key referencing 'Authors'). Then, write an SQL query that lists all book titles along with their author names.

Guidance

• Define primary keys for each table appropriately.
• Ensure the foreign key relationship between 'Books' and 'Authors' is correct.
• Write a SELECT query joining both tables to display each book title with its corresponding author.

Hints

• Use 'CREATE TABLE' statements to define the tables with columns and data types.
• Use 'INNER JOIN' to connect the 'Books' table to the 'Authors' table on 'AuthorID'.
• Remember to use aliases for tables to make the SELECT query easier to read.

Starter code

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  AuthorName VARCHAR(100)
);

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(200),
  AuthorID INT,
  FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

-- Write your query below

Expected output

Title | AuthorName ----------------|---------------- Harry Potter | J.K. Rowling The Hobbit | J.R.R. Tolkien To Kill a Mockingbird | Harper Lee

Core concepts

SQL CREATE TABLEPrimary KeyForeign KeyJOIN

Challenge a Friend

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