Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 6
View and examine the description of the BOOKS_TRANSACTIONS table and exhibit.
Table: BOOKS_TRANSACTIONS
- Name Null?? Type
- TRANSACTION_ID NOT NULL VARCHAR2(6)
- TRANSACTION_TYPE NOT NULL VARCHAR2(3)
- BORROWED_DATE DATE
- BOOK_ID VARCHAR2(6)
- MEMBER_ID VARCHAR2(6)
Examine this partial SQL statement.
Exhibit: 1
- SELECT *
- FROM books_transactions
Identify two WHERE conditions render the same result. (Choose two)
-
A
- WHERE (borrowed_date = SYSDATE
- AND transaction_type = 'RM')
- OR member_id IN ('A101','A102');
-
B
- WHERE borrowed_date = SYSDATE
- AND (transaction_type = 'RM'
- AND member_id = 'A101'
- OR member_id = 'A102');
-
C
- WHERE borrowed_date = SYSDATE
- AND (transaction_type = 'RM'
- OR member_id IN ('A101','A102'));
-
D
- WHERE borrowed_date = SYSDATE
- OR (transaction_type = 'RM'
- AND (member_id = 'A101'
- OR member_id = 'A102'));
-
E
- WHERE borrowed_date = SYSDATE
- AND transaction_type = 'RM'
- OR member_id IN ('A101','A102');
Reveal correct answers
Correct answers: A, E
Explanation
The two WHERE conditions that render the same result are:
1.
- sql
- WHERE borrowed_date = SYSDATE
- AND (transaction_type = 'RM'
- OR member_id IN ('A101', 'A102'));
2.
- sql
- WHERE (borrowed_date = SYSDATE
- AND transaction_type = 'RM')
- OR member_id IN ('A101', 'A102');
Both conditions logically evaluate the same set of records because:
First condition:
borrowed_date = SYSDATEAND (either
transaction_type = 'RM'ormember_id IN ('A101', 'A102'))This means only records where
borrowed_date = SYSDATEare considered, but within those, it allows eithertransaction_type = 'RM'or specificmember_ids.
Second condition:
(borrowed_date = SYSDATE AND transaction_type = 'RM')OR
member_id IN ('A101', 'A102')This means:
Either (
borrowed_date = SYSDATEandtransaction_type = 'RM') ORAny row where
member_id IN ('A101', 'A102'), regardless ofborrowed_date.
This effectively produces the same result as the first condition.
Why the Other Options Are Incorrect:
- WHERE borrowed_date = SYSDATE
- AND (transaction_type = 'RM'
- OR member_id IN ('A101','A102'));
This condition allows all records where
borrowed_date = SYSDATE, even if they don’t matchtransaction_type = 'RM'ormember_idconditions.It’s too broad and includes extra rows.
- WHERE borrowed_date = SYSDATE
- OR (transaction_type = 'RM'
- AND (member_id = 'A101'
- OR member_id = 'A102'));
Due to operator precedence, this condition is evaluated as:
- sql
- (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101', 'A102')
Which is the same as the second correct condition.
However, because it lacks parentheses, its meaning is ambiguous and should be avoided in favor of clearer syntax.
- WHERE borrowed_date = SYSDATE
- AND (transaction_type = 'RM'
- AND member_id = 'A101'
- OR member_id = 'A102');
This is interpreted as:
- sql
- (borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101') OR member_id = 'A102'
This allows all records where
member_id = 'A102', regardless ofborrowed_date, making it incorrect.
A. This WHERE condition checks for rows where either the borrowed date is the current date and the transaction type is 'RM', or the member ID is either 'A101' or 'A102'. This condition will return results where either of the two conditions is met, making it equivalent to the first condition.
B. This WHERE condition checks for rows where the borrowed date is the current date, the transaction type is 'RM', and the member ID is either 'A101' or 'A102'. This condition will only return results where all three conditions are met, making it different from the other conditions.
C. This WHERE condition checks for rows where the borrowed date is the current date and the transaction type is 'RM', or the member ID is either 'A101' or 'A102'. This condition will return results where either of the two conditions is met.
D. This WHERE condition checks for rows where the borrowed date is the current date, or the transaction type is 'RM' and the member ID is either 'A101' or 'A102'. This condition will return results where either the borrowed date is the current date, or both the transaction type is 'RM' and the member ID is either 'A101' or 'A102'.
E. This WHERE condition checks for rows where the borrowed date is the current date and the transaction type is 'RM', or the member ID is either 'A101' or 'A102'. This condition will return results where either of the two conditions is met, making it equivalent to the first condition.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
