Posts

Practice SQL Questions 31-51HR Schema with Solutions

31. From the following tables, write a  SQL query to find those employees whose salaries exceed 50% of their department's total salary bill. Return first name, last name. with calc as (select department_id, sum(salary)/2 as half_total_salary from employees   group by department_id) select e.first_name,e.last_name from employees e join calc c on e.department_id=c.department_id and e.salary>c.half_total_salary; 32. From the following tables, write a  SQL query to find those employees who are managers. Return all the fields of employees table. select * from employees where employee_id in ( select distinct manager_id from employees where manager_id is not null); 33. From the following table, write a  SQL query to find those employees who manage a department. Return all the fields of employees table. select * from employees where employee_id in (select distinct manager_id from employees); 34. From the following table, write a  SQL query to search for employee...
Post a Comment
Read more

Practice SQL Questions 16-30 HR Schema with Solutions

16. From the following table, write a SQL query to find those employees whosedepartment is located at ‘Toronto’. Return first name, last name, employee ID, job ID. select first_name,last_name,employee_id,job_id from employees  where department_id = (select department_id from departments d inner join locations l on d.location_id=l.location_id where city='Toronto'); 17. From the following table, write a SQL query to find those employees whose salary is lower than that of employees whose job title is ‘MK_MAN’. Return employee ID, first name, last name, job ID. select employee_id,first_name,last_name,job_id from employees where salary < (select salary from employees where job_id = 'MK_MAN'); 18. From the following table, write a SQL query to find those employees whose salary is lower than that of employees whose job title is "MK_MAN". Exclude employees ofJob title ‘MK_MAN’. Return employee ID, first name, last name, job ID. select employee_id,first_name,last_na...
Post a Comment
Read more

Practice SQL Questions 1-15 HR Schema with Solutions

1.From the following table, write a SQL query to find those employees who receive a higher salary than the employee with ID 163. Return first name, last name. SELECT E.* FROM EMPLOYEES E WHERE SALARY > (SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 163); 2. From the following table, write a SQL query to find out which employees have the same designation as the employee whose ID is 169. Return first name, last name, department ID and job ID. SELECT first_name,last_name,salary,department_id,job_id FROM EMPLOYEES  WHERE JOB_ID = (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 169); 3. From the following table, write a SQL query to find those employees whose salary matches the lowest salary of any of the departments. Return first name, last name and department ID. SELECT FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID FROM EMPLOYEES WHERE SALARY IN  (SELECT MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID); 4. From the following table, write a SQL query to find those employees ...
Post a Comment
Read more