SQL INSERT INTO SELECT query for updating on same table with some modification
So you're close you just need to add the data modifications you want to each field in your SELECT list like so:
INSERT INTO 
    Student (Name, Department)
SELECT CONCAT('Mr. ', Name), CONCAT('SCIENCE(', Department, ')') FROM Student;

You can use the CONCAT() function to concatenate strings in MySQL