合格させるOracle 1z0-071テスト問題でテストエンジンとPDFを提供中 [Q136-Q160]

質問136、 Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A sequence can issue duplicate values

A sequence’s unallocated cached value are lost if the instance shuts down

Sequences can always have gaps

Two or more tables cannot have keys generated from the same sequence

A sequence can only be dropped by a DBA

A sequence number that was allocated can be rolled back if a transaction fails

質問137、 Which two true about a sql statement using SET operations such as UNION?

The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query

The data type of each column retuned by the second query must exactly match the data type of the corresponding column returned by the first query

The number, but not names, of columns must be identical for all SELECT statements in the query

The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query

The names and number of columns must be identical for all SELECT statements in the query.

質問138、 Which two statements are true regarding constraints? (Choose two.)

A constraint is enforced only for an INSERT operation on a table.

A foreign key cannot contain NULL values.

A column with the UNIQUE constraint can store NULLS.

You can have more than one column in a table as part of a primary key.

質問139、 Examine this description of the PRODUCTS table:

You successfully execute this command:

Which two statements execute without errors?

Option A

Option B

Option C

Option D

質問140、 Which two statements are true regarding constraints? (Choose two.)

A table can have only one primary key and one foreign key.

A table can have only one primary key but multiple foreign keys.

Only the primary key can be defined at the column and table levels.

The foreign key and parent table primary key must have the same name.

Both primary key and foreign key constraints can be defined at both column and table levels.

質問141、 View the Exhibit and examine the data in the PRODUCT_INFORMATIONtable.

Which two tasks would require subqueries? (Choose two.)

displaying all the products whose minimum list prices are more than average list price of products having the status orderable

displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE

displaying the number of products whose list prices are more than the average list price

displaying all supplier IDs whose average list price is more than 500

displaying the minimum list price for each product status

質問142、 Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’
ALTER SESSION SET TIME_ ZONE = ‘-5:00’;
SELECT DBTIMEZONE, SYSDATE FROM DUAL
Examine the result:

If LOCALTIMESTAMP was selected at the same time what would it return?

11-JUL-2019 6,00,00,00000000 AM – 05:00

11-JUL-2019 11,00,00,00000000 AM

11-JUL-2019 6,00,00,000000 AM

11-JUL-2019 11,00,00,000000AM -05:00

質問143、 Evaluate the following statement.

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent WHEN clauses.

All rows are evaluated by all the three WHEN clauses.

Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

The INSERT statement will return an error because the ELSE clause is missing.

質問144、 View the exhibit and examine the ORDERStable.

The ORDERStable contains data and all orders have been assigned a customer ID. Which statement would add a NOTNULLconstraint to the CUSTOMER_IDcolumn?

ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

質問145、 Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’
ALTER SESSION SET TIME_ ZONE = ‘-5:00’;
SELECT DBTIMEZONE, SYSDATE FROM DUAL
Examine the result:

If LOCALTIMESTAMP was selected at the same time what would it return?

11-JUL-2019 6,00,00,00000000 AM – 05:00

11-JUL-2019 11,00,00,00000000 AM

11-JUL-2019 6,00,00,000000 AM

11-JUL-2019 11,00,00,000000AM -05:00

質問146、 Which two are true about creating tables in an Oracle database?

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

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

A system privilege is required.

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

A primary key constraint is manadatory.

質問147、 View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.

Which CREATE VIEW statement would create the view successfully?

CREATE OR REPLACE VIEW ord_vu (order_id,order_date)
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
“NO OF ITEMS”
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;

CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
“NO OF ITEMS”
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;

CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
FROM orders o JOIN order_items i ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;

CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS’
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date
WITH CHECK OPTION;

質問148、 Examine the description of the PRODUCTS table:

Which two statements execute without errors?

MERGE INTO new_prices n
USING (SELECT * FROM products) p
WHEN MATCHED THEN
UPDATE SET n.price= p.cost* 01
WHEN NOT MATCHED THEN
INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)
WHERE(p.cost<200);

MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
UPDATE SET n.price= p.cost*.01
DELETE WHERE (p.cost<200);

MERGE INTO new_prices n
USING products p
ON (p.prod_id =n.prod_id)
WHEN NOT MATCHED THEN
INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)
WHERE (p.cost<200);

MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id= p.prod_id)
WHEN MATCHED THEN
DELETE WHERE (p.cost<200)

質問149、 Examine this statement:

Which two things must be changed for it to execute successfully?

The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.

The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.

One of the LONG columns must be changed to a VARCHAR2 or CLOB.

The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.

The primary key constraint on BMP_ID must have a name.

質問150、 Which three statements are true about an ORDER BY clause?

An ORDER BY clause always sorts NULL values last.

An ORDER BY clause can perform a binary sort

An ORDER BY clause can perform a linguistic sort

By default an ORDERBY clause sorts rows in ascending order

An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

質問151、 Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name “Last name”
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)

ORDER BY “Last name”

ORDER BY 2, cust_id

ORDER BY CUST_NO

ORDER BY 2,1

ORDER BY “CUST_NO”

質問152、 Examine the structure of the EMPLOYEEStable.
Name Null? Type
– —————- —– —— ——
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
There is a parent/child relationship between EMPLOYEE_IDand MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_IDis 123.
Which query provides the correct output?

SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.manager_id = m.employee_id)
AND e.employee_id = 123;

SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;

SELECT e.last_name, e.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.employee_id)
WHERE e.employee_id = 123;

SELECT m.last_name, e.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.manager_id = m.manager_id)
WHERE e.employee_id = 123;

質問153、 Examine the description of the ENPLOYES table:

Which query requires explicit data type conversion?

SELECT SUBSTR(join date, 1, 2) – 10 FROM employees;

SELECT join_date + ’20’ EROM employees;

SELECT join_date 11.’11 salary FROM employees;

SELECT join_date FROM employees WHERE join date > ’10-02-2018′;

SELECT salary + ‘120.50’ FROM employees;

質問154、 View the Exhibit and examine the structure of the ORDERStable.

Which UPDATE statement is valid?
UPDATE orders

SET order_date = ’12-mar-2007’,
order_total IS NULL
WHERE order_id = 2455;
UPDATE orders

SET order_date = ’12-mar-2007’,
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455;
UPDATE orders

SET order_date = ’12-mar-2007’,
order_total = NULL
WHERE order_id = 2455;
UPDATE orders

SET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),
SET order_total = TO_NUMBER (NULL)
WHERE order_id = 2455;

質問155、 Examine the description of the EMPLOYEES table:

Which statement increases each employee's SALARY by the minimum SALARY for their DEPARTM
ENT_ID?

UPDATE employees e1
SET salary =(SELECT e2. salary + MIN(e2.salary)
FROM employees e2
WHERE e1.department_ id = e2. department_id GROUP BY e2. department_id) ;

UPDATE employees e1
SET salary = salary +
(SELECT MIN(e1. salary)
FROM employees e2
WHERE e1.department_id = e2 .department_id);

UPDATE employees e1
SET salary = salary+(SELECT MIN (salary)
FROM employees e2) ;

UPDATE employees e1
SET salary=
(SELECT e1.salary + MIN(e2.salary)
FROM employees e2
WHERE e1. department_ id = e2.department_id);

質問156、 Examine these two queries and their output:
SELECT deptno, dname FROM dept;

SELECT ename, job, deptno FROM emp ORDER BY deptno;

Now examine this query:
SELECT ename, dname
FROM emp CROSS JOIN dept WHERE job = 'MANAGER'
AND dept.deptno IN (10, 20) ;

64

6

3

12

質問157、 The EMPLOYEES table contains columns EMP_IDof data type NUMBERand HIRE_DATEof data type DATE.
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORYparameter is set to AMERICAin the session and, therefore, Sunday is the first day of the week.
Which query can be used?

SELECT emp_id, NEXT_DAY (MONTHS_BETWEEN (hire_date, SYSDATE), 6) FROM

employees;
SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees;

SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;

SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

質問158、 View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

FULL OUTER JOIN

JOIN

LEFT OUETR JOIN

RIGHT OUTER JOIN

質問159、 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?

Option A

Option B

Option C

Option D

Option E

Option F

質問160、 Examine the structure of the MEMBERS table:

You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?