Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 81:

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

  

  Evaluate the query statement:

  

  What would be the outcome of the above statement?

  A. It executes successfully.
  B. It produces an error because the condition on CUST_LAST_NAME is invalid.
  C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
  D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.
  A. It executes successfully.

• Question 82:

  You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.

  Which statement accomplishes this task?

  A. ALTER TABLE students ADD PRIMARY KEY student_id;
  B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
  C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
  D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
  E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
  D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

  ---

  ALTER TABLE table_name

  ADD [CONSTRAINT constraint] type (coloumn);

  Incorrect answer:

  A. wrong syntax

  B. wrong syntax

  C. wrong syntax

  E. no such MODIFY keyword

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

• Question 83:

  Examine the data in the ORD_ITEMS table:

  ORD_NO ITEM_NO QTY

  1 111 10

  1 222 20

  1 333 30

  2 333 30

  2 444 40

  3 111 40

  Evaluate the following query:

  SQL>SELECT item_no, AVG(qty) FROM ord_items

  HAVING AVG(qty) > MIN(qty) * 2

  GROUP BY item_no;

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

  A. It gives an error because the HAVING clause should be specified after the GROUP BY clause.
  B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.
  C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
  D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
  C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.

• Question 84:

  View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:

  

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

  

  A. It executes successfully.
  B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
  C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
  D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
  A. It executes successfully.

• Question 85:

  Evaluate the following SQL statements:

  Exhibit:

  

  Exhibit:

  

  The above command fails when executed. What could be the reason?

  A. The BETWEEN clause cannot be used for the CHECK constraint
  B. SYSDATE cannot be used with the CHECK constraint
  C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
  D. The CHECK constraint cannot be placed on columns having the DATE data type
  B. SYSDATE cannot be used with the CHECK constraint

  ---

  CHECK Constraint

  The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:

  References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows

  A single column can have multiple CHECK constraints that refer to the column in its definition.

  There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.

  CREATE TABLE employees

  (...

  salary NUMBER(8, 2) CONSTRAINT emp_salary_min

  CHECK (salary > 0),

• Question 86:

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

  

  You need to generate a report in the following format: CATEGORIES

  5MP Digital Photo Camera's category is Photo

  Y Box's category is Electronics

  Envoy Ambassador's category is Hardware

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

  A. SELECT prod_name || q'''s category is ' || prod_category CATEGORIES FROM products;
  B. SELECT prod_name || q'['s ]'category is ' || prod_category CATEGORIES FROM products;
  C. SELECT prod_name || q'\'s\' || ' category is ' || prod_category CATEGORIES FROM products;
  D. SELECT prod_name || q'
  C. SELECT prod_name || q'\'s\' || ' category is ' || prod_category CATEGORIES FROM products;
  D. SELECT prod_name || q'

  ---

  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 multibyte, 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 87:

  The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.

  Which statement would create a synonym ORD so that HR can execute the following query successfully?

  SELECT * FROM ord;

  A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
  B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
  C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
  D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
  D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

  ---

  Creating a Synonym for an Object

  To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and

  provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views.

  In the syntax:

  PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines

  The object cannot be contained in a package.

  A private synonym name must be distinct from all other objects that are owned by the same user.

  If you try to execute the following command (alternative B, issued by OE):

• Question 88:

  You need to perform these tasks:

  Which set of SQL statements achieves the desired results?

  A. CREATE ROLE manager;GRANT create table, create viewTO manager;GRANT manager TO BLAKE, CLARK;
  B. CREATE ROLE manager;GRANT create table, create voewTO manager;GRANT manager ROLE TO BLAKE, CLARK;
  C. GRANT manager ROLE TO BLAKE, CLARK;GRANT create table, create voewTO BLAKE CLARK;***MISSING***
  A. CREATE ROLE manager;GRANT create table, create viewTO manager;GRANT manager TO BLAKE, CLARK;

  ---

  Result of commands:

  

• Question 89:

  The customers table has the following structure:

  

  You need to write a query that does the following tasks:

  1.

  Display the first name and tax amount of the customers. Tax is 5% of their credit limit.

  2.

  Only those customers whose income level has a value should be considered.

  3.

  Customers whose tax amount is null should not be considered. Which statement accomplishes all the required tasks?

  

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

• Question 90:

  On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:

  $> sqlldr hr/hr@pdb table=employees

  Which two statements are true regarding the command?

  A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
  B. It fails because no SQL *Loader data file location is specified.
  C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
  D. It fails because no SQL *Loader control file location is specified.
  A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
  C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.

  ---

  *SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics.