Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 281:

  View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

  

  A. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customeridFROM customersWHERE cust_last_iiame='Roberts' ANDcredit_limit=600). 1000);
  B. . INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer idFROM customersWHERE cust_last_iiame='Roberts' ANDredit_limit=600).order_total)VALUES(L'10-mar-2007'. 'direct', andandcustomer_id, 1000):
  C. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id.
  D. ordertotalFROM orders o. customers cWHERE o.customer_id = c.customeridAND
  E. cust_la$t_name-RoberTs' ANDc.credit_liinit=600)VALUES (L'10-mar-2007\ 'direct'.( SELECT customer_idFROM customersWHERE cust_last_iiame='Roberts' ANDcredit_limit=600). 1000);
  F. INSERT INTO orders (order_id.order_date.order_mode.(SELECT customer_idFROM customersWHERE cust_last_iiame='Roberts' ANDcredit_limit=600).order_total)VALUES(l.'10-mar-2007\ 'direct'. andcustomer_id. 1000):
  A. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customeridFROM customersWHERE cust_last_iiame='Roberts' ANDcredit_limit=600). 1000);

• Question 282:

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

  

  Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

  A. listing of customers who do not have a credit limit and were born before 1980
  B. finding the number of customers, in each city, whose marital status is 'married'
  C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
  D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
  E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
  D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
  E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

  ---

  Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another.

  Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses

  two statements:

  select avg(salary) from employees;

  select last_name from employees where salary < result_of_previous_query ; Alternatively, this example uses one statement with a subquery:

  select last_name from employees where salary < (select avg(salary)from employees); In this example, the subquery is used to substitute a value into the WHERE clause of the parent query: it is returning a single value, used for comparison

  with the rows retrieved by the parent query.

  The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them:

  select department_name from departments where department_id in (select distinct(department_id) from employees);

• Question 283:

  Study this view creation statement:

  create view dept30 as select department_id,employee_id,last_name from employees where department_id=30 with check option;

  What might make the following statement fail? (Choose the best answer.)

  update dept30 set department_id=10 where employee_id=114;

  A. Unless specified otherwise, views will be created as WITH READ ONLY.
  B. The view is too complex to allow DML operations.
  C. The WITH CHECK OPTION will reject any statement that changes the DEPARTMENT_ID.
  D. The statement will succeed.
  C. The WITH CHECK OPTION will reject any statement that changes the DEPARTMENT_ID.

  ---

  INCORRECT: A, B, and D are incorrect. A is incorrect because views are, by default, created read-write. B is incorrect because the view is a simple view. D is incorrect because the statement cannot succeed because the CHECK option will reject it.

• Question 284:

  The STUDENT_GRADES table has these columns:

  STUDENT_ID. NUMBER(12)

  SEMESTER_END. DATE

  GPA. NUMBER(4, 3)

  The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date.

  Which statement accomplish this?

  A. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end DESC, gpa DESC;
  B. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end, gpa ASC
  C. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;
  D. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end DESC;
  E. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;
  F. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end, gpa DESC
  F. SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end, gpa DESC

• Question 285:

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

  

  Evaluate the following query:

  

  What would be the outcome of executing the above SQL statement?

  A. It produces an error.
  B. It shows the names of all products in the table.
  C. It shows the names of products whose list price is the second highest in the table.
  D. It shows the names of all products whose list price is less than the maximum list price.
  C. It shows the names of products whose list price is the second highest in the table.

• Question 286:

  You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. Which statement accomplishes this task?

  A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
  B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id =
  C. dept_id AND a.sal < b.maxsal;
  D. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
  E. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
  B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id =

  ---

  function MAX(column_name) Incorrect answer:

  A. invalid statement

  C. inner query return more than one line

  D. column maxsal does not exists.

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

• Question 287:

  User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.

  Which SQL statement can Mary use to accomplish that task?

  A. CREATE PUBLIC SYNONYM EDL_VU. ON emp_dept_loc_vu;
  B. CREATE PUBLIC SYNONYM EDL:VU. FOR mary (emp_dept_loc_vu);
  C. CREATE PUBLIC SYNONYM EDL_VU. FOR emp_dept_loc_vu;
  D. CREATE SYNONYM EDL_VU. ON emp_dept_loc_vu. FOR EACH USER;
  E. CREATE SYNONYM EDL_VU. FOR EACH USER. ON emp_dept_loc_vu;
  F. CREATE PUBLIC SYNONYM EDL_VU. ON emp_dept_loc_vu. FOR ALL USERS;
  C. CREATE PUBLIC SYNONYM EDL_VU. FOR emp_dept_loc_vu;

  ---

  The general syntax to create a synonym is:

  CREATE [PUBLIC] SYNONYM synonym FOR object;

• Question 288:

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

  

  You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

• Question 289:

  Which object privileges can be granted on a view?

  A. none
  B. DELETE, INSERT, SELECT
  C. ALTER, DELETE, INSERT, SELECT
  D. DELETE, INSERT, SELECT, UPDATE
  D. DELETE, INSERT, SELECT, UPDATE

  ---

  Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE. Incorrect answer:

  A. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE B. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE C. Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

• Question 290:

  You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:

  CUST_ID. NUMBER(4). NOT NULL

  CUST_NAME. VARCHAR2(100). NOT NULL

  CUST_ADDRESS. VARCHAR2(150)

  CUST_PHONE. VARCHAR2(20)

  Which SELECT statement accomplishes this task?

  A. SELECT * FROM customers
  B. SELECT name, address FROM customers;
  C. SELECT id, name, address, phone FROM customers;
  D. SELECT cust_name, cust_address FROM customers;
  E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;
  D. SELECT cust_name, cust_address FROM customers;

  ---

  This answer provides correct list of columns for the output.

  Incorrect Answers

  A:. This answer does not provide correct list of columns for the output. It is not required to show all columns of the table. Symbol "*" is used in the SELECT command to substitute a list of all columns of the table.

  B:. This answer does not provide correct list of columns for the output. There are not NAME and ADDRESS columns in the CUSTOMERS table.

  C:. This answer does not provide correct list of columns for the output. There are not ID, NAME, ADDRESS or PHONE columns in the CUSTOMERS table.

  E:. This answer does not provide correct list of columns for the output. It is not required to show all columns of the table.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24 Chapter 1: Overview of Oracle Databases