更新された2022年01月合格させる1z0-071試験リアル練習テスト問題
無料ダウンロードOracle 1z0-071リアル試験問題
質問 120
Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)
- A. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
- B. The CUST_STATUS column would store exactly one character.
- C. The CUST_STATUS column would give an error.
- D. The TRANS_VALIDITY column would give an error.
- E. The CUST_CREDIT_LIMIT column would be able to store decimal values.
- F. The TRANS_VALIDITY column would have a maximum size of one character.
正解: B,D
質問 121
In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:
What would be the outcome?
- A. Abigail IS
- B. An error message
- C. Abigail Pa
- D. Abigail PA
正解: C
質問 122
Which two statements are true regarding the DELETEand TRUNCATEcommands? (Choose two.)
- A. DELETEcan be used to remove rows from multiple tables in one statement.
- B. DELETEcan be used to remove data from specific columns as well as complete rows.
- C. DELETEand TRUNCATEcan be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETErule.
- D. DELETEcan be used to remove rows from only one table in one statement.
- E. DELETEcan be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent.
正解: C,D
質問 123
Which three statements are true regarding subqueries? (Choose three.)
- A. The ORDER BY Clause can be used in a subquery.
- B. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
- C. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
- D. If a subquery returns NULL, the main query may still return rows.
- E. A subquery can be used in the FROM clause of a SELECT statement.
正解: A,D,E
質問 124
Examine the structure of the EMPLOYEES table.
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?
- A. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
- B. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
- C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
- D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
正解: A
質問 125
Examine the structure of the BOOKS_TRANSACTIONS table:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
- A. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
- B. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS
- C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
- D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
正解: D
質問 126
Which task can be performed by using a single Data Manipulation Language (OML) statement?
- A. adding a column with a default value when inserting a row into a table
- B. removing all data only from one single column on which a unique constraint is defined
- C. removing all data only from one single column on which a primary key constraint is defined
- D. adding a column constraint when inserting a row into a table
正解: C
質問 127
View the Exhibit and examine the description for the PRODUCTS and SALES table.
PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the last three years. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.
Which is the valid DELETE statement?
- A. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );
- B. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );
- C. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );
- D. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE -
3*365 >= time_id);
正解: D
質問 128
In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?
- A. fourth normal form
- B. second normal form
- C. first normal form
- D. third normal form
正解: B
解説:
References:
https://blog.udemy.com/database-normal-forms/
質問 129
Which statement is true about transactions?
- A. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
- B. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
- C. Each Data Definition Language (DDL) statement executed forms a single transaction.
- D. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
正解: C
解説:
Explanation
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038
質問 130
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b'
FROM departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a'
FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output? (Choose two.)
- A. ORDER BY DEPT_NAME;
- B. ORDER BY 3;
- C. ORDER BY 'b';
- D. ORDER BY DEPT_ID;
正解: B,D
質問 131
View the Exhibit and examine the structure of the ORDERS table.
The columns ORDER_MODE and ORDER TOTAL have the default values'direct "and respectively.
Which two INSERT statements are valid? (Choose two.)
- A. INSERT INTO orders (order id, order_date, order mode, order_total)VALUES (1,'10-mar-2007','online', 1000)
- B. INSERT INTO orders VALUES('09-mar-2007',DEFAULT,101, DEFALLT);
- C. INSERT INTO orders (order_id, order_date, order mode,customer_id, order_total) VALUES (1, TO_DATE (NULL),'online',101, NULL) ;
- D. INSERT INTO orders VALUES (1, '09-mar-2007', 'online',' ',1000);
- E. INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, '09-mar-2007",101);
正解: B,E
質問 132
On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEEStable in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command? (Choose two.)
- A. It succeeds with default settings if the EMPLOYEEStable belonging to HR is already defined in the database.
- B. It fails because no SQL *Loader data file location is specified.
- C. It fails if the HR user does not have the CREATE ANY DIRECTORYprivilege.
- D. It fails because no SQL *Loader control file location is specified.
正解: A,C
解説:
Explanation/Reference:
質問 133
Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE); SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id)); You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?
- A. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
- B. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
- C. Disable the FOREIGN KEY in SALES table and then delete the rows.
- D. Use CASCADE keyword with DELETE statement.
- E. Disable the primary key in the STORES table.
正解: A,B,C
解説:
Explanation/Reference:
質問 134
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true?
- 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 do not match.
- C. The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
正解: C
質問 135
You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE.
Examine the sample output:
Which query will provide the required output?
- A. SELECT LISTAGG(last_name, '; ') "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;ORDER BY (hire_date);
- B. SELECT LISTAGG(last_name, '; ') "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;WITHIN GROUP ORDER BY (hire_date);
- C. SELECT LISTAGG(last_name, '; ')WITHIN GROUP ORDER BY (hire_date) "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;
- D. SELECT LISTAGG(last_name)WITHIN GROUP ORDER BY (hire_date) "Emp_list", MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;
正解: C
質問 136
Which statement correctly grants a system privilege?
GRANT CREATE VIEW
- A. TO user1, user2;
GRANT CREATE SESSION - B. ON table1 TO
user1;
GRANT ALTER TABLE - C. TO PUBLIC;
GRANT CREATE TABLE - D. TO ALL;
正解: A
質問 137
Examine the structure of the BOOKS_TRANSACTIONS table:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
- A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
- B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
- C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
- D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
正解: D
質問 138
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 generates an error because a subquery cannot have a join condition in an update statement.
- B. It executes successfully but does not produce the desired update.
- C. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
- D. It executes successfully and produces the desired update.
正解: B
質問 139
The customers table has the following structure:
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
- A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE (cust_income_level, tax_amount) IS NOT NULL; - B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
tax_amount IS NOT NULL; - C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level <> NULL AND
tax_amount <> NULL; - D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
cust_credit_limit IS NOT NULL;
正解: D
質問 140
You issued this command:
Which three statements are true?
- A. Sequences used to populate columns in the HR.EMPLOYEES table are dropped.
- B. All constraints defined on HR.EMPLOYEES are dropped.
- C. All indexes defined on HR.EMPLOYEES are dropped.
- D. Views referencing HR.EMPLOYEES are dropped.
- E. The HR.EMPLOYEES table may be moved to the recycle bin.
- F. Synonyms for HR.EMPLOYEES are dropped.
正解: B,C,D
質問 141
Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)
- A. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
- B. The CUST_STATUS column would store exactly one character.
- C. The CUST_STATUS column would give an error.
- D. The TRANS_VALIDITY column would give an error.
- E. The CUST_CREDIT_LIMIT column would be able to store decimal values.
- F. The TRANS_VALIDITY column would have a maximum size of one character.
正解: B,D
質問 142
......
Oracle 1z0-071 認定試験の出題範囲：
| トピック | 出題範囲 |
|---|---|
| トピック 1 |
|
| トピック 2 |
|
| トピック 3 |
|
| トピック 4 |
|
| トピック 5 |
|
| トピック 6 |
|
| トピック 7 |
|
| トピック 8 |
|
| トピック 9 |
|
| トピック 10 |
|
1z0-071問題集100パー合格保証には最新のサンプル：https://www.goshiken.com/Oracle/1z0-071-mondaishu.html
あなたを合格させる試験には100%確認済み1z0-071試験問題：https://drive.google.com/open?id=12GNomY41XDfXHNYWVp0iAX50x39SewSS