Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 331:

  View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

  

  The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer

  active with the company.

  You need to find those customers who have never changed their address.

  Which SET operator would you use to get the required output?

  A. INTERSECT
  B. UNION ALL
  C. MINUS
  D. UNION
  C. MINUS

• Question 332:

  Evaluate the following SQL statements:

  DELETE FROM sales;

  There are no other uncommitted transactions on the SALES table.

  Which statement is true about the DELETE statement?

  A. It removes all the rows as well as the structure of the table
  B. It removes all the rows in the table and deleted rows cannot be rolled back
  C. It removes all the rows in the table and deleted rows can be rolled back
  D. It would not remove the rows if the table has a primary key
  C. It removes all the rows in the table and deleted rows can be rolled back

• Question 333:

  You execute the following commands:

  

  For which substitution variables are you prompted for the input?

  A. None, because no input required
  B. Both the substitution variables 'hiredate' and 'mgr_id\
  C. Only 'hiredate'
  D. Only 'mgr_id'
  D. Only 'mgr_id'

• Question 334:

  View the Exhibit and examine the structure of the SALES and PRODUCTS tables.

  

  In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table.

  You want to list each product ID and the number of times it has been sold.

  Evaluate the following query:

  SQL>SELECT p.prod_id, COUNT(s.prod_id)

  FROM products p _____________ sales s

  ON p.prod_id = s.prod_id

  GROUP BY p.prod_id;

  Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)

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

• Question 335:

  You need to list the employees in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE. Examine the sample output:

  

  Which query will provide the required output?

  

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

• Question 336:

  Which SQL statement returns a numeric value?

  A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
  B. SELECT ROUND(hire_date) FROM EMP;
  C. SELECT sysdate-hire_date FROM EMP;
  D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;
  C. SELECT sysdate-hire_date FROM EMP;

  ---

  DATE value subtract DATE value will return numeric value. Incorrect answer:

  A. does not return numeric value

  B. does not return numeric value

  D. does not return numeric value Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-13

• Question 337:

  Which is the valid CREATE [TABLE statement?

  A. CREATE TABLE emp9$# (emp_no NUMBER(4));
  B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
  C. CREATE TABLE emp*123 (emp_no NUMBER(4));
  D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);
  A. CREATE TABLE emp9$# (emp_no NUMBER(4));

  ---

  Schema Object Naming Rules

  Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier. A quoted identifier begins and ends with double quotation marks ("). If you name a schema object

  using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

  A nonquoted identifier is not surrounded by any punctuation. The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated:

  Names must be from 1 to 30 bytes long with these exceptions:

  Names of databases are limited to 8 bytes.

  Names of database links can be as long as 128 bytes. If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long.

  Each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, suppose you identify a column like this:

  "schema"."table"."column"

  Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object,

  names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system

  table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if

  you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results.

  In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions.

  You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems.

  Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters

  from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

  Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

  Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can

  give the following names to different objects in the same namespace:

  employees

  "employees"

  "Employees"

  "EMPLOYEES"

  Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

  employees

  EMPLOYEES

  "EMPLOYEES"

  Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments

  are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

• Question 338:

  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 339:

  Examine the structure and data of the CUSTJTRANS table: CUSTJRANS Name Null? Type CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT 11 01-JAN-07 1000 22 01-FEB-07 2000 33 01-MAR-07 3000 Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.)

  A. SELECT transdate + '10' FROM custjrans;
  B. SELECT * FROM custjrans WHERE transdate = '01-01-07':
  C. SELECT transamt FROM custjrans WHERE custno > '11':
  D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07':
  E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000:
  A. SELECT transdate + '10' FROM custjrans;
  C. SELECT transamt FROM custjrans WHERE custno > '11':
  D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07':