Tables, rows, and columns concept in SQL - Mini Project: Build & Apply

Understanding Tables, Rows, and Columns in SQL

📖 Scenario: You are working as a junior data analyst for a small bookstore. You need to create a simple database table to store information about books in the store.

🎯 Goal: Create a table called Books with columns for BookID, Title, and Author. Then insert some rows with book data. Finally, write a query to select all rows and columns from the table.

📋 What You'll Learn

Create a table named Books with columns BookID (integer), Title (text), and Author (text).

Insert exactly three rows into the Books table with the specified data.

Write a query to select all columns and rows from the Books table.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use tables to organize data about products, customers, and transactions.

💼 Career

Understanding tables, rows, and columns is fundamental for database jobs like data analyst, database administrator, and backend developer.

1

Create the Books table

Write a SQL statement to create a table called Books with three columns: BookID as an integer, Title as text, and Author as text.

SQL

-- Create the Books table with BookID, Title, and Author columns
-- Your code here

Need a hint?

Use CREATE TABLE Books and define each column with its data type.

2

Insert rows into the Books table

Write three SQL INSERT INTO statements to add these rows to the Books table:
1. BookID: 1, Title: 'The Great Gatsby', Author: 'F. Scott Fitzgerald'
2. BookID: 2, Title: '1984', Author: 'George Orwell'
3. BookID: 3, Title: 'To Kill a Mockingbird', Author: 'Harper Lee'

SQL

CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Author TEXT
);
-- Insert the three specified rows into Books
-- Your code here

Need a hint?

Use three separate INSERT INTO Books (BookID, Title, Author) VALUES (...) statements with the exact values.

3

Write a query to select all data

Write a SQL SELECT statement to retrieve all columns and all rows from the Books table.

SQL

CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Author TEXT
);

INSERT INTO Books (BookID, Title, Author) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald');
INSERT INTO Books (BookID, Title, Author) VALUES (2, '1984', 'George Orwell');
INSERT INTO Books (BookID, Title, Author) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Select all columns and rows from Books
-- Your code here

Need a hint?

Use SELECT * FROM Books; to get all rows and columns.

4

Add a primary key constraint

Modify the Books table creation statement to add a primary key constraint on the BookID column.

SQL

-- Modify the Books table creation to add PRIMARY KEY on BookID
CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    Author TEXT
);

INSERT INTO Books (BookID, Title, Author) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald');
INSERT INTO Books (BookID, Title, Author) VALUES (2, '1984', 'George Orwell');
INSERT INTO Books (BookID, Title, Author) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee');

SELECT * FROM Books;

Need a hint?

Add PRIMARY KEY after BookID INTEGER in the table creation.