Room queries (Insert, Update, Delete, Select) in Android Kotlin - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ ui_behavior

intermediate

What is the output of this Room query?

Given this DAO function, what will be the result of calling getAllUsers() if the database has 3 users?

Android Kotlin

import androidx.room.*

@Entity
data class User(
  @PrimaryKey val id: Int,
  val name: String
)

@Dao
interface UserDao {
  @Query("SELECT * FROM User")
  fun getAllUsers(): List<User>
}

A A list of 3 User objects

B A list of 0 User objects

C A single User object

D A Cursor object

💡 Hint

The query selects all rows from the User table.

The @Query annotation with 'SELECT * FROM User' returns all rows as a list of User objects.

📝 Syntax

intermediate

Which option correctly inserts a User into the database?

Select the correct DAO function to insert a User object into the Room database.

Android Kotlin

import androidx.room.*

@Entity
data class User(
  @PrimaryKey val id: Int,
  val name: String
)

@Dao
interface UserDao {
  // Insert function here
}

A

@Query("INSERT INTO User VALUES(:user)")
fun insertUser(user: User)

B

@Insert
fun insertUser(user: User)

C fun insertUser(user: User) { database.insert(user) }

D

@Update
fun insertUser(user: User)

💡 Hint

Use the Room annotation designed for inserting entities.

The @Insert annotation tells Room to generate code to insert the entity. Option B is invalid SQL syntax, C is not Room syntax, and D uses @Update which is for updating, not inserting.

❓ lifecycle

advanced

What happens if you update a User with a non-existing ID?

Consider this DAO update function. What will happen if you call it with a User object whose ID does not exist in the database?

Android Kotlin

@Update
fun updateUser(user: User): Int

A A new User is inserted with that ID

B The function returns 1 and updates a random row

C The function returns 0 and no rows are updated

D The function throws an exception

💡 Hint

Think about how Room handles updates when no matching row exists.

Room's @Update returns the number of rows updated. If no row matches the primary key, it returns 0 and does not insert a new row.

🔧 Debug

advanced

Why does this delete query cause a runtime error?

This DAO delete function causes a runtime crash. What is the cause?

Android Kotlin

@Query("DELETE FROM User WHERE id = :userId")
fun deleteUser(userId: Int)

A The function must return Int for number of rows deleted

B The User entity does not have an id field

C The DELETE query syntax is invalid

D The userId parameter type should be Int, not String

💡 Hint

Check the data type of the primary key in the User entity.

If the User entity's id is Int, passing a String parameter causes a type mismatch at runtime.

🧠 Conceptual

expert

How does Room handle conflicts on insert with @Insert(onConflict = OnConflictStrategy.REPLACE)?

If you insert a User with an existing primary key using @Insert(onConflict = OnConflictStrategy.REPLACE), what happens?

A The existing row is replaced with the new User data

B The insert is ignored and the old row remains unchanged

C A SQLiteConstraintException is thrown

D Both rows exist causing duplicate primary keys

💡 Hint

Consider what REPLACE means in SQLite conflict strategies.

REPLACE deletes the existing row with the same primary key and inserts the new row, effectively replacing it.

Practice

1. Which Room annotation is used to add a new record to the database?

easy

A. @Query

B. @Insert

C. @Delete

D. @Update

Solution

1. Step 1: Understand Room annotations for data changes

   @Insert is used to add new data, @Update modifies existing data, and @Delete removes data.

2. Step 2: Identify the annotation for adding records

   Since adding means inserting new data, @Insert is the correct annotation.

3. Final Answer:

   @Insert -> Option B

4. Quick Check:

   Add new record = @Insert [OK]

Hint: Insert means add new data, so use @Insert [OK]

Common Mistakes:

• Confusing @Update with adding new data
• Using @Delete instead of @Insert
• Thinking @Query inserts data

2. Which of the following is the correct syntax to delete a user by ID using @Query in Room?

easy

A. @Query("DELETE users WHERE id = :userId")

B. @Query("REMOVE FROM users WHERE id = :userId")

C. @Query("DELETE FROM users WHERE id = :userId")

D. @Query("DELETE * FROM users WHERE id = :userId")

Solution

1. Step 1: Recall SQL DELETE syntax

   The correct SQL command to delete rows is DELETE FROM table WHERE condition.

2. Step 2: Match correct syntax in options

   @Query("DELETE FROM users WHERE id = :userId") matches the correct SQL syntax with parameter binding (:userId).

3. Final Answer:

   @Query("DELETE FROM users WHERE id = :userId") -> Option C

4. Quick Check:

   DELETE FROM table WHERE condition = correct syntax [OK]

Hint: Use 'DELETE FROM' not 'REMOVE' or 'DELETE *' [OK]

Common Mistakes:

• Using REMOVE instead of DELETE
• Omitting FROM keyword
• Adding * after DELETE

3. Given the DAO method below, what will be the return type when calling getAllUsers()?

@Query("SELECT * FROM users")
fun getAllUsers(): List<User>

medium

A. An integer count of users

B. A single User object

C. A Boolean indicating success

D. A list of User objects

Solution

1. Step 1: Analyze the method signature

   The method returns List<User>, which means a list of User objects.

2. Step 2: Understand the SQL query

   SELECT * FROM users returns all rows, so the method returns multiple User objects in a list.

3. Final Answer:

   A list of User objects -> Option D

4. Quick Check:

   Return type List<User> means multiple users [OK]

Hint: Return type List<User> means multiple users returned [OK]

Common Mistakes:

• Thinking it returns a single User
• Confusing return type with count
• Expecting a Boolean result

4. Identify the error in this Room DAO update method:

@Update
fun updateUser(user: User): Int {
  return 1
}

medium

A. The method should not have a body; it must be abstract

B. The method should return Unit, not Int

C. The method must not have a return statement

D. The @Update annotation is missing parameters

Solution

1. Step 1: Understand Room DAO method requirements

   DAO methods with @Update must be abstract or interface methods without a body.

2. Step 2: Identify the error in the method

   This method has a body with a return statement, which is not allowed for Room DAO annotated methods.

3. Final Answer:

   The method should not have a body; it must be abstract -> Option A

4. Quick Check:

   Room DAO methods are abstract, no method body [OK]

Hint: Room DAO methods must be abstract, no method body [OK]

Common Mistakes:

• Adding method body in DAO interface
• Returning wrong types intentionally
• Missing @Update annotation

5. You want to update a user's email by their ID using Room. Which DAO method below correctly performs this update?

hard

A. @Query("UPDATE users SET email = :email WHERE id = :userId")
fun updateEmail(userId: Int, email: String)

B. @Update
fun updateEmail(userId: Int, email: String)

C. @Insert(onConflict = OnConflictStrategy.REPLACE)
fun updateEmail(userId: Int, email: String)

D. @Delete
fun updateEmail(userId: Int, email: String)

Solution

1. Step 1: Understand update options in Room

   @Update requires a full entity object, so partial updates need @Query with SQL.

2. Step 2: Check which method updates email by ID

   @Query("UPDATE users SET email = :email WHERE id = :userId") fun updateEmail(userId: Int, email: String) uses @Query with SQL UPDATE statement and parameters for userId and email, which is correct.

3. Final Answer:

   @Query("UPDATE users SET email = :email WHERE id = :userId") fun updateEmail(userId: Int, email: String) -> Option A

4. Quick Check:

   Partial update needs @Query with SQL UPDATE [OK]

Hint: Use @Query with UPDATE for partial field changes [OK]

Common Mistakes:

• Using @Update without full entity
• Using @Insert to update data
• Using @Delete for updates