Consider the following SQL query to retrieve ecode of employees from EMPLOYEE table, whose address contains 'INDIA':
SELECT ecode FROM EMPLOYEE WHERE _______;
Which of the following is most suitable to complete the query?

Question

Consider the following SQL query to retrieve ecode of employees from EMPLOYEE table, whose address contains 'INDIA': 
SELECT ecode FROM EMPLOYEE WHERE _______; 
Which of the following is most suitable to complete the query?

Accepted Answer

The correct option is (4):
The correct option is **address LIKE '%INDIA%'**. In Structured Query Language (SQL), when you need to search for a string that appears *anywhere* within a column's text, you must employ the `LIKE` operator along with the appropriate wildcard characters. The percent sign (`%`) is the standard SQL wildcard that represents any sequence of zero or more characters. By placing a percent sign both before and after the substring `'INDIA'`, we construct the pattern **`%INDIA%`**, which correctly instructs the database to find all addresses where the word 'INDIA' is surrounded by any characters, or no characters at all, thereby satisfying the requirement that the address *contains* 'INDIA'. This is the standard and correct way to perform a substring search in SQL.

The incorrect option is (1):
The expression **`address LIKE '_INDIA_'`** is incorrect because the underscore wildcard (`_`) in SQL represents exactly **one** single character, not zero or more. Therefore, this pattern would only successfully retrieve addresses that are exactly seven characters long, starting with any single character, followed by the fixed string 'INDIA', and ending with any single character (for example, 'XINDIAY' or '7INDIAS'). This pattern is too restrictive and fails the general requirement of finding 'INDIA' anywhere in addresses of varying lengths, such as 'North INDIA' or just 'INDIA'.

The incorrect option is (2):
The condition **`address = %INDIA%`** is syntactically and functionally incorrect for pattern matching in SQL. The equals operator (`=`) is used solely for an *exact* match against the entire string value in the column. Wildcard characters like `%` have no special meaning when used with the `=` operator. If executed, most SQL databases would attempt to find an address that is literally the string "%INDIA%", which is not the intended outcome of finding an address that merely contains the substring 'INDIA'.

The incorrect option is (3):
The syntax **`address AS '%INDIA\_'`** is grammatically invalid for a comparison or filtering operation within a `WHERE` clause. The keyword `AS` is reserved in SQL for defining an alias (an alternative name) for a column, table, or result set, and it cannot be used to establish a search condition. Furthermore, even if we were to change `AS` to `LIKE`, the trailing underscore (`_`) would still incorrectly restrict the pattern by demanding that the address contain 'INDIA' followed by **exactly one** character before the end of the string.