CAST and CONVERT for type changes in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this CAST query?

Consider a table Products with a column Price of type VARCHAR storing numeric values as strings. What will be the result of this query?

SELECT CAST(Price AS INT) AS PriceInt FROM Products WHERE Price = '100';

SQL

SELECT CAST(Price AS INT) AS PriceInt FROM Products WHERE Price = '100';

A Empty result set

B [{"PriceInt": "100"}]

C SyntaxError

D [{"PriceInt": 100}]

💡 Hint

CAST converts string numbers to integers for numeric operations.

CAST changes the string '100' to the integer 100, so the output shows PriceInt as 100.

📝 Syntax

intermediate

Which option correctly converts a datetime to a string in SQL?

You want to convert a datetime column OrderDate to a string format using CONVERT. Which of these options is syntactically correct?

A SELECT CONVERT(VARCHAR(10), OrderDate, 101) FROM Orders;

B SELECT CONVERT(OrderDate, VARCHAR, 101) FROM Orders;

C SELECT CONVERT(VARCHAR, OrderDate, 101) FROM Orders;

D SELECT CONVERT(OrderDate, 101, VARCHAR) FROM Orders;

💡 Hint

The correct syntax is CONVERT(target_type(length), source, style).

Option A uses the correct syntax: CONVERT(VARCHAR(10), OrderDate, 101).

❓ optimization

advanced

Which query is more efficient for converting and filtering numeric strings?

Given a table Sales with a VARCHAR column Amount storing numbers as strings, you want to select rows where Amount is greater than 100 after conversion. Which query is more efficient?

A SELECT * FROM Sales WHERE CAST(Amount AS INT) > 100;

B SELECT * FROM Sales WHERE CONVERT(INT, Amount) > 100;

C SELECT * FROM Sales WHERE Amount > '100';

D SELECT * FROM Sales WHERE Amount::INT > 100;

💡 Hint

Filtering on the original string column can use indexes better than converting each row.

Option C compares strings directly, which can use indexes and is faster than converting each row.

🔧 Debug

advanced

Why does this CAST query raise an error?

You run this query:

SELECT CAST('abc' AS INT) AS NumberValue;

Why does it fail?

SQL

SELECT CAST('abc' AS INT) AS NumberValue;

A Because 'abc' cannot be converted to an integer, causing a conversion error.

B Because CAST requires numeric literals, not strings.

C Because the syntax of CAST is incorrect.

D Because INT is not a valid target type for CAST.

💡 Hint

CAST fails when the string cannot be interpreted as a number.

The string 'abc' is not numeric, so CAST to INT raises a conversion error.

🧠 Conceptual

expert

What is the difference between CAST and CONVERT in SQL?

Which statement best describes the difference between CAST and CONVERT for type changes in SQL?

A CAST can only convert strings; CONVERT can convert any data type.

B CAST is ANSI SQL standard and portable; CONVERT is specific to some SQL dialects and supports style formatting.

C CAST is faster than CONVERT in all cases.

D CONVERT is ANSI SQL standard; CAST is proprietary and less portable.

💡 Hint

Think about standards and extra features like formatting styles.

CAST is part of the ANSI SQL standard and works across many databases; CONVERT is specific (e.g., SQL Server) and allows style codes for formatting.