Concatenation with || operator in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to concatenate first_name and last_name with a space in between.

PostgreSQL

SELECT first_name [1] ' ' [2] last_name AS full_name FROM employees;

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

A||

B+

C&

DCONCAT

Attempts:

3 left

💡 Hint

Common Mistakes

Using + instead of || for string concatenation.

Using CONCAT function without parentheses.

In PostgreSQL, the concatenation operator is ||. You use it to join strings together.

2fill in blank

medium

Complete the code to concatenate city and country with a comma and space between.

PostgreSQL

SELECT city [1] ', ' [2] country AS location FROM locations;

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

A||

B+

CCONCAT

D&

Attempts:

3 left

💡 Hint

Common Mistakes

Using + operator which works in some languages but not in PostgreSQL.

Forgetting spaces around the comma.

The || operator concatenates strings in PostgreSQL. Here it joins city, a comma and space, and country.

3fill in blank

hard

Fix the error in the code to concatenate first_name and last_name with a dash between.

PostgreSQL

SELECT first_name [1] '-' [2] last_name AS full_name FROM users;

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

A||

B+

CCONCAT

D&

Attempts:

3 left

💡 Hint

Common Mistakes

Using + operator which is invalid for string concatenation in PostgreSQL.

Using CONCAT without parentheses.

PostgreSQL uses || to concatenate strings. The + operator causes an error here.

4fill in blank

hard

Fill both blanks to concatenate street and postal_code with a comma and space between.

PostgreSQL

SELECT street [1] ', ' [2] postal_code AS address FROM addresses;

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

A||

B+

CCONCAT

D&

Attempts:

3 left

💡 Hint

Common Mistakes

Using + operator which causes errors.

Using CONCAT without parentheses.

Use || to concatenate strings in PostgreSQL. Both blanks need this operator.

5fill in blank

hard

Fill all four blanks to concatenate title, first_name, and last_name with spaces between.

PostgreSQL

SELECT title [1] ' ' [2] first_name [3] ' ' [4] last_name AS full_name FROM staff;

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

A||

B+

CCONCAT

D&

Attempts:

3 left

💡 Hint

Common Mistakes

Using + or & operators which cause errors.

Using CONCAT without parentheses.

Use || operator for all concatenations to join title, first_name, and last_name with spaces.

Practice

1. What does the || operator do in PostgreSQL?

easy

A. Joins two or more text strings into one string

B. Adds two numbers together

C. Compares two strings for equality

D. Deletes a row from a table

Solution

1. Step 1: Understand the operator purpose

   The || operator is used to combine text strings in PostgreSQL.

2. Step 2: Differentiate from other operators

   It does not perform arithmetic or comparison; it concatenates strings.

3. Final Answer:

   Joins two or more text strings into one string -> Option A

4. Quick Check:

   Concatenation = Joins strings [OK]

Hint: Remember: || joins text, not numbers or comparisons [OK]

Common Mistakes:

• Thinking || adds numbers
• Confusing || with equality operator
• Assuming it deletes data

2. Which of the following is the correct syntax to concatenate 'Hello' and 'World' with a space in between using || in PostgreSQL?

easy

A. 'Hello' & ' ' & 'World'

B. 'Hello' + ' ' + 'World'

C. 'Hello' CONCAT ' ' CONCAT 'World'

D. 'Hello' || ' ' || 'World'

Solution

1. Step 1: Identify correct concatenation syntax

   PostgreSQL uses || to join strings, so 'Hello' || ' ' || 'World' is correct.

2. Step 2: Check other options for errors

   Options A and B use invalid operators for strings (& and +); D uses incorrect CONCAT syntax.

3. Final Answer:

   'Hello' || ' ' || 'World' -> Option D

4. Quick Check:

   Use || for string join [OK]

Hint: Use || to join strings, not + or & [OK]

Common Mistakes:

• Using + instead of || for strings
• Using & which is not valid in SQL
• Trying CONCAT keyword without function syntax

3. Given the table users with columns first_name = 'Jane' and last_name = 'Doe', what is the result of this query?

SELECT first_name || ' ' || last_name AS full_name FROM users;

medium

A. Jane || ' ' || Doe

B. JaneDoe

C. Jane Doe

D. NULL

Solution

1. Step 1: Understand concatenation with spaces

   The query joins first_name, a space, and last_name using ||, so the output includes a space between names.

2. Step 2: Apply values from the table

   With 'Jane' and 'Doe', the result is 'Jane Doe'.

3. Final Answer:

   Jane Doe -> Option C

4. Quick Check:

   Concatenate with space = 'Jane Doe' [OK]

Hint: Add spaces manually when joining strings [OK]

Common Mistakes:

• Forgetting to add space between names
• Expecting || to add space automatically
• Confusing output with literal || symbols

4. What is wrong with this query?

SELECT first_name || last_name FROM users WHERE id = 1;

Assuming first_name is 'John' and last_name is NULL for user with id 1.

medium

A. The query returns NULL

B. The query returns 'JohnNULL'

C. The query causes a syntax error

D. The query returns 'John '

Solution

1. Step 1: Understand NULL behavior in concatenation

   In PostgreSQL, concatenating any string with NULL using || results in NULL.

2. Step 2: Apply to given data

   Since last_name is NULL, first_name || last_name returns NULL, not 'JohnNULL'.

3. Final Answer:

   NULL -> Option A

4. Quick Check:

   NULL in concat makes result NULL [OK]

Hint: NULL in concatenation yields NULL result [OK]

Common Mistakes:

• Expecting NULL to convert to 'NULL' string
• Thinking query has syntax error
• Assuming concatenation adds spaces automatically

5. You want to create a full address string by concatenating street, city, and zipcode columns with commas and spaces in between. Which query correctly handles NULL values so the result never becomes NULL?

SELECT street || ', ' || city || ', ' || zipcode AS full_address FROM addresses;

hard

A. Use CONCAT_WS(',', street, city, zipcode) instead of ||

B. Use COALESCE to replace NULLs: COALESCE(street, '') || ', ' || COALESCE(city, '') || ', ' || COALESCE(zipcode, '')

C. Use + operator instead of || to avoid NULL

D. Leave as is; NULLs are ignored in concatenation

Solution

1. Step 1: Identify NULL handling in concatenation

   Using || with NULL returns NULL, so we must replace NULLs with empty strings using COALESCE.

2. Step 2: Apply COALESCE to each column

   Replace each column with COALESCE(column, '') to avoid NULL results.

3. Final Answer:

   Use COALESCE to replace NULLs: COALESCE(street, '') || ', ' || COALESCE(city, '') || ', ' || COALESCE(zipcode, '') -> Option B

4. Quick Check:

   Replace NULLs with COALESCE to avoid NULL output [OK]

Hint: Use COALESCE to avoid NULL in concatenation [OK]

Common Mistakes:

• Assuming NULLs are ignored automatically
• Using + operator which is invalid for strings
• Not handling NULLs causing full result to be NULL