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

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

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

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;

Hint

Add PRIMARY KEY after BookID INTEGER in the table creation.

Practice

1. What does a row represent in a database table?

easy

A. A group of tables linked together

B. The name of the table

C. The type of data stored in the table

D. A single record containing data for each column

Solution

1. Step 1: Understand the structure of a table

   A table is made up of rows and columns where columns define data types and rows hold data.

2. Step 2: Identify what a row represents

   A row is one complete record with values for each column in the table.

3. Final Answer:

   A single record containing data for each column -> Option D

4. Quick Check:

   Row = single record [OK]

Hint: Rows = records, columns = data types [OK]

Common Mistakes:

• Confusing rows with columns
• Thinking rows are table names
• Mixing up rows with database schemas

2. Which of the following is the correct way to select all columns from a table named Employees?

easy

A. GET * FROM Employees;

B. SELECT ALL COLUMNS Employees;

C. SELECT * FROM Employees;

D. SELECT COLUMNS FROM Employees;

Solution

1. Step 1: Recall SQL syntax for selecting all columns

   The standard SQL command to select all columns is SELECT * FROM table_name;.

2. Step 2: Match the correct syntax

   SELECT * FROM Employees; matches the correct syntax exactly.

3. Final Answer:

   SELECT * FROM Employees; -> Option C

4. Quick Check:

   SELECT * = all columns [OK]

Hint: Use * to select all columns [OK]

Common Mistakes:

• Using GET instead of SELECT
• Writing SELECT ALL COLUMNS which is invalid
• Omitting FROM keyword

3. Given the table Students with columns id, name, and age, what will this query return?

SELECT name FROM Students WHERE age > 20;

medium

A. Only the names of students older than 20

B. All columns for students older than 20

C. Only the ages of students older than 20

D. An error because age is not selected

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only the name column from the table.

2. Step 2: Analyze the WHERE condition

   It filters rows where age is greater than 20, so only those students are included.

3. Final Answer:

   Only the names of students older than 20 -> Option A

4. Quick Check:

   SELECT name WHERE age > 20 = names only [OK]

Hint: SELECT columns control output, WHERE filters rows [OK]

Common Mistakes:

• Thinking all columns are returned
• Assuming age column is returned without selecting it
• Believing query causes error due to WHERE clause

4. What is wrong with this SQL query?

SELECT id, name FROM Students WHERE age = 'twenty';

medium

A. Using quotes around a number in WHERE clause causes error

B. Comparing age to a string 'twenty' may cause no matches

C. Selecting multiple columns requires commas

D. Missing FROM keyword before table name

Solution

1. Step 1: Check the WHERE clause comparison

   The query compares age to the string 'twenty', but age is numeric.

2. Step 2: Understand effect of comparing number to string

   This will not cause a syntax error but likely returns zero rows because no numeric age equals 'twenty'.

3. Final Answer:

   Comparing age to a string 'twenty' may cause no matches -> Option B

4. Quick Check:

   Wrong data type comparison = no results [OK]

Hint: Match data types in WHERE clause [OK]

Common Mistakes:

• Thinking quotes cause syntax error
• Assuming query returns matching rows
• Ignoring data type mismatch

5. You have a table Orders with columns order_id, customer_id, and amount. You want to find the total amount spent by each customer. Which SQL query correctly groups and sums the data?

hard

A. SELECT customer_id, SUM(amount) FROM Orders GROUP BY customer_id;

B. SELECT order_id, SUM(amount) FROM Orders GROUP BY order_id;

C. SELECT customer_id, amount FROM Orders WHERE SUM(amount) > 100;

D. SELECT customer_id, amount FROM Orders GROUP BY amount;

Solution

1. Step 1: Identify the goal

   We want total amount spent per customer, so we must group by customer_id.

2. Step 2: Use aggregation function

   SUM(amount) calculates total amount per group.

3. Step 3: Check query correctness

   SELECT customer_id, SUM(amount) FROM Orders GROUP BY customer_id; correctly groups by customer_id and sums amount.

4. Final Answer:

   SELECT customer_id, SUM(amount) FROM Orders GROUP BY customer_id; -> Option A

5. Quick Check:

   Group by customer_id + SUM(amount) = total per customer [OK]

Hint: Group by key, aggregate values with SUM [OK]

Common Mistakes:

• Grouping by wrong column
• Using WHERE with aggregate functions incorrectly
• Selecting columns not in GROUP BY without aggregation