Q1. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. The statement would execute successfully to produce the required output.
B. The statement would not execute because inline views and outer joins cannot be used together.
C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.
Answer: A
Q2. Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order' FROM product_information
WHERE product_status = 'obsolete'
You received the following error while executing the above query: ERROR:
ORA-01756: quoted string not properly terminated What would you do to execute the query successfully?
A. Enclose the character literal string in the SELECT clause within the double quotation marks.
B. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
C. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.
Answer: C
Q3. Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name" FROM customers
WHERE country_id = 10 UNION
SELECT cust_id CUST_NO, cust_last_name FROM customers
WHERE country_id = 30;
Which ORDER BY clause are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
Answer: A,C,E
Explanation:
Using the ORDER BY Clause in Set Operations
- The ORDER BY clause can appear only once at the end of the compound query.
- Component queries cannot have individual ORDER BY clauses.
- The ORDER BY clause recognizes only the columns of the first SELECT query.
- By default, the first column of the first SELECT query is used to sort the output in an ascending order.
Q4. You want to display 5 percent of the rows from the sales table for products with the lowestAMOUNT_SOLD and also want to include the rows that have the sameAMOUNT_SOLDeven if this causes the output to exceed 5 percent of the rows.
Which query willprovide the required result?
A. SELECT prod_id, cust_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS WITH TIES;
B. SELECT prod_id, cust_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
C. SELECT prod_ id, cust_id, amount_sold FROM sales
ORDER BY araount_sold
FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
D. SELECT prod_id, cust_id, amount_sold FROM sales
ORDER BY amount sold
FETCH FIRST 5 PERCENT ROWS ONLY;
Answer: B
Q5. Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All the constraints can be defined at the column level as well as the table level
Answer: B,D
Q6. Which three statements are true regarding the data types?
A. Only one LONG column can be used per table.
B. ATIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a varchar2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
Answer: A,D,E
Q7. View the Exhibits and examine the structures of the costs and promotions tables?
/1.
/2.
Evaluate the following SQL statement: SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_begin_date)));
What would be the outcome of the above SQL statement?
A. It displays prod IDs in the promo with the lowest cost.
B. It displays prod IDs in the promos with the lowest cost in the same time interval.
C. It displays prod IDs in the promos with the highest cost in the same time interval.
D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.
Answer: D
Q8. Which two statements are true about Data Manipulation Language (DML) statements?
A. AH INSERT INTO. . .VALUES. . statement can add multiple rows per execution to a table.
B. An UPDATE...SET... statement can modify multiple rows based on multiple conditions on a table.
C. A DELETE FROM ..... statement can remove rows based on only a single condition on a table.
D. An INSERT INTO...VALUES..... statement can add a single row based on multiple conditions on a table.
E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
F. An UPDATE...SET.... statement can modify multiple rows based on only a single condition on a table.
Answer: A,C
Q9. Examine thestructure of the BOOKS_TRANSACTIONS table:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
A)
B)
C)
D)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q10. The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
A. It recovers only the table structure.
B. It recovers the table structure, data, and the indexes.
C. It recovers the table structure and data but not the related indexes.
D. It is not possible to recover the table structure, data, or the related indexes.
Answer: D