/*
You are given a dataset containing employee information, including their employee_id, employee_name, age, department, and salary.

Generate a summary report that includes the following details for each department:

- Number of Employees: The total count of employees working in each department.
- Employee Info: A concatenated string of employee names and their corresponding ages, separated by a specific delimiter.

The output should be structured in a table format with the following columns:
department, no_of_employees and employee_info
*/

-- Schema Setup
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(15),
    age INT,
    department VARCHAR(15),
    salary INT
);

INSERT INTO employees (employee_id, employee_name, age, department, salary) VALUES
(1, 'Sharma', 40, 'Engineering', 85000),
(2, 'Pandey', 34, 'Marketing', 62000),
(3, 'Kumar', 35, 'Sales', 82000),
(4, 'Priya', 29, 'HR', 66000),
(5, 'Vivek', 37, 'Finance', 88000),
(6, 'Joshi', 32, 'Engineering', 80000),
(7, 'Patil', 26, 'Marketing', 66000),
(8, 'Yadav', 43, 'Sales', 84000),
(10, 'Pooja', 45, 'Finance', 95000),
(11, 'Karan', 28, 'Engineering', 68000),
(13, 'Menon', 26, 'Sales', 82000),
(14, 'Pillai', 41, 'HR', 62000),
(16, 'Sanjay', 33, 'Engineering', 78000),
(17, 'Rekha', 69, 'Marketing', 65000),
(20, 'Verma', 27, 'Marketing', 55000),
(50, 'Suresh', 40, 'Finance', 40000),
(81, 'Desai', 58, 'Sales', 88000);

-- Solution
SELECT
    department,
    COUNT(employee_id) AS no_of_employees,
    GROUP_CONCAT(CONCAT(employee_name,':',age)) AS employee_info
FROM employees
GROUP BY department;