Relational model mental model in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this SQL query on a simple employee table?

Consider a table Employees with columns id, name, and department. The table has these rows:

• (1, 'Alice', 'HR')
• (2, 'Bob', 'IT')
• (3, 'Charlie', 'IT')

What is the result of this query?

SELECT department, COUNT(*) AS count FROM Employees GROUP BY department;

SQL

SELECT department, COUNT(*) AS count FROM Employees GROUP BY department;

A [{"department": "HR", "count": 1}, {"department": "IT", "count": 2}]

B [{"department": "HR", "count": 2}, {"department": "IT", "count": 1}]

C [{"department": "HR", "count": 3}]

D [{"department": "IT", "count": 3}]

💡 Hint

Grouping counts how many rows share the same department.

The query groups rows by department and counts how many employees are in each. HR has 1 employee, IT has 2.

🧠 Conceptual

intermediate

Which statement best describes a primary key in the relational model?

In the relational model, what is the main purpose of a primary key in a table?

A It allows duplicate values in a column.

B It defines the order in which rows are stored.

C It stores the largest value in a column.

D It uniquely identifies each row in the table.

💡 Hint

Think about how you find one specific record in a table.

A primary key ensures each row is unique and can be identified without confusion.

📝 Syntax

advanced

Which SQL query correctly finds employees with no department assigned?

Given a table Employees with a nullable department column, which query correctly returns employees where department is not assigned?

A SELECT * FROM Employees WHERE department IS NULL;

B SELECT * FROM Employees WHERE department = 'NULL';

C SELECT * FROM Employees WHERE department = NULL;

D SELECT * FROM Employees WHERE department != NULL;

💡 Hint

Remember how SQL treats NULL comparisons.

In SQL, IS NULL checks for NULL values. Using = NULL or != NULL does not work as expected.

❓ optimization

advanced

Which index improves performance for this query on a large table?

Consider a large Orders table with columns order_id, customer_id, and order_date. You run this query often:

SELECT * FROM Orders WHERE customer_id = 12345;

Which index will improve query speed the most?

A CREATE INDEX idx_order_date ON Orders(order_date);

B CREATE INDEX idx_customer_id ON Orders(customer_id);

C CREATE INDEX idx_order_id ON Orders(order_id);

D CREATE INDEX idx_customer_order ON Orders(customer_id, order_date);

💡 Hint

Think about which column is used in the WHERE clause.

Indexing the column used in the WHERE clause speeds up filtering. Here, customer_id is filtered.

🔧 Debug

expert

Why does this SQL query return zero rows?

Given tables Students(id, name) and Enrollments(student_id, course_id), this query returns no rows:

SELECT s.name FROM Students s JOIN Enrollments e ON s.id = e.student_id WHERE e.course_id = 101;

What is the most likely reason?

A The WHERE clause should be on Students table, not Enrollments.

B The JOIN condition is incorrect; it should be ON s.id = e.course_id.

C There are no students enrolled in course 101.

D The query needs a GROUP BY clause to return results.

💡 Hint

Check if any rows match the filter condition.

If no enrollments exist for course 101, the JOIN returns no rows. The JOIN condition is correct.

Practice

1. In the relational model, what does a table represent?

easy

A. A collection of related records about a thing

B. A single piece of data

C. A programming function

D. A type of database software

Solution

1. Step 1: Understand the relational model basics

   In the relational model, data is organized into tables that represent entities or things.

2. Step 2: Identify what a table holds

   A table holds many rows, each row is a record about the thing the table represents.

3. Final Answer:

   A collection of related records about a thing -> Option A

4. Quick Check:

   Table = collection of records [OK]

Hint: Tables = things, rows = records, columns = details [OK]

Common Mistakes:

• Confusing a table with a single data value
• Thinking a table is a program or software
• Mixing up rows and columns

2. Which of the following is the correct way to describe a primary key in a table?

easy

A. A column or set of columns that uniquely identifies each row

B. A column that stores only numbers

C. A column that can have duplicate values

D. A column used to sort the table

Solution

1. Step 1: Recall the role of a primary key

   A primary key uniquely identifies each record in a table, so no two rows share the same primary key value.

2. Step 2: Check the options

   Only A column or set of columns that uniquely identifies each row describes uniqueness and identification, others describe unrelated properties.

3. Final Answer:

   A column or set of columns that uniquely identifies each row -> Option A

4. Quick Check:

   Primary key = unique identifier [OK]

Hint: Primary key means unique row ID [OK]

Common Mistakes:

• Thinking primary key allows duplicates
• Confusing primary key with sorting or data type
• Assuming primary key is always numeric

3. Given a table Employees with columns EmpID (primary key), Name, and Department, what will the following SQL query return?

SELECT Name FROM Employees WHERE Department = 'Sales';

medium

A. Only employees with no department assigned

B. All employee names regardless of department

C. All employee names who work in the Sales department

D. An error because Department is not a primary key

Solution

1. Step 1: Understand the SELECT and WHERE clauses

   The query selects the Name column from Employees where Department equals 'Sales'.

2. Step 2: Determine the output

   It returns all employee names whose Department is exactly 'Sales'. It does not return others or cause errors.

3. Final Answer:

   All employee names who work in the Sales department -> Option C

4. Quick Check:

   WHERE filters rows, SELECT chooses columns [OK]

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

Common Mistakes:

• Thinking it returns all employees ignoring WHERE
• Assuming Department must be primary key to filter
• Expecting error due to non-primary key in WHERE

4. Consider this SQL snippet:

CREATE TABLE Products (ProductID INT, Name VARCHAR(50), Price DECIMAL);
ALTER TABLE Products ADD PRIMARY KEY (ProductID);
INSERT INTO Products VALUES (1, 'Pen', 1.5), (1, 'Pencil', 0.5);

What is the problem with this code?

medium

A. Price column cannot be DECIMAL

B. It tries to insert duplicate primary key values

C. The primary key is not defined correctly

D. The table has no columns

Solution

1. Step 1: Check primary key uniqueness

   Primary key ProductID must be unique. The insert tries to add two rows with ProductID = 1.

2. Step 2: Identify the error cause

   Inserting duplicate primary keys violates uniqueness, causing an error.

3. Final Answer:

   It tries to insert duplicate primary key values -> Option B

4. Quick Check:

   Primary key must be unique [OK]

Hint: Primary keys cannot repeat values [OK]

Common Mistakes:

• Ignoring duplicate key violation
• Thinking DECIMAL is invalid for Price
• Assuming primary key syntax is wrong

5. You have two tables: Orders(OrderID, CustomerID, Amount) and Customers(CustomerID, Name). Which relational model concept explains why CustomerID appears in both tables?

hard

A. Column aliasing to rename data

B. Primary key duplication for faster queries

C. Data redundancy to store copies

D. Foreign key linking related records between tables

Solution

1. Step 1: Understand the role of CustomerID in both tables

   CustomerID uniquely identifies customers in Customers and links orders to customers in Orders.

2. Step 2: Identify relational model concept

   This linking is done by a foreign key, which references the primary key in another table to connect related data.

3. Final Answer:

   Foreign key linking related records between tables -> Option D

4. Quick Check:

   Foreign key = link between tables [OK]

Hint: Foreign key connects tables via shared column [OK]

Common Mistakes:

• Confusing foreign key with primary key duplication
• Thinking it's just data redundancy
• Mixing foreign key with column aliasing