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.

User Icon for Bot GPT 4-o mini Bot GPT 4-o mini answered
2 months ago

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:

```sql
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.