Count Salary Categories - Leetcode Solution

Problem statement:

Given a table Employee, create a new table called EmployeeSalary with the following schema:

EmployeeSalary(employee_id, salary_category)

where salary_category is calculated based on the following rules:

• If the employee's salary is less than 1000, then salary_category is 'A'.
• If the employee's salary is between 1000 and 2000 (inclusive), then salary_category is 'B'.
• If the employee's salary is between 2000 and 3000 (inclusive), then salary_category is 'C'.
• If the employee's salary is greater than 3000, then salary_category is 'D'.

Solution:

To solve this problem, we can use a SQL query to insert data into a new table called EmployeeSalary. The INSERT INTO statement will select the employee_id and salary_category from the Employee table and insert it into the EmployeeSalary table. To calculate the salary_category, we can use a CASE statement to check the employee's salary and return the appropriate category.

Here is the SQL query to create the new table:

CREATE TABLE EmployeeSalary ( employee_id INT, salary_category CHAR(1) );

And here is the SQL query to insert data into the EmployeeSalary table:

INSERT INTO EmployeeSalary (employee_id, salary_category) SELECT employee_id, CASE WHEN salary < 1000 THEN 'A' WHEN salary >= 1000 AND salary <= 2000 THEN 'B' WHEN salary >= 2000 AND salary <= 3000 THEN 'C' WHEN salary > 3000 THEN 'D' END AS salary_category FROM Employee;

This query will calculate the salary_category for each employee and insert the employee_id and salary_category into the new table. The CASE statement checks the salary value for each employee and assigns the appropriate salary_category based on the rules given in the problem statement.

Once the above SQL query is executed successfully, the new table EmployeeSalary is created and populated with data based on the given rules. This completes the solution to the Count Salary Categories problem on Leetcode.

1