Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 91:

  You work as a database administrator at ABC.com. You study the exhibit carefully.

  Exhibit

  

  Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001 starting with the latest promo. Which query would give the required result? (Choose all that apply.)

  A. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY 1 DESC;
  B. SELECT promo_name, promo_begin_date "START DATE"FROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY "START DATE" DESC;
  C. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY 2 DESC;
  D. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY promo_name DESC;
  B. SELECT promo_name, promo_begin_date "START DATE"FROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY "START DATE" DESC;
  C. . SELECT promo_name, promo_begin_dateFROM promotionsWHERE promo_begin_date > '01-JAN-01'ORDER BY 2 DESC;

• Question 92:

  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;

  ---

  Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key. Incorrect answer:

  A. there is no join

  B . invalid syntax

  C.

  does not involve the join in the primary and foreign key Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8

• Question 93:

  See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:

  

  

  You issue the following query:

  

  Which statement is true regarding the outcome of this query?

  A. It produces an error because the NATURAL join can be used only with two tables
  B. It produces an error because a column used in the NATURAL join cannot have a qualifier
  C. It produces an error because all columns used in the NATURAL join should have a qualifier
  D. It executes successfully
  B. It produces an error because a column used in the NATURAL join cannot have a qualifier

  ---

  Creating Joins with the USING Clause Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The Natural JOIN USING Clause The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2...); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN...USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN...USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.

• Question 94:

  View the Exhibit and examine the data in the promotions table.

  

  PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.

  You need to produce a report that provides the name, cost, and start date of all promos in the post category that were launched before January 1, 2000.

  Which SQL statement would you use?

  

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

• Question 95:

  Evaluate these two SQL statements:

  SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;

  SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

  What is true about them?

  A. The two statements produce identical results.
  B. The second statement returns a syntax error.
  C. There is no need to specify DESC because the results are sorted in descending order by default.
  D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
  A. The two statements produce identical results.

  ---

  the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.

  Incorrect answer:

  B. there is no syntax error

  C. result are sorted in ascending order by default D. ORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

• Question 96:

  Which two statements complete a transaction? (Choose two)

  A. DELETE employees;
  B. DESCRIBE employees;
  C. ROLLBACK TO SAVEPOINT C;
  D. GRANT SELECT ON employees TO SCOTT;
  E. ALTER TABLE employeesSET UNUSED COLUMN sal;
  F. Select MAX(sal)FROM employeesWHERE department_id = 20;
  D. GRANT SELECT ON employees TO SCOTT;
  E. ALTER TABLE employeesSET UNUSED COLUMN sal;

  ---

  D: GRANT is a DML operation which will cause an implicit commit

  E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or an

  ALTER TABLE statement, used to alter a database object.

  Incorrect Answers

  A:. The DELETE command is data-manipulation language (DML) command and it does not complete a transaction.

  B:. The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction.

  C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction

  F:. SELECT command is used to retrieve data. It does not complete a transaction.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in Oracle

• Question 97:

  You need to generate a list of all customer last names with their credit limits from the customers table.

  Those customers who do not have a credit limit should appear last in the list.

  Which two queries would achieve the required result? (Choose two.)

  

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

  ---

  If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order.

  Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting

  The default sort order is ascending:

  Numeric values are displayed with the lowest values first (for example, 1 to 999). ?Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01- JAN-95).

  Character values are displayed in the alphabetical order (for example, "A" first and "Z" last).

  Null values are displayed last for ascending sequences and first for descending sequences.

  -ANSWER B

  You can also sort by a column that is not in the SELECT list.

• Question 98:

  Examine the structure of the PROMOS table:

  

  You want to generate a report showing promo names and their duration (number of days).

  If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)

  A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date, 'ONGOING')) FROM promos;
  B. SELECT promo_name, COALESCE(TO_CHAR(promo_end_date - promo_start_date), 'ONGOING') FROM promos;
  C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date), 'ONGOING') FROM promos;
  D. SELECT promo_name, DECODE(promo_end_date-promo_start_date, NULL, 'ONGOING', promo_end_date - promo_start_date) FROM promos;
  E. SELECT promo_name, ecode(coalesce(promo_end_date, promo_start_date), null, 'ONGOING', promo_end_date - promo_start_date)FROM promos;
  B. SELECT promo_name, COALESCE(TO_CHAR(promo_end_date - promo_start_date), 'ONGOING') FROM promos;
  C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date), 'ONGOING') FROM promos;
  D. SELECT promo_name, DECODE(promo_end_date-promo_start_date, NULL, 'ONGOING', promo_end_date - promo_start_date) FROM promos;

• Question 99:

  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.

  ---

  '=' is use in the statement and sub query will return more than one row. Employees table has 2 row matching the employee name Smith. The update statement will fail.

  Incorrect Answers :

  A. The Update statement will fail no update was done. B. The update statement will fail no update was done. C. The update statement will fail no update was done. E. The update statement will fail but not due to job_it='IT_ADMIN' F. The update statement will fail but not due to department_id='Administration'

  Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12

• Question 100:

  Which three statements are true regarding views? (Choose three.)

  A. Views can be created only from tables.
  B. Views can be created from tables or other views.
  C. Only simple views can use indexes existing on the underlying tables.
  D. Both simple and complex views can use indexes existing on the underlying tables.
  E. Complex views can be created only on multiple tables that exist in the same schema.
  F. Complex views can be created on multiple tables that exist in the same or different schemas.
  B. Views can be created from tables or other views.
  D. Both simple and complex views can use indexes existing on the underlying tables.
  F. Complex views can be created on multiple tables that exist in the same or different schemas.

  ---

  Creating a Sequence (continued)

  CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value

  (NOCYCLE is the default option.)

  CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)