Question

PostgreSQL

1. Write a query to join two tables employees and departments to display the department name, first_name and last_name, hire date, and salary for all managers who have more than 15 years of experience.

2. Write a query to join the employees and departments table to find the name of the employee including the name and last name, department ID and department name.

3. Write a SQL query to join three tables of employees, departments, and locations to find the names, including first and last name, job title, department name and ID, of employees working in London.

4. Write a query to concatenate the two tables of employees and yourself to find the employee id, last_name as Employee, and their manager_id and last name as Manager.

5. Write a query to connect to the employee table and to yourself to find the name, including first_name and last_name, and the hiring date for those employees who were hired after employee Jones.

6. Write a query to combine the two employee and department tables to get the department name and the number of employees working in each department.

7. Write a query to find employee ID, title and number of days he worked, who worked in department with ID 90.

8. Write a query to combine the two employee and department tables to display the department ID, department name, and manager name.

Answer 1

1. select dep.department_name,emp.first_name,emp.last_name,emp.hire_date,emp.salary from employees emp join departments dep on emp.employee_id=dep.manager_id where (DATEDIFF(now(),hire_date))/365>15;

2. select (emp.first_name,emp.last_name) as "Name",dep.department_id,dep.department_name from employees emp join departments dep on emp.employee_id=dep.manager_id;

3. select (emp.first_name,emp.last_name) as "Name",emp.job_id,dep.department_id,dep.department_name from employees emp join departments dep on emp.employee_id=dep.manager_id join locations loc using (location_id) where loc.city='LONDON';

4. select emp.employee_id as "Employee_id" , emp.last_name as "Employee", man.employee_id as "Manager ID", man.last_name as "Manager" from employees emp JOIN employees man on emp.employee_id=man.manager_id;

5. select (emp.first_name,emp.last_name) as "Name",emp.hire_date from employees emp join employees davies ON (davies.last_name = 'Jones') where davies.hire_date < emp.hire_date;

6. select department_name as 'Department Name', COUNT(*) as 'No of Employees' from departments JOIN employees ON employees.department_id = departments.department_id GROUP BY departments.department_id, department_name ORDER BY department_name;

7. select employee_id, job_title, (end_date - start_date) as 'No. of Days' FROM job_history natural join jobs WHERE department_id=90;

8. select dep.department_id, dep.department_name, dep.manager_id, emp.first_name from departments dep join employees emp ON (dep..manager_id = emp.employee_id);