Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 91:

  Which two statements are true about sequences created in a single instance database? (Choose two.)

  A. The numbers generated by a sequence can be used only for one table.
  B. DELETE would remove a sequence from the database.
  C. CURRVAL is used to refer to the last sequence number that has been generated.
  D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
  E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
  C. CURRVAL is used to refer to the last sequence number that has been generated.
  D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.

• Question 92:

  View the Exhibit and examine the structure of the ORDERS table.

  You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101 Which query would give you the desired output?

  

  A. SELECT order id, order_date FROM ordersWHERE order_date > ALL (SELECT MAX(order_date) FROM orders) ANDCustomer_id = 101;
  B. SELECT order id, order_date FROM ordersWHERE order_date > ANY (SELECT order_dateFROM ordersWHERE customer_id = 101);
  C. SELECT order _id, order_date FROM ordersWHERE order_date > ALL (SELECT order_dateFROM ordersWHERE customer_id = 101);
  D. SELECT order id, order_date FROM ordersWHERE order_date IN (SELECT order_dateFROM ordersWHERE customer id = 101);
  C. SELECT order _id, order_date FROM ordersWHERE order_date > ALL (SELECT order_dateFROM ordersWHERE customer_id = 101);

• Question 93:

  View the Exhibit and examine the description of the ORDERS table.

  Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

  

  A. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY")
  B. WHERE TO_CHAR(order_date,'MON DD YYYY} = 'JAN 20 2003'
  C. WHERE order_date > T0_CHAR(ADD_M0NTHS(SYSDATE,6),'M0N DD YYYY')
  D. WHERE order_date IN (T0_DATE('0ct 21 2003','Mon DD YYYY'), T0_CHAR('N0V 21 2003','Mon DD YYYY'))
  A. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY")
  B. WHERE TO_CHAR(order_date,'MON DD YYYY} = 'JAN 20 2003'

• Question 94:

  View the Exhibit and examine the structure of the EMPLOYEES table.

  You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'.

  Which SQL statement would give you the desired result?

  

  A. SELECTfirst_name, salary FROM employeesWHERE (manager_id, salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name 'Neena';
  B. SELECT first_name, salary FROM employeesWHERE (manager_id, salary) >= (SELECT manager_id, salaryFROM employeesWHERE first_name = 'Neena')AND first_name 'Neena';
  C. SELECT first_name, salary FROM employeesWHERE (manager_id, salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name 'Neena';
  D. SELECT first_name, salary FROM employeesWHERE (manager_id = (SELECT manager_idFROM employeesWHERE first_name = 'Neena')AND salary >= (SELECT salaryFROM employeesWHERE first_name = 'Neena'))AND first name 'Neena';
  D. SELECT first_name, salary FROM employeesWHERE (manager_id = (SELECT manager_idFROM employeesWHERE first_name = 'Neena')AND salary >= (SELECT salaryFROM employeesWHERE first_name = 'Neena'))AND first name 'Neena';

• Question 95:

  View the Exhibit and examine the description of the PRODUCT_INFORMATION table.

  SELECT product_name, list_price, min_price, list_price - min_price Difference FROM product_information

  

  Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE? (Choose all that apply.)

  A. ORDER BY 4
  B. ORDER BY MIN_PRICE
  C. ORDER BY DIFFERENCE
  D. ORDER BY LIST_PRICE
  E. ORDER BY LIST_PRICE MIN_PRICE
  A. ORDER BY 4
  C. ORDER BY DIFFERENCE
  E. ORDER BY LIST_PRICE MIN_PRICE

• Question 96:

  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.)

  A. WHERE REGEXP_LIKE (category_description, 'hard+.s');
  B. WHERE REGEXP_LIKE (category_description, `^H|hard+.s');
  C. WHERE REGEXP_LIKE (category_description, '^H|hard+.s$');
  D. WHERE REGEXP_LIKE (category_description, '[^Hlhard+.s]');
  A. WHERE REGEXP_LIKE (category_description, 'hard+.s');
  B. WHERE REGEXP_LIKE (category_description, `^H|hard+.s');

• Question 97:

  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.
  A. The statement would execute successfully to produce the required output.

• Question 98:

  User OE, the owner of the ORDERS table, issues the following command:

  GRANT SELECT .INSERT ON orders TO hr WITH GRANT OPTION;

  The user HR issues the following command:

  GRANT SELECT ON oe. orders TO scott;

  Then, OE issues the following command:

  REVOKE ALL ON orders FROM hr;

  Which statement is correct?

  A. The user SCOTT loses the privilege to select rows from OE.ORDERS.
  B. The user SCOTT retains the privilege to select rows from OE.ORDERS.
  C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.
  D. The REVOKE statement generates an error because the ALL keyword cannot be used for privileges that have been granted using WITH GRANT OPTION.
  A. The user SCOTT loses the privilege to select rows from OE.ORDERS.

• Question 99:

  A subquery is called a single-row subquery when

  A. the inner query returns a single value to the main query
  B. the inner query uses an aggregate function and returns one or more values
  C. there is only one inner query in the main query and the inner query returns one or more values
  D. the inner query returns one or more values and the main query returns a single value as output
  A. the inner query returns a single value to the main query

• Question 100:

  Which statements are true? (Choose all that apply.)

  A. The data dictionary is created and maintained by the database administrator.
  B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
  C. The usernames of all the users including the database administrators are stored in the data dictionary.
  D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
  E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
  F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary
  C. The usernames of all the users including the database administrators are stored in the data dictionary.
  D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
  F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary