SQL Employee Table Assignment Guide
Uploaded by
2002bhoopesh2SQL Employee Table Assignment Guide
Uploaded by
2002bhoopesh2Common questions
Powered by AITo find all employees working in the 'IT' department, you would use the query: `SELECT * FROM Employee WHERE Department = 'IT';` This query retrieves all columns for employees whose department field is 'IT' .
You can retrieve a list of unique departments by using the query: `SELECT DISTINCT Department FROM Employee;`. This will give you the following list of departments: IT, HR, Finance, and Marketing .
The query `SELECT FirstName, LastName, Salary FROM Employee WHERE HireDate > '2020-01-01';` lists names and salaries of employees hired after this date. Alice Johnson, Sophia Lee, Emma Brown, and Noah Thompson meet these conditions .
Adding a new employee with a salary higher than any current would increase the average salary. To calculate the new average, sum the existing salaries, add the new salary, and divide by the new employee count. For example, adding an employee with a 9000 salary, the average becomes ((6290 * 10) + 9000) / 11 = 6472.73 .
You can order employees by hiring date from newest to oldest with the query: `SELECT * FROM Employee ORDER BY HireDate DESC;`. This ordering shows the latest hiring was Noah Thompson and the earliest was Ethan Davis. This analysis could reveal hiring trends such as spikes or lulls in hiring activity over time .
Based on the employee table, the departments and their corresponding employee counts are: IT with 3 employees, HR with 2 employees, Finance with 3 employees, and Marketing with 2 employees. None of the departments have more than 3 employees, so no department would be included in a list that only considers departments with more than 3 employees .
Use the query: `SELECT * FROM Employee WHERE FirstName LIKE 'A%';`. This SQL operation filters employees whose first name starts with the letter 'A'. In this dataset, only Alice Johnson matches the criterion .
Employees with a salary greater than 6000 are Sophia Lee, Noah Thompson, Liam Patel, and Ethan Davis. This indicates that high salaries are more common in the Finance and IT departments .
To determine the average salary of employees, use the SQL query: `SELECT AVG(Salary) FROM Employee;`. This query calculates the average of all salary values. The average salary calculated from the provided data is 6290 .
To list employees with salaries between 4000 and 7000, use the query: `SELECT * FROM Employee WHERE Salary BETWEEN 4000 AND 7000;`. This query will include Alice Johnson, Mark Rivera, Daniel Kim, Emma Brown, Olivia Garcia, and Ava Martinez .