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.