Oracle 1Z0-047 Online Practice Questions and Exam Preparation

Oracle 1Z0-047 Online Questions & Answers

• Question 101:

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

  

  Evaluate the following SQL statement:

  SELECT first_name, employee_id, NEXr_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees;

  The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees.

  The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.

  Which statement is true regarding this query?

  A. The query would execute to give the desired output.
  B. The query would not execute because date functions cannot be nested.
  C. The query would execute but the output would give review dates that are Sundays.
  D. The query would not execute because the NEXT_DAY function accepts a string as argument.
  C. The query would execute but the output would give review dates that are Sundays.

• Question 102:

  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?

  A. 1
  B. 10
  C. 100
  D. an error
  A. 1

• Question 103:

  Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

  A. The outer query stops evaluating the result set of the inner query when the first value is found.
  B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
  C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
  D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
  A. The outer query stops evaluating the result set of the inner query when the first value is found.
  C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

• Question 104:

  View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.

  

  Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?

  SELECT location_id, city FROM locations

  SELECT location_id, city FROM locations JOIN departments USING(location_id);

  A. UNION
  B. MINUS
  C. INTERSECT
  D. UNION ALL
  C. INTERSECT

• Question 105:

  View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

  You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

  Which SOL statement would you execute to accomplish the task?

  

  A. UPDATE deptdSET city = ANY (SELECT city FROM locations I);
  B. UPDATE deptdSET city = (SELECT cityFROM locations I)WHERE d.location_id = l.location_id;
  C. UPDATE dept dSET city = (SELECT cityFROM locations IWHERE d.location_id = l.location_id);
  D. UPDATE dept dSET city = ALL (SELECT cityFROM locations IWHERE d.location_id =location_id);
  C. UPDATE dept dSET city = (SELECT cityFROM locations IWHERE d.location_id = l.location_id);

• Question 106:

  Which statements are correct regarding indexes? (Choose all that apply.)

  A. When a table is dropped, the corresponding indexes are automatically dropped.
  B. For each DML operation performed, the corresponding indexes are automatically updated.
  C. Indexes should be created on columns that are frequently referenced as part of an expression.
  D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
  A. When a table is dropped, the corresponding indexes are automatically dropped.
  B. For each DML operation performed, the corresponding indexes are automatically updated.
  D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

• Question 107:

  Which CREATE TABLE statement is valid?

  A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date date NOT NULL);
  B. CREATE TABLE ord_details(ord_no NUMBER(2) UNIQUE, NOT NULL,item_no NUMBER(3),ord_date date DEFAULT SYSDATE NOT NULL);
  C. CREATE TABLE ord_details (ord_no NUMBER(2) , item_no NUMBER(3),ord_date date DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE (ord_no), CONSTRAINT ord_pk PRIMARY KEY (ord_no));
  D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3),ord_date date DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
  D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3),ord_date date DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

• Question 108:

  Given below are the SQL statements executed in a user session:

  CREATE TABLE product

  (pcode NUMBER(2),

  pnameVARCHAR2(10));

  INSERT INTO product VALUES(1, `pen');

  INSERT INTO product VALUES (2,'penci');

  SAVEPOINT a;

  UPDATE product SET pcode = 10 WHERE pcode = 1;

  SAVEPOINT b;

  DELETE FROM product WHERE pcode = 2;

  COMMIT;

  DELETE FROM product WHERE pcode=10;

  ROLLBACK TO SAVEPOINT a;

  Which statement describes the consequences?

  A. No SQL statement would be rolled back.
  B. Both the DELETE statements would be rolled back.
  C. Only the second DELETE statement would be rolled back.
  D. Both the DELETE statements and the UPDATE statement would be rolled back.
  A. No SQL statement would be rolled back.

• Question 109:

  Evaluate the CREATE TABLE statement:

  CREATE TABLE products

  (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15));

  Which statement is true regarding the PROD_ID_PK constraint?

  A. It would be created only if a unique index is manually created first.
  B. It would be created and would use an automatically created unique index.
  C. It would be created and would use an automatically created nonunique index.
  D. It would be created and remains in a disabled state because no index is specified in the command.
  B. It would be created and would use an automatically created unique index.

• Question 110:

  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.