MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE OCCUPATIONS (NAME varchar(255), occupation varchar(255));
INSERT INTO OCCUPATIONS VALUES
  ('Samantha', 'Doctor'),  
  ('Julia', 'Actor'),
  ('Maria', 'Actor'),
  ('Meera', 'Singer'),
  ('Ashely', 'Professor'),
  ('Ketty', 'Professor'),
  ('Christeen', 'Professor'),
  ('Jane', 'Actor'),
  ('Jenny', 'Doctor'),
  ('Priya', 'Singer'),
  ('Subhas', 'Student');

select * from OCCUPATIONS;

Records: 11  Duplicates: 0  Warnings: 0

NAME
occupation

Samantha
Doctor

Julia
Actor

Maria
Actor

Meera
Singer

Ashely
Professor

Ketty
Professor

Christeen
Professor

Jane
Actor

Jenny
Doctor

Priya
Singer

Subhas
Student

SELECT CONCAT(NAME,' ', '(',UPPER(SUBSTR(occupation,1,1)),')') AS occupation 
FROM OCCUPATIONS 
ORDER BY occupation;

occupation

Ashely (P)

Christeen (P)

Jane (A)

Jenny (D)

Julia (A)

Ketty (P)

Maria (A)

Meera (S)

Priya (S)

Samantha (D)

Subhas (S)

SELECT CONCAT('There are a total of',' ',COUNT(occupation),' ', LOWER(occupation),'s','.') AS OCCUPATION_COUNT 
FROM OCCUPATIONS 
GROUP BY OCCUPATION 
ORDER BY COUNT(OCCUPATION) ASC;

OCCUPATION_COUNT

There are a total of 1 students.

There are a total of 2 doctors.

There are a total of 2 singers.

There are a total of 3 actors.

There are a total of 3 professors.

SELECT CONCAT('There are a total of',' ',COUNT(occupation),' ', LOWER(occupation),IF(COUNT(occupation)>1, 's', ''),'.') AS OCCUPATION_COUNT 
FROM OCCUPATIONS 
GROUP BY OCCUPATION 
ORDER BY COUNT(OCCUPATION) ASC;

OCCUPATION_COUNT

There are a total of 1 student.

There are a total of 2 doctors.

There are a total of 2 singers.

There are a total of 3 actors.

There are a total of 3 professors.

NAME	occupation
Samantha	Doctor
Julia	Actor
Maria	Actor
Meera	Singer
Ashely	Professor
Ketty	Professor
Christeen	Professor
Jane	Actor
Jenny	Doctor
Priya	Singer
Subhas	Student