Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 27
View and examine the following available responses.
Given: All fields and data referenced in the required statement are valid.
Required: Select all fields from the contacts table whose last_name is 'Smith', contact_id is greater than or equal 1000 and contact_id is less than or equal to 2000.
Indicate the SQL query that does not fulfil the requirement.
-
A
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id >= 1000
- AND contact_id <= 2000;
-
B
None of the statements solve the requirement.
-
C
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id >= 1000
- OR contact_id <= 2000;
-
D
All of the statements solve the requirement.
-
E
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id BETWEEN 1000 AND 2000;
Reveal correct answer
Correct answer: C
Explanation
The question is asking which DOES NOT fulfil the requirement. The question is not asking which does fulfil the requirement.
I know this is tricky, but you may run across a question like this on the exam to be sure you are reading the question.
The queries that do the required are:
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id >= 1000
- AND contact_id <= 2000;
or
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id BETWEEN 1000 AND 2000;
The WHERE and AND conditions dominate this query and although there is a guarantee that the contact_id will be greater than or equal to 1000.
There is NO guarantee that it will be less than or equal to 2000 because of the OR clause.
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id >= 1000
- OR contact_id <= 2000;
The problem with this statement is the OR.
If the last_name = 'Smith' and the contact_id is >= 1000, for example 1001, then this would be a desired selection and it would fit both tests for contact_id.
However, if the contact_id were 999 for example, this would fail the test one of being >= 1000, but it would pass the test two of <=2000 and would be populated in the results. With an OR, the contact_id must meet test one OR test two to be included in query results.
The AND test requires the contact_id to pass test one AND test two → be between 999 and 2001; or as the requirement is stated, contact_id must be >= 1000 AND <= 2000.
...997, 998, 999, [desired contact ID list], 2001, 2001, 2003...
https://www.techonthenet.com/oracle/and_or.php
https://www.techonthenet.com/oracle/where.php
https://www.oracletutorial.com/oracle-basics/oracle-between/
A.
This query correctly selects all fields from the contacts table where the last_name is 'Smith' and the contact_id is between 1000 and 2000, fulfilling the requirement.
B.
- Only the following statement does NOT fulfil the requirement:
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id >= 1000
- OR contact_id <= 2000;
C.
This query does not fulfil the requirement as it uses the OR operator instead of the AND operator between the conditions for contact_id. This would result in selecting all fields where the last_name is 'Smith' and either the contact_id is greater than or equal to 1000 or less than or equal to 2000, which is not the desired outcome.
D.
- The following statement does NOT fulfil the requirement:
- SELECT *
- FROM contacts
- WHERE last_name = 'Smith'
- AND contact_id >= 1000
- OR contact_id <= 2000;
E.
This query fulfils the requirement by selecting all fields from the contacts table where the last_name is 'Smith' and the contact_id falls within the range of 1000 to 2000.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
