Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 81:

  Examine the description of the ORDERS table:

  

  Examine the description of the INVOICES table:

  

  Which three statements execute successfully? (Choose three.)

  A. SELECT * FROM orders ORDER BY order_id INTERSECT SELECT * FROM invoices ORDER BY invoice_id;
  B. SELECT * FROM orders UNION ALL SELECT * FROM invoices) ORDER BY order_id;
  C. SELECT order_id, order_date FROM orders UNION ALL SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id;
  D. SELECT * FROM orders MINUS SELECT * FROM invoices ORDER BY 1;
  E. SELECT order_id invoice_id, order_date FROM orders MINUS SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;
  F. SELECT * FROM orders ORDER BY order_id UNION SELECT * FROM invoices;
  G. SELECT order_id, order_date FROM orders INTERSECT SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;
  C. SELECT order_id, order_date FROM orders UNION ALL SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id;
  D. SELECT * FROM orders MINUS SELECT * FROM invoices ORDER BY 1;
  E. SELECT order_id invoice_id, order_date FROM orders MINUS SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;

• Question 82:

  Which two statements are true about external tables? (Choose two.)

  A. Indexes can be created on them.
  B. You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
  C. DML statements cannot be used on them.
  D. Their data can be retrieved by using only SQL or PL/SQL.
  E. Their metadata and actual data are both stored outside the database.
  B. You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
  C. DML statements cannot be used on them.

• Question 83:

  User HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE privileges. User SCOTT has CREATE SESSION, CREATE TABLE and UNLIMITED TABLESPACE privileges. HR successfully executes this statement:

  

  HR attempts to execute:

  1.

  INSERT INTO scott.products VALUES (1, 'LAPTOP'); SCOTT attempts to execute:

  2.

  SELECT * FROM products;

  3.

  INSERT INTO scott.products VALUES (2, 'HDD');

  4.

  CREATE SYNONYM prod FOR products; Which will execute successfully?

  A. 1, 2 and 3
  B. 2, 3 and 4
  C. 1 only
  D. 2 and 3 only
  D. 2 and 3 only

• Question 84:

  Which two statements will convert the string Hello World to ello world? (Choose two.)

  A. SELECT INITCAP(TRIM(`H' FROM `Hello World')) FROM DUAL;
  B. SELECT SUBSTR(`Hello World', 2) FROM DUAL;
  C. SELECT LOWER(SUBSTR(`Hello World', 2, 1)) FROM DUAL;
  D. SELECT LOWER(TRIM(`H' FROM `Hello World')) FROM DUAL;
  E. SELECT LOWER(SUBSTR(`Hello World', 2)) FROM DUAL;
  D. SELECT LOWER(TRIM(`H' FROM `Hello World')) FROM DUAL;
  E. SELECT LOWER(SUBSTR(`Hello World', 2)) FROM DUAL;

• Question 85:

  Which three statements about roles are true? (Choose three.)

  A. Roles are assigned to users using the ALTER USER statement.
  B. Privileges are assigned to a role using the GRANT statement.
  C. A role is a named group of related privileges that can only be assigned to a user.
  D. A single user can be assigned multiple roles.
  E. Privileges are assigned to a role using the ALTER ROLE statement.
  F. Roles are assigned to roles using the ALTER ROLE statement.
  G. A single role can be assigned to multiple users.
  B. Privileges are assigned to a role using the GRANT statement.
  D. A single user can be assigned multiple roles.
  G. A single role can be assigned to multiple users.

• Question 86:

  Which two are true about using constraints? (Choose two.)

  A. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.
  B. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
  C. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
  D. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.
  E. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.
  F. NOT NULL can be specified at the column and at the table level.
  D. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.
  E. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

• Question 87:

  Which three are true about privileges? (Choose three.)

  A. A combination of object and system privileges can be granted to a role.
  B. All types of schema objects have associated object privileges.
  C. Schema owners can grant object privileges on objects in their schema to any other user or role.
  D. Only users with the DBA role can create roles.
  E. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users.
  F. Object privileges granted on a table automatically apply to all synonyms for that table.
  A. A combination of object and system privileges can be granted to a role.
  C. Schema owners can grant object privileges on objects in their schema to any other user or role.
  F. Object privileges granted on a table automatically apply to all synonyms for that table.

• Question 88:

  Which two are true about self joins? (Choose two.)

  A. They can use INNER JOIN and LEFT JOIN.
  B. They require the EXISTS operator in the join condition.
  C. They require the NOT EXISTS operator in the join condition.
  D. They require table aliases.
  E. They have no join condition.
  F. They are always equijoins.
  A. They can use INNER JOIN and LEFT JOIN.
  D. They require table aliases.

• Question 89:

  View the exhibit and examine the structure of the PROMOTIONS table.

  

  You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the `INTERNET' category.

  Which query would give you the required output?

  A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date) FROM promotions) AND promo_category= `INTERNET';
  B. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category= `INTERNET');
  C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = `INTERNET');
  D. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ANY (SELECT promo_begin_date FROM promotions WHERE promo_category= `INTERNET');
  C. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = `INTERNET');

• Question 90:

  Which four statements are true about constraints on Oracle tables? (Choose four.)

  A. A PRIMARY KEY constraint can be added after a table has been created and populated.
  B. A FOREIGN KEY column can contain NULLs.
  C. A CHECK constraint can refer to values in other rows.
  D. A NOT NULL constraint can be defined at the table level.
  E. A UNIQUE constraint can use a pre-existing index on the constrained column or columns.
  F. A UNIQUE constraint permits NULLs.
  G. A column can have only one CHECK constraint.
  A. A PRIMARY KEY constraint can be added after a table has been created and populated.
  B. A FOREIGN KEY column can contain NULLs.
  E. A UNIQUE constraint can use a pre-existing index on the constrained column or columns.
  F. A UNIQUE constraint permits NULLs.