Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 311:

  Evaluate the following query:

  

  What would be the outcome of the above query?

  A. It produces an error because flower braces have been used.
  B. It produces an error because the data types are not matching.
  C. It executes successfully and introduces an 's at the end of each PROMO_NAME in the output.
  D. It executes successfully and displays the literal "{'s start date was \} * for each row in the output.
  C. It executes successfully and introduces an 's at the end of each PROMO_NAME in the output.

  ---

  So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark

  in the character string

  Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any

  other character literal. These character-enclosing symbols could have been anything other than single quotation marks.

  Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any

  single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or . Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other

  character

  The syntax of the alternative quote operator is as follows:

  q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be any character or bracket.

  Alternative Quote (q) Operator

  Specify your own quotation mark delimiter.

  Select any delimiter.

  Increase readability and usability.

  SELECT department_name || q'[ Department's Manager Id: ]' || manager_id

  AS "Department and Manager"

  FROM departments;

  Alternative Quote (q) Operator

  Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter.

  You can choose any convenient delimiter, single-byte or multi byte, or any of the following character pairs: [ ], { }, ( ), or < >.

  In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between

  the brackets delimiters is interpreted as a literal character string.

• Question 312:

  The STUDENT_GRADES table has these columns:

  STUDENT_ID NUMBER(12)

  SEMESTER_END DATE

  GPA NUMBER(4, 3)

  Which statement finds the highest grade point average (GPA) per semester?

  A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
  B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
  C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
  D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
  E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
  C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

  ---

  For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed Incorrect answer:

  A. per semester condition is not included

  B. result would not display the highest gpa value D. invalid syntax error

  E. invalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

• Question 313:

  You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:

  Exhibit:

  

  PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. What is the outcome?

  A. Number of days since the promo started based on the current Chicago data and time
  B. Number of days since the promo started based on the current Singapore data and time.
  C. An error because the WHERE condition specified is invalid
  D. An error because the ROUND function specified is invalid
  A. Number of days since the promo started based on the current Chicago data and time

• Question 314:

  Which constraint can be defined only at the column level?

  A. UNIQUE
  B. NOT NULL
  C. CHECK
  D. PRIMARY KEY
  E. FOREIGN KEY
  B. NOT NULL

  ---

  The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row.

  Incorrect Answers

  A:. The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level.

  C:. The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column.

  D:. The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL.

  E:. The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table.

  OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects

• Question 315:

  Which are /SQL*Plus commands? (Choose all that apply.)

  A. INSERT
  B. UPDATE
  C. SELECT
  D. DESCRIBE
  E. DELETE
  F. RENAME
  D. DESCRIBE

  ---

  Describe is a valid iSQL*Plus/ SQL*Plus command.

  INSERT, UPDATE and DELETE are SQL DML Statements. A SELECT is an ANSI Standard SQL Statement not an iSQL*Plus Statement.

  RENAME is a DDL Statement.

• Question 316:

  Examine the SQL statement that creates ORDERS table:

  CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS (PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date));

  For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)

  A. SER_NO
  B. ORDER_ID
  C. STATUS
  D. PROD_ID
  E. ORD_TOTAL
  F. composite index on ORDER_ID and ORDER_DATE
  A. SER_NO
  F. composite index on ORDER_ID and ORDER_DATE

  ---

  Index exist for UNIQUE and PRIMARY KEY constraints

  Incorrect answer:

  B. ORDER_ID is neither UNIQUE nor PRIMARY KEY

  C. STATUS is neither UNIQUE nor PRIMARY KEY

  D. PROD_ID is neither UNIQUE nor PRIMARY KEY

  E. ORD_TOTAL is neither UNIQUE nor PRIMARY KEY

  Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-15

• Question 317:

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

  

  There is only one customer with the CUST_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 ordersVALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
  B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', andandcustomer_id, 1000);
  C. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id,
  D. order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND
  E. cust_last_name='Roberts' ANDc.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
  F. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', andcustomer_id, 1000);
  A. INSERT INTO ordersVALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);

• Question 318:

  Which two statements are true regarding sub queries? (Choose two.)

  A. A sub query can retrieve zero or more rows.
  B. Only two sub queries can be placed at one level.
  C. A sub query can be used only in SQL query statements.
  D. A sub query can appeal* on either side of a comparison operator.
  E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.
  A. A sub query can retrieve zero or more rows.
  D. A sub query can appeal* on either side of a comparison operator.

• Question 319:

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

  

  The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.

  The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.

  You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.

  Which query would give the required result?

  A. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);
  B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
  C. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
  D. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);
  B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);

• Question 320:

  View the Exhibit and examine the description for the CUSTOMERS table.

  

  You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

  A. UPDATE customersSET cust_credit_limit = NULLWHERE CUST_INCOME_LEVEL = NULL;
  B. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;
  C. UPDATE customersSET cust_credit_limit = TO_NUMBER(NULL)WHERE cust_income_level = TO_NUMBER(NULL);
  D. UPDATE customersSET cust_credit_limit = TO_NUMBER(' ', 9999)WHERE cust_income_level IS NULL;
  B. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;