Oracle Database SQL Expert - 1Z0-047 Exam Practice Test
Which three statements are true regarding group functions? (Choose three.)
Correct Answer: A,B,D
Vote an answer
View the Exhibit and examine the data in the CUST_DET table.
You executed the following multitable INSERT statement:
INSERT FIRST
WHEN credit_limit >= 5000 THEN
INTO cust_1 VALUES(cust_id, credit_limit, grade, gender)
WHEN grade = THE
INTO cust_2 VALUES(cust_id, credit_limit, grade, gender)
WHEN gender = THE
INTO cust_3 VALUES(cust_id, credit_limit, grade, gender)
INTO cust_4 VALUES(cust_id, credit_limit, grade, gender)
ELSE
INTO cust_5 VALUES(cust_id, credit_limit, grade, gender)
SELECT * FROM cust_det;
The row will be inserted in________.
You executed the following multitable INSERT statement:
INSERT FIRST
WHEN credit_limit >= 5000 THEN
INTO cust_1 VALUES(cust_id, credit_limit, grade, gender)
WHEN grade = THE
INTO cust_2 VALUES(cust_id, credit_limit, grade, gender)
WHEN gender = THE
INTO cust_3 VALUES(cust_id, credit_limit, grade, gender)
INTO cust_4 VALUES(cust_id, credit_limit, grade, gender)
ELSE
INTO cust_5 VALUES(cust_id, credit_limit, grade, gender)
SELECT * FROM cust_det;
The row will be inserted in________.
Correct Answer: B
Vote an answer
ORD is a private synonym for the OE.ORDERS table.
The user OE issues the following command: DROP SYNONYM ord;
Which statement is true regarding the above SOL statement?
The user OE issues the following command: DROP SYNONYM ord;
Which statement is true regarding the above SOL statement?
Correct Answer: B
Vote an answer
View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement: SELECT category_name ,category_description
FROM categories_tab
You want to display only the rows that have 'harddisks' as part of the string in the
CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)
FROM categories_tab
You want to display only the rows that have 'harddisks' as part of the string in the
CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)
Correct Answer: A,D
Vote an answer
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100
INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?
CREATE SEQUENCE seql START WITH 100
INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?
Correct Answer: D
Vote an answer
View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?
Correct Answer: A
Vote an answer
Evaluate the following expression using meta character for regular expression: '[AAle|ax.r$]'
Which two matches would be returned by this expression? (Choose two.)
Which two matches would be returned by this expression? (Choose two.)
Correct Answer: A,E
Vote an answer
View the Exhibit and examine the description of the EMPLOYEES table. You want to calculate the total remuneration for each employee. Total remuneration is the sum of
the annual salary and the percentage commission earned for a year. Only a few employees earn
commission.
Which SOL statement would you execute to get the desired output?
the annual salary and the percentage commission earned for a year. Only a few employees earn
commission.
Which SOL statement would you execute to get the desired output?
Correct Answer: C
Vote an answer
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following
GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
Correct Answer: D
Vote an answer
Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
Correct Answer: A,B,C
Vote an answer
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
There are some products listed in the PRODUCT_INFORMATION table that have no value in the
LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME
for these products:
SELECT product_name, list_price
FROM product_information WHERE list_price = NULL;
The query returns no rows. What changes would you make in the statement to get the desired result?
There are some products listed in the PRODUCT_INFORMATION table that have no value in the
LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME
for these products:
SELECT product_name, list_price
FROM product_information WHERE list_price = NULL;
The query returns no rows. What changes would you make in the statement to get the desired result?
Correct Answer: A
Vote an answer