Top Salary SQL Queries for Interviews Based on Employee Table

3. Write SQL query for updating employee salary to 30000 whose name is 'Nilesh Patil'
UPDATE salary
SET salary = 30000
WHERE id = (SELECT id FROM employee WHERE empname = 'Nilesh Patil');
Description:
This question updates the salary of employee "Nilesh Patil" to 30,000. It finds the id of the employee making an update on the table by using a subquery based on the name of the employee. After finding the appropriate employee it updates his salary. Such a question can be helpful in providing promotions, annual increments or changes in compensation policy at the company. It keeps certain records according to precise conditions without unintentionally changing some irrelevant data records.
Note for MySQL/SQL Server/PostgreSQL:
•This query works in all databases but you would need to ensure you are returning just one from the subquery in case of MySQL or PostgreSQL and SQL Server supports this construction of a subquery.

4. Write SQL query for displaying Employee name, Birth date, Designation and salary in Data Grid View.
SELECT e.empname, e.birth_date, s.designation, s.salary
FROM employee e
JOIN salary s ON e.id = s.id;
•Description:
This query gives the names of employees, date of birth, designation and salaries in a data grid view. JOIN operation is nothing but the joining of employee tables with salary tables with an id field so that all related designations and respective salaries are displayed for all employees. Such queries are commonly used in applications or web pages that display information about employees in a tabular form as adopted in the HR management or payroll systems. Data grid view is useful for showing a large data of employees in an organized and readable manner.
Note for MySQL/SQL Server/PostgreSQL:
•This will have answers identical in MySQL, SQL Server and PostgreSQL, although the output grid will likely require formatting using tools provided by the GUI on the appropriate platform, which might be MySQL Workbench or SQL Server Management Studio depending on the application.

5. Select employees whose birthday is in May
SELECT empname, birth_date
FROM employee
WHERE EXTRACT(MONTH FROM birth_date) = 5;
Description:
This query fetches names and birth dates of employees born in May. This function uses EXTRACT(MONTH FROM birth_date) to pull out the month portion from column birth_date and filters those whose month of birth is May or in other words, whose month = 5. This type of query could be used for creating birthday lists for HR or coordinating recognition events for employees. It's a simple query that filters based on a specific month. This makes it useful for numerous business and social events.
Note for MySQL/SQL Server/PostgreSQL:
•In the MySQL, we may use MONTH() instead of EXTRACT().
•In SQL Server use MONTH(birth_date), in order to extract a month.
•In PostgreSQL, EXTRACT(MONTH FROM birth_date) works as is.

6. Select employees with salary greater than 50000
SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary > 50000;
Description:
This statement is returning employees whose salary is more than 50,000. It makes a join between the employee and the salary table based on id column. The WHERE clause filters the result those whose salaries are less than or equal to 50,000. This can be handy for finding employees that make a lot of money or for doing salary analysis and reviewing. These could prove useful for the department/manager of human resource. This data could let know how salary distribution will appear in the organization if needed adjustments are to be implemented.
Note for MySQL/SQL Server/PostgreSQL :
The query will appear almost alike for MySQL, SQL Server and PostgreSQL due to their support of JOIN/WHERE clauses. Their implementation might differ based on specific database requirements of formats that salaries are saved within.

7.Find employees whose salary is between 30,000 and 60,000
SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary BETWEEN 30000 AND 60000;
•Description:
This query retrieves employees whose salary ranges between 30,000 and 60,000. The BETWEEN operator filters employees whose salary is between the declared range. Such a statement can be used to budget for employees, make salary comparisons and determine if employees fall within a specified compensation category. It also helps the HR department determine whether the distribution of salaries is competitive and ensures pay policies are in place.
MySQL/SQL Server/PostgreSQL Note
•The query should be executed in all databases however, SQL Server and PostgreSQL require checking whether column salary is indexed to optimize.

