Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 101:

  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 subquery 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 subquery.
  D. It ignores the NULL value and generates output for the other values produced by the subquery.
  C. It generates output for NULL as well as the other values produced by the subquery.

• Question 102:

  View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:

  

  What would be the outcome of the above statement?

  

  A. It executes successfully.
  B. It produces an error because the condition on CUST_LAST_NAME is invalid.
  C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
  D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.
  A. It executes successfully.

• Question 103:

  Examine the structure of the EMPLOYEES and DEPARTMENTS tables:

  

  You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

  A. SELECT last_name, department_name, location_id FROM employees , departments ;
  B. SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
  C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
  D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
  D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

• Question 104:

  Examine the structure of the EMPLOYEES table: Which INSERT statement is valid?

  

  A. INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, `John', `Smith', `01/01/01');
  B. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, `John', `Smith', '01 January 01');
  C. INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, `John', `Smith', To_date(`01/01/01'));
  D. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, `John', `Smith', 01-Jan-01);
  D. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, `John', `Smith', 01-Jan-01);

• Question 105:

  Which two statements about sub queries are true? (Choose two.)

  A. A sub query should retrieve only one row.
  B. A sub query can retrieve zero or more rows.
  C. A sub query can be used only in SQL query statements.
  D. Sub queries CANNOT be nested by more than two levels.
  E. A sub query CANNOT be used in an SQL query statement that uses group functions.
  F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.
  B. A sub query can retrieve zero or more rows.
  F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

• Question 106:

  The EMPLOYEES table contains these columns:

  EMPLOYEE_ID NUMBER(4)

  LAST_NAME VARCHAR2 (25)

  JOB_ID VARCHAR2(10)

  You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?

  A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_' ESCAPE '\';
  B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_';
  C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";
  D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_';
  A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_' ESCAPE '\';

• Question 107:

  Examine the statement:

  Create synonym emp for hr.employees;

  What happens when you issue the statement?

  A. An error is generated.
  B. You will have two identical tables in the HR schema with different names.
  C. You create a table called employees in the HR schema based on you EMP table.
  D. You create an alternative name for the employees table in the HR schema in your own schema.
  D. You create an alternative name for the employees table in the HR schema in your own schema.

• Question 108:

  View the Exhibit and examine the description for the CUSTOMERS table.

  

  You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.

  Which UPDATE statement will accomplish the task?

  A. UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;
  B. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;
  C. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;
  D. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;
  A. UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;

• Question 109:

  Evaluate the SQL statement: TRUNCATE TABLE DEPT;

  Which three are true about the SQL statement? (Choose three.)

  A. It releases the storage space used by the table.
  B. It does not release the storage space used by the table.
  C. You can roll back the deletion of rows after the statement executes.
  D. You can NOT roll back the deletion of rows after the statement executes.
  E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
  F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
  A. It releases the storage space used by the table.
  D. You can NOT roll back the deletion of rows after the statement executes.
  F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

• Question 110:

  View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.

  

  Evaluate the following SQL statements:

  

  Which statement is true regarding the output of the above query?

  A. It gives details of product IDs that have been sold irrespective of whether they had a promo or not
  B. It gives the details of promos for which there have been no sales
  C. It gives the details of promos for which there have been sales
  D. It gives details of all promos irrespective of whether they have resulted in a sale or not
  D. It gives details of all promos irrespective of whether they have resulted in a sale or not