We have following relations:employees(emp_id, first_name, last_name, email, salary, dept_id)departments(dept_id, dept_name, location_id)(1) Find the employee who is getting second highest salary.(2) Display all the employee name along with department name who are working neitherin ‘Marketing’ Department nor earns more than 75000
Question
We have following relations:employees(emp_id, first_name, last_name, email, salary, dept_id)departments(dept_id, dept_name, location_id)(1) Find the employee who is getting second highest salary.(2) Display all the employee name along with department name who are working neitherin ‘Marketing’ Department nor earns more than 75000
Solution
Sure, here are the SQL queries for your questions:
(1) To find the employee who is getting the second highest salary, you can use the following SQL query:
SELECT first_name, last_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
)
)
This query first finds the maximum salary, then finds the maximum salary that is less than the first maximum. This gives us the second highest salary. Then it selects the employee with this salary.
(2) To display all the employee names along with department names who are working neither in ‘Marketing’ Department nor earns more than 75000, you can use the following SQL query:
SELECT e.first_name, e.last_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name != 'Marketing' AND e.salary <= 75000
This query joins the employees and departments tables on the dept_id field, then selects the employees who are not in the Marketing department and who earn 75000 or less.
Similar Questions
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
For given relation:Employee(eid, ename, address,deptname ,salary)Project(eid, pid, pname, location)(1) Display name and salary of employee who is taking maximum salary.(2) Display highest salary department wise and name of employee who is taking thatsalary.(3) Find details of employee who works on a pid equal to 10
We have following relationEMP(empo,ename,jobtitle,manager_no,hiredate,salary,deptno)DEPT (deptno, dname,location)1) Find employees whose name start with letter A or letter a.2) Find the employees who are working in smith's department.3) Display employees who are getting maximum salary in each department
we have following relationsEMP(empno, ename, jobtitle, manager, hiredate, salary, deptno)DEPT(deptno, dname,location)Answer the following queries .1) Find the Employees who get salary more than Chris salary.2) Display department number along with the number of employees which belongs to thatdepartment number
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.
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.