we have following relations:employees(emp_id, name, dept_id, city, DOJ)salaries (emp_id, salary)1) Find out the names of employees who belong to the same department as John.2) Find out names of employees whose salaries are greater than average salary.3) Retrieve the names of employees who have corresponding entries of the salary.
Question
we have following relations:employees(emp_id, name, dept_id, city, DOJ)salaries (emp_id, salary)1) Find out the names of employees who belong to the same department as John.2) Find out names of employees whose salaries are greater than average salary.3) Retrieve the names of employees who have corresponding entries of the salary.
Solution
- To find out the names of employees who belong to the same department as John, you would first need to find out which department John belongs to. You can do this by running the following SQL query:
SELECT dept_id
FROM employees
WHERE name = 'John'
Let's say that John's dept_id is 5. You would then use this dept_id to find the names of all employees in the same department:
SELECT name
FROM employees
WHERE dept_id = 5
- To find out the names of employees whose salaries are greater than the average salary, you would first need to calculate the average salary. You can do this by running the following SQL query:
SELECT AVG(salary)
FROM salaries
Let's say that the average salary is 50000. You would then use this average salary to find the names of all employees who earn more than this:
SELECT e.name
FROM employees e
JOIN salaries s ON e.emp_id = s.emp_id
WHERE s.salary > 50000
- To retrieve the names of employees who have corresponding entries of the salary, you would need to join the
employeesandsalariestables on theemp_idfield and select the names of the employees:
SELECT e.name
FROM employees e
JOIN salaries s ON e.emp_id = s.emp_id
Similar Questions
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
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
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
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
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.
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.