Why dialect awareness matters in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Output difference due to string concatenation syntax

Consider the following SQL queries that concatenate first and last names. Which option returns the full name as 'John Doe' in PostgreSQL?

SQL

SELECT first_name || ' ' || last_name AS full_name FROM users WHERE id = 1;

A SELECT first_name + ' ' + last_name AS full_name FROM users WHERE id = 1;

B SELECT first_name || ' ' || last_name AS full_name FROM users WHERE id = 1;

C SELECT first_name & ' ' & last_name AS full_name FROM users WHERE id = 1;

D SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users WHERE id = 1;

💡 Hint

PostgreSQL uses || for string concatenation, not + or &.

In PostgreSQL, the string concatenation operator is ||. Using + or & will cause errors or unexpected results.

📝 Syntax

intermediate

Syntax error due to LIMIT clause placement

Which SQL query will run without syntax errors in MySQL to get the top 5 rows ordered by salary descending?

A SELECT TOP 5 * FROM employees ORDER BY salary DESC;

B SELECT * FROM employees LIMIT 5 ORDER BY salary DESC;

C SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

D SELECT * FROM employees FETCH FIRST 5 ROWS ONLY ORDER BY salary DESC;

💡 Hint

MySQL requires LIMIT after ORDER BY.

In MySQL, LIMIT must come after ORDER BY. Using TOP or FETCH FIRST is not valid syntax in MySQL.

❓ optimization

advanced

Optimizing date filtering across SQL dialects

You want to select orders from 2023 in both MySQL and PostgreSQL. Which query is portable and efficient?

A SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

B SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYY') = '2023';

C SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

D SELECT * FROM orders WHERE YEAR(order_date) = 2023;

💡 Hint

Filtering by range on dates is usually faster and portable.

Using a date range filter avoids function calls on columns, which helps indexes work efficiently and works across dialects.

🔧 Debug

advanced

Debugging NULL handling differences

Given the query to count non-null emails, which option correctly counts non-null emails in SQL Server?

SQL

SELECT COUNT(email) FROM customers;

A SELECT COUNT(*) FROM customers WHERE email IS NOT NULL;

B SELECT COUNT(email) FROM customers WHERE email IS NOT NULL;

C SELECT COUNT(DISTINCT email) FROM customers;

D SELECT COUNT(email) FROM customers;

💡 Hint

COUNT(column) ignores NULLs automatically.

COUNT(column) counts only non-null values, so option D is correct and simplest. Option D also works but is redundant.

🧠 Conceptual

expert

Understanding dialect-specific transaction isolation levels

Which statement about transaction isolation levels is true across most SQL dialects?

A SERIALIZABLE isolation level guarantees no phantom reads and is the strictest level.

B READ UNCOMMITTED allows dirty reads and is supported by all major SQL databases.

C READ COMMITTED isolation level prevents dirty reads and phantom reads in all databases.

D REPEATABLE READ isolation level is the weakest isolation level.

💡 Hint

Think about the strictness of isolation levels and their effects.

SERIALIZABLE is the strictest isolation level preventing phantom reads. Other levels vary in support and behavior across dialects.