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.

Practice

1. What is the main purpose of using CAST or CONVERT in SQL?

easy

A. To change the data type of a value

B. To delete rows from a table

C. To create a new table

D. To join two tables

Solution

1. Step 1: Understand the function of CAST and CONVERT

   Both CAST and CONVERT are used to change the data type of a value in SQL.

2. Step 2: Compare with other options

   Deleting rows, creating tables, and joining tables are unrelated to changing data types.

3. Final Answer:

   To change the data type of a value -> Option A

4. Quick Check:

   CAST and CONVERT change types = A [OK]

Hint: CAST and CONVERT always change data types [OK]

Common Mistakes:

• Thinking CAST deletes data
• Confusing CONVERT with table creation
• Mixing data type change with joins

2. Which of the following is the correct syntax to convert a string '123' to an integer using CAST in SQL?

easy

A. CAST('123' AS INT)

B. CAST(INT AS '123')

C. CONVERT('123', INT)

D. CONVERT(INT, '123')

Solution

1. Step 1: Recall CAST syntax

   CAST requires the format CAST(value AS datatype), so CAST('123' AS INT) is correct.

2. Step 2: Check other options

   Options A, B, and D use incorrect order or syntax for CAST or CONVERT.

3. Final Answer:

   CAST('123' AS INT) -> Option A

4. Quick Check:

   CAST(value AS type) = C [OK]

Hint: CAST syntax is always CAST(value AS datatype) [OK]

Common Mistakes:

• Swapping value and datatype order
• Using CONVERT with wrong argument order
• Missing AS keyword in CAST

3. Given the SQL query:

SELECT CAST('2024-06-15' AS DATE) AS ConvertedDate;

What will be the output type of ConvertedDate?

medium

A. String

B. Boolean

C. Integer

D. Date

Solution

1. Step 1: Analyze the CAST operation

   The query casts the string '2024-06-15' to the DATE data type.

2. Step 2: Determine the output type

   Since CAST converts to DATE, the output column ConvertedDate will be of type Date.

3. Final Answer:

   Date -> Option D

4. Quick Check:

   CAST to DATE returns Date type = D [OK]

Hint: CAST to DATE returns a date type value [OK]

Common Mistakes:

• Assuming output stays string
• Confusing DATE with integer
• Thinking CAST changes value format only

4. Identify the error in this SQL statement:

SELECT CONVERT(INT, 'abc123');

medium

A. No error, converts string to integer

B. Syntax error due to wrong CONVERT argument order

C. Runtime error converting non-numeric string to INT

D. Missing AS keyword in CONVERT

Solution

1. Step 1: Check CONVERT syntax

   CONVERT(INT, 'abc123') is correct syntax in some SQL dialects like SQL Server.

2. Step 2: Consider conversion feasibility

   Trying to convert 'abc123' to INT causes a runtime error because 'abc123' is not numeric.

3. Final Answer:

   Runtime error converting non-numeric string to INT -> Option C

4. Quick Check:

   Non-numeric string to INT causes runtime error = A [OK]

Hint: Non-numeric strings cause errors when converting to INT [OK]

Common Mistakes:

• Assuming CONVERT always succeeds
• Confusing syntax error with runtime error
• Expecting implicit string to int conversion

5. You have a table Orders with a column OrderDate stored as VARCHAR in format 'YYYYMMDD'.
Which SQL query correctly converts OrderDate to a DATE type and selects orders from June 2024?

Options:
A) SELECT * FROM Orders WHERE CAST(OrderDate DATE) BETWEEN '2024-06-01' AND '2024-06-30';
B) SELECT * FROM Orders WHERE CONVERT(DATE, OrderDate, 112) BETWEEN '2024-06-01' AND '2024-06-30';
C) SELECT * FROM Orders WHERE CAST(OrderDate AS INT) BETWEEN 20240601 AND 20240630;
D) SELECT * FROM Orders WHERE CONVERT(INT, OrderDate) BETWEEN 20240601 AND 20240630;

hard

A. Option A only

B. Option B only

C. Option C only

D. Option D only

Solution

1. Step 1: Understand the data format and conversion

   OrderDate is VARCHAR in 'YYYYMMDD' format. CONVERT with style 112 converts this string correctly to DATE.

2. Step 2: Evaluate each option

   Option B only only tries CAST without style, which may fail or misinterpret format. Option B only only uses CONVERT with style 112, correct for 'YYYYMMDD'. Options C and D convert to INT, which is not reliable for date comparisons.

3. Final Answer:

   Option B only -> Option B

4. Quick Check:

   CONVERT with style 112 converts 'YYYYMMDD' to DATE = B [OK]

Hint: Use CONVERT with style 112 for 'YYYYMMDD' to DATE [OK]

Common Mistakes:

• Using CAST without format for 'YYYYMMDD'
• Converting dates to INT for comparison
• Ignoring CONVERT style parameter