FORMAT and LPAD/RPAD in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to format the number 1234.567 to 2 decimal places.

MySQL

SELECT FORMAT(1234.567, [1]);

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

A2

B3

C1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 will remove decimals.

Using 3 will show too many decimals.

The FORMAT function formats a number to the specified number of decimal places. Here, 2 decimal places is correct.

2fill in blank

medium

Complete the code to pad the string '42' on the left with zeros to make it 5 characters long.

MySQL

SELECT LPAD('42', [1], '0');

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

A3

B5

C4

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 3 will only add one zero.

Using 2 will not add any padding.

LPAD pads the string on the left to the total length specified. Here, 5 means the result will be 5 characters long.

3fill in blank

hard

Fix the error in the code to pad the string 'abc' on the right with spaces to length 6.

MySQL

SELECT RPAD('abc', [1], ' ');

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

A3

B5

C6

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 3 will not add any padding.

Using 4 or 5 will add fewer spaces than needed.

RPAD pads the string on the right to the total length specified. To get length 6, use 6.

4fill in blank

hard

Fill both blanks to format the number 9876.54321 with 3 decimals and pad the result on the left with '*' to length 12.

MySQL

SELECT LPAD(FORMAT(9876.54321, [1]), [2], '*');

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

A3

B10

C12

D8

Attempts:

3 left

💡 Hint

Common Mistakes

Using 10 for length will not pad enough.

Using 8 for length is too short for formatted string.

FORMAT with 3 decimals shows 3 digits after decimal. LPAD pads the formatted string to length 12 with '*'.

5fill in blank

hard

Fill all three blanks to pad the string 'data' on the right with '-' to length 10, then format the number 123.456 with 1 decimal place.

MySQL

SELECT FORMAT(123.456, [1]), RPAD('data', [2], [3]);

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

A1

B10

C'-'

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using 5 for length pads too little.

Using wrong padding character causes unexpected output.

FORMAT with 1 decimal shows one digit after decimal. RPAD pads 'data' to length 10 with '-'.

Practice

1. What does the MySQL function FORMAT(12345.6789, 2) do?

easy

A. Formats the number with commas and 2 decimal places, like '12,345.68'.

B. Rounds the number to the nearest integer, like '12346'.

C. Converts the number to a string without decimals, like '12345'.

D. Adds leading zeros to make the number 2 digits long.

Solution

1. Step 1: Understand FORMAT function usage

   The FORMAT function formats a number with commas and rounds it to the specified decimal places.

2. Step 2: Apply FORMAT(12345.6789, 2)

   The number 12345.6789 is rounded to 2 decimals and commas are added, resulting in '12,345.68'.

3. Final Answer:

   Formats the number with commas and 2 decimal places, like '12,345.68'. -> Option A

4. Quick Check:

   FORMAT adds commas and decimals = A [OK]

Hint: FORMAT adds commas and rounds decimals [OK]

Common Mistakes:

• Thinking FORMAT rounds to integer only
• Confusing FORMAT with LPAD or RPAD
• Expecting no commas in output

2. Which of the following is the correct syntax to add leading zeros to make a string 6 characters long using LPAD in MySQL?

easy

A. LPAD(123, '6', '0')

B. LPAD('123', '6', 0)

C. LPAD('123', 6)

D. LPAD('123', 6, '0')

Solution

1. Step 1: Recall LPAD syntax

   LPAD(string, length, pad_string) pads the string on the left to the given length using pad_string.

2. Step 2: Check each option

   LPAD('123', 6, '0') uses correct types: string '123', length 6, pad '0'. Others have wrong types or missing arguments.

3. Final Answer:

   LPAD('123', 6, '0') -> Option D

4. Quick Check:

   LPAD needs string, length, pad string = C [OK]

Hint: LPAD needs string, length, and pad string in order [OK]

Common Mistakes:

• Using numbers instead of strings for pad character
• Missing the pad string argument
• Passing length as a string instead of number

3. What is the result of this query?
SELECT LPAD('abc', 6, '*'), RPAD('abc', 6, '!');

medium

A. 'abc***', '!!!abc'

B. '***abc', 'abc!!!'

C. 'abc', 'abc'

D. '***abc', '!!!abc'

Solution

1. Step 1: Understand LPAD and RPAD behavior

   LPAD pads on the left, RPAD pads on the right to reach total length 6.

2. Step 2: Calculate LPAD('abc', 6, '*')

   'abc' length is 3, so 3 '*' are added on left: '***abc'.

3. Step 3: Calculate RPAD('abc', 6, '!')

   Similarly, 3 '!' are added on right: 'abc!!!'.

4. Final Answer:

   '***abc', 'abc!!!' -> Option B

5. Quick Check:

   LPAD left stars, RPAD right exclamations = B [OK]

Hint: LPAD pads left, RPAD pads right to total length [OK]

Common Mistakes:

• Confusing LPAD and RPAD directions
• Not counting original string length
• Expecting padding to replace characters

4. Identify the error in this query:
SELECT LPAD('test', 4, '0');

medium

A. Syntax error due to missing RPAD.

B. Error because length is less than string length.

C. No error; it returns 'test'.

D. Error because pad string must be numeric.

Solution

1. Step 1: Check LPAD length vs string length

   LPAD pads string to length 4, but 'test' length is 4, so no padding needed.

2. Step 2: Confirm behavior when length equals string length

   LPAD returns original string if length equals string length, no error occurs.

3. Final Answer:

   No error; it returns 'test'. -> Option C

4. Quick Check:

   LPAD returns original if length equals string length = D [OK]

Hint: LPAD returns original if length equals string length [OK]

Common Mistakes:

• Expecting error when length equals string length
• Thinking pad string must be numeric
• Confusing LPAD with RPAD requirements

5. You want to display a price column with 2 decimals and commas, and pad the result on the left with '#' to make it 12 characters wide. Which query achieves this?

hard

A. SELECT LPAD(FORMAT(price, 2), 12, '#') FROM products;

B. SELECT FORMAT(LPAD(price, 12, '#'), 2) FROM products;

C. SELECT RPAD(FORMAT(price, 2), 12, '#') FROM products;

D. SELECT LPAD(price, 12, '#') FROM products;

Solution

1. Step 1: Format price with commas and 2 decimals

   Use FORMAT(price, 2) to get string like '1,234.56'.

2. Step 2: Pad formatted string on left to length 12 with '#'

   Use LPAD(formatted_string, 12, '#') to add '#' on left if needed.

3. Step 3: Check options

   SELECT LPAD(FORMAT(price, 2), 12, '#') FROM products; correctly formats then pads left. SELECT FORMAT(LPAD(price, 12, '#'), 2) FROM products; tries to pad number before formatting (wrong). SELECT RPAD(FORMAT(price, 2), 12, '#') FROM products; pads right, not left. SELECT LPAD(price, 12, '#') FROM products; pads number without formatting.

4. Final Answer:

   SELECT LPAD(FORMAT(price, 2), 12, '#') FROM products; -> Option A

5. Quick Check:

   Format first, then LPAD left padding = A [OK]

Hint: Format number first, then LPAD to pad left [OK]

Common Mistakes:

• Padding before formatting number
• Using RPAD instead of LPAD for left padding
• Padding raw number without formatting