Objective

Write an SQL query to fetch the names, department names, email addresses, and position levels of employees. The query should filter employees based on the following criteria:

• Employee names start with the letter 'A'.
• Employee email addresses contain the substring '@tech'.
• Employee position levels contain the word 'Senior'.
  The results should be ordered by employee names in ascending order.

Additional information

• Assume the existence of two tables: employees and departments.
• The employees table contains the columns: id, name, department_id, email, and position_level.
• The departments table contains the columns: id, name, and location.
• Join the two tables on the department_id column from the employees table and the id column from the departments table.
• The results should only include columns: name, department, email, and position_level.

Constraints:

• The length of name will be within [1, 100].
• The length of email will be within [5, 100].
• The length of position_level will be within [5, 50].
• The number of rows in the employees and departments tables will not exceed 1000.

SELECT
    e.name,
    d.name AS department,
    e.email,
    e.position_level
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE
    e.name           LIKE 'A%'
    AND e.email      LIKE '%@tech%'
    AND e.position_level LIKE '%Senior%'
ORDER BY e.name;

Explanation

Step 1: Join employees with departments

JOIN departments d ON e.department_id = d.id makes the department name available for selection.

FROM employees e
JOIN departments d ON e.department_id = d.id

Step 2: Apply LIKE filters for name, email, and position level

Three LIKE conditions narrow the result: e.name LIKE 'A%' matches names starting with A; e.email LIKE '%@tech%' matches emails containing @tech; e.position_level LIKE '%Senior%' matches positions containing the word Senior.

WHERE e.name           LIKE 'A%'
  AND e.email          LIKE '%@tech%'
  AND e.position_level LIKE '%Senior%'

Step 3: Order by employee name

ORDER BY e.name presents the qualifying employees in ascending alphabetical order.

ORDER BY e.name;