CONCAT and CONCAT_WS in SQL - Mini Project: Build & Apply

Using CONCAT and CONCAT_WS to Combine Text in SQL

📖 Scenario: You are managing a small customer database for a local bookstore. You want to create a full name field by combining first and last names, and also create a contact info field by combining the full name with the email address, separated by a comma.

🎯 Goal: Build SQL queries that use CONCAT and CONCAT_WS functions to combine text columns into new fields.

📋 What You'll Learn

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

Insert three specific customer records into the customers table.

Write a query using CONCAT to create a full_name column by joining first_name and last_name with a space.

Write a query using CONCAT_WS to create a contact_info column by joining full_name and email separated by a comma and space.

💡 Why This Matters

🌍 Real World

Combining text fields like names and contact info is common in customer databases, reports, and user interfaces.

💼 Career

Knowing how to use CONCAT and CONCAT_WS helps database developers and analysts prepare readable and formatted data outputs.

1

Create the customers table and insert data

Create a table called customers with columns id (integer), first_name (text), last_name (text), and email (text). Then insert these three rows exactly: (1, 'Alice', 'Johnson', '[e-mail]'), (2, 'Bob', 'Smith', '[e-mail]'), and (3, 'Carol', 'Davis', '[e-mail]').

SQL

-- 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

Add a query to combine first and last names

Write a SQL query that selects id and creates a new column called full_name by using CONCAT to join first_name and last_name with a space between them from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

INSERT INTO customers (id, first_name, last_name, email) VALUES
  (1, 'Alice', 'Johnson', '[e-mail]'),
  (2, 'Bob', 'Smith', '[e-mail]'),
  (3, 'Carol', 'Davis', '[e-mail]');

-- Write a SELECT query using CONCAT to create full_name
-- Your code here

Need a hint?

Use CONCAT with a space string ' ' between the names.

3

Add a query to combine full name and email with CONCAT_WS

Write a SQL query that selects id and creates a new column called contact_info by using CONCAT_WS with a comma and space separator to join first_name and last_name as a full name, and email from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

INSERT INTO customers (id, first_name, last_name, email) VALUES
  (1, 'Alice', 'Johnson', '[e-mail]'),
  (2, 'Bob', 'Smith', '[e-mail]'),
  (3, 'Carol', 'Davis', '[e-mail]');

SELECT id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

-- Write a SELECT query using CONCAT_WS to create contact_info
-- Your code here

Need a hint?

Use CONCAT_WS with ', ' as the separator and nest CONCAT for the full name.

4

Combine all queries into one final query

Write a single SQL query that selects id, creates full_name by concatenating first_name and last_name with a space using CONCAT, and creates contact_info by concatenating full_name and email separated by a comma and space using CONCAT_WS from the customers table.

SQL

CREATE TABLE customers (
  id INTEGER,
  first_name TEXT,
  last_name TEXT,
  email TEXT
);

INSERT INTO customers (id, first_name, last_name, email) VALUES
  (1, 'Alice', 'Johnson', '[e-mail]'),
  (2, 'Bob', 'Smith', '[e-mail]'),
  (3, 'Carol', 'Davis', '[e-mail]');

-- Write a single SELECT query that creates full_name and contact_info
-- Your code here

Need a hint?

Combine the previous two queries into one SELECT statement with multiple columns.