What is a database in SQL - Hands-On Activity

Understanding What a Database Is

📖 Scenario: Imagine you run a small bookstore. You want to keep track of your books, customers, and sales in an organized way. A database helps you store and manage this information easily.

🎯 Goal: You will create a simple table to represent a database storing book information. This will help you understand what a database is and how data is organized inside it.

📋 What You'll Learn

Create a table called Books with columns BookID, Title, and Author

Insert three books with exact details into the Books table

Write a query to select all columns from the Books table

💡 Why This Matters

🌍 Real World

Databases are used everywhere to keep information organized, like in stores, websites, and apps.

💼 Career

Understanding databases is important for jobs in data management, software development, and many tech roles.

1

Create the Books table

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

SQL

-- Write the CREATE TABLE statement for Books
-- Your code here

Hint

Use CREATE TABLE followed by the table name and define each column with its data type.

2

Insert book records

Write three SQL INSERT INTO Books statements to add these exact books:
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 PRIMARY KEY,
  Title TEXT,
  Author TEXT
);
-- Insert the three books below
-- Your code here

Hint

Use INSERT INTO Books (BookID, Title, Author) VALUES (...) for each book.

3

Select all books

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

SQL

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');

-- Write a SELECT query to get all books
-- Your code here

Hint

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

4

Explain what a database is

Add a SQL comment at the top of your code explaining in simple words what a database is. Use the exact phrase: -- A database is a place to store and organize data so we can find and use it easily.

SQL

-- Add a comment explaining what a database is
-- Your code here

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;

Hint

Start your code with a comment using -- and write the exact phrase given.

Practice

1. What is a database primarily used for?

easy

A. Writing emails

B. Designing websites

C. Storing and organizing data

D. Creating graphics

Solution

1. Step 1: Understand the purpose of a database

   A database is designed to store and organize data efficiently.

2. Step 2: Compare with other options

   Designing websites, writing emails, and creating graphics are not main functions of a database.

3. Final Answer:

   Storing and organizing data -> Option C

4. Quick Check:

   Database = Store and organize data [OK]

Hint: Databases store data, not create websites or emails [OK]

Common Mistakes:

• Confusing database with website design
• Thinking database is for email writing
• Mixing database with graphic creation

2. Which SQL command is used to create a new table in a database?

easy

A. INSERT

B. UPDATE

C. SELECT

D. CREATE

Solution

1. Step 1: Identify the command for creating tables

   The SQL command to create a new table is CREATE.

2. Step 2: Understand other commands

   INSERT adds data, SELECT retrieves data, and UPDATE modifies data, none create tables.

3. Final Answer:

   CREATE -> Option D

4. Quick Check:

   CREATE = Make new table [OK]

Hint: CREATE makes tables; INSERT adds data [OK]

Common Mistakes:

• Using INSERT to create tables
• Confusing SELECT with CREATE
• Thinking UPDATE creates tables

3. Given the table Users with columns id and name, what does this query return?

SELECT name FROM Users WHERE id = 2;

medium

A. User name with id 2

B. All user names

C. User id 2

D. Error: missing table

Solution

1. Step 1: Analyze the SELECT statement

   The query selects the name column from Users where id equals 2.

2. Step 2: Understand the WHERE clause effect

   It filters rows to only those with id = 2, so only that user's name is returned.

3. Final Answer:

   User name with id 2 -> Option A

4. Quick Check:

   SELECT name WHERE id=2 = User name [OK]

Hint: WHERE filters rows; SELECT picks columns [OK]

Common Mistakes:

• Thinking it returns all names
• Confusing id with name in output
• Assuming syntax error without reason

4. Identify the error in this SQL statement:

SELEC * FROM Customers;

medium

A. Missing table name

B. Typo in SELECT keyword

C. Incorrect use of *

D. No error

Solution

1. Step 1: Check the SQL keywords

   The keyword SELEC is misspelled; it should be SELECT.

2. Step 2: Verify other parts

   The table name Customers is present and * is correctly used to select all columns.

3. Final Answer:

   Typo in SELECT keyword -> Option B

4. Quick Check:

   SELECT typo causes error [OK]

Hint: Check spelling of SQL keywords carefully [OK]

Common Mistakes:

• Ignoring keyword typos
• Thinking * is invalid
• Assuming missing table name

5. You want to store information about books with columns id, title, and author. Which SQL command correctly creates this table?

hard

A. CREATE TABLE Books (id INT, title VARCHAR(100), author VARCHAR(100));

B. INSERT INTO Books (id, title, author);

C. SELECT id, title, author FROM Books;

D. UPDATE Books SET title = 'New Title';

Solution

1. Step 1: Identify the command to create a table

   The CREATE TABLE command defines a new table with specified columns and types.

2. Step 2: Check the syntax correctness

   CREATE TABLE Books (id INT, title VARCHAR(100), author VARCHAR(100)); correctly uses CREATE TABLE with column names and data types.

3. Step 3: Understand other commands

   INSERT INTO adds data, SELECT retrieves data, and UPDATE modifies data, none create tables.

4. Final Answer:

   CREATE TABLE Books (id INT, title VARCHAR(100), author VARCHAR(100)); -> Option A

5. Quick Check:

   CREATE TABLE defines structure [OK]

Hint: CREATE TABLE defines columns and types [OK]

Common Mistakes:

• Using INSERT to create tables
• Confusing SELECT with CREATE
• Trying to UPDATE before table exists