Examine the structure and data in the PRICE_LIST table:

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

No-user-defined locks are used in your database.
Which three are true about Transaction Control Language (TCL)?

• A. COMMIT ends the transaction and makes all its changes permanent.
• B. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.
• C. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.
• D. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks and erases its savepoints.
• E. ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction.
• F. COMMIT erases all the transaction's savepoints and releases its locks.

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

• A. The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
• B. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
• C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• D. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

• A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
• B. During executing, the oracle server may read data from storage if the required data is not already in memory.
• C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
• D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

View the exhibit and examine the structure of ORDERSand CUSTOMERStables.

Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

• A. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) VALUES (1,'10-mar-2007',
  'direct',(SELECT customer_id FROM customers
  WHERE cust_last_name='Roberts' AND
  Credit_limit=600), 1000);
• B. INSERT INTO orders
  VALUES (1,'10-mar-2007', 'direct',
  (SELECT customer_id
  FROM customers
  WHERE cust_last_name='Roberts' AND
  credit_limit=600), 1000);
• C. INSERT INTO orders (order_id.order_date.order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit _limit=600) .order_total) VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);
• D. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total) VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);