Question
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..Create this for MYSQL
Answers
GPT-4o mini
To create a SQL query that satisfies your requirements, you would need to select the relevant fields from the employee table, apply the conditions for department and salary, and then sort the results accordingly. Assuming your table is named `employees` and the relevant fields are `employee_number`, `name`, `salary`, and `department_id`, the query would look something like this:
```sql
SELECT employee_number, name, salary
FROM employees
WHERE department_id IN (10, 20)
AND salary > 10000
ORDER BY name ASC, salary DESC;
```
### Explanation:
- **SELECT**: This part defines which columns you want to retrieve — employee number, name, and salary.
- **FROM**: Specifies the table to query data from (`employees`).
- **WHERE**: This clause is used to filter the results. It checks that the `department_id` is either 10 or 20 and that the `salary` is greater than R10000.
- **ORDER BY**: This clause sorts the results first by `name` in ascending order (`ASC`). If there are employees with the same name, it then sorts by `salary` in descending order (`DESC`).
Make sure to adjust the table and column names as per your actual database schema if they differ.
```sql
SELECT employee_number, name, salary
FROM employees
WHERE department_id IN (10, 20)
AND salary > 10000
ORDER BY name ASC, salary DESC;
```
### Explanation:
- **SELECT**: This part defines which columns you want to retrieve — employee number, name, and salary.
- **FROM**: Specifies the table to query data from (`employees`).
- **WHERE**: This clause is used to filter the results. It checks that the `department_id` is either 10 or 20 and that the `salary` is greater than R10000.
- **ORDER BY**: This clause sorts the results first by `name` in ascending order (`ASC`). If there are employees with the same name, it then sorts by `salary` in descending order (`DESC`).
Make sure to adjust the table and column names as per your actual database schema if they differ.
Related Questions
3.Write a query to display the annual salary of all employees in department_id 20. Display the names...
5.Write a query to display the details of employees in the shipping' department who were hired after...
Create a query to display every employee working in department 10 or 20, whose salary is more than R...
Year of Experience Salary
1 $33,300
2 $33,450
4 $33,750
6 $34,050
The table shows the salar...