Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 21:

  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 '\';

  ---

  ESCAPE identifier to search for the _ symbol

  Incorrect answer:

  B. ESCAPE identifier must be use

  C. wrong syntax

  D. wrong syntax

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-13

• Question 22:

  Which two statements are true regarding constraints?

  A. A foreign key cannot contain null values.
  B. A column with the unique constraint can contain null values.
  C. A constraint is enforced only for the insert operation on a table.
  D. A constraint can be disabled even if the constraint column contains data.
  E. All constraints can be defined at the column level as well as the table level.
  B. A column with the unique constraint can contain null values.
  D. A constraint can be disabled even if the constraint column contains data.

  ---

  B: Any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything.

  D: Constraints can be added, dropped, enabled, disabled, or validated. DISABLE allows incoming data, regardless of whether it conforms to the constraint

  Incorrect:

  Not A: The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null.

  References:

• Question 23:

  The COMMISSION column shows the monthly commission earned by the employee.

  Exhibit

  

  Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

  A. listing the employees who earn the same amount of commission as employee 3
  B. finding the total commission earned by the employees in department 10
  C. finding the number of employees who earn a commission that is higher than the average commission of the company
  D. listing the departments whose average commission is more that 600
  E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
  F. listing the employees whose annual commission is more than 6000
  A. listing the employees who earn the same amount of commission as employee 3
  C. finding the number of employees who earn a commission that is higher than the average commission of the company

• Question 24:

  You need to calculate the number of days from 1st Jan 2007 till date.

  Dates are stored in the default format of dd-mon-rr.

  Which two SQL statements would give the required output? (Choose two.)

  A. SELECT SYSDATE - TO_DATE ('01/JANUARY/2007') FROM DUAL;
  B. SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
  C. SELECT SYSDATE - TO_DATE ('01-JANUARY-2007') FROM DUAL;
  D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL;
  E. SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
  A. SELECT SYSDATE - TO_DATE ('01/JANUARY/2007') FROM DUAL;
  C. SELECT SYSDATE - TO_DATE ('01-JANUARY-2007') FROM DUAL;

• Question 25:

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

  A. A table name can be of any length.
  B. A table can have any number of columns.
  C. A column that has a DEFAULT value cannot store null values.
  D. A table and a view can have the same name in the same schema.
  E. A table and a synonym can have the same name in the same schema.
  F. The same table name can be used in different schemas in the same database.
  E. A table and a synonym can have the same name in the same schema.
  F. The same table name can be used in different schemas in the same database.

  ---

  Synonyms

  Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.

• Question 26:

  Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

  A. The HAVING clause can be used with aggregate functions in subqueries.
  B. The WHERE clause can be used to exclude rows after dividing them into groups.
  C. The WHERE clause can be used to exclude rows before dividing them into groups.
  D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
  E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
  A. The HAVING clause can be used with aggregate functions in subqueries.
  C. The WHERE clause can be used to exclude rows before dividing them into groups.

• Question 27:

  Examine the create table statements for the stores and sales tables.

  SQL> CREATE TABLE stores (store_id NUMBER(4)CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12, store_address VARCHAR2(20, start_date DATE);

  SQL> CREATE TABLE sales (sales_id NUMBER(4)CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4,quantity NUMBER(10), sales_date DATE, store_id NUMBER(4, CONSTRAINT store_id_fk FOREIGN KEY(store_id)

  REFERENCES stores(store_id));

  You executed the following statement:

  SQL> DELETE from stores

  WHERE store_id=900;

  The statement fails due to the integrity constraint error:

  ORA-02292: integrity constraint (HR.STORE_ID_FK) violated

  Which three options ensure that the statement will execute successfully? (Choose three.)

  A. Disable the primary key in the STORES table.
  B. Use CASCADE keyword with DELETE statement.
  C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
  D. Disable the FOREIGN KEY in SALES table and then delete the rows.
  E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
  A. Disable the primary key in the STORES table.
  C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
  D. Disable the FOREIGN KEY in SALES table and then delete the rows.

• Question 28:

  Evaluate the following SQL statement:

  

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

  A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
  B. It produces an error because positional notation cannot be used in the order by clause with set operators.
  C. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement.
  D. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.
  D. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.

• Question 29:

  View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.

  In product table, PDTNO is the primary key.

  In component table, COMPNO is the primary key.

  In PDT_COMP table,

  You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.

  Evaluate the following query:

  SQL>SELECT pdtno, pdtname, compno, compname

  FROM product _____________ pdt_comp

  USING (pdtno) ____________ component USING (compno)

  WHERE compname IS NOT NULL;

  Which combination of joins used in the blanks in the above query gives the correct output?

  

  A. JOIN; JOIN
  B. FULL OUTER JOIN; FULL OUTER JOIN
  C. RIGHT OUTER JOIN; LEFT OUTER JOIN
  D. LEFT OUTER JOIN; RIGHT OUTER JOIN
  C. RIGHT OUTER JOIN; LEFT OUTER JOIN

• Question 30:

  The DBA issues this SQL command:

  CREATE USER Scott

  IDENTIFIED by tiger;

  What privileges does the user Scott have at this point?

  A. No privileges.
  B. Only the SELECT privilege.
  C. Only the CONNECT privilege.
  D. All the privileges of a default user.
  A. No privileges.

  ---

  There are no privileges for the user Scott at this point. They are not added themselves to the user immediately after creation. The DBA needs to grant all privileges explicitly.

  Incorrect Answers

  B:. There are no privileges for the user Scott at this point. SELECT privilege needs to be added to the user Scott.

  C:. There are no privileges for the user Scott at this point. CONNECT privilege needs to be added to the user Scott.

  D:. There is no default user in Oracle.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351 Chapter 8: User Access in Oracle