3.Write a query to display the annual salary of all employees in department_id 20. Display the names in increasing order or salary.

4.Write a query to display the average salary of employees. Label the column as ' Average Salary.

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

See suggestions in response at:

http://www.jiskha.com/display.cgi?id=1304962096

Write a query to display the employee last name, email, phone_number, hire_date, and department_name of all employees who earn a commission.

3. To display the annual salary of all employees in department_id 20, use the following query:

```
SELECT employee_name, salary * 12 AS annual_salary
FROM employees
WHERE department_id = 20
ORDER BY annual_salary ASC;
```

This query retrieves the names and salaries of employees in department_id 20 and multiplies the monthly salary by 12 to calculate the annual salary. The results are then sorted in ascending order of annual salary.

4. To display the average salary of employees, use the following query:

```
SELECT AVG(salary) AS average_salary
FROM employees;
```

This query calculates the average salary of all employees using the AVG() function and labels the column as "Average Salary".

5. To display the details of employees in the "shipping" department who were hired after "01-JAN 1990", use the following query with a join:

```
SELECT e.employee_name, e.hire_date, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'shipping' AND e.hire_date > '01-JAN-1990';
```

This query uses a join between the "employees" and "departments" tables to retrieve the employee name, hire date, and department name. It then applies the required conditions to filter for employees in the "shipping" department hired after "01-JAN-1990".

6. To display all unique department_id's in the employees table, use the following query:

```
SELECT DISTINCT department_id
FROM employees;
```

This query selects the distinct values of the department_id column from the employees table. The DISTINCT keyword ensures that only unique department_id values are returned.

7. To display the details of employees whose names start with 'A' and are in departments 20, 60, or 100, use the following query:

```
SELECT *
FROM employees
WHERE employee_name LIKE 'A%' AND department_id IN (20, 60, 100);
```

This query uses the LIKE operator with the pattern 'A%' to match employee names starting with 'A'. The department_id column is filtered using the IN operator to include departments 20, 60, and 100.

8. To display the employee last name, email, phone_number, hire_date, and department_name of all employees who earn a commission, use the following query:

```
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;
```

This query joins the "employees" and "departments" tables using the department_id column. It retrieves the desired columns and applies a condition to only select employees with a non-null commission_pct.

9. To find the earliest hire_date in each department in the Employees table, use the following query:

```
SELECT department_id, MIN(hire_date) AS earliest_hire_date
FROM employees
GROUP BY department_id;
```

This query groups the employees by department_id and calculates the minimum (earliest) hire_date for each group using the MIN() function. It retrieves both the department_id and earliest_hire_date columns.

10. To display the name, salary, department_name, and job_title of all employees whose salary is greater than $10,000, use the following query:

```
SELECT e.employee_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;
```

This query joins the "employees", "departments", and "jobs" tables using the appropriate foreign keys. It selects the desired columns and applies a condition to only include employees with a salary greater than $10,000.