View the Exhibit and examine the details of PRODUCT_INFORMATION table.
PRODUCT_NAME CATEGORY_ID SUPPLIER_ID
Inkjet C/8/HQ 12
102094
Inkjet C/4 12
102090
LaserPro 600/6/BW 12
102087
LaserPro 1200/8/BW 12
102099
Inkjet B/6 12
102096
Industrial 700/ID 12
102086
Industrial 600/DQ 12
102088
Compact 400/LQ 12
102087
Compact 400/DQ 12
102088
HD 12GB /R 13
102090
HD 10GB /I 13
102071
HD 12GB @7200 /SE 13
102057
HD 18.2GB @10000 /E 13
102078
HD 18.2GB @10000 /I 13
102050
HD 18GB /SE 13
102083
HD 6GB /I 13
102072
HD 8.2GB@5400 13
102093
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

1. A. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
2. B. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
3. C. It would execute and the output would display the desired result.
4. D. It would execute but the output would return no rows.

Correct Answer: D

See the Exhibit and examine the structure of the PROMOTIONS table:

Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

1. A. It generates an error because multiple conditions cannot be specified for the WHEN clause.
2. B. It executes successfully and gives the required result.
3. C. It generates an error because CASE cannot be used with group functions.
4. D. It generates an error because NULL cannot be specified as a return value.

Correct Answer: B
CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn ELSE else_expr]
END

View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion functions?

1. A. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003', 'MON DD YYYY') )
2. B. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
3. C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
4. D. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Correct Answer: CD

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

1. A. SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL
2. B. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;
3. C. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;
4. D. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;
5. E. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Correct Answer: ACE

View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

1. A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
2. B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
3. C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
4. D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"FROM customers;

Correct Answer: A