CompTIA Data+ (DA0-002)Data MiningMedium
A data analyst is querying a database to find all employees who have a job title containing the word 'Engineer' (case-insensitive) and whose salary is above $80,000. The `Employees` table has columns `EmployeeID`, `JobTitle`, and `Salary`. Which SQL query correctly filters the data to meet these requirements?
- ASELECT * FROM Employees WHERE JobTitle = 'Engineer' OR Salary > 80000;
- BSELECT * FROM Employees WHERE JobTitle ILIKE '%Engineer%' AND Salary > 80000;
- CSELECT * FROM Employees WHERE JobTitle LIKE '%Engineer%' AND Salary > 80000;
- DSELECT * FROM Employees WHERE LOWER(JobTitle) LIKE '%engineer%' AND Salary > 80000;
Show answer & explanationAnswer & explanation
Correct answer: D. SELECT * FROM Employees WHERE LOWER(JobTitle) LIKE '%engineer%' AND Salary > 80000;
To achieve a case-insensitive search for 'Engineer', the `LOWER()` function (or `UPPER()`) must be applied to the `JobTitle` column before using `LIKE`. Option D, `ILIKE`, is a PostgreSQL-specific extension and not universally available in all SQL dialects.
Why the other options are wrong
- A. This query uses `OR`, which would return employees with 'Engineer' in their title even if their salary is not above $80,000, or employees with salary above $80,000 even if their title doesn't contain 'Engineer'.
- B. While `ILIKE` performs a case-insensitive match, it is a PostgreSQL-specific operator and not a standard SQL function, making option B more universally correct for general SQL.
- C. This query is case-sensitive, meaning 'engineer' or 'ENGINEER' would not be matched if 'Engineer' was expected.
SQL Case-Insensitive Search
Performing a search in SQL that ignores the case of characters, often achieved by converting strings to a common case (e.g., lowercase) before comparison.
- Typically uses `LOWER()` or `UPPER()` functions.
- Some SQL dialects offer specific operators like `ILIKE` (PostgreSQL).
- Crucial for robust data matching and filtering.
Memory trick: Filter carefully, get precise results.