Write a query to display the ename of employees working in 'HR' department and who draw an annual salary greater than 1000000. [Consider employees who work in multiple departments too]. Display the records sorted in ascending order based on employee ename.Note:1. Display all the ename of employees working in 'HR' department and having annual salary greater than 1000000.2. Use Joins. Tables involved are employee, works and department3. Order by is mandatory.
Question
Write a query to display the ename of employees working in 'HR' department and who draw an annual salary greater than 1000000. [Consider employees who work in multiple departments too]. Display the records sorted in ascending order based on employee ename.Note:1. Display all the ename of employees working in 'HR' department and having annual salary greater than 1000000.2. Use Joins. Tables involved are employee, works and department3. Order by is mandatory.
Solution
Here is the SQL query you asked for:
SELECT employee.ename
FROM employee
JOIN works ON employee.emp_id = works.emp_id
JOIN department ON works.dept_id = department.dept_id
WHERE department.dept_name = 'HR' AND employee.annual_salary > 1000000
ORDER BY employee.ename ASC;
Here's the step by step explanation:
-
SELECT employee.ename: This line is selecting the employee names from the employee table. -
FROM employee: This line is specifying the employee table as the main table we are working from. -
JOIN works ON employee.emp_id = works.emp_id: This line is joining the works table to the employee table based on the employee ID. -
JOIN department ON works.dept_id = department.dept_id: This line is joining the department table to the works table based on the department ID. -
WHERE department.dept_name = 'HR' AND employee.annual_salary > 1000000: This line is filtering the results to only include employees who work in the HR department and have an annual salary greater than 1000000. -
ORDER BY employee.ename ASC: This line is ordering the results in ascending order based on the employee's name.
Similar Questions
Write a query to display the ename of employee who draw the highest salary. In case of multiple records, display the records sorted in ascending order based on ename.
Write a query to find employees who have the highest salary in each of the departments.Input format :The input tables are already prepopulated, as given in the problem statement.Output format :The output display the name of each department along with the name of the employee who has the highest salary within that department and their respective salary. Order the output by department name as shown below.Refer to the sample output for the column headers.
we have following relations:employees(employee_id,first_name,last_name,email,salary,department_id)departments(department_id,department_name,location_id)(1) Find the employees who have the highest salary.(2) Display all the employee name along with department name who are working neitherin ‘HR’ Department nor earns more than 50000.2
Consider the following EmployeeDetails table.You want to display the details of employees whose salary is more than the lowest salary of department having ID 10.To perform this task, which of the following query you can use the
we have following relations:employees(emp_id,name,dept_id)salaries (emp_id,salary)1) Find out the names of employees who belong to the same department as Mark andsalary greater than 50,000.2) Find out names of employees whose salaries are greater than 50,000.3) Retrieve the names of employees who have higher salary then John
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.