8.List employees whose salary is below the average salary.
SELECT e.empname, s.salary FROM employee e JOIN salary s ON e.id = s.id WHERE s.salary < (SELECT AVG(salary) FROM salary);
Description:
The query selects employees whose salaries are below the average in the salaries of all the employees. It calculates the average salary using a subquery from the salaries table and then compares the salary of each employee against the average. This is an extremely useful query especially in identifying those who might be underpaid in the workforce. Such queries are widely used by HR departments in performance reviews or restructuring of salaries to ensure that there is fair compensation within the workforce.
Note for MySQL/SQL Server/PostgreSQL:
•The query is accepted in all of the above databases. But in SQL Server and PostgreSQL the subquery must have a scalar value, otherwise an error will occur.

9. Find the employee with the highest salary.
SELECT e.empname, s.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary = (SELECT MAX(salary) FROM salary);
Description:
This query retrieves an employee who has the highest paid by using a subquery to find the maximum amount of salary in the salary table. It compares each one's salary with this number and returns the employee. This query can be effective for identifying top earners to use for performance recognition and salary benchmarking. Much of this data is usually used by HR departments as well as managers to reward superior performers or make strategic choices regarding salary adjustments.
Note for MySQL/SQL Server/PostgreSQL:
The given query is standard in all of MySQL, SQL Server and PostgreSQL but indexing the salary column helps reduce the execution time for big data queries.datasets.

10. Find Find employees who were born after a specific date.
SELECT empname, birth_date
FROM employee
WHERE birth_date > TO_DATE('2000-01-01', 'YYYY-MM-DD');
Description:
This is a query that brings employees born after a certain date. Here, the date has been considered as January 1, 2000. This query uses the birth_date column to filter the employees. The TO_DATE function in Oracle converts the date string into a date format. This query may help one who is doing demographic analysis or want to identify employees born during a certain time period.
Note for MySQL/SQL Server/PostgreSQL:
•For MySQL, use the following expression: STR_TO_DATE('2000-01-01', '%Y-%m-%d').
•SQL Server: Use CAST('2000-01-01' AS DATE) for date conversion.
•For PostgreSQL, use TO_DATE('2000-01-01', 'YYYY-MM-DD').

11. List employees and their manager’s name if applicable.
SELECT e.empname, m.empname AS manager_name
FROM employee e
LEFT JOIN employee m ON e.mgr_id = m.id;
Description:
This query lists all employees along with their respective manager names. It uses a LEFT JOIN link between the employee table itself using the field as mgr_id in the employee table and the id from the manager table. Thus, using a LEFT JOIN has the effect of including top-level executives in the result who lack managers with the manager name being NULL. This question is helpful to understand an organizational chart and reporting lines, usually required for employee surveys, team-building purposes or in organizational planning.
Note for MySQL/SQL Server/PostgreSQL:
•The query works the same on all databases. Still, MySQL and SQL Server also allow NULL checks using IS NULL if further filtering is necessary.

12. Find the total number of employees in the company.
SELECT COUNT(id) AS total_employees FROM employee;
Description:
This statement is used to find the overall number of employees in the firm by counting the number of rows in the employee table. The COUNT(id) function counts all the IDs of the employees in a table. This statement can be used to produce general work force statistics or to determine the general size of the company.
Important Note for MySQL/SQL Server/PostgreSQL:
MySQL: This statement works the same way in MySQL.
SQL Server: This query works in SQL Server as written.
PostgreSQL: This statement works as-is in PostgreSQL.

13. Select employees who have not been assigned to a manager.
SELECT empname
FROM employee
WHERE mgr_id IS NULL;
Description:
This query selects employees with no assignment to a manager. It filters the employee table to find records where mgr_id is NULL, meaning it doesn't report to any. This is helpful where in an organization there are some employees in a position like executive-level or top-level management who dose not have direct reports there at all. It is beneficial in identifying potential organizational gaps in finding managers missing for specific teams.
Note for MySQL/SQL Server/PostgreSQL:
• IS NULL condition is supported alike in MySQL, SQL Server and PostgreSQL.

