Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 111:

  Which two statements are true regarding roles? (Choose two.)

  A. A role can be granted to itself.
  B. A role can be granted to PUBLIC.
  C. A user can be granted only one role at any point of time.
  D. The REVOKE command can be used to remove privileges but not roles from other users.
  E. Roles are named groups of related privileges that can be granted to users or other roles.
  B. A role can be granted to PUBLIC.
  E. Roles are named groups of related privileges that can be granted to users or other roles.

• Question 112:

  View the Exhibit and examine the data in EMP and DEPT tables.

  In the DEPT table, DEPTNO is the PRIMARY KEY.

  In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.

  What would be the outcome of the following statements executed in the given sequence?

  DROP TABLE emp;

  FLASHBACK TABLE emp TO BEFORE DROP;

  INSERT INTO emp VALUES (2.COTT 10);

  INSERT INTO emp VALUES (3,ING 55);

  

  A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back.
  B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
  C. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back.
  D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
  D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

• Question 113:

  Evaluate the following SQL statement:

  ALTER TABLE hr.emp SET UNUSED (mgr_id);

  Which statement is true regarding the effect of the above SQL statement?

  A. Any synonym existing on the EMP table would have to be re-created.
  B. Any constraints defined on the MGR_ID column would be removed by the above command.
  C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
  D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed.
  B. Any constraints defined on the MGR_ID column would be removed by the above command.

• Question 114:

  You need to create a table for a banking application with the following considerations:

  1) You want a column in the table to store the duration of the credit period.

  2) The data in the column should be stored in a format such that it can be easily added and subtracted with

  3) date type data without using the conversion functions.

  4) The maximum period of the credit provision in the application is 30 days.

  5) The interest has to be calculated for the number of days an individual has taken a credit for.

  Which data type would you use for such a column in the table?

  A. INTERVAL YEAR TO MONTH
  B. INTERVAL DAY TO SECOND
  C. TIMESTAMP WITH TIME ZONE
  D. TIMESTAMP WITH LOCAL TIME ZONE
  B. INTERVAL DAY TO SECOND

• Question 115:

  Evaluate the following DELETE statement:

  DELETE FROM orders;

  There are no other uncommitted transactions on the ORDERS table.

  Which statement is true about the DELETE statement?

  A. It removes all the rows in the table and allows ROLLBACK
  B. It would not remove the rows if the table has a primary key.
  C. It removes all the rows as well as the structure of the table.
  D. It removes all the rows in the table and does not allow ROLLBACK
  A. It removes all the rows in the table and allows ROLLBACK

• Question 116:

  Which two statements are true? (Choose two.)

  A. The USER_SYNONYMS view can provide information about private synonyms.
  B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
  C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
  D. The USER_OBJECTS view can provide information about the tables and views created by the user only.
  E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
  A. The USER_SYNONYMS view can provide information about private synonyms.
  D. The USER_OBJECTS view can provide information about the tables and views created by the user only.

• Question 117:

  View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

  

  A. displaying the total order value for sales representatives 161 and 163
  B. displaying the order total for sales representative 161 in the year 1999
  C. displaying the number of orders that have order mode online and order date in 1999
  D. displaying the number of orders whose order total is more than the average order total for all online orders
  D. displaying the number of orders whose order total is more than the average order total for all online orders

• Question 118:

  Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

  A. The HAVING clause conditions can have aggregate functions.
  B. The HAVING clause conditions can use aliases for the columns.
  C. WHERE and HAVING clauses cannot be used together in a SQL statement.
  D. The WHERE clause is used to exclude rows before the grouping of data.
  E. The HAVING clause is used to exclude one or more aggregated results after grouping data.
  A. The HAVING clause conditions can have aggregate functions.
  D. The WHERE clause is used to exclude rows before the grouping of data.
  E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

• Question 119:

  Evaluate the SQL statements:

  CREATE TABLE new_order

  (orderno NUMBER(4),

  booking_date TIMESTAMP WITH LOCAL TIME ZONE);

  The database is located in San Francisco where the time zone is -8:00.

  The user is located in New York where the time zone is -5:00.

  A New York user inserts the following record:

  INSERT INTO new_order

  VALUES(1, TIMESTAMP 007-05-10 6:00:00 -5:00");

  Which statement is true?

  A. When the New York user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
  B. When the New York user selects the row, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'.
  C. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
  D. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000 -8:00'
  C. When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'

• Question 120:

  View the Exhibit and examine the structure of the CUSTOMERS table. CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers.

  What is the error in the following MERGE statement?

  MERGE INTO customers c USING customer_vu cv ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN UPDATE SET

  A. customer_id = cv.customer_id,
  B. cust_name = cv.cust_name,
  C. cust_email = cv.cust_email,
  D. income_level = cv. Income_level WHEN NOT MATCHED THEN INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level) WHERE cv. Income_level >100000;
  E. The CUSTOMERJD column cannot be updated.
  F. The INTO clause is misplaced in the command.
  G. The WHERE clause cannot be used with INSERT.
  H. CUSTOMER VU cannot be used as a data source
  A. customer_id = cv.customer_id,