Advanced SQLite Queries for Mobile App Development Quiz

Explore essential advanced SQLite query techniques in mobile app development with this quiz. Perfect for developers aiming to improve their skills in data retrieval, filtering, joins, and transaction management within SQLite databases used in mobile applications.

1. Using the LIKE Operator

   Which SQL operator would you use in SQLite to perform case-insensitive pattern matching for user input, such as finding all usernames that start with 'Sam'?

   1. SIMILAR
   2. LIKE
   3. EQUALS
   4. MATCHES

   Explanation: The LIKE operator allows pattern matching in SQLite queries, which makes it suitable for tasks such as finding entries that start or end with particular strings. EQUALS only matches exact values, while MATCHES and SIMILAR are invalid or unsupported in standard SQLite for this purpose. Therefore, LIKE is the correct choice for flexible string pattern matching.

2. Primary Key Uniqueness

   In an SQLite database table for a mobile app, which constraint guarantees that each row can be uniquely identified by a column named 'user_id'?

   1. FOREIGN KEY
   2. NOT NULL
   3. PRIMARY KEY
   4. UNIQUE INDEX

   Explanation: The PRIMARY KEY constraint uniquely identifies each record in a table, ensuring that each value in 'user_id' is distinct and cannot be NULL. FOREIGN KEY is used for linking tables but does not ensure uniqueness in the current table. UNIQUE INDEX enforces uniqueness but not nullability, and NOT NULL ensures values are present but does not require uniqueness.

3. Inner Join Query

   What kind of join would you use in SQLite to retrieve only matching records from two tables, such as users who have made at least one purchase in a purchases table?

   1. INNER JOIN
   2. RIGHT JOIN
   3. LEFT JOIN
   4. OUTER JOIN

   Explanation: An INNER JOIN returns only rows where there is a match in both tables, which is ideal for finding users with related purchases. LEFT JOIN returns all records from the left table with potential matches in the right, while OUTER JOIN is not directly supported by SQLite and would represent a full outer join. RIGHT JOIN is also unsupported in SQLite.

4. Using Aggregate Functions

   Which function in SQLite would you use to count the total number of rows in a 'messages' table for an inbox feature?

   1. SUM
   2. TALLY
   3. COUNT
   4. CALC

   Explanation: The COUNT function aggregates the number of rows that match a query's conditions, making it the correct method for this scenario. SUM would add the values in a column but not count rows. Both CALC and TALLY are not valid aggregate functions in SQLite.

5. Parameter Binding Benefits

   Why is parameter binding recommended when constructing SQLite queries in a mobile app, for example, searching for 'city = ?' instead of concatenating user input directly?

   1. It speeds up application launch
   2. It prevents SQL injection attacks
   3. It creates new indexes automatically
   4. It improves the database's network features

   Explanation: Parameter binding securely separates code from data, helping protect against SQL injection by not directly inserting user input into queries. It does not improve network features, automatically create indexes, or have a direct effect on app launch speed. Preventing SQL injection is the main reason for this best practice.

6. Ordering Query Results

   Which clause should you add to an SQLite SELECT statement to sort the retrieved rows by 'created_date' in descending order?

   1. SORT created_date DOWN
   2. ARRANGE BY created_date
   3. ORDER BY created_date DESC
   4. GROUP BY created_date ASC

   Explanation: The ORDER BY ... DESC clause sorts query results in descending order based on the specified column. GROUP BY is for grouping, not sorting. There is no clause called SORT or ARRANGE BY in SQLite, making both incorrect options.

7. Transaction Handling

   Which statement starts an explicit transaction in SQLite when you need to execute several database changes as a unit in your mobile app?

   1. START TRANSACT
   2. RUN BATCH
   3. BEGIN TRANSACTION
   4. INITIATE

   Explanation: BEGIN TRANSACTION starts a transaction block in SQLite, which lets you group multiple changes together for atomic execution. INITIATE and START TRANSACT are not recognized SQLite commands, and RUN BATCH is not a valid statement in SQLite. Using BEGIN TRANSACTION ensures that all changes succeed or fail as one.

8. Null Value Filtering

   How would you write a condition in an SQLite WHERE clause to select all rows where the 'profile_pic' column is empty, for example, to find users without profile pictures?

   1. WHERE profile_pic = ''
   2. WHERE profile_pic IS NULL
   3. WHERE profile_pic IS BLANK
   4. WHERE profile_pic HAS NO VALUE

   Explanation: The IS NULL check identifies rows with no value set in the specified column, which is essential for handling NULLs that represent missing information. Checking for an empty string with = '' would only find text fields explicitly set to blank, not genuine NULLs. IS BLANK and HAS NO VALUE are not valid SQLite conditions.

9. Query Limiting

   To implement pagination and restrict the number of results in SQLite, such as displaying only the first 20 contacts, which clause should you add?

   1. BOUNDARY 20
   2. ROWCOUNT 20
   3. LIMIT 20
   4. TOP 20

   Explanation: LIMIT 20 restricts the result set to 20 rows, making it suitable for pagination in mobile apps. BOUNDARY, TOP, and ROWCOUNT are not standard SQLite clauses, with TOP being used in other SQL variants but not in SQLite. Only LIMIT achieves the described pagination effect.

10. Updating Multiple Columns

    Which SQLite statement correctly updates both 'last_login' and 'status' fields for a user in a single query based on their 'user_id'?

    1. CHANGE users last_login status WHERE user_id = ?
    2. UPDATE users SET last_login = ?, status = ? WHERE user_id = ?
    3. MODIFY users SET last_login AND status WHERE user_id = ?
    4. ALTER users last_login = ?, status = ? FOR user_id = ?

    Explanation: The UPDATE statement with the SET clause allows you to modify multiple columns in a row simultaneously using proper syntax. CHANGE, ALTER, and MODIFY are not correct for updating data in SQLite in this context. Only the first option also correctly includes a WHERE clause to specify the user to update.