5.Write a query to display the details of employees in the shipping' department who were hired after '01-JAN 1990' note that the query must use a join.
6.Write a query to display all unique department_id's in the employees table.
7.Write a query to display the detail of employees whose names start with 'A' and are in any one of the following departments; 20, 60, 100.
8.Write a query to display the employee last name, email, phone_number, hire_date, and department_name of all employees who earn a commission.
9.Write a query to find the earliest hire_date in each department in the Employees table.
10.Write a query to display the name, salary, department_name and job_title of all employees whose salary is greater than $10.000
5. To display the details of employees in the 'shipping' department who were hired after '01-JAN 1990' using a join, you can use the following query:
```sql
SELECT e.*
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'shipping'
AND e.hire_date > TO_DATE('01-JAN-1990', 'DD-MON-YYYY');
```
Explanation:
- The query uses the `JOIN` keyword to combine the `employees` and `departments` tables based on the `department_id` column.
- The `WHERE` clause filters the results to include only employees in the 'shipping' department and those hired after '01-JAN-1990'.
6. To display all unique department_id's in the employees table, you can use the following query:
```sql
SELECT DISTINCT department_id
FROM employees;
```
Explanation:
- The `SELECT` statement selects the `department_id` column from the `employees` table.
- The `DISTINCT` keyword ensures that only unique department_id's are returned.
7. To display the details of employees whose names start with 'A' and are in any one of the following departments (20, 60, 100), you can use the following query:
```sql
SELECT *
FROM employees
WHERE (department_id IN (20, 60, 100))
AND (first_name LIKE 'A%');
```
Explanation:
- The `SELECT` statement selects all columns from the `employees` table.
- The `WHERE` clause filters the results to include only employees whose names start with 'A' and are in any one of the specified departments.
8. To display the employee last name, email, phone_number, hire_date, and department_name of all employees who earn a commission, you can use the following query:
```sql
SELECT e.last_name, e.email, e.phone_number, e.hire_date, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.commission_pct IS NOT NULL;
```
Explanation:
- The query uses the `JOIN` keyword to combine the `employees` and `departments` tables based on the `department_id` column.
- The `SELECT` statement selects the specified columns from the combined tables.
- The `WHERE` clause filters the results to include only employees who earn a commission (where `commission_pct` is not null).
9. To find the earliest hire_date in each department in the Employees table, you can use the following query:
```sql
SELECT department_id, MIN(hire_date) AS earliest_hire_date
FROM employees
GROUP BY department_id;
```
Explanation:
- The `SELECT` statement selects the `department_id` column and the minimum (`MIN`) hire_date from the `employees` table.
- The `GROUP BY` clause groups the results by department_id to get the earliest hire_date for each department.
10. To display the name, salary, department_name, and job_title of all employees whose salary is greater than $10,000, you can use the following query:
```sql
SELECT e.first_name || ' ' || e.last_name AS name, e.salary, d.department_name, j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id
WHERE e.salary > 10000;
```
Explanation:
- The query uses the `JOIN` keyword to combine the `employees`, `departments`, and `jobs` tables based on the corresponding columns.
- The `SELECT` statement selects the concatenated `first_name` and `last_name` as the name, along with the salary, department_name, and job_title columns.
- The `WHERE` clause filters the results to include only employees whose salary is greater than $10,000.