Oracle PL/SQL Developer Certified Associate 1z1-071練習テストエンジン: 今すぐ試そう323試験問題
試験合格保証付きのOracle PL/SQL Developer Certified Associate 1z1-071試験問題集
Oracle Database SQL試験としても知られるOracle 1Z1-071試験は、Oracleデータベースを使用している専門家のスキルと知識をテストするように設計されています。この試験は、リレーショナルデータベース管理システム（RDBMS）のデータの管理と操作に使用されるSQLまたは構造化されたクエリ言語に焦点を当てています。この試験に合格すると、個人はSQLを確実に理解しており、データのクエリ、データベースオブジェクトの作成と変更、トランザクションの制御などのタスクを効果的に実行できることが確認されます。
質問 # 40
Which three are true about dropping columns from a table?
- A. A column must be set as unused before it is dropped from a table.
- B. A column can be removed only if it contains no data.
- C. A column drop is implicitly committed
- D. A column that is referenced by another column in any other table cannot be dropped.
- E. Multiple columns can be dropped simultaneously using the ALTER TABLE command.
- F. A primary key column cannot be dropped.
正解：C、D、E
質問 # 41
Which statement is true regarding external tables?
- A. The data and metadata for an external table are stored outside the database.
- B. The default REJECT LIMITfor external tables is UNLIMITED.
- C. The CREATE TABLE AS SELECTstatement can be used to upload data into a normal table in the database from an external table.
- D. ORACLE_LOADERand ORACLE_DATAPUMPhave exactly the same functionality when used with an external table.
正解：C
解説：
Explanation/Reference:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm
質問 # 42
View the Exhibit and examine the details of the PRODUCT_INFORMATIONtable.
Exhibit
You must display PRODUCT_NAME from the table where the CATEGORY_IDcolumn has values 12or 13, and the SUPPLIER_IDcolumn has the value 102088.
You executed this SQL statement:
Which statement is true regarding the execution?
- A. It would not execute because the same column has been used twice with the ANDlogical operator.
- B. It would execute but would return no rows.
- C. It would not execute because the entire WHEREclause is not enclosed within parentheses.
- D. It would execute and return the desired result.
正解：B
質問 # 43
which three statements are true about indexes and their administration in an Oracle database?
- A. The same table column can be part of a unique and non-unique index
- B. IF a query filters on an indexed column then it will always be used during execution of query
- C. AN INVISIBLE INDEX is not maintained when DML is performed on its underlying table.
- D. AN INDEX CAN BE CREATED AS part of a CREATE TABLE statement
- E. A DESCENDING INDEX IS A type of function-based index
- F. A DROP INDEX statement always prevents updates to the table during the drop operation
正解：D、E、F
質問 # 44
Examine the structure of the SALES table. (Choose two.)
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 will not be created because of the invalid WHERE clause.
- C. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- D. It is created with no rows.
- E. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
正解：A、D
質問 # 45
Examine this business rule:
Each student can work on multiple projects and earth project can have multiple students.
You must decide an Entity Relationship (ER) model for optional data storage and allow generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true?
- A. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
- B. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
- C. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
- D. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.
- E. The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
正解：C、D
解説：
For the described business rule, the relationship between the students and projects entities is a many-to-many relationship, meaning that each student can be involved in multiple projects and each project can have multiple students.
* Statement A is true because to implement a many-to-many relationship in a relational database, an associative (junction) table is typically used. This table will contain the primary keys from both related entities as foreign keys in the associative table, making a composite primary key consisting of STUDENT_ID and PROJECT_ID. This setup allows the database to effectively manage the relationships between students and projects.
* Statement B is true as it accurately describes the solution to managing a many-to-many relationship. A direct many-to-many relationship cannot be physically implemented in a relational database. It is instead resolved into two 1-to-many relationships using an associative table as described in A. This is a fundamental relational database design principle aimed at normalizing the database and avoiding redundancy.
* Statements C, D, and E are incorrect as they misrepresent the nature of the relationships and key constraints necessary for this scenario. Specifically, C and E incorrectly suggest that one entity's primary key should serve as a foreign key in another, which does not align with the many-to-many relationship requirement described. Statement D incorrectly suggests a 1-to-many relationship directly between students and projects, which does not meet the business rule requirement.
質問 # 46
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?
- A. DELETE order_idFROM ordersWHERE order_total < 1000;
- B. DELETE ordersWHERE order_total < 1000;
- C. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
- D. DELETE orders o, order_items IWHERE o.order_id = i.order_id;
正解：C
質問 # 47
View the Exhibit and examine the structure of the PRODUCT_INFORMATIONand INVENTORIEStables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID,and QUANTITY_ON_HANDfor all the products wherein QUANTITY_ON_HANDis less than five.
Which two SQL statements can accomplish the task? (Choose two.)
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id
- A. FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id - B. FROM product_information
NATURAL JOIN inventories AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id - C. FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id)
WHERE quantity_on_hand < 5;
SELECT product_id, quantity_on_hand, supplier_id - D. FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id)
USING (product_id) AND quantity_on_hand < 5;
正解：A、C
質問 # 48
Which statement is true regarding the INTERSECToperator?
- A. The names of columns in all SELECTstatements must be identical.
- B. The number of columns and data types must be identical for all SELECTstatements in the query.
- C. It ignores NULLvalues.
- D. Reversing the order of the intersected tables alters the result.
正解：C
質問 # 49
View the Exhibit and examine the data in the PROMOTIONStable.
PROMO_BEGIN_DATEis stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?
SELECT promo_name, promo_cost, promo_begin_date
- A. FROM promotions
WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
SELECT promo_name, promo_cost, promo_begin_date - B. FROM promotions
WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
SELECT promo_name, promo_cost, promo_begin_date - C. FROM promotions
WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
SELECT promo_name, promo_cost, promo_begin_date - D. FROM promotions
WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';
正解：D
質問 # 50
The stores table has a column start__date of data type date, containing the date the row was Inserted.
You only want to display details of rows where start date is within the last 25 months." Which where clause can be used?
- A. WHERE MONTHS_BETWEEN (SYSDATE, start-_date) <= 25
- B. WHERE ADD_MONTHS (3tart_datO, 25) <= SYSDATE
- C. WHERE TO_NUMBER (start_date - SYSDATE) <= 25
- D. WHERE MONTHS_UETWEEN (start_date, SYSDATE) <= 25
正解：A
質問 # 51
You are designing the structure of a table in which two columns have the specifications:
COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row
EXECUTION_DATETIME - contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently? (Choose two.)
- A. The COMPONENT_ID must be of ROWID data type.
- B. The COMPONENT_ID must be of VARCHAR2 data type.
- C. The EXECUTION_DATETIME must be of DATE data type.
- D. The EXECUTION_DATETIME must be of TIMESTAMP data type.
- E. The COMPONENT_ID column must be of CHAR data type.
- F. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
正解：C、E
質問 # 52
View the exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
- A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= 'INTERNET');
- B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');
- C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= 'INTERNET');
- D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= 'INTERNET';
正解：B
質問 # 53
View the exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
- A. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date> ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET'); - B. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET'); - C. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET'); - D. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)
FROM promotions) AND
promo_category= 'INTERNET';
正解：B
質問 # 54
View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERStables.
You are asked to retrieve the ORDER_ID,product_ID, and total price (UNIT_PRICEmultiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECTprder_id, product_id, unit_price*quantity "Total Price"
FROM order_items
WHERE unit_price*quantity > 50000
NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?
- A. The statement would execute and provide the desired result.
- B. The statement would not execute because the WHEREclause is before the NATURAL JOINclause.
- C. The statement would not execute because the USING keyword is missing in the NATURAL JOINclause.
- D. The statement would not execute because the ON keyword is missing in the NATURAL JOINclause.
正解：B
質問 # 55
Which three are true about multiple INSERT statements?
- A. They can be performed on remote tables.
- B. They can be performed on views.
- C. They can be performed on relational tables.
- D. They can insert each computed row into more than one table.
- E. They can be performed on external tables using SQL*Loader.
- F. They can be performed only by using a subquery.
正解：A、B、C
解説：
Multiple INSERT statements allow data insertion into one or more tables based on different conditions or datasets:
* Option A: False. Multiple INSERT operations can be performed using direct values, subqueries, or even default values, not exclusively through subqueries.
* Option B: True. They can indeed be performed on relational tables, which is the standard use case in most relational databases.
* Option C: True. INSERT operations can be performed on updatable views, assuming the view is not complex (involving joins, GROUP BY clauses, etc.).
* Option D: True. Oracle allows INSERT operations on remote tables via database links, enabling distributed database interactions.
* Option E: False. Direct INSERT statements cannot be performed on external tables. External tables are typically used for read operations, with data loading handled through utilities like SQL*Loader or external data processing tools.
* Option F: False. Each INSERT statement inserts data into one table. While a single SQL command block can contain multiple INSERT statements, each one is directed at a single table.
質問 # 56
Examine the data in the COLORS table:
Examine the data in the BRICKS table:
Which two queries return all the rows from COLORS?
- A. SELECT *
EROM bricks | b
JOIN colors C
ON b. color_ rgb_ hex_ value =c. rgb _hex value; - B. SELECT
EROM colors C
LEET JOIN bricks b
ON b. color_ rgb_ hex value = c. rgb. hex.
value
WHERE b. brick_ id > 0; - C. SELECT.
FROM bricks b
RIGHT JOIN colors c
ON b. color _rgb_ hex_ value = c. rgb hex_ value; - D. SELECT
EROM colors C
LEFT JOIN bricks
USING (rgb _ hex_ value) ; - E. SELECT
FROM bricks b
FULL JOIN colors C
ON b. color rgb _ hex_ value = c. rgb _hex_ value;
正解：C、D
解説：
The queries that will return all the rows from the COLORS table are those that ensure every record from COLORS is selected, regardless of whether there's a matching record in the BRICKS table:
* Option A:
* SELECT * FROM bricks b RIGHT JOIN colors c ON b.color_rgb_hex_value = c.rgb_hex_value;
* A right join will return all the rows from the right table (COLORS), with the matching rows from the left table (BRICKS). If there is no match, NULL will be returned for columns from BRICKS.
* Option B:
* SELECT * FROM colors c LEFT JOIN bricks b USING (rgb_hex_value);
* A left join will return all the rows from the left table (COLORS), with the matching rows from the right table (BRICKS). If there is no match, NULL will be returned for columns from BRICKS. The USING clause indicates a join condition where columns with the same names are compared for equality.
Options C, D, and E will not return all the rows from the COLORS table:
* Option C: The full join will return all rows from both tables, but it is not restricted to only the rows from COLORS.
* Option D: An inner join will return only the matching rows between both tables, not all rows from COLORS.
* Option E: This is a left join, which would typically return all rows from COLORS, but the WHERE clause restricts the result set to only those rows from COLORS that have a matching BRICK_ID in BRICKS which is greater than 0, potentially excluding rows from COLORS.
質問 # 57
......
Oracle 1z1-071試験は、SQL SELECT文、データ操作言語、データ定義言語、データベースオブジェクト、およびデータベースセキュリティなど、さまざまなトピックに関する候補者の能力をテストします。候補者はSQLコードを記述および実行し、一般的なSQLエラーをトラブルシューティングする能力を示す必要があります。この試験は73問の多肢選択問題から構成され、候補者は105分間でテストを完了する必要があります。
テストエンジン練習1z1-071テスト問題：https://jp.fast2test.com/1z1-071-premium-file.html