OCA OCP Oracle Certification Exam Braindumps

4. Examine the structure and data in the PRICE_LIST table:
Name Null? Type
-------------- -------- ----------------
PROD_ID NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD_PRICE
------- ----------
100 $234.55
101 $6,509.75
102 $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?
A.SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
B.SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
C.SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
D.SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
Answer: C

5. View the Exhibit and examine the structure of the CUSTOMERS table.Evaluate the following SQL statement:SQL> SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;
Which statement is true regarding the outcome of the above query?
A.It executes successfully.
B.It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
C.It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
D.It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
Answer: A

6. View the Exhibit and examine the structure of CUSTOMERS and GRADES tables.You need to display names and grades of customers who have the highest credit limit.Which two SQL statements would accomplish the task? (Choose two.)
A.SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
B.SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;
C.SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;
D.SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
Answer: BC