14. Write Query to list the top 5 highest-paid employees.
SELECT empname, salary
FROM employee e
JOIN salary s ON e.id = s.id
ORDER BY s.salary DESC
FETCH FIRST 5 ROWS ONLY;
This kind of query retrieves the name and salary of the top five paid employees. It joins both tables for employees and salaries then orders the result, depending on the the salary column and is in descending order. The FETCH FIRST 5 ROWS ONLY clause gaurantees the result to the first five employees. This can be very useful in various conditions finding top earners performing compensation analysis, preparation before granting bonuses or rewards to outstanding performers.
Note for MySQL/SQL Server/PostgreSQL:
•MySQL: Use LIMIT 5 instead of FETCH FIRST 5 ROWS ONLY.
•SQL Server:Use TOP 5 instead of FETCH FIRST 5 ROWS ONLY.
• PostgreSQL: Same as Oracle with FETCH FIRST 5 ROWS ONLY.

15. Update employee salary based on specific salary.
UPDATE salary SET salary = salary + 5000 WHERE salary < 40000;
Description:
The query updates the "salary" table by increasing the salary by 5000 for all employees whose current salary is less than 40,000. The UPDATE statement identifies records in the "salary" table where the condition salary < 40000 is met. For those records, the SET salary = salary + 5000 part increments the salary by 5000. This ensures that employees with a salary below 40,000 receive a raise, making the query useful for salary adjustments based on predefined criteria.
Note for MySQL/SQL Server/PostgreSQL:
•This query works similarly in all databases.

16. Find employees who joined before a specific date.
SELECT empname, hire_date
FROM employee
WHERE hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');
Description:
This would be a query that could get the names and hire date for all employees hired before 1 January 2020. The TO_DATE() function converts the date string to a date object and this date object is applied to the WHERE clause to evaluate the hire date of all employees. This query may be useful for reports based on years of service at the company or for generation of seniority lists.
Note for MySQL/SQL Server/PostgreSQL:
For MySQL, you'd use STR_TO_DATE() to convert the date string
SQL Server: The query works as is. The database knows how to read the date literals directly.
PostgreSQL: You can cast the date string like this CAST('2020-01-01' AS DATE).

17. Write query to Find employees name who are managers.
SELECT DISTINCT e.empname
FROM employee e
WHERE e.id IN (SELECT mgr_id FROM employee WHERE mgr_id IS NOT NULL);
Description:
This query lists unique employees that are managers. It makes use of the subquery where it calls employees whose id is included in the mgr_id column of other employees. Such a thing gives one the identification of those persons who in the company play managerial roles. The use of this query can further be used in checking if management coverage exists, creating plans for developing leadership and ensuring that there is no lack of management in fulfilling managerial roles.
Note: For MySQL/SQL Server/PostgreSQL:
•The query works exactly the same on MySQL, SQL Server and PostgreSQL.

18. Find the employees who earn a salary greater than the average salary.
SELECT e.empname, e.salary
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.salary > (SELECT AVG(salary) FROM salary);
Description:
This will return employees whose salary is higher than the average of the salary in the company. First, it uses a subquery to find the average of the salaries in the table. It then retrieves the names and salary of those employees whose salary is greater than that average. Such an analysis helps in determining how pay structures compare in the organization. It also helps in avoiding maldistribution of pay.
MySQL/SQL Server/PostgreSQL note:
•This query will work identically in all DBs.

19. Count the total number of employees in each Designation.
SELECT designation, COUNT(*) AS total_employees
FROM salary
GROUP BY designation;
Description:
This query counts the overall number of employees for each of the unique designations in salary table. GROUP BY designation designates the columns by this column and COUNT(*) counts the number of employees at each group. The result would print the designation with an employee count for that specific designation in the total_employees column. This will be useful for knowing how big a workforce is spread for different job roles. It corresponds to the table design where designations and salaries are stored in the "salary" table.

