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

Using CONCAT and CONCAT_WS in MySQL

📖 Scenario: You are managing a customer database for a small online store. You want to create full names and formatted contact information by combining different columns in your database.

🎯 Goal: Build SQL queries that use CONCAT and CONCAT_WS functions to combine customer first names, last names, and phone numbers into readable strings.

📋 What You'll Learn

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

Insert three specific customer records with exact values.

Write a query using CONCAT to combine first_name and last_name with a space in between.

Write a query using CONCAT_WS to combine first_name, last_name, and phone separated by commas.

💡 Why This Matters

🌍 Real World

Combining columns into readable strings is common for displaying full names, addresses, or contact info in reports and user interfaces.

💼 Career

Database developers and analysts often use CONCAT and CONCAT_WS to prepare data for reports, exports, or application display.

1

Create the customers table and insert data

Create a table called customers with columns id (integer primary key), first_name (varchar 50), last_name (varchar 50), and phone (varchar 15). Then insert these three rows exactly: (1, 'John', 'Doe', '123-456-7890'), (2, 'Jane', 'Smith', '234-567-8901'), and (3, 'Emily', 'Jones', '345-678-9012').

MySQL

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

2

Set up a query to combine first and last names

Write a SELECT query that uses CONCAT to combine first_name and last_name from the customers table with a space between them. Name the combined column full_name.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(15)
);

INSERT INTO customers (id, first_name, last_name, phone) VALUES
(1, 'John', 'Doe', '123-456-7890'),
(2, 'Jane', 'Smith', '234-567-8901'),
(3, 'Emily', 'Jones', '345-678-9012');

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

Need a hint?

Use CONCAT(first_name, ' ', last_name) to join names with a space.

3

Use CONCAT_WS to combine name and phone with commas

Write a SELECT query that uses CONCAT_WS with a comma and space separator to combine first_name, last_name, and phone from the customers table. Name the combined column contact_info.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(15)
);

INSERT INTO customers (id, first_name, last_name, phone) VALUES
(1, 'John', 'Doe', '123-456-7890'),
(2, 'Jane', 'Smith', '234-567-8901'),
(3, 'Emily', 'Jones', '345-678-9012');

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(', ', first_name, last_name, phone) to join with commas and spaces.

4

Combine both CONCAT and CONCAT_WS queries in one statement

Write a SELECT query that returns id, full_name using CONCAT (first and last name with space), and contact_info using CONCAT_WS (first name, last name, phone separated by commas) from the customers table.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(15)
);

INSERT INTO customers (id, first_name, last_name, phone) VALUES
(1, 'John', 'Doe', '123-456-7890'),
(2, 'Jane', 'Smith', '234-567-8901'),
(3, 'Emily', 'Jones', '345-678-9012');

-- Write a SELECT query that returns id, full_name, and contact_info
-- Your code here

Need a hint?

Include both CONCAT and CONCAT_WS in the SELECT clause.