Struggling with SQL Query

adhyaa khare 238 21-May-2023

Hello everyone,

I am trying to solve an SQL query problem but I am having difficulty. I am trying to write a query that returns a list of employees who have worked in at least one department for more than 8 years.

The database consists of two tables:

Employee Table

Name | Age | Department

John | 30 | Marketing

Sara | 32 | Finance

Alice | 25 | HR

Employee History Table

Name | Department | Years of Experience

John | Marketing | 5

John | HR | 1

Sara | Finance | 8

Alice | HR | 10

I am trying to write a query to return the names of the employees and the departments they have worked in for more than 8 years, but I am having difficulty coming up with the correct syntax. Any help would be greatly appreciated!

Thank you.

sql

Updated on 27-Jul-2023

adhyaa khare

Just like architecture, which is an art used by people daily and affects their everyday life, so is code. You can create something that people interact with, use, touch, and work with. That is an amazing feeling.

Can you answer this question?

Answer

2 Answers

Aryan Kumar

23-May-2023

To solve the given problem, you can use a combination of JOIN and GROUP BY clauses along with a HAVING clause to filter the results. Here's an example SQL query that should return the desired results:

SELECT EH.Name, EH.Department
FROM EmployeeHistoryTable EH
JOIN EmployeeTable E ON EH.Name = E.Name
WHERE EH.YearsOfExperience > 8
GROUP BY EH.Name, EH.Department;

This query performs an inner join between the `EmployeeHistoryTable` and `EmployeeTable` on the `Name` column to retrieve the relevant information. The `WHERE` clause filters out the records where the years of experience are greater than 8. Then, the `GROUP BY` clause is used to group the results by employee name and department. Finally, the `SELECT` statement selects the employee name and department from the joined tables.

Please note that this query assumes that the employee's name is unique in the `EmployeeTable` and `EmployeeHistoryTable`. If there can be multiple entries for the same employee in the `EmployeeHistoryTable`, you may need to modify the query accordingly to consider the maximum years of experience for each employee.

ICSM Computer

21-May-2023

You should add ID column in all tables.

Add Inner Join to merge all tables data in the new table.

SELECT E.Name, E.Age, E.Department, HE.[Years of Experience] 
FROM Employee E 
INNER JOIN Employee_History HE 
ON HE.Name = E.Name 
WHERE HE.[Years of Experience] >8

forum

Struggling with SQL Query

adhyaa khare

2 Answers

Liked By