20. Write query to Find the top 3 highest paid employees.
SELECT SELECT empname, salary
FROM salary
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;
Explanation:
This query returns names and salaries of the highest three paid employees, taking the order of the descending order of the column by the salary column. The use of FETCH FIRST 3 ROWS ONLY will then result in the return of just the top three salaries. This kind of query could be helpful for performance review purposes, bonus planning and compensation trend analysis on the best performers.
Note for MySQL/SQL Server/PostgreSQL
MySQL: Use LIMIT 3 instead of FETCH FIRST 3 ROWS ONLY
SQL Server: Tuse TOP 3 instead of FETCH FIRST 3 ROWS ONLY
PostgreSQL: Exactly like Oracle FETCH FIRST 3 ROWS ONLY.

21. Find employees who have not received a salary increase.
SELECT empname, salary
FROM salary
WHERE salary = (SELECT salary FROM salary WHERE id = 101);
Description:
This query fetches the names and salaries of employees whose salary is the same as that of employee with ID 101. This can be helpful in identifying employees who might not have received recent pay raises, help HR or managers track salary changes, and ensure equitable compensation adjustments for all employees.
Note for MySQL/SQL Server/PostgreSQL:
•The query works the same across all databases. However, it can be optimized further if there's an explicit last_updated column for salary changes.

22. Find the employees names who reports to a specific manager.
SELECT e.empname
FROM employee e
WHERE e.mgr_id = (SELECT id FROM employee WHERE empname = 'Abhijit Joshi');
Explanation:
This query returns the names of the employees who are reporting to the manager 'Abhijit Joshi'. It uses a subquery to get the manager's id and then selects the employees whose mgr_id matches the retrieved id. This query is useful in building team rosters assessing team structures or in determining manager employee relationships.
MySQL/SQL Server/PostgreSQL Note:
•This question behaves identically across MySQL, SQL Server and PostgreSQL.

23. Update Employee's Birth Date only for those employees whose salary is of a particular amount.
UPDATE employee
SET birth_date = TO_DATE('1990-01-01', 'YYYY-MM-DD')
where id IN (SELECT id FROM salary WHERE salary = 40000);
This statement updates the column named birth_date in the table "employee" of those employees earning a salary of 40,000. TO_DATE is used to ensure that the date is formatted correctly for Oracle. The subquery in the WHERE clause receives id values from the table "salary", so only those records from the "employee" table get updated . The query updates the birth dates based on the relation between the tables "employee" and "salary", hence maintaining data consistency.
Notes for Other Databases:
•MySQL
Use the STR_TO_DATE function to convert the string to a valid date format
•SQL Server
Cast or Convert to perform a date conversion for a string.
•PostgreSQL
Use DATE syntax to cast a string to a date format.
In each database, there may be specific functions to handle a date, but the query logic is the same.

24. Get all employees who do not have an assigned manager.
SELECT id, empname, birth_date FROM employee WHERE mgr_id IS NULL;
This query fetches information related to employees who do not have a manager assigned to them. It picks from the "employee" table the id, empname and birth_date columns by using the condition where mgr_id is NULL. This column mgr_id, relates to the ID of an employee's manager. For a NULL value in it, it means no assigned manager. This query becomes useful in identifying top-level employees for example, executives or stand alone contributors who work without a particular managerial supervision.
Notes for Other Databases:
•MySQL:
The syntax is the same because MySQL also makes use of IS NULL to check for NULL values.
•SQL Server:
The syntax is the same because SQL Server uses IS NULL to check for NULL values.
•PostgreSQL:
The syntax is the same because PostgreSQL also uses IS NULL to check for NULL values.

