[完全版]2025年最新のFast2test 1z1-071のPDFで最近更新された問題です
1z1-071試験には保証が付きます。更新されたのは325問があります
質問 # 87
Which two are true about the data dictionary?
- A. All user actions are recorded in the data dictionary.
- B. Base tables in the data dictionary have the prefix DBA_.
- C. All users have permissions to access all information in the data dictionary by default
- D. The SYS user owns all base tables and user-accessible views in the data dictionary.
- E. The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
正解：D、E
質問 # 88
Sales data of a company is stored in two tables, SALES1and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1table, which are not present in the SALES2table.
Which set operator generates the required output?
- A. PLUS
- B. SUBTRACT
- C. INTERSECT
- D. MINUS
- E. UNION
正解：D
解説：
Explanation/Reference:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
質問 # 89
Examine the commands used to create DEPARTMENT_DETAILS and
COURSE_DETAILS:
SQL>CREATE TABLE DEPARTMENT_DETAILS
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAMEVARCHAR2(50),
HODVARCHAR2(50));
SQL>CREATE TABLE COURSE_DETAILS
(COURSE_IDNUMBER PRIMARY KEY,
COURSE_NAMEVARCHAR2(50),
DEPARTMENT_IDVARCHAR2(50));
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?
- A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);
- B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);
- C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);
- D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);
正解：B
質問 # 90
Examine the description of the CUSTONERS table
CUSTON is the PRIMARY KEY.
You must derermine if any customers'derails have entered more than once using a different costno,by listing duplicate name Which two methode can you use to get the requlred resuit?
- A. SUBQUERY
- B. seif join
- C. FULL OUTER JOIN with seif join
- D. RIGHT OUTER JOIN with seif join
- E. LEFT OUTER JOIN with seif join
正解：A、B
質問 # 91
Examine the structure of the SALES table.
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- B. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- C. It is created with no rows.
- D. It will not be created because of the invalid WHERE clause.
- E. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
正解：A、C
質問 # 92
Examine these SQL statements that are executed in the given order:
What will be the status of the foreign key EMP_MGR_FK?
- A. It will remain disabled and can be re-enabled manually.
- B. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
- C. It will be enabled and immediate.
- D. It will be enabled and deferred.
正解：A
質問 # 93
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries? (Choose two.)
- A. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
- B. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
- C. displaying all supplier IDs whose average list price is more than 500
- D. displaying the number of products whose list prices are more than the average list price
- E. displaying the minimum list price for each product status
正解：A、D
質問 # 94
View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)
- A. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;
- B. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iUSING (product_id) AND quantity_on_hand < 5;
- C. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
- D. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;
正解：C、D
質問 # 95
Examine the structure of the SALES table.
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- B. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- C. It is created with no rows.
- D. It will not be created because of the invalid WHERE clause.
- E. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
正解：A、C
質問 # 96
View the Exhibit and examine the structure of the SALES table.
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 produces an error because COUNT (*)should be only in the HAVING clause and not in the WHERE clause.
- B. It produces an error because COUNT (*)should be specified in the SELECTclause also.
- C. It executes successfully but produces no result because COUNT(prod_id)should be used instead of COUNT(*).
- D. It executes successfully and generates the required result.
正解：A
質問 # 97
Examine the description or the BOOKS_TRANSACTIONS table:
FOR customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?
- A. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND due_amount !=NULL; - B. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level <> NULL
AND due_amount <> NULL; - C. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND cust credit_level !=NULL; - D. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND cust_credit_limit IS NOT NULL; - E. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level IS NOT NULL
AND due_amount IS NOT NULL;
正解：D
質問 # 98
View the Exhibit and examine the structures of the employees and departments tables.
You must update the employees table according to these requirements::
-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 (locationid 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission In location_id 2100 to 1.5 times the average commission of their department.
You issue this command:
What is the result?
- A. It executes successfully but does not produce the desired update.
- B. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
- C. It generates an error because a subquery cannot have a join condition in an update statement.
- D. It executes successfully and produces the desired update.
正解：A
質問 # 99
Examine the description of the EMPLOYEES table:
Which statement will fail?
- A. SELECT department_id, COUNT (*)
FROM employees
WHERE department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id; - B. SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 HAVING COUNT(*) >= 3
GROUP BY department_id; - C. SELECT department_id, COUNT (*)
FROM employees
HAVING department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id; - D. SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 GROUP BY department_id
HAVING COUNT(*) >= 3;
正解：A
解説：
The statement that will fail is B. In Oracle SQL, the WHERE clause cannot contain aggregate functions directly. The HAVING clause is used instead to apply conditions that involve aggregates, but it is applied after the GROUP BY clause.
A). While the HAVING clause is used before the GROUP BY clause which is not standard SQL syntax, Oracle SQL may still execute it successfully due to its flexibility in syntax.
B). This statement will fail because it uses an aggregate function, COUNT(*), in the WHERE clause, which is not allowed. The correct approach is to use the HAVING clause to filter the results of aggregate functions after the GROUP BY clause.
C). This statement is correct; it places the HAVING clause after the GROUP BY clause, applying the filter on the aggregated count.
D). This is a correctly constructed statement with the WHERE clause filtering individual records before grouping, and the HAVING clause filtering groups based on the aggregate function.
References:
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "WHERE Clause"
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"
質問 # 100
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)' "CURRENT AVAILABILITY" 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" FROM product_status WHERE status = 'OUT OF STOCK';
- E. SELECT prod_id || q's not available'' FROM product_status WHERE status='OUT OF STOCK';
- F. SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';
正解：B、C
質問 # 101
The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.
Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)
AS SELECT I prod_id,cust_id,time_id FROM sales.
Which statement is true regarding the above command?
- A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.
- C. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
- D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
正解：B
解説：
The statement true regarding the CREATE TABLE command:
* C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match: The SQL command tries to create a table with columns prod_id, cust_id, and order_date, but the SELECT statement specifies columns prod_id
, cust_id, and time_id. The mismatch in column names and the number of columns specified will prevent the table from being created.
Incorrect options:
* A: It is possible to specify a DEFAULT value in the column definition when creating a table with the CREATE TABLE AS SELECT syntax.
* B: Not all NOT NULL constraints (or any other constraints, for that matter) are automatically passed to the new table unless explicitly stated in the CREATE TABLE statement.
* D: FOREIGN KEY constraints are not automatically included when creating a table using the CREATE TABLE AS SELECT syntax; they would need to be added explicitly afterwards.
質問 # 102
The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR
Which two WHERE conditions demonstrate the correct usage of conversion functions?
- A. WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');
- B. WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')
- C. WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');
- D. WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';
- E. WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));
正解：A、D
質問 # 103
Which three actions can you perform by using the ORACLE DATAPUMP access driver?
- A. Execute DML statements on an external table.
- B. Read data from a table in the database and insert it into an external table.
- C. Query data from an external table.
- D. Read data from an external table and load it into a table in the database.
- E. Create a directory object for a flat file.
- F. Create a directory object for an external table.
正解：B、C、D
解説：
The Oracle Data Pump access driver allows for specific actions with external tables:
B . Read data from an external table and load it into a table in the database. Data Pump can be used to efficiently transfer data between external tables and internal database tables.
C . Query data from an external table. The Data Pump access driver supports querying data directly from external tables.
F . Read data from a table in the database and insert it into an external table. The Data Pump can also export data from database tables to external table formats.
Options A, D, and E are incorrect:
A and D are incorrect as the creation of a directory object is not specific to the Data Pump access driver but is a general external table requirement.
E is incorrect because DML operations directly on external tables are not supported; they are read-only.
質問 # 104
Which statement is true regarding the INTERSECT operator?
- A. The number of columns and data types must be identical for all SELECT statements in the query.
- B. It ignores NULLs.
- C. The names of columns in all SELECT statements must be identical.
- D. Reversing the order of the intersected tables alters the result.
正解：A
解説：
INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
質問 # 105
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?
- A. UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
- B. UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
- C. UPDATE dept dSET city = ANY (SELECT cityFROM locations l)
- D. UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
正解：B
質問 # 106
......
Oracle 1Z1-071認定試験は、Oracleデータベースを使用してSQLプログラミングで個人の知識とスキルをテストするように設計されています。この試験は、Oracle SQLの開発と管理の専門知識を証明したい個人を対象としています。この試験に合格すると、SQLを使用してOracleデータベース環境でデータを管理および操作する候補者のスキルが検証されます。
最新の1z1-071合格保証付き試験問題集の認定サンプル問題：https://jp.fast2test.com/1z1-071-premium-file.html