Concatenation with || operator in PostgreSQL - Mini Project: Build & Apply

Concatenate Customer Names Using || Operator

📖 Scenario: You work at a small online store. The store keeps customer first and last names in separate columns in a table called customers. You want to create a new column that shows the full name by joining the first and last names.

🎯 Goal: Build a SQL query that selects the customer ID and a new column called full_name which combines first_name and last_name using the || concatenation operator with a space between the names.

📋 What You'll Learn

Create a table called customers with columns customer_id, first_name, and last_name.

Insert three customers with exact names: (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), (3, 'Alice', 'Johnson').

Write a SELECT query that concatenates first_name and last_name with a space using the || operator.

Alias the concatenated column as full_name.

💡 Why This Matters

🌍 Real World

Combining first and last names into a full name is common in customer databases, contact lists, and user profiles.

💼 Career

Knowing how to concatenate strings in SQL helps you prepare data for reports, user interfaces, and data exports.

1

Create the customers table and insert data

Create a table called customers with columns customer_id (integer), first_name (text), and last_name (text). Then insert these exact rows: (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), and (3, 'Alice', 'Johnson').

PostgreSQL

-- Create the customers table and insert the three rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set up the SELECT statement

Start a SELECT query that selects customer_id from the customers table. Write the FROM clause to specify the table customers.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER,
    first_name TEXT,
    last_name TEXT
);

INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson');

-- Start the SELECT query with customer_id and FROM customers
-- Your code here

Need a hint?

Use SELECT customer_id and FROM customers to start your query.

3

Concatenate first_name and last_name with || operator

In the SELECT statement, add a new column that concatenates first_name, a space ' ', and last_name using the || operator. Alias this concatenated column as full_name.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER,
    first_name TEXT,
    last_name TEXT
);

INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson');

SELECT customer_id, 
-- Add concatenation of first_name, space, and last_name as full_name
-- Your code here
FROM customers;

Need a hint?

Use first_name || ' ' || last_name AS full_name to join the names with a space.

4

Complete the query and verify structure

Ensure the full query selects customer_id and the concatenated full_name from the customers table. The query should be complete and ready to run.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER,
    first_name TEXT,
    last_name TEXT
);

INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson');

SELECT customer_id, 
       first_name || ' ' || last_name AS full_name
FROM customers;

-- Finalize the query structure
-- Your code here

Need a hint?

Make sure the query is complete and selects both columns correctly.