25. Write a query to find employee names who are hired in a particular month.
SELECT empname, hire_date
FROM employee
WHERE EXTRACT(MONTH FROM hire_date) = 5;
Description:
Returns names and hire dates of those who have been hired in the month of May (5). This uses EXTRACT function to extract month from the hire_date and checks with 5. This will help for reports on new joiner report, hiring trends by season/months.
Note: For MySQL/SQL Server/PostgreSQL
• MySQL Use MONTH(hire_date) instead of EXTRACT(MONTH FROM hire_date).
•SQL Server: MONTH(hire_date) instead of EXTRACT(MONTH FROM hire_date).
•PostgreSQL: This works just like Oracle using EXTRACT(MONTH FROM hire_date).

26. Find employees with salaries between two values.
SELECT id, empname, salary FROM employee e JOIN salary s ON e.id = s.id WHERE s.salary BETWEEN 30000 AND 50000;
Description:
This query will get the data of those employees whose salary is between two given specified values. The query gets the columns id,empname, and salary from tables employee and salary tables. BETWEEN operator used to filter out those employees in whose salaries range between 30,000 and 50,000. The query uses JOIN to join the "salary" table with the "employee" table on the id column bringing an employee's details for correlation with his or her salary details. This SQL is useful for reporting where an employee falls within certain limits of salary.
Note for Other Databases:
MySQL:
The above SQL syntax is the same as MySQL because BETWEEN operators are supported to filter numbers to be within a value.
SQL Server:
In SQL Server, the syntax stays the same and between works in filtering values falling within the given range.
PostgreSQL:
The syntax is similar in PostgreSQL because BETWEEN can also be used for the filtration of values within a particular range.

27. Find employees whose age are more than 30 years.
SELECT id, empname, birth_date FROM employee WHERE birth_date <= ADD_MONTHS(SYSDATE, -360);
Description:
This query will fetch information for employees whose age is over 30 years. This selects the id, empname, and birth_date columns from the "employee" table. The function ADD_MONTHS(SYSDATE, -360) calculates the date which is 30 years prior to the current date and then the WHERE clause checks for whether the birth_date falls on or before that date calculated. This will help get the employees who are 30 years or older and are identified by their date of birth. The fact that SYSDATE forces the comparison to be of the current system date.
Additional Database Notes
MySQL:
Use CURDATE() instead of SYSDATE. Use DATE_SUB() when subtracting dates:
WHERE birth_date <= DATE_SUB(CURDATE(), INTERVAL 30 YEAR);
SQL Server:
Use GETDATE() instead of SYSDATE. Use DATEADD() when subtracting dates:
WHERE birth_date <= DATEADD(YEAR, -30, GETDATE());
PostgreSQL:
Use CURRENT_DATE instead of SYSDATE, and the INTERVAL keyword for date subtraction
WHERE birth_date <= CURRENT_DATE - INTERVAL '30 years';

28. Find the name of the highest salaried employee for each Designation.
SELECT s.designation, e.empname, s.salary
FROM salary s
JOIN employee e ON s.id = e.id
WHERE s.salary = (
SELECT MAX(salary)
FROM salary
WHERE designation = s.designation
)
ORDER BY s.designation;
This query fetches the names of the highest salaried employees for each designation. It selects the designation, empname and salary from the salary and employee tables. The subquery in the WHERE clause finds the maximum salary for each designation in the salary table. The main query then matches employees whose salary equals this maximum for each designation. This will help determine who are the highest paid in each position or job title. The result is sorted by designation for easy reading.
Notes on Other Databases:
•MySQL:
The syntax remains the same as MySQL supports subqueries and joins in this way.
•SQL Server:
The syntax is the same. SQL Server also supports subqueries and joins in the same way.
•PostgreSQL:
The syntax remains the same. PostgreSQL also supports subqueries and joins in the same way.

