Why string functions matter in queries in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Using LOWER() to find case-insensitive matches

Given a table Users with a column username, which query returns all users whose username is 'alice' regardless of case?

SQL

SELECT username FROM Users WHERE LOWER(username) = 'alice';

A SELECT username FROM Users WHERE LOWER(username) = 'alice';

B SELECT username FROM Users WHERE username LIKE 'Alice';

C SELECT username FROM Users WHERE UPPER(username) = 'ALICE';

D SELECT username FROM Users WHERE username = 'alice';

💡 Hint

Think about how to ignore uppercase or lowercase differences in text.

The LOWER() function converts all characters to lowercase, so comparing LOWER(username) to 'alice' finds matches regardless of original case.

❓ query_result

intermediate

Extracting domain from email using SUBSTRING and POSITION

Which query correctly extracts the domain part (after '@') from the email column in the Contacts table?

SQL

SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain FROM Contacts;

A SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS domain FROM Contacts;

B SELECT SUBSTRING(email, POSITION('@' IN email)) AS domain FROM Contacts;

C SELECT SUBSTRING(email FROM 1 FOR POSITION('@' IN email)) AS domain FROM Contacts;

D SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain FROM Contacts;

💡 Hint

Remember that POSITION('@' IN email) gives the location of '@'.

Option D extracts substring starting right after '@' to the end, which is the domain part.

📝 Syntax

advanced

Identifying syntax error in string concatenation

Which option contains a syntax error when trying to concatenate first and last names with a space in between in SQL?

A SELECT first_name || ' ' || last_name AS full_name FROM Employees;

B SELECT first_name || ' ' || last_name FROM Employees;

C SELECT first_name + ' ' + last_name AS full_name FROM Employees;

D SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM Employees;

💡 Hint

Check which operator is valid for string concatenation in SQL.

Option C uses '+' which is invalid for string concatenation in standard SQL, causing a syntax error.

❓ optimization

advanced

Optimizing query with TRIM to remove spaces

Which query efficiently removes leading and trailing spaces from the product_code column in the Inventory table before comparison?

A SELECT * FROM Inventory WHERE TRIM(product_code) = 'ABC123';

B SELECT * FROM Inventory WHERE product_code = TRIM(' ABC123 ');

C SELECT * FROM Inventory WHERE product_code = 'ABC123';

D SELECT * FROM Inventory WHERE LTRIM(RTRIM(product_code)) = 'ABC123';

💡 Hint

Consider which side the spaces might be on and how to remove them from the column data.

Option A trims spaces from the column value before comparing, ensuring correct matches even if data has spaces.

🧠 Conceptual

expert

Why use string functions in queries?

Which reason best explains why string functions are important in database queries?

A They speed up database hardware performance automatically.

B They allow manipulation and comparison of text data to handle variations and extract useful parts.

C They replace the need for indexing tables.

D They convert all data types into strings for easier storage.

💡 Hint

Think about what string functions do to text data in queries.

String functions help clean, compare, and extract parts of text data, making queries more flexible and accurate.