Raw SQL when needed in Ruby on Rails - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to execute a raw SQL query using ActiveRecord.

Ruby on Rails

User.find_by_sql("SELECT * FROM users WHERE id = [1]")

Drag options to blanks, or click blank then click option'

A1

Bid

Cuser_id

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name instead of a value.

Using a string without quotes in SQL.

The raw SQL query requires a specific value for the user ID, so '1' is the correct choice.

2fill in blank

medium

Complete the code to safely insert a variable into a raw SQL query using ActiveRecord.

Ruby on Rails

User.find_by_sql(["SELECT * FROM users WHERE email = ?", [1]])

Drag options to blanks, or click blank then click option'

Auser_email

Bemail

C:email

D"[e-mail]"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a symbol or variable name instead of a string.

Not quoting the email string.

When using parameterized queries, the value must be a string with quotes, so '"[e-mail]"' is correct.

3fill in blank

hard

Fix the error in the raw SQL query that fetches users with age greater than 30.

Ruby on Rails

User.find_by_sql("SELECT * FROM users WHERE age [1] 30")

Drag options to blanks, or click blank then click option'

A=

B>

C<

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which selects only age 30.

Using '<' which selects younger users.

The query should select users older than 30, so the '>' operator is correct.

4fill in blank

hard

Fill both blanks to write a raw SQL query that selects users with name starting with 'A' and orders by created_at descending.

Ruby on Rails

User.find_by_sql("SELECT * FROM users WHERE name [1] 'A%' ORDER BY created_at [2]")

Drag options to blanks, or click blank then click option'

ALIKE

BASC

CDESC

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of 'LIKE' for pattern matching.

Ordering ascending instead of descending.

Use 'LIKE' to match names starting with 'A' and 'DESC' to order newest first.

5fill in blank

hard

Fill all three blanks to write a raw SQL query that updates the user's email where id matches, using parameterized query.

Ruby on Rails

User.connection.execute(ActiveRecord::Base.send(:sanitize_sql_array, ["UPDATE users SET email = [1] WHERE id = [2]", [3]]))

Drag options to blanks, or click blank then click option'

A"[e-mail]"

Buser_id

D42

Attempts:

3 left

💡 Hint

Common Mistakes

Using unquoted strings for email.

Using variable names instead of actual values.

The email is a string, so use a quoted string for the first blank. The id is a number, so use 42 for the second and third blanks (the id value).

Practice

1. What is the main reason to use raw SQL in a Rails application?

easy

A. To write database queries that Rails helpers cannot easily express

B. To avoid using ActiveRecord models completely

C. To speed up Rails server startup time

D. To automatically generate database migrations

Solution

1. Step 1: Understand Rails helpers limitations

   Rails helpers cover many common queries but sometimes complex queries need direct SQL.

2. Step 2: Identify why raw SQL is used

   Raw SQL is used when Rails helpers cannot express the needed query efficiently or at all.

3. Final Answer:

   To write database queries that Rails helpers cannot easily express -> Option A

4. Quick Check:

   Raw SQL = complex queries [OK]

Hint: Use raw SQL when Rails queries are too limited [OK]

Common Mistakes:

• Thinking raw SQL replaces ActiveRecord entirely
• Believing raw SQL speeds up server startup
• Confusing raw SQL with migration generation

2. Which Rails method is used to run a raw SELECT SQL query and get model objects back?

easy

A. Model.connection.execute(sql_query)

B. Model.find_by_sql(sql_query)

C. Model.where(sql_query)

D. Model.raw_query(sql_query)

Solution

1. Step 1: Identify method for raw SELECT returning models

   Rails provides find_by_sql to run raw SELECT queries returning model instances.

2. Step 2: Differentiate from execute method

   connection.execute runs SQL but returns raw results, not model objects.

3. Final Answer:

   Model.find_by_sql(sql_query) -> Option B

4. Quick Check:

   Raw SELECT with models = find_by_sql [OK]

Hint: Use find_by_sql for raw SELECT queries returning models [OK]

Common Mistakes:

• Using connection.execute for SELECT expecting models
• Confusing where with raw SQL execution
• Assuming raw_query is a valid method

3. Given the code:

users = User.find_by_sql("SELECT * FROM users WHERE age > 30")
puts users.first.name

What will this code do?

medium

A. Return an array of user IDs only

B. Raise a syntax error due to invalid SQL

C. Print the total count of users older than 30

D. Print the name of the first user older than 30

Solution

1. Step 1: Understand find_by_sql behavior

   find_by_sql runs the raw SELECT and returns model objects matching the query.

2. Step 2: Analyze the query and output

   The query selects users with age > 30, and users.first.name prints the first matching user's name.

3. Final Answer:

   Print the name of the first user older than 30 -> Option D

4. Quick Check:

   find_by_sql returns models = prints first name [OK]

Hint: find_by_sql returns model objects, so you can access attributes [OK]

Common Mistakes:

• Expecting raw SQL to return only IDs
• Thinking it raises syntax error without reason
• Confusing output with count instead of first record

4. What is wrong with this code snippet?

result = ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE name = 'Alice'")
puts result.name

medium

A. The result is a raw result set, not a model, so .name is undefined

B. The SQL query syntax is incorrect

C. ActiveRecord::Base.connection.execute cannot run SELECT queries

D. The connection object is not accessible this way

Solution

1. Step 1: Understand connection.execute return type

   connection.execute returns a raw database result, not model objects.

2. Step 2: Identify why .name fails

   Raw result sets do not have model attributes like .name; you must extract data manually.

3. Final Answer:

   The result is a raw result set, not a model, so .name is undefined -> Option A

4. Quick Check:

   connection.execute returns raw results, no model methods [OK]

Hint: Use find_by_sql for models; execute returns raw results [OK]

Common Mistakes:

• Assuming execute returns model objects
• Thinking SQL syntax is wrong
• Believing connection is inaccessible

5. You want to update all users' status to 'active' using raw SQL in Rails. Which code snippet correctly does this?

hard

A. User.find_by_sql("UPDATE users SET status = 'active'")

B. User.where("status = 'active'").update_all

C. ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'")

D. User.connection.execute("SELECT * FROM users WHERE status = 'active'")

Solution

1. Step 1: Identify method for non-SELECT raw SQL

   For UPDATE queries, use connection.execute to run raw SQL commands.

2. Step 2: Check each option's correctness

   ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'") uses connection.execute with UPDATE SQL correctly. User.find_by_sql("UPDATE users SET status = 'active'") misuses find_by_sql which is for SELECT. User.where("status = 'active'").update_all uses ActiveRecord but not raw SQL. User.connection.execute("SELECT * FROM users WHERE status = 'active'") runs SELECT, not UPDATE.

3. Final Answer:

   ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'") -> Option C

4. Quick Check:

   Raw UPDATE = connection.execute [OK]

Hint: Use connection.execute for UPDATE, INSERT, DELETE raw SQL [OK]

Common Mistakes:

• Using find_by_sql for UPDATE queries
• Confusing ActiveRecord methods with raw SQL
• Running SELECT instead of UPDATE