29. Retrieve employees whose name starts with a specific letter.
SELECT empname
FROM employee
WHERE empname LIKE 'A%';
Description:
This is a query that fetches the names of employees whose names start with the alphabet 'A'. The LIKE operator is used with the pattern 'A%' where % denotes any sequence of characters following 'A'. The same query can be used for producing reports on employees whose names have certain initial letters targeted email marketing to a particular set of people, or just filtering employees based on their names for some other reason.
Note for MySQL/SQL Server/PostgreSQL:
•This query executes identically in all databases. Make sure the field empname is indexed so as to be looked up faster.

30. Find employees with a specific job Designation.
SELECT e.id, e.empname, s.designation
FROM employee e
JOIN salary s ON e.id = s.id
WHERE s.designation = 'Manager';
Description:
This is a query that fetches information about employees who have a particular job designation which is "Manager" in this case. It will pick the id, empname and designation columns from the employee and salary tables. The JOIN operation combines the two tables using the id field to join them so that employee's information is matched with his salary and designation. The WHERE clause filters the employees whose designation is 'Manager'. You can replace 'Manager' with any other designation to get employees with that specific job title.
Notes for Other Databases.
MySQL:
MySQL uses the same syntax as JOIN and WHERE since the operations of JOIN and WHERE work exactly the same.
SQL Server:
SQL Server still keeps the same syntax since filtering by a particular designation is still the same.
PostgreSQL:
PostgreSQL uses the same syntax as it supports JOIN and also the WHERE clause in filtering the designation.

31. Select employees who are eligible for retirement (age 60 or older)
SELECT empname, birth_date
FROM employee
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birth_date) >= 60;
Description:
This will extract the names and birth dates of employee records who are 60 years or older. This was assuming that the retirement age is 60. Subtracting the employee's year of birth from the current year results in SYSDATE in the calculation. It is typically used for retirement planning and succession planning or benefits eligibility analysis.
MySQL/SQL Server/PostgreSQL Note :
MySQL: Use YEAR(CURDATE()) instead of the EXTRACT(YEAR FROM SYSDATE).
SQL Server: Use YEAR(GETDATE()) instead of the EXTRACT(YEAR FROM SYSDATE).
PostgreSQL: Use EXTRACT(YEAR FROM CURRENT_DATE) instead of SYSDATE.

32. Write Query to get the total salary of the top 3 highest-paid employees.
SELECT SUM(salary) AS total_top_3_salaries
FROM (
SELECT salary
FROM salary
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY
) AS top_salaries;
Description:
This query sums up the salary paid to the top 3 most paid employees. It orders employees by salary in descending order and then picks the first three. It then sums up the salary using the SUM() function. This query is helpful to understand how much compensation for top talent is having on the bottom line.
MySQL/SQL Server/PostgreSQL Note:
MySQL: Use LIMIT 3 instead of FETCH FIRST 3 ROWS ONLY.
Sql Server: TOP 3 instead of FETCH FIRST 3 ROWS ONLY;
PostgreSQL: LIMIT 3 instead of FETCH FIRST 3 ROWS ONLY;

33.Write Query to find the second highest paid employee in the company.
SELECT empname, salary
FROM salary s
JOIN employee e ON s.id = e.id
WHERE s.salary = (
SELECT MAX(salary)
FROM salary
WHERE salary < (
SELECT MAX(salary) FROM salary
)
);
It finds the details of the company's second-highest paid employee. It first selects the columns empname and salary from both the salary and employee tables. This inner subquery SELECT MAX(salary) FROM salary finds the highest salary that exists in the company and therefore the outer query SELECT MAX(salary) FROM salary WHERE salary <. That is the highest salary found which is less than this value, thus it is the salary of the second-highest salary of any employee in the company. WHERE clause filters the employee whose salary is equal to this second-highest value. This query can be used to identify employees earning just below the top earner.
Notes for Other Databases:
MySQL:
Syntax is the same. MySQL allows subqueries and joins in this fashion.
SQL Server:
Syntax is the same in SQL Server too, no changes are required to that structure for this query.
The syntax does not change in PostgreSQL as it also accepts subqueries and joins in the very same format.