Type casting and conversion in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert the string '123' to an integer using CAST.

MySQL

SELECT CAST('123' AS [1]);

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

ASIGNED

BDECIMAL

CDATE

DCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using CHAR instead of SIGNED causes no numeric conversion.

Using DATE or DECIMAL changes the type incorrectly.

In MySQL, to convert a string to an integer, you use CAST with AS SIGNED.

2fill in blank

medium

Complete the code to convert the integer 2023 to a string using CAST.

MySQL

SELECT CAST(2023 AS [1]);

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

ASIGNED

BDECIMAL

CDATE

DCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using SIGNED keeps it as a number.

Using DATE or DECIMAL changes the type incorrectly.

CAST AS CHAR converts a number to a string in MySQL.

3fill in blank

hard

Fix the error in the code to convert the string '2023-06-01' to a DATE type.

MySQL

SELECT CAST('2023-06-01' AS [1]);

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

ASIGNED

BCHAR

CDATE

DDECIMAL

Attempts:

3 left

💡 Hint

Common Mistakes

Using SIGNED or DECIMAL causes conversion errors.

Using CHAR keeps it as a string.

To convert a string to a date, use CAST AS DATE in MySQL.

4fill in blank

hard

Fill both blanks to convert the string '123.45' to a decimal number with 5 digits and 2 decimals.

MySQL

SELECT CAST('123.45' AS [1]([2], 2));

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

ADECIMAL

BSIGNED

C5

D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using SIGNED instead of DECIMAL causes wrong type.

Using 10 instead of 5 changes precision.

DECIMAL(5, 2) defines a decimal number with 5 total digits and 2 decimal places.

5fill in blank

hard

Fill all three blanks to convert the string '2023-06-01 12:30:00' to a DATETIME type and alias it as 'converted'.

MySQL

SELECT CAST([1] AS [2]) AS [3];

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

A'2023-06-01 12:30:00'

BDATETIME

Cconverted

D'2023-06-01'

Attempts:

3 left

💡 Hint

Common Mistakes

Using only the date string loses time information.

Wrong alias names cause confusion.

CAST the full datetime string as DATETIME and alias the result as 'converted'.

Practice

1. What does the CAST() function do in MySQL?

easy

A. It changes the database user permissions.

B. It creates a new table in the database.

C. It converts a value from one data type to another.

D. It deletes rows from a table.

Solution

1. Step 1: Understand the purpose of CAST()

   The CAST() function is used to change the data type of a value, for example from string to integer.

2. Step 2: Compare with other options

   Options A, B, and C describe unrelated database operations, not type conversion.

3. Final Answer:

   It converts a value from one data type to another. -> Option C

4. Quick Check:

   CAST() = type conversion [OK]

Hint: CAST() changes data types, not structure or permissions [OK]

Common Mistakes:

• Thinking CAST() creates or deletes tables
• Confusing CAST() with permission commands
• Assuming CAST() modifies data values without type change

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

easy

A. CAST('123' AS SIGNED)

B. CONVERT('123', TO INTEGER)

C. CAST('123' TO INTEGER)

D. CONVERT('123' AS INT)

Solution

1. Step 1: Recall correct CAST syntax

   In MySQL, the correct syntax is CAST(value AS type), so CAST('123' AS SIGNED) is valid.

2. Step 2: Check other options for syntax errors

   Options A, B, and D use incorrect syntax: A uses 'TO' instead of 'AS', B and D misuse CONVERT syntax, which is invalid in MySQL.

3. Final Answer:

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

4. Quick Check:

   CAST(value AS type) = correct syntax [OK]

Hint: Use CAST(value AS type) format for conversions [OK]

Common Mistakes:

• Using TO instead of AS in CAST
• Using CONVERT with wrong syntax
• Mixing syntax from other SQL dialects

3. What is the result of this query?

SELECT CAST('2023-06-15' AS DATE) + INTERVAL 5 DAY AS new_date;

medium

A. 2023-06-20

B. 2023-06-10

C. 2023-06-15 00:00:00

D. Error: Invalid date operation

Solution

1. Step 1: Convert string to DATE type

   CAST('2023-06-15' AS DATE) converts the string to a date value representing June 15, 2023.

2. Step 2: Add 5 days interval

   Adding INTERVAL 5 DAY moves the date forward by 5 days, resulting in June 20, 2023.

3. Final Answer:

   2023-06-20 -> Option A

4. Quick Check:

   Date + 5 days = 2023-06-20 [OK]

Hint: Add intervals to dates after casting string to DATE [OK]

Common Mistakes:

• Forgetting to cast string to DATE before adding interval
• Adding interval to string without conversion
• Expecting time part in result

4. Identify the error in this query:

SELECT CONVERT('abc' AS UNSIGNED);

medium

A. Returns NULL

B. No error, returns 0

C. Runtime error: cannot convert 'abc' to number

D. Syntax error: wrong CONVERT syntax

Solution

1. Step 1: Check CONVERT syntax

   CONVERT(value, type) is correct syntax in MySQL, but 'AS' keyword is invalid here, so syntax error would occur.

2. Step 2: Analyze actual query

   The query uses CONVERT('abc' AS UNSIGNED), which is invalid syntax and causes syntax error, but if corrected to CONVERT('abc', UNSIGNED), it returns 0 because 'abc' cannot be converted to a number.

3. Final Answer:

   Syntax error: wrong CONVERT syntax -> Option D

4. Quick Check:

   CONVERT() syntax must be CONVERT(value, type) [OK]

Hint: Use CONVERT(value, type) without AS keyword [OK]

Common Mistakes:

• Using AS keyword inside CONVERT()
• Expecting NULL instead of 0 for invalid numeric conversion
• Confusing CONVERT syntax with CAST

5. You have a table sales with a column amount stored as VARCHAR. You want to calculate the total sales as a number. Which query correctly converts and sums the amounts?

hard

A. SELECT SUM(amount) FROM sales;

B. SELECT SUM(CAST(amount AS DECIMAL(10,2))) FROM sales;

C. SELECT SUM(CONVERT(amount AS CHAR)) FROM sales;

D. SELECT SUM(CAST(amount AS DATE)) FROM sales;

Solution

1. Step 1: Understand data type of amount

   amount is VARCHAR, so summing directly treats values as strings, causing wrong results or errors.

2. Step 2: Convert amount to numeric type before summing

   CAST(amount AS DECIMAL(10,2)) converts string to decimal number, allowing correct sum calculation.

3. Step 3: Check other options

   SELECT SUM(amount) FROM sales; sums strings (incorrect), C converts to CHAR (string again), D converts to DATE (invalid for amounts).

4. Final Answer:

   SELECT SUM(CAST(amount AS DECIMAL(10,2))) FROM sales; -> Option B

5. Quick Check:

   Convert strings to numbers before SUM [OK]

Hint: CAST strings to DECIMAL before SUM to avoid errors [OK]

Common Mistakes:

• Summing VARCHAR without conversion
• Casting to wrong type like DATE or CHAR
• Using CONVERT with wrong syntax