Create a query to display every employee working in department 10 or 20, whose salary is more than R10000 per annum, his employee number, name, and annual salary. List these employees in alphabetical order according to names. If the names are the same, then sort from highest to lowest annual salary.

Answer 1

To create a SQL query that meets the specified requirements, you can use the following SQL statement. I'll assume that the relevant columns in your employees table are employee_number, name, salary, and department_id. Here’s how the query would look:

SELECT employee_number, name, salary AS annual_salary
FROM employees
WHERE (department_id IN (10, 20))
  AND salary > 10000
ORDER BY name ASC, salary DESC;

Explanation:

1. SELECT statement: This selects the employee_number, name, and salary (renamed to annual_salary).
2. FROM clause: This specifies that the data comes from the employees table.
3. WHERE clause:
   • department_id IN (10, 20): Filters employees to those in departments 10 or 20.
   • salary > 10000: Ensures the salary is over R10000 per annum.
4. ORDER BY clause:
   • First sorts the results by name in ascending order.
   • If there are employees with the same name, it sorts them by salary in descending order.

Make sure to adjust the column and table names if they differ in your database schema.