Relational database basics in Intro to Computing - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Primary Keys in Tables

Which of the following best describes the role of a primary key in a relational database table?

A It uniquely identifies each row in the table.

B It stores the largest value in a column.

C It links two unrelated tables without any common data.

D It is used to encrypt the data in the table.

💡 Hint

Think about how you would find one unique record among many in a list.

A primary key is a unique identifier for each row in a table, ensuring no two rows have the same key value.

❓ trace

intermediate

Trace the Result of a JOIN Operation

Given two tables, Students and Enrollments, what will be the result of the following SQL query?

SELECT Students.Name, Enrollments.Course FROM Students JOIN Enrollments ON Students.ID = Enrollments.StudentID;

Intro to Computing

Students table:
ID | Name
1  | Alice
2  | Bob

Enrollments table:
StudentID | Course
1         | Math
1         | Science
2         | History

A

Alice - Math
Alice - Science
Bob - Science

B

Alice - Math
Bob - Science
Bob - History

C

Alice - Math
Alice - Science
Bob - History

D

Alice - History
Bob - Math
Bob - Science

💡 Hint

Think about matching rows where Students.ID equals Enrollments.StudentID.

The JOIN matches each student with their enrolled courses by matching IDs, producing one row per matching pair.

❓ identification

advanced

Identify the Error in a Table Design

Which of the following table designs violates the rules of normalization in relational databases?

A A table with columns for product ID, product name, and price, with product ID as primary key.

B A table with a unique ID column as the primary key and separate columns for first and last names.

C Two tables linked by a foreign key where one stores orders and the other stores customers.

D A table where multiple phone numbers are stored in a single column separated by commas.

💡 Hint

Normalization avoids storing multiple values in one column.

Storing multiple values in one column breaks the first normal form, which requires atomic (single) values per field.

❓ Comparison

advanced

Compare INNER JOIN and LEFT JOIN

What is the main difference between an INNER JOIN and a LEFT JOIN in SQL?

A INNER JOIN returns only matching rows; LEFT JOIN returns all rows from the left table and matching rows from the right.

B INNER JOIN returns all rows from both tables; LEFT JOIN returns only matching rows.

C INNER JOIN returns rows only from the right table; LEFT JOIN returns rows only from the left table.

D INNER JOIN and LEFT JOIN are identical and return the same results.

💡 Hint

Think about which rows appear when there is no match in the right table.

INNER JOIN returns rows where there is a match in both tables. LEFT JOIN returns all rows from the left table, with NULLs for unmatched right table rows.

🚀 Application

expert

Determine the Number of Rows After a Complex JOIN

Consider these two tables:

Authors:
ID | Name
1  | Jane
2  | Mark

Books:
ID | Title       | AuthorID
1  | Book A      | 1
2  | Book B      | 1
3  | Book C      | 3

What is the number of rows returned by this SQL query?

SELECT Authors.Name, Books.Title FROM Authors LEFT JOIN Books ON Authors.ID = Books.AuthorID;

A 1 row

B 3 rows

C 2 rows

D 4 rows

💡 Hint

Remember LEFT JOIN returns all rows from the left table, matching rows from the right, or NULL if no match.

Jane has two books, so two rows; Mark has no books, so one row with NULL for book title; total 3 rows.

Practice

1. What is the main purpose of a relational database?

easy

A. To run computer programs

B. To store data as plain text files

C. To organize data into tables with rows and columns

D. To create graphics and charts

Solution

1. Step 1: Understand the structure of relational databases

   Relational databases store data in tables made of rows and columns, similar to a spreadsheet.

2. Step 2: Compare options with this structure

   Only 'To organize data into tables with rows and columns' matches the core purpose of relational databases.

3. Final Answer:

   To organize data into tables with rows and columns -> Option C

4. Quick Check:

   Relational database = tables with rows and columns [OK]

Hint: Think of data like a spreadsheet with rows and columns [OK]

Common Mistakes:

• Confusing databases with file storage
• Thinking databases create graphics
• Believing databases run programs

2. Which SQL command is used to add new data into a table?

easy

A. SELECT

B. INSERT

C. CREATE

D. DELETE

Solution

1. Step 1: Recall SQL commands and their purposes

   SELECT retrieves data, CREATE makes tables, DELETE removes data, and INSERT adds new data.

2. Step 2: Match the command to adding data

   INSERT is the command used to add new rows of data into a table.

3. Final Answer:

   INSERT -> Option B

4. Quick Check:

   Adding data = INSERT [OK]

Hint: INSERT means putting new data inside [OK]

Common Mistakes:

• Using SELECT to add data
• Confusing CREATE with INSERT
• Thinking DELETE adds data

3. Given the table Students with columns ID, Name, and Age, what will this SQL query return?

SELECT Name FROM Students WHERE Age > 20;

medium

A. All student names where age is greater than 20

B. All student IDs where age is greater than 20

C. All student ages where age is greater than 20

D. All data from the Students table

Solution

1. Step 1: Analyze the SELECT clause

   The query selects the Name column only, so the output will be student names.

2. Step 2: Analyze the WHERE condition

   The condition Age > 20 filters rows to only those students older than 20.

3. Final Answer:

   All student names where age is greater than 20 -> Option A

4. Quick Check:

   SELECT Name with Age > 20 = student names over 20 [OK]

Hint: SELECT column filters output; WHERE filters rows [OK]

Common Mistakes:

• Thinking SELECT returns all columns
• Ignoring the WHERE condition
• Confusing column names in SELECT

4. Identify the error in this SQL statement:

INSERT INTO Students (ID, Name Age) VALUES (1, 'Alice', 22);

medium

A. Missing comma between column names

B. Incorrect table name

C. VALUES keyword is misspelled

D. Extra parentheses around values

Solution

1. Step 1: Check the column list syntax

   The columns are listed as ID, Name Age without a comma between Name and Age.

2. Step 2: Confirm correct syntax for INSERT

   Column names must be separated by commas. Missing comma causes syntax error.

3. Final Answer:

   Missing comma between column names -> Option A

4. Quick Check:

   Columns need commas between names [OK]

Hint: Check commas between column names carefully [OK]

Common Mistakes:

• Overlooking missing commas
• Assuming VALUES is misspelled
• Thinking parentheses are extra

5. You have two tables: Orders(OrderID, CustomerID, Amount) and Customers(CustomerID, Name). Which SQL query correctly lists all orders with the customer names?

hard

A. SELECT OrderID, Name FROM Orders INNER JOIN Customers ON Orders.OrderID = Customers.CustomerID;

B. SELECT OrderID, Name FROM Orders, Customers WHERE Orders.OrderID = Customers.CustomerID;

C. SELECT OrderID, Name FROM Orders LEFT JOIN Customers ON Orders.Amount = Customers.CustomerID;

D. SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Solution

1. Step 1: Understand the relationship between tables

   Orders and Customers are linked by CustomerID, so join must use this key.

2. Step 2: Check each JOIN condition

   SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID; joins on Orders.CustomerID = Customers.CustomerID, which is correct. Others join on wrong columns.

3. Final Answer:

   SELECT Orders.OrderID, Customers.Name FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID; -> Option D

4. Quick Check:

   Join tables on matching CustomerID [OK]

Hint: Join tables using matching keys (CustomerID) [OK]

Common Mistakes:

• Joining on wrong columns
• Mixing OrderID with CustomerID
• Using incorrect JOIN types