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.