Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 191:

  What is true about sequences?

  A. Once created, a sequence belongs to a specific schema.
  B. Once created, a sequence is linked to a specific table.
  C. Once created, a sequence is automatically available to all users.
  D. Only the DBA can control which sequence is used by a certain table.
  E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.
  A. Once created, a sequence belongs to a specific schema.

• Question 192:

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

  A. A table can have only one primary key and one foreign key.
  B. A table can have only one primary key but multiple foreign keys.
  C. Only the primary key can be defined at the column and table levels.
  D. The foreign key and parent table primary key must have the same name.
  E. Both primary key and foreign key constraints can be defined at both column and table levels.
  B. A table can have only one primary key but multiple foreign keys.
  E. Both primary key and foreign key constraints can be defined at both column and table levels.

• Question 193:

  Which two statements are true regarding the count function?

  A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
  B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
  C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
  D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
  E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.
  B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
  D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.

  ---

  Using the COUNT Function

  The COUNT function has three formats:

  COUNT(*)

  COUNT(expr)

  COUNT(DISTINCT expr)

  COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement,

  COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.

  In contrast,

  COUNT(expr) returns the number of non-null values that are in the column identified by expr.

  COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

• Question 194:

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

  

  You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category. Which query would give you the required output?

  A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )ANDpromo_category = 'INTERNET';
  B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category='INTERNET');
  C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');
  D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');
  C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');

• Question 195:

  What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models?

  A. OODB supports multiple objects in the same database, whereas RDB supports only tables.
  B. RDB supports E.F. Codd's rules, whereas OODB does not support them.
  C. OODB incorporates methods with data structure definition, whereas RDB does not allow this.
  D. RDB allows the definition of relationships between different tables, whereas OODB does not allow this.
  C. OODB incorporates methods with data structure definition, whereas RDB does not allow this.

• Question 196:

  Evaluate this SQL statement:

  SELECT e.emp_name, d.dept_name

  FROM employees e

  JOIN departments d

  USING (department_id)

  WHERE d.department_id NOT IN (10, 40)

  ORDER BY dept_name;

  The statement fails when executed. Which change fixes the error?

  A. remove the ORDER BY clause
  B. remove the table alias prefix from the WHERE clause
  C. remove the table alias from the SELECT clause
  D. prefix the column in the USING clause with the table alias
  E. prefix the column in the ORDER BY clause with the table alias
  F. replace the condition"d.department_id NOT IN (10, 40)"in the WHERE clause with"d.department_id 10 AND d.department_id 40"
  C. remove the table alias from the SELECT clause
  E. prefix the column in the ORDER BY clause with the table alias

  ---

  Prefix the column in the ORDER BY Clause would cause the statement to succeed, assuming that the statement failed because the dept_name existed in employee and department tables. Not C: Removing the alias from the columns in the SELECT clause would cause the Statement to fail if the columns existing in both tables.

• Question 197:

  In which four clauses can a sub query be used? (Choose four.)

  A. in the INTO clause of an INSERT statement
  B. in the FROM clause of a SELECT statement
  C. in the GROUP BY clause of a SELECT statement
  D. in the WHERE clause of a SELECT statement
  E. in the SET clause of an UPDATE statement
  F. in the VALUES clause of an INSERT statement
  A. in the INTO clause of an INSERT statement
  B. in the FROM clause of a SELECT statement
  D. in the WHERE clause of a SELECT statement
  E. in the SET clause of an UPDATE statement

  ---

  A: a sub query is valid on the INTO clause of an ISERT Statement

  B: a sub query can be used in the FROM clause of a SELECT statement

  D: a sub query can be used in the WHERE clause of a SELECT statement,

  E: a sub query can be used in the SET clauses of an UPDATE statement,

  Incorrect answer:

  C. sub query cannot be used

  F: is incorrect.

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

• Question 198:

  Evaluate the SQL statement:

  SELECT LPAD (salary, 10, '*')

  FROM EMP

  WHERE EMP_ID = 1001;

  If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

  A. 17000.00
  B. 17000*****
  C. ****170.00
  D. **17000.00
  E. an error statement
  D. **17000.00

• Question 199:

  Which substitution variable would you use if you want to reuse the variable without prompting the user each time?

  A. and
  B. ACCEPT
  C. PROMPT
  D. andand
  D. andand

  ---

  To reuse the variable without prompting the user each time you can use andand substitution variable.

  Incorrect Answers

  A:. This substitution variable will prompt the user each time.

  B:. ACCEPT is command, not substitution variable. It used to define more accurate or specific prompt or when you want more output to display as the values are defined.

  C:. PROMPT is part of the ACCEPT command, it is not a variable.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173 Chapter 4: Sub queries

• Question 200:

  The SQL statements executed in a user session as follows:

  

  Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

  A. Both the DELETE statements and the UPDATE statement are rolled back
  B. The rollback generates an error
  C. Only the DELETE statements are rolled back
  D. Only the seconds DELETE statement is rolled back
  E. No SQL statements are rolled back
  B. The rollback generates an error
  E. No SQL statements are rolled back