1z1-071問題集、Oracle実際の試験問題

質問 1

Which three statements are true about inner and outer joins?

A. An inner join returns matched rows.

B. A full outer join must use Oracle syntax.

C. A full outer join returns matched and unmatched rows.

D. A left or right outer join returns only unmatched rows.

E. Outer joins can only be used between two tables per query.

F. Outer joins can be used when there are multiple join conditions on two tables.

正解: A,C

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

質問 2

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 DEFINE command before executing the query.

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

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

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

E. Use the UNDEFINE command before executing the query.

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

正解: A,B

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

質問 3

MANAGER is an existing role with no privileges or roles.
EMP is an existing role containing the CREATE TABLE privilege.
EMPLOYEES is an existing table in the HR schema.
Which two commands execute successfully?

A. GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION:

B. GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;

C. GRANT CREATE TABLE, emp TO manager;

D. GRANT CREATE TABLE, SELECT ON hr. employees TO manager;

E. GRANT CREATE SEQUENCE TO manager, emp;

正解: B,E

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

質問 4

Examine this list of queries:
Which two statements are true?

A. 2 and 3 give the same result.

B. 1 and 4 give the same result.

C. 3 returns an error.

D. 1 and 4 give different results.

E. 2 returns the value 20.

正解: B,E

質問 5

Examine the data in the EMP table:

You execute this query:
SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary" FROM emp WHERE sal >= 12000 GROUP BY "Department " ORDER BY AverageSalary; Why does an error occur?

A. An alias name must always be specified in quotes.

B. An alias name must not be used in a GROUP BY clause.

C. An allas name must not contain space characters.

D. An alias name must not be used in an ORDER BY clause.

正解: B

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

質問 6

Examine the description of the EMPLOYEES table:

Which query is valid?

A. SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id;

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

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

D. SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date;

正解: D

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

質問 7

Evaluate the following SQL statement
SQL>SELECT promo_id, prom _category FROM promotions
WHERE promo_category='Internet' ORDER BY promo_id
UNION
SELECT promo_id, promo_category FROM Pomotions
WHERE promo_category = 'TV'
UNION
SELECT promoid, promocategory FROM promotions WHERE promo category='Radio' Which statement is true regarding the outcome of the above query?

A. It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.

B. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

C. It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.

D. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

正解: D

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

質問 8

Which two statements are true about Entity Relationships?

A. A Relationship can be mandatory for both entities

B. A table name can be specified just once when selecting data from a table having a selfreferencing relationship

C. A one-to-one relationship is always a self-referencing relationship

D. A one-to-many relationship in one direction is a one-to-one relationship in the other direction

E. A many-to-many relationship can be implemented only by using foreign keys

正解: A

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

質問 9

Examine the description of the SALES table:

The SALES table has 5,000 rows.
Examine this statement:
CREATE TABLE sales1 (prod id, cust_id, quantity_sold, price)
AS
SELECT product_id, customer_id, quantity_sold, price
FROM sales
WHERE 1=1
Which two statements are true?

A. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.

B. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table.

C. SALES1 Is created with 5,000 rows.

D. SALES1 is created with 1 row.

正解: A,D

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

質問 10

Which two are true about unused columns?

A. Unused columns retain their data until they are dropped

B. A query can return data from unused columns, but no DML is possible on those columns.

C. Once a column has been set to unused, a new column with the same name can be added to the table.

D. A foreign key column cannot be set to unused.

E. The DESCRIBE command displays unused columns

F. A primary key column cannot be set to unused.

正解: A,C

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

質問 11

Which three are true?

A. ADD_MONTHS adds a number of calendar months to a date.

B. CEIL returns the largest integer less than or equal to a specified number.

C. ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type.

D. CEIL requires an argument which is a numeric data type.

E. LAST_DAY returns the date of the last day of the current ,month onlyu.

F. LAST_DAY return the date of the last day the previous month only.

G. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

正解: A,C,G

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

質問 12

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro motion time interval.
Examine this SQL statement:
SELECT prod id
FROM costs
WHERE promo id IN
(SELECT promo id
FROM promotions
WHERE promo_cost < ALL
(SELECT MAX (promo cost)
FROM promotions
GROUP BY (promo_end date-promo_begin_date)) );
What will be the result?

A. It executes successfully and gives the required result.

B. It executes successfully but does not give the required result.

C. It gives an error because the ALL keyword is not valid.

D. It gives an error because the GROUP BY clause is not valid

正解: A

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

質問 13

Examine the description of the CUSTOMERS table:

Which two SELECT statements will return these results:
CUSTOMER_ NAME
--------------------
Mandy
Mary

A. SELECT customer_ name FROM customers WHERE customer_ name='*Ma*';

B. SELECT customer_ name FROM customers WHERE customer_ name LIKE ' % a % ' ;

C. SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma%' ;

D. SELECT customer_ name FROM customers WHERE customer_ name KIKE .*Ma*';

E. SELECT customer_ name FROM customers WHERE UPPER (customer name) LIKE 'MA&';

F. SELECT customer_ name FROM customers WHERE UPPER (customer_ name ) LIKE 'MA*. ;

G. SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma*';

正解: C,F

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

質問 14

Which two statements are true about truncate and delete?

A. the result of a delete can be undone by issuing a rollback

B. TRUNCATE can use a where clause to determine which row(s) should be removed.

C. the result of a truncate can be undone by issuing a ROLLBACK.

D. delete can use a where clause to determine which row(s) should be removed.

E. truncate leavers any indexes on the table in an UNUSABLE STATE.

正解: A,D

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

質問 15

Which two queries execute successfully?

A. SELECT prod_id, prod_name FROM products
INTERSECT
SELECT 100, prod_name FROM newproducts;

B. SELECT prod_id FROM products
UNION ALL
SELECT prod_id, prod_name FROM new_products;

C. SELECT * FROM products
UNION
SELECT * FROM new_products;

D. SELECT prod_id, exp_date FROM products
UNION ALL
SELECT prod_id, NULL FROM new_products;

E. SELECT k FROM products
MINUS
SELECT prod_id FROM new_products;

正解: C,D

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

Oracle Database SQL - 1z1-071 模擬練習