Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 261:

  View the Exhibit and examine the data in the EMPLOYEES table:

  You want to display all the employee names and their corresponding manager names.

  Evaluate the following query:

  SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME" FROM employees e ______________ employees m

  ON e.manager_id = m.employee_id;

  Which JOIN option can be used in the blank in the above query to get the required output? Exhibit:

  A. only inner JOIN
  B. only FULL OUTER JOIN
  C. only LEFT OUTER JOIN
  D. only RIGHT OUTER JOIN
  C. only LEFT OUTER JOIN

• Question 262:

  Which constraint can be defined only at the column level?

  A. UNIQUE
  B. NOT NULL
  C. CHECK
  D. PRIMARY KEY
  E. FOREIGN KEY
  B. NOT NULL

• Question 263:

  EMPLOYEES and DEPARTMENTS data: EMPLOYEES

  

  DEPARTMENTS

  

  On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID. On the DEPARTMENTS table DEPARTMENT_ID is the primary key.

  Evaluate this UPDATE statement.

  UPDATE employees SET mgr_id = (SELECT mgr_id FROM employees

  WHERE dept_id=

  (SELECT department_id

  FROM departments

  WHERE department_name = 'Administration')),

  Salary = (SELECT salary

  FROM employees

  WHERE emp_name = 'Smith')

  WHERE job_id = 'IT_ADMIN';

  What happens when the statement is executed?

  A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
  B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
  C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
  D. The statement fails because there is more than one row matching the employee name Smith.
  E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.
  F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.
  D. The statement fails because there is more than one row matching the employee name Smith.

• Question 264:

  Which three statements/commands would cause a transaction to end? (Choose three.)

  A. COMMIT
  B. SELECT
  C. CREATE
  D. ROLLBACK
  E. SAVEPOINT
  A. COMMIT
  C. CREATE
  D. ROLLBACK

• Question 265:

  Which two statements are true regarding the ORDER BY clause? (Choose two.)

  A. It is executed first in the query execution.
  B. It must be the last clause in the SELECT statement.
  C. It cannot be used in a SELECT statement containing a HAVING clause.
  D. You cannot specify a column name followed by an expression in this clause.
  E. You can specify a combination of numeric positions and column names in this clause.
  B. It must be the last clause in the SELECT statement.
  E. You can specify a combination of numeric positions and column names in this clause.

• Question 266:

  The STUDENT_GRADES table has these columns:

  STUDENT_ID NUMBER(12)

  SEMESTER_END DATE

  GPA NUMBER(4,3)

  Which statement finds the highest grade point average (GPA) per semester?

  A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
  B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
  C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
  D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
  E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
  C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

• Question 267:

  Evaluate this SQL statement:

  SELECT ename, sal, 12*sal+100 FROM emp;

  The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

  A. No change is required to achieve the desired results.
  B. SELECT ename, sal, 12*(sal+100) FROM emp;
  C. SELECT ename, sal, (12*sal)+100 FROM emp;
  D. SELECT ename, sal+100,*12 FROM emp;
  B. SELECT ename, sal, 12*(sal+100) FROM emp;

• Question 268:

  Where can sub queries be used? (Choose all that apply)

  A. field names in the SELECT statement
  B. the FROM clause in the SELECT statement
  C. the HAVING clause in the SELECT statement
  D. the GROUP BY clause in the SELECT statement
  E. the WHERE clause in only the SELECT statement
  F. the WHERE clause in SELECT as well as all DML statements
  A. field names in the SELECT statement
  B. the FROM clause in the SELECT statement
  C. the HAVING clause in the SELECT statement
  F. the WHERE clause in SELECT as well as all DML statements

• Question 269:

  Examine the structure of the PROMOTIONS table:

  

  The management wants to see a report of unique promotion costs in each promotion category. Which query would achieve the required result?

  A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
  B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
  C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
  D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
  D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

• Question 270:

  Evaluate the following SQL query;

  

  What would be the outcome?

  A. 200
  B. 16
  C. 160
  D. 150
  E. 100
  C. 160