[2023年12月27日] 365日無料更新1z0-071知能問題集をゲット [Q14-Q39]

[2023年12月27日] 365日無料更新1z0-071知能問題集をゲット

ベスト品質のOracle 1z0-071試験問題

質問 # 14
Examine the structure proposed for the TRANSACTIONStable:

Which two statements are true regarding the creation and storage of data in the above table structure?
(Choose two.)

A. The TRANS_VALIDITYcolumn would give an error.
B. The CUST_CREDIT_LIMITcolumn would be able to store decimal values.
C. The CUST_STATUScolumn would give an error.
D. The TRANS_DATEcolumn would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
E. The TRANS_VALIDITYcolumn would have a maximum size of one character.
F. The CUST_STATUScolumn would store exactly one character.

正解：A、F

質問 # 15
Examine this SQL statement:

Which two are true?

A. All existing rows in the EMPLOYEES table are deleted.
B. The subquery is not a correlated subquery.
C. The subquery is executed before the DELETE statement is executed.
D. The subquery is executed for every row in the employees table.
E. The DELETE statement executes successfully even if the subquery selects multiple rows.

正解：C、D

質問 # 16
Which three queries use valid expressions?

A. SELECT product_id, expiry_date * 2 from products;
B. SELECT product_id,unit_price || 5 "Discount" , unit_price + surcharge - discount FROM products;
C. SELECT product_id,unit_price,5 "Discount", unit_price + surcharge-discount FROM products;
D. SELECT product_id,(expiry_date - delivery_date) * 2 FROM products;
E. SELECT product_id, unit_price, unit_price + surcharge FROM products;
F. SELECT product_id,(unit_price * 0.15 / (4.75 + 552.25)) FROM products;

正解：D、E、F

質問 # 17
View the Exhibit and examine the description for the SALES and CHANNELS tables.

You issued this SQL statement:

Which statement is true regarding the result? (Choose the best answer.)

A. The statement will fail because a subquery cannot be used in a VALUES clause.
B. The statement will fail because the VALUES clause is not required with the subquery.
C. The statement will execute and a new row will be inserted in the SALES table.
D. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.

正解：C

質問 # 18
View the Exhibit and examine the description of the tables.

You execute this SQL statement:

Which three statements are true?

A. The statement will fail if a row already exists in the SALES table for product 23.
B. A customer can exist in many countries.
C. The statement will fail because a subquery may not be contained in a VALUES clause.
D. A product can have a different unit price at different times.
E. The SALES table has five foreign keys.
F. The statement will execute successfully and a new row will be inserted into the SALES table.

正解：B、E、F

質問 # 19
Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

A. NATURAL JOIN
B. FULL OUTER JOIN
C. RIGHT OUTER JOIN
D. JOIN
E. LEFT OUTER JOIN

正解：C、D

質問 # 20
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

A. ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
B. ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
ALTER TABLE orders
C. MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
ALTER TABLE orders
D. MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
ALTER TABLE orders

正解：D

質問 # 21
Which three statements are true about Data Manipulation Language (DML)?

A. INSERT statements can insert NULLS explicitly into a column.
B. UPDATE statements can have different subqueries to specify the values for each updated column.
C. DML statements require a primary key be defined on a table.
D. DELETE statements can remove multiple rows based on multiple conditions.
E. INSERT INTO...SELECT...FROM statements automatically commit.

正解：B、D、E

質問 # 22
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

A. The NATURAL JOIN clause is missing the USING clause.
B. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
C. The table prefix is missing for the column names in the SELECT clause.
D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

正解：D

解説：
Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)

質問 # 23
Examine the description of the EMP_DETAILS table given below:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?

A. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
C. An EMP_IMAGE column can be included in the GROUP BY clause.
D. You cannot add a new column to the table with LONG as the data type.

正解：B、D

質問 # 24
You must find the number of employees whose salary is lower than employee 110.
Which statement fails to do this?

A. SELECT COUNT (* )
FROM employees
WHERE salary < (SELECT salary FROM employees WHERE employee.id =
110) ;
B. SELECT COUNT (*)
FROM employees e
JOIN (SELECT salary FROM employees WHERE employee_ id= 110) a
ON e. salary< a. salary;
C. SELECT COUNT (* )
FROM employees e
WHERE e. salary < (SELECT a. salary FROM employees a WHERE e. employee_ id = 110);
D. SELECT COUNT (*)
FROM employees
JOIN employees a
ON e. salary< a. salary
WHERE a. employee_ id= 110;

正解：C

質問 # 25
Examine this description of the EMP table:

You execute this query:
SELECT deptno AS "departments", SUM (sal) AS "salary"
FROM emp
GROUP | BY 1
HAVING SUM (sal)> 3 000;
What is the result?

A. all departments and a sum of the salaries of employees with a salary greater than 3000
B. an error
C. only departments where the total salary is greater than 3000, ordered by department
D. only departments where the total salary is greater than 3000, returned in no particular order

