CONCAT and CONCAT_WS in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to concatenate first_name and last_name without any separator.

SQL

SELECT CONCAT(first_name, [1]) AS full_name FROM employees;

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

Alast_name

B' '

CNULL

Dfirst_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using a space string instead of the last_name column.

Trying to concatenate only one column.

The CONCAT function joins the strings given as arguments. To join first_name and last_name, you need to include last_name as the second argument.

2fill in blank

medium

Complete the code to concatenate first_name and last_name with a space between them using CONCAT_WS.

SQL

SELECT CONCAT_WS([1], first_name, last_name) AS full_name FROM employees;

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

A'-'

B''

C' '

D','

Attempts:

3 left

💡 Hint

Common Mistakes

Using CONCAT instead of CONCAT_WS for separator.

Using no separator or wrong separator like comma.

CONCAT_WS joins strings with a separator. To add a space between first_name and last_name, use ' ' as the separator.

3fill in blank

hard

Fix the error in the code to concatenate city and state with a comma and space using CONCAT_WS.

SQL

SELECT CONCAT_WS([1], city, state) AS location FROM addresses;

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

A' '

Bcity, state

Ccity + ', ' + state

D', '

Attempts:

3 left

💡 Hint

Common Mistakes

Passing multiple columns as separator.

Using plus signs for concatenation inside CONCAT_WS.

CONCAT_WS requires a single string separator as the first argument. Using ', ' (comma and space inside quotes) correctly separates city and state.

4fill in blank

hard

Fill both blanks to concatenate street, city, and state with ', ' as separator using CONCAT_WS.

SQL

SELECT CONCAT_WS([1], street, [2], state) AS full_address FROM addresses;

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

A', '

Bcity

Cstate

D' '

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong separator like space.

Putting state instead of city in second blank.

CONCAT_WS uses ', ' as the separator. street, city, and state are concatenated with ', ' between them.

5fill in blank

hard

Fill all three blanks to concatenate first_name, middle_name, and last_name with spaces using CONCAT_WS.

SQL

SELECT CONCAT_WS([1], first_name, [2], [3]) AS full_name FROM employees;

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

A' '

Bfirst_name

Cmiddle_name

Dlast_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong separator like comma.

Mixing up column order.

CONCAT_WS(' ', first_name, middle_name, last_name) joins the three names with spaces between them.

Practice

1. What is the main difference between CONCAT and CONCAT_WS in SQL?

easy

A. CONCAT joins strings directly; CONCAT_WS adds a separator between strings.

B. CONCAT adds a separator; CONCAT_WS joins strings directly.

C. CONCAT works only with numbers; CONCAT_WS works only with text.

D. CONCAT is used for arrays; CONCAT_WS is used for tables.

Solution

1. Step 1: Understand CONCAT behavior

   CONCAT joins text parts directly without any separator.

2. Step 2: Understand CONCAT_WS behavior

   CONCAT_WS joins text parts but inserts a separator you specify between them.

3. Final Answer:

   CONCAT joins strings directly; CONCAT_WS adds a separator between strings. -> Option A

4. Quick Check:

   Difference = separator usage [OK]

Hint: Remember WS means With Separator [OK]

Common Mistakes:

• Thinking CONCAT_WS adds no separator
• Confusing which function adds separators
• Assuming CONCAT works only on numbers

2. Which of the following is the correct syntax to join columns first_name and last_name with a space using CONCAT_WS?

easy

A. CONCAT_WS(' ', first_name + last_name)

B. CONCAT_WS(first_name, last_name, ' ')

C. CONCAT_WS(' ', first_name, last_name)

D. CONCAT_WS(first_name || ' ' || last_name)

Solution

1. Step 1: Recall CONCAT_WS syntax

   The first argument is the separator string, followed by the columns to join.

2. Step 2: Match syntax to options

   CONCAT_WS(' ', first_name, last_name) correctly uses a space as separator and then the two columns.

3. Final Answer:

   CONCAT_WS(' ', first_name, last_name) -> Option C

4. Quick Check:

   Separator first, then columns [OK]

Hint: Separator is always first argument in CONCAT_WS [OK]

Common Mistakes:

• Putting separator last instead of first
• Using operators inside CONCAT_WS arguments
• Passing concatenated string as single argument

3. Given a table users with columns city = 'Paris', country = 'France', what is the result of this query?

SELECT CONCAT(city, ', ', country) AS location FROM users;

medium

A. 'Paris, France'

B. 'Paris , France'

C. 'Paris,France'

D. 'ParisFrance'

Solution

1. Step 1: Understand CONCAT arguments

   CONCAT joins city, then the string ', ', then country exactly as given.

2. Step 2: Combine values

   city = 'Paris', separator = ', ', country = 'France' results in 'Paris, France'.

3. Final Answer:

   'Paris, France' -> Option A

4. Quick Check:

   CONCAT joins exactly as given [OK]

Hint: CONCAT joins exactly, including spaces in strings [OK]

Common Mistakes:

• Ignoring spaces inside separator string
• Assuming CONCAT adds spaces automatically
• Confusing CONCAT_WS with CONCAT

4. Identify the error in this SQL query:

SELECT CONCAT_WS(first_name, ' ', last_name) FROM employees;

medium

A. Using CONCAT_WS instead of CONCAT

B. Missing separator argument as first parameter

C. Incorrect column names

D. No error, query is correct

Solution

1. Step 1: Check CONCAT_WS syntax

   First argument must be the separator string, but here first_name is used instead.

2. Step 2: Identify correct usage

   Separator should be a string like ' ', then columns follow.

3. Final Answer:

   Missing separator argument as first parameter -> Option B

4. Quick Check:

   Separator must be first argument [OK]

Hint: Separator always first in CONCAT_WS [OK]

Common Mistakes:

• Putting column name as separator
• Swapping argument order
• Assuming CONCAT_WS works like CONCAT

5. You want to create a full address string from columns street, city, and zipcode separated by commas. Which query correctly uses CONCAT_WS to achieve this, and what will be the output if street = '123 Main St', city = NULL, zipcode = '90210'?

hard

A. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, NULL, 90210'

B. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, , 90210'

C. SELECT CONCAT(street, ', ', city, ', ', zipcode) AS address FROM table; Output: '123 Main St, , 90210'

D. SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, 90210'

Solution

1. Step 1: Understand CONCAT_WS behavior with NULLs

   CONCAT_WS skips NULL values and does not add extra separators for them.

2. Step 2: Apply to given data

   With city = NULL, CONCAT_WS(', ', street, city, zipcode) returns '123 Main St, 90210' without extra commas.

3. Final Answer:

   SELECT CONCAT_WS(', ', street, city, zipcode) AS address FROM table; Output: '123 Main St, 90210' -> Option D

4. Quick Check:

   CONCAT_WS skips NULLs cleanly [OK]

Hint: CONCAT_WS skips NULLs, no extra separators [OK]

Common Mistakes:

• Expecting NULL to appear as text
• Assuming CONCAT_WS adds separators for NULLs
• Using CONCAT which does not skip NULLs