SQL Queries for Employee Data Analysis: Find Designations, Job IDs, Update Phone Numbers, and More

This article presents a series of SQL queries designed to analyze employee data, encompassing tasks like counting designations, finding job IDs, updating phone numbers, extracting employee details based on first name length, formatting salaries, and manipulating strings within employee data.

1. Count Designations in the Employees Table

SELECT COUNT(DISTINCT designation) AS number_of_designations
FROM employees;

2. Get Job ID and Related Employee IDs

SELECT job_id, GROUP_CONCAT(employee_id) AS employee_ids
FROM employees
GROUP BY job_id;

3. Update Phone Numbers by Replacing Substring

UPDATE employees
SET phone_number = REPLACE(phone_number, '124', '999');

4. Get Employee Details with First Name Length >= 8

SELECT *
FROM employees
WHERE LENGTH(first_name) >= 8;

5. Display Leading Zeros for Maximum and Minimum Salary

SELECT LPAD(MAX(salary), 10, '0') AS max_salary, LPAD(MIN(salary), 10, '0') AS min_salary
FROM employees;

6. Get Employee ID and Email ID (Discard Last Three Characters)

SELECT employee_id, SUBSTRING(email, 1, LENGTH(email) - 3) AS email_id
FROM employees;

7. Get Last Word of Street Address

SELECT SUBSTRING_INDEX(street_address, ' ', -1) AS last_word
FROM employees;

8. Display First Word from Multi-Word Job Titles

SELECT SUBSTRING_INDEX(job_title, ' ', 1) AS first_word
FROM employees
WHERE job_title LIKE '% %';

9. Display First Name Length for Employees with 'c' After 2nd Position in Last Name

SELECT LENGTH(first_name) AS first_name_length
FROM employees
WHERE last_name LIKE '___c%';

10. Display First Eight Characters of First Name and Right-Aligned Salaried with '$' Sign

SELECT SUBSTRING(first_name, 1, 8) AS first_name,  RPAD('$' || salary, 10, ' ') AS salary
FROM employees;

These SQL queries provide a solid foundation for analyzing and manipulating employee data. Feel free to adapt and modify them to suit your specific needs and data structures. Remember to test each query thoroughly before implementing them in a production environment.

SQL Queries for Employee Data Analysis: Find Designations, Job IDs, Update Phone Numbers, and More