Advanced SQL Filtering: Combining AND and OR Conditions

This challenge tests your ability to construct complex SQL queries that filter data based on multiple conditions using both AND and OR logical operators. Effectively combining these operators is crucial for retrieving specific subsets of data from relational databases, enabling targeted analysis and reporting.

Problem Description

You are tasked with writing a SQL query that filters a table named employees based on a combination of AND and OR conditions. The employees table has the following columns: employee_id (INT, primary key), name (VARCHAR), department (VARCHAR), salary (DECIMAL), and hire_date (DATE).

The query should return all employees who meet at least one of the following criteria:

1. Employees in the 'Sales' department and with a salary greater than $60,000.
2. Employees in the 'Marketing' department or employees hired before January 1, 2023.

Your SQL query must accurately reflect this combined logic using the appropriate AND and OR operators. The query should be efficient and avoid unnecessary complexity.

Key Requirements:

• The query must use both AND and OR operators.
• The query must filter based on multiple columns (department, salary, hire_date).
• The query must return only the rows that satisfy the specified conditions.
• The query must be syntactically correct and executable against a standard SQL database.

Expected Behavior:

The query should return a result set containing only those employees who meet at least one of the specified conditions. If no employees meet the criteria, the query should return an empty result set.

Edge Cases to Consider:

• Empty employees table: The query should return an empty result set.
• Null values in relevant columns (e.g., salary): Consider how null values should be handled in the comparison operations. The problem assumes that null values should not be included in the results unless explicitly specified in the conditions.
• Dates: Ensure correct date comparisons are performed.

Examples

Example 1:

Input:
employees table:
employee_id | name | department | salary | hire_date
------------|------|------------|--------|------------
1           | Alice| Sales      | 70000  | 2022-01-15
2           | Bob   | Marketing  | 50000  | 2023-03-10
3           | Carol | Sales      | 55000  | 2022-12-01
4           | David | IT         | 80000  | 2021-11-20
5           | Eve   | Marketing  | 65000  | 2022-06-05

Output:
employee_id | name | department | salary | hire_date
------------|------|------------|--------|------------
1           | Alice| Sales      | 70000  | 2022-01-15
2           | Bob   | Marketing  | 50000  | 2023-03-10
5           | Eve   | Marketing  | 65000  | 2022-06-05

Explanation: Alice satisfies the first condition (Sales department and salary > 60000). Bob satisfies the second condition (Marketing department). Eve satisfies the second condition (Marketing department).

Example 2:

Input:
employees table:
employee_id | name | department | salary | hire_date
------------|------|------------|--------|------------
1           | Alice| Sales      | 50000  | 2023-01-01
2           | Bob   | Marketing  | 40000  | 2024-01-01
3           | Carol | IT         | 70000  | 2022-01-01

Output:
employee_id | name | department | salary | hire_date
------------|------|------------|--------|------------
2           | Bob   | Marketing  | 40000  | 2024-01-01

Explanation: Bob satisfies the second condition (Marketing department and hire_date before 2023-01-01).

Constraints

• The employees table always exists.
• The table structure is as described above.
• The SQL dialect should be compatible with standard SQL (e.g., PostgreSQL, MySQL, SQL Server).
• The query should be reasonably efficient; avoid full table scans if possible.
• The salary values are always non-negative.

Notes

• Consider using parentheses to explicitly define the order of operations for the AND and OR conditions. This improves readability and ensures the logic is interpreted correctly.
• Think carefully about how the AND and OR operators interact. AND has higher precedence than OR, but parentheses can override this.
• The problem focuses on the logical combination of conditions, not on performance optimization. However, writing a clear and efficient query is still encouraged.
• The date comparison should be based on the year, month, and day.