Oracle 1z0-061 問題集

質問 1：
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 but does not give the correct result.
B. It generates an error because a subquery cannot have a join condition in an update statement.
C. It executes successfully and gives the correct result.
D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.
正解：A
解説: (Topexam メンバーにのみ表示されます)

質問 2：
View the Exhibit and examine the structure of the customers table.

Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK.
Which SQL statement would produce the required result?

A. Option A
B. Option C
C. Option D
D. Option B
正解：B

質問 3：
View the Exhibits and examine products and sales tables.


You issue the following query to display product name and the number of times the product has been sold:

What happens when the above statement is executed?
A. The statement executes successfully and produces the required output.
B. The statement produces an error because the group by clause cannot be used in a subquery in the from clause.
C. The statement produces an error because a subquery in the from clause and outer-joins cannot be used together.
D. The statement produces an error because item_cnt cannot be displayed in the outer query.
正解：A

質問 4：
Which two statements are true regarding the count function?
A. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
B. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
C. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
D. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.
E. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
正解：A,E
解説: (Topexam メンバーにのみ表示されます)

質問 5：
See the Exhibit and examine the structure of ORD table:
Exhibit:

Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, '25-jan-2007, 101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
B. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
C. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
D. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
正解：A