1z1-071試験無料問題集（325題）「Oracle Database SQL 認定」

出題：1

Examine this query:
SELECT employee_id,first_name,salary
FROM employees
WHERE hire_date>'&1';
Which two methods should you use to prevent prompting for a hire date value when this query is executed?

A. Use the UNDEFINE command before executing the query.

B. Use the DEFINE command before executing the query.

C. Store the query in a script and pass the substitution value to the script when executing it.

D. Execute the SET VERIFY ON command before executing the query.

E. Execute the SET VERIFY OFF command before executing the query.

F. Replace'&1' with'&&1' in the query.

正解：B,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：2

Examine the data in the EMPLOYEES table:

Which statement will compute the total annual compensation for each employee?

A. SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp

B. SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees:

C. SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;

D. select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees

正解：A 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：3

Examine the command to create the BOOKS table.
SQL> create table books(book id CHAR(6) PRIMARY KEY,
title VARCHAR2(100) NOT NULL,
publisher_id VARCHAR2(4),
author_id VARCHAR2 (50));
The BOOK ID value 101 does not exist in the table.
Examine the SQL statement.
insert into books (book id title, author_id values
('101','LEARNING SQL','Tim Jones')

A. It executes successfully and the row is inserted with a null PLBLISHER_ID.

B. It executes successfully only if NULL is explicitly specified in the INSERT statement.

C. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.

D. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.

正解：A 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：4

Which two statements are true about single row functions?

A. FLOOR: returns the smallest integer greater than or equal to a specified number

B. CONCAT: can be used to combine any number of values

C. MOD: returns the quotient of a division operation

D. CEIL: can be used for positive and negative numbers

E. TRUNC: can be used with NUMBER and DATE values

正解：D,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：5

Examine the data in the NEW_EMPLOYEES table:

Examine the data in the EMPLOYEES table:

You want to:
1. Update existing employee details in the EMPLOYEES table with data from the NEW EMPLOYEES table.
2. Add new employee detail from the NEW_ EMPLOYEES able to the EMPLOYEES table.
Which statement will do this:

A. MERGE INTO employees e
USING new_employees n
WHERE e.employee_id = ne.employee_id
WHEN FOUND THEN
UPDATE SET e.name=ne.name,e.job_id =ne.job_id, e.salary=ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_ id,ne.name,ne.job id,ne.salary) ;

B. MERGE INTO employees e
USING new_employees n
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name,ne.job_id,ne.salary);

C. MERGE INTO employees e
USING new employees ne
WHERE e.employee_id = ne.employee_ id
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job_id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name, ne.job_id,ne.salary) ;

D. MERGE INTO employees e
USING new employees ne
ON (e.employee_id = ne.employee_id)
WHEN FOUND THEN
UPDATE SET e.name =ne.name, e.job_id=ne.job_id, e.salary =ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_id,ne.name,ne.job_id,ne.salary) ;

正解：B 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：6

Which two statements are true about Oracle synonyms?

A. All private synonym names must be unique in the database.

B. A synonym can have a synonym.

C. A synonym can be created on an object in a package.

D. A synonym has an object number.

E. Any user can create a public synonym.

正解：C,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：7

Examine the description of the PRODUCT_ STATUS table:

The STATUS column contains the values IN STOCK or OUT OF STocK for each row.
Which two queries will execute successfully?

A. SELECT prod_id ||q'(' s not available)' 'CURRENT AVAILABILITY' FROM
product_ status WHERE status = 'OUT OF STOCK'

B. SELECT PROD_ID||q'('s not available)' FROM
product_ status WHERE status = 'OUT OF STOCK';

C. SELECT prod_id ||q"' s not available" FROM
product_ status WHERE status = 'OUT OF STOCK';

D. SELECT PROD_ID||q'('s not available)' "CURRENT AVAILABILITY"
FROM product_ status WHERE status = 'OUT OF STOCK';

E. SELECT prod_id "CURRENT AVAILABILITY"||q' ('s not available)' from product_ status WHERE status
= 'OUT OF STOCK';

F. SELECT prod_id q's not available" from product_ status WHERE status = 'OUT OF STOCK';

正解：D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：8

Which two are true about queries using set operators such as UNION?

A. CHAR columns of different lengths used with a set operator retum a vAacsua mhtoe e equals the longest CHAR value.

B. An expression in the first SELECT list must have a column alias for the expression

C. Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric)

D. All set operators are valid on columns all data types.

E. In a query containing multiple set operators INTERSECT always takes precedence over UNION and UNION ALL

正解：A,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：9

You create a table named 123.
Which statement runs successfully?

A. SELECT * FROM TABLE (123) ;

B. SELECT * FROM "123";

C. SELECT * FROM V'123V';

D. SELECT * FROM '123';

正解：B 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：10

Which three privileges can be restricted to a subset of columns in a table?

A. UPDATE

B. SELECT

C. ALTER

D. INDEX

E. DELETE

F. REFERENCES

G. INSERT

正解：A,F,G 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：11

Choose the best answer.
Examine the description of the EMPLOYEES table:

Which query is valid?

A. SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;

B. SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;

C. SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:

D. SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;

正解：A 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：12

Which statement executes successfully?

A. SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;

B. SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;

C. SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;

D. SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;

E. SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;

正解：B 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：13

You have been asked to create a table for a banking application.
One of the columns must meet three requirements:
1: Be stored in a format supporting date arithmetic without using conversion functions
2: Store a loan period of up to 10 years
3: Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?

A. INTERVAL YEAR TO MONTH

B. TIMESTAMP WITH TIMEZONE

C. TIMESTAMP WITH LOCAL TIMEZONE

D. TIMESTAMP

E. INTERVAL DAY TO SECOND

正解：E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：14

Which two are true about creating tables in an Oracle database?

A. Creating an external table will automatically create a file using the specified directory and file name.

B. A system privilege is required.

C. A create table statement can specify the maximum number of rows the table will contain.

D. A primary key constraint is manadatory.

E. The same table name can be used for tables in different schemas.

正解：B,E 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

1z1-071試験無料問題集「Oracle Database SQL 認定」