正解：B

質問 # 26
View the Exhibit and examine the details of the ORDER_ITEMS table.

Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)

A. Both the statements would give the same output.
B. Statement 1 would return only one row of output.
C. Statement 1 would not return give the same output.
D. Statement 2 would return multiple rows of output.
E. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.

正解：B、D、E

質問 # 27
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

A. The NATURAL JOIN clause is missing the USING clause.
B. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
C. The table prefix is missing for the column names in the SELECT clause.
D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

正解：D

解説：
Explanation
Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)

質問 # 28
Examine the structure of the EMPLOYEEStable.

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?

A. 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;
B. 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;
C. SELECT e.last_name, m.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;
D. 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;

正解：A

質問 # 29
Examine this Statement which returns the name of each employee and their manager,
SELECT e.last name AS emp,,m.last_name AS mgr
FROM employees e JOIN managers m
ON e.manager_ id = m. employee_ id ORDER BY emp;
You want to extend the query to include employees with no manager. What must you add before JOIN to do this?

A. CROSS
B. LEFT OUTER
C. RIGHT OUTER
D. FULL OUTER

正解：B

質問 # 30
Examine the structure of the INVOICE table.

Which two SQL statements would execute successfully? (Choose two.)

A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;
C. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;
D. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;

正解：A、D

質問 # 31
View the Exhibit and examine the structure of the SALEStable.

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

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 SELECTclause 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

質問 # 32
Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1)
FROM DUAL;
What would be the outcome?

A. 0
B. 1
C. 2
D. 3
E. 4

正解：A

解説：
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm
https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

質問 # 33
Examine the description of the PROMTIONS table:

You want to display the unique promotion costs in each promotion category.
Which two queries can be used?

A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
B. SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;
C. SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;
D. SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:
E. SELECT DISTINCT promo_category ||'has'|| promo_cost AS COSTS FROM promotions ORDER BY 1;

正解：A、E

質問 # 34
View the Exhibit and examine the structure of the ORDERStable. The ORDER_IDcolumn is the PRIMARY KEY in the ORDERStable.

Evaluate the following CREATE TABLEcommand:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

A. The NEW_ODRDERStable would get created and only the NOT NULLconstraint defined on the specified columns would be passed to the new table.
B. The NEW_ODRDERStable would not get created because the column names in the CREATE TABLE command and the SELECTclause do not match.
C. The NEW_ODRDERStable would not get created because the DEFAULTvalue cannot be specified in the column definition.
D. The NEW_ODRDERStable would get created and all the constraints defined on the specified columns in the ORDERStable would be passed to the new table.

正解：A

質問 # 35
Which statements are true? (Choose all that apply.)

A. The USER_CONS_COLUMNSview should be queried to find the names of the columns to which a constraint applies.
B. The data dictionary views consists of joins of dictionary base tables and user-defined tables.
C. The data dictionary is created and maintained by the database administrator.
D. The usernames of all the users including the database administrators are stored in the data dictionary.
E. Views with the same name but different prefixes, such as DBA, ALLand USER, use the same base tables from the data dictionary.
F. Both USER_OBJECTSand CATviews provide the same information about all the objects that are owned by the user.

正解：A、D、E

解説：
Explanation/Reference:
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

質問 # 36
Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

A. When the MAXVALUElimit for a sequence is reached, it can be increased by using the ALTER SEQUENCEstatement.
B. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.
C. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
D. DELETE <sequencename>would remove a sequence from the database.
E. CURRVALis used to refer to the most recent sequence number that has been generated for a particular sequence.

正解：A、E

解説：
Explanation/Reference:
References:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm

質問 # 37
Evaluate the following statement.

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

A. 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.
B. The INSERT statement will return an error because the ELSE clause is missing.
C. All rows are evaluated by all the three WHEN clauses.
D. 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.

正解：C

質問 # 38
A subquery is called a single-row subquery when _______.

A. The inner query returns a single value to the outer query.
B. There is only one subquery in the outer query and the inner query returns one or more values
C. The inner query uses an aggregating function and returns one or more values.
D. The inner query returns one or more values and the outer query returns a single value.

正解：A

質問 # 39
......

Oracle 1z0-071（Oracle Database SQL）認定試験は、SQLとOracleデータベースの知識をテストするために設計された人気のある認定試験です。この試験は、データベース開発者やデータベース管理者としてのキャリアを追求したい個人、および既にこの分野で働いていて自分のスキルや知識を検証したい個人を対象としています。この試験では、データ操作、データ取得、およびデータベース管理に関連する幅広いトピックがカバーされます。

Oracle試験練習テスト問題で高得点を目指そう：https://www.passtest.jp/Oracle/1z0-071-shiken.html

検証された材料は決まってこれ！1z0-071：https://drive.google.com/open?id=1QkOqZuIOFRCMVudZ38v7JqBFI-G9zyQM

関するブログ

もっと

1z0-071 無料問題集