Oracle 1Z0-061 Online Practice Questions and Exam Preparation

Oracle 1Z0-061 Online Questions & Answers

• Question 151:

  Examine the structure of the products table:

  

  You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND. Which SQL statement gives the required output?

  

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

• Question 152:

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

  

  The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

  

  Which statement is true regarding this SQL statement?

  A. It executes successfully and generates the required result.
  B. It produces an error because count(*) should be specified in the SELECT clause also.
  C. It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.
  D. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).
  C. It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.

  ---

  Restricting Group Results with the HAVING Clause You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. In the syntax, group_condition restricts

  the groups of rows returned to those groups for which the specified condition is true.

  The Oracle server performs the following steps when you use the HAVING clause:

  1.

  Rows are grouped.

  2.

  The group function is applied to the group.

  3.

  The groups that match the criteria in the HAVING clause are displayed. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are

  formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

  Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

• Question 153:

  Examine the statement:

  GRANT select, insert, update

  ON student_grades

  TO manager

  WITH GRANT OPTION;

  Which two are true? (Choose two.)

  A. MANAGER must be a role.
  B. It allows the MANAGER to pass the specified privileges on to other users.
  C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.
  D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.
  E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.
  F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.
  B. It allows the MANAGER to pass the specified privileges on to other users.
  E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.

  ---

  GRANT ROLE to ROLE/USER Incorrect answer:

  A. Role can be grant to user

  C. Create table privilege is not granted

  D. Execute privilege is not granted

  F. Delete privilege is not granted Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15

• Question 154:

  Examine the structure of the TRANSACTIONS table:

  Name Null Type

  TRANS_ID NOT NULL NUMBER (3)

  CUST_NAME VARCHAR2(30)

  TRANS_DATE DATE

  TRANS_AMT NUMBER(10, 2)

  You want to display the transaction date and specify whether it is a weekday or weekend.

  Evaluate the following two queries:

  

  Which statement is true regarding the above queries?

  A. Both give wrong results.
  B. Both give the correct result.
  C. Only the first query gives the correct result.
  D. Only the second query gives the correct result.
  C. Only the first query gives the correct result.

  ---

  Range Conditions Using the BETWEEN Operator Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Range Conditions Using the BETWEEN Operator You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2, 500 and $3, 500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first. You can also use the BETWEEN operator on character values: SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Smith';

• Question 155:

  View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.

  

  Evaluate the following two queries:

  SQL>SELECT DISTINCT promo_category || to_char(promo_cost)"code"

  FROM promotions

  ORDER BY code;

  SQL>SELECT DISTINCT promo_category || promo_cost "code"

  FROM promotions

  ORDER BY 1;

  Which statement is true regarding the execution of the above queries?

  A. Only the first query executes successfully.
  B. Only the second query executes successfully.
  C. Both queries execute successfully but give different results.
  D. Both queries execute successfully and give the same result.
  B. Only the second query executes successfully.

  ---

  Note: You cannot use column alias in the WHERE clause.

• Question 156:

  View the Exhibit and examine the structures of the employees and departments tables.

  

  You want to update the employees table as follows:

  -Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

  -Set department_id for these employees to the department_id corresponding to London (location_id 2100).

  -Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.

  -Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department.

  You issue the following command:

  

  What is the outcome?

  A. It executes successfully and gives the correct result.
  B. It executes successfully but does not give the correct result.
  C. It generates an error because a subquery cannot have a join condition in an update statement.
  D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.
  B. It executes successfully but does not give the correct result.

  ---

  Not that employees is used both in the first line (UPDATE employees) and later (FROM employees, departments). This would not cause the correct output. Instead aliases should be use. The following would be the correct query: UPDATE employees a SET department_id = (SELECT department_id FROM departments WHERE location_id = '2100'), (salary, commission_pct) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b WHERE a.department_id = b.department_id) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700);

• Question 157:

  You want to create a sales table with the following column specifications and data types: SALESID: Number STOREID: Number ITEMID: Number QTY: Number, should be set to 1 when no value is specified SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?

  

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

  ---

  To specify the default value of payment field you must use DEFAULT 'CASH'.

• Question 158:

  In which three situations does a transaction complete?

  A. When a DELETE statement is executed
  B. When a ROLLBACK command is executed
  C. When a PL/SQL anonymous block is executed
  D. When a data definition language (DDL) statement is executed
  E. When a TRUNCATE statement is executed after the pending transaction
  B. When a ROLLBACK command is executed
  D. When a data definition language (DDL) statement is executed
  E. When a TRUNCATE statement is executed after the pending transaction

• Question 159:

  Examine the structure of the PRODUCTS table:

  

  You want to change the definition of the PRODUCTS table. The PROD_DETAILS column must be changed to allow 4000 characters. Which statement is valid?

  A. ALTER TABLE productsMODIFY (prod_details CHAR2 (4000));
  B. ALTER TABLE productsMODIFY COLUMN (prod_details CHAR (4000));
  C. ALTER TABLE productsCHANGE (prod_details VARCHAR2 (4000));
  D. ALTER TABLE productsMODIFY (prod_details VARCHAR2 (4000));
  B. ALTER TABLE productsMODIFY COLUMN (prod_details CHAR (4000));

• Question 160:

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

  

  you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'king'.

  

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

  A. It produces an error and the < operator should be replaced by < ANY to get the required output
  B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output
  C. It executes and shows the required result
  D. It produces an error and the < operator should be replaced by < ALL to get the required output
  C. It executes and shows the required result