Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 1:

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

  A. A subquery can retrieve zero or more rows.
  B. Only two subqueries can be placed at one level.
  C. A subquery can be used only in SQL query statements.
  D. A subquery can appear on either side of a comparison operator.
  E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
  A. A subquery can retrieve zero or more rows.
  D. A subquery can appear on either side of a comparison operator.

• Question 2:

  Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

  

  Which MERGE statement is valid?

  A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
  B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
  C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
  D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
  E. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
  F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
  G. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
  H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);
  A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET

• Question 3:

  Which statement is true regarding transactions? (Choose all that apply.)

  A. A transaction can consist only of a set of DML and DDL statements.
  B. A part or an entire transaction can be undone by using ROLLBACK command.
  C. A transaction consists of a set of DML or DCL statements.
  D. A part or an entire transaction can be made permanent with a COMMIT.
  E. A transaction can consist of only a set of queries or DML or DDL statements.
  B. A part or an entire transaction can be undone by using ROLLBACK command.
  C. A transaction consists of a set of DML or DCL statements.

• Question 4:

  You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.

  Which statement accomplishes this task?

  A. ALTER TABLE students ADD PRIMARY KEY student_id;
  B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
  C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
  D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
  E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
  D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

• Question 5:

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

  A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
  B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
  C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
  D. When a table is dropped, the corresponding indexes are automatically dropped
  A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
  B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
  D. When a table is dropped, the corresponding indexes are automatically dropped

• Question 6:

  Evaluate the following SQL statement:

  SQL> SELECT cust_id. cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city='Singapore'):

  Which statement is true regarding the above query if one of the values generated by the sub query is NULL?

  A. It produces an error.
  B. It executes but returns no rows.
  C. It generates output for NULL as well as the other values produced by the sub query.
  D. It ignores the NULL value and generates output for the other values produced by the sub query.
  C. It generates output for NULL as well as the other values produced by the sub query.

• Question 7:

  View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

  

  Evaluate the following SQL statements:

  

  Which statement is true regarding the above compound query?

  A. It shows products that have a cost recorded irrespective of sales
  B. It shows products that were sold and have a cost recorded
  C. It shows products that were sold but have no cost recorded
  D. It reduces an error
  C. It shows products that were sold but have no cost recorded

• Question 8:

  See the structure of the PROGRAMS table:

  

  Which two SQL statements would execute successfully? (Choose two.)

  A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
  B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
  C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;
  D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;
  A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
  D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM programs;

• Question 9:

  View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

  

  Evaluate the following SQL statement:

  UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id

  FROM sales)

  SET time_id = '22-MAR-2007'

  WHERE cust_id = (SELECT cust_id

  FROM customers

  WHERE cust_last_name = 'Roberts' AND

  credit_limit = 600);

  Which statement is true regarding the execution of the above UPDATE statement?

  A. It would not execute because two tables cannot be used in a single UPDATE statement.
  B. It would not execute because the SELECT statement cannot be used in place of the table name.
  C. It would execute and restrict modifications to only the columns specified in the SELECT statement.
  D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
  C. It would execute and restrict modifications to only the columns specified in the SELECT statement.

• Question 10:

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

  

  Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:

  ENAME HIREDATE

  SMITH 17-DEC-80

  ALLEN 20-FEB-81

  WARD 22-FEB-81

  You want to generate a list of user IDs as follows:

  USERID

  Smi17DEC80

  All20FEB81

  War22FEB81

  You issue the following query:

  SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-')) "USERID" FROM employees;

  What is the outcome?

  A. It executes successfully and gives the correct output.
  B. It executes successfully but does not give the correct output.
  C. It generates an error because the REPLACE function is not valid.
  D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.
  A. It executes successfully and gives the correct output.