Oracle 1z1-071テストエンジン練習テスト問題、試験問題集 [Q20-Q39]

Oracle 1z1-071テストエンジン練習テスト問題、試験問題集

100%無料1z1-071日常練習試験には323問があります

Oracle Database SQL認定試験としても知られるOracle 1Z1-071は、SQLの分野の候補者の知識とスキルをテストするように設計されています。この試験は、特にSQLの分野で、データベース管理の認定専門家になることに関心のある個人を対象としています。

 

質問 # 20
You need to display the first names of all customers from the CUSTOMERStable that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
SELECT cust_first_name

• A. FROM customers
  WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
  SUBSTR(cust_first_name, 1, -2)='a';
  SELECT cust_first_name
• B. FROM customers
  WHERE INSTR(cust_first_name, 'e')<>0 AND
  SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)='a';
• C. FROM customers
  WHERE INSTR(cust_first_name, 'e')<>'' AND
  SUBSTR(cust_first_name, -2, 1)='a';
  SELECT cust_first_name
• D. FROM customers
  WHERE INSTR(cust_first_name, 'e')<>0 AND
  SUBSTR(cust_first_name, -2, 1)='a';
  SELECT cust_first_name

正解：D

質問 # 21
The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.
The constraint is defined with on DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL less than 1000.
Which three DELETE statements execute successfully?

• A. DELETE FROM orders;
• B. DELETE * FROM orders WHERE order_total<1000;
• C. DELETE order_id FROM orders WHERE order_total<1000;
• D. DELETE FROM orders WHERE order_total<1000;
• E. DELETE orders WHERE order_total<1000;

正解：A、D、E

質問 # 22
You create a table named 123.
Which statement runs successfully?

• A. SELECT * FROM "123";
• B. SELECT * FROM TABLE (123) ;
• C. SELECT * FROM &apos;123&apos;;
• D. SELECT * FROM V&apos;123V&apos;;

正解：A

解説：
C). True. If you have a table named 123, which is not a standard naming convention because it begins with a digit, you can reference it in SQL by using double quotation marks. The correct syntax to reference a table with a name that does not follow standard naming conventions is to enclose the name in double quotation marks.
A, B, and D are incorrect because they do not use the proper quoting mechanism for a table named with non-standard characters. In Oracle, double quotation marks are used for case-sensitive table names or those that do not follow the usual naming rules.

質問 # 23
Which three statements are true about the DESCRIBEcommand? (Choose three.)

• A. It can be used from SQL Developer
• B. It displays the PRIMARY KEYconstraint for any column or columns that have that constraint
• C. It displays all constraints that are defined for each column
• D. It displays the NOT NULLconstraint for any columns that have that constraint
• E. It can be used only from SQL*Plus
• F. It can be used to display the structure of an existing view

正解：A、D、F

質問 # 24
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 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;
• B. 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. SELECT product_id, quantity_on_hand, supplier_idFROM
  product_informationNATURAL JOIN inventories 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)USING (product_id) AND quantity_on_hand < 5;

正解：A、B

質問 # 25
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

• A. It shows COST_REMARK for all the promos in the table.
• B. It shows COST_REMARK for all the promos in the promo category 'TV'.
• C. It produces an error because subqueries cannot be used with the CASE expression.
• D. It produces an error because the subquery gives an error.

正解：A

質問 # 26
You have the privileges to create any type of synonym.
Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accesible to all users?

• A. CREATE SYNONYM SYS.emp FOR hcm.employee_records;
• B. CREATE SYNONYM emp FOR hcm.employee_records;
• C. CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;
• D. CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;
• E. CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;

正解：D

質問 # 27
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 CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  ALTER TABLE orders
• B. MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
  ALTER TABLE orders
• C. MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  ALTER TABLE orders
• D. ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

正解：B

解説：
Explanation/Reference:

質問 # 28
Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

• A. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
• B. IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
• C. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
• D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
• E. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

正解：A、E

質問 # 29
Examine the description of the PRODUCT_INFORMATION table:

Which query retrieves the number of products with a null list price?

• A. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price IS NULL;
• B. SELECT COUNT (list_price) FROM product_information WHERE list_price = NULL;
• C. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;
• D. SELECT COUNT (list_price) FROM product_information WHERE list_price IS NULL;

正解：C

質問 # 30
Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? (Choose three.)

• A. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEYand the PRIMARY KEYcolumns are not referenced in the defining query of the view.
• B. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCTkeyword.
• C. Insert statements can always be done on a table through a view.
• D. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
• E. The WITH CHECKclause has no effect when deleting rows from the underlying table through the view.
• F. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.

正解：A、D、F

質問 # 31
Which two statements are true about Oracle databases and SQL?

• A. The database guarantees read consistency at select level on user-created tablers.
• B. A query can access only tables within the same schema.
• C. When you execute an UPDATE statement, the database instance locks each updated row.
• D. A user can be the owner of multiple schemas In the same database.
• E. Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.

正解：A、C

解説：
B). True. Oracle databases guarantee read consistency at the transaction level, meaning that each query can only see data that has been committed before the query began executing.
C). True. When an UPDATE statement is executed, Oracle locks each row as it is updated to prevent other transactions from modifying it until the transaction is committed or rolled back.
A is incorrect because updates made by one user cannot be rolled back by another user. D is incorrect because a query can access tables in other schemas if proper permissions are granted. E is incorrect because a user can own only one schema, which has the same name as the user in an Oracle database.

質問 # 32
Which three are true about system and object privileges? (Choose three.)

• A. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC
• B. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
• C. Adding a primary key constraint to an existing table in another schema requires a system privilege
• D. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.
• E. WITH GRANT OPTION can be used when granting an object privilege to both users and roles
• F. Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect

正解：B、D、E

解説：
Reference:
https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG004

質問 # 33
Evaluate the following statement.

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

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

正解：D

質問 # 34

Which three statements are true?

• A. The DEPTNO column in the EMP table can contain the value 1.
• B. The DNAME column has a unique constraint.
• C. The DEPTNO column in the EMP table can contain NULLS .
• D. The COMMISSION column can contain negative values .
• E. The MANAGER column is a foreign key referencing the EMPNO column.
• F. The SALARY column must have a value .
• G. An index is created automatically in the MANAGER column.

正解：A、C、G

質問 # 35
Examine this statement:

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

• A. The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.
• B. The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.
• C. One of the LONG columns must be changed to a VARCHAR2 or CLOB.
• D. The primary key constraint on BMP_ID must have a name.
• E. The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.

正解：C、E

解説：
The statement is trying to create a table with columns of different data types and constraints. Here's what needs to be corrected:
* C: In Oracle, the LONG data type is used for character data of variable length up to 2 Gigabytes, but it is deprecated, and you should use CLOB or VARCHAR2 instead. Furthermore, a table cannot have more than one LONG column.
* D: The NOT NULL constraint should be specified at the column level, not at the table level. The correct syntax for creating a table with a NOT NULL constraint is to define it inline with the column definition, like this:
ename VARCHAR2(15) CONSTRAINT ename_nn NOT NULL,
The other options are incorrect:
* A: The foreign key constraint syntax is correct; the word CONSTRAINT is followed by the constraint name and then the REFERENCES clause.
* B: The foreign key constraint can be defined at the column level.
* E: While it's a good practice to name constraints, it is not mandatory for the primary key constraint to have a name; Oracle will generate one if it's not provided.
References:
* Oracle Documentation on CREATE TABLE: SQL Language Reference - CREATE TABLE
* Oracle Documentation on Data Types: SQL Language Reference - Data Types

質問 # 36
Examine the description of the EMPLOYEEStable:

Which two queries return all the rows for employees whose salary is greater than the average salary in their department? (Choose two.)

• A.
• B.
• C.
• D.
• E.

正解：A、E

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

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

正解：A、C、E

質問 # 38
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 be enabled and immediate.
• 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 deferred.
• D. It will remain disabled and can be re-enabled manually.

正解：D

質問 # 39
......

有効な問題最新版を試そう1z1-071テスト解釈1z1-071有効な試験ガイド：https://jp.fast2test.com/1z1-071-premium-file.html