Oracle 1z1-071リアル試験問題テストエンジン問題集トレーニングには323問あります
1z1-071実際の問題解答PDFには100%カバー率リアル試験問題
質問 # 99
View the exhibit and examine the structure in ORDERSand ORDER_ITEMStables.
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATEVIEWstatement would create the views successfully?
- A. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date; - B. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date; - C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date; - D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
WHITH CHECK OPTION;
正解：C
質問 # 100
View the exhibit and examine the structure of the STOREStable.
You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATEin the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
- A. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, - B. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36;
SELECT name, concat (address||','| |city| |', ', country) AS full_address, - C. start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
SELECT name, address||','||city||','||country AS full_address, - D. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36;
正解：D
質問 # 101
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 cust credit_level !=NULL; - B. 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; - C. 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; - D. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level <> NULL
AND due_amount <> NULL; - E. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND due_amount !=NULL;
正解：C
解説：
D: True. This query selects the first name and calculates the due amount as 5% of the credit limit from the customers table where the income level is not null and the credit limit is also not null. The IS NOT NULL operator is used to check for non-null values correctly.
* The IS NOT NULL operator is the correct way to check for non-null values in SQL. The <> NULL and
!= NULL comparisons are incorrect because NULL is not a value that can be compared using these operators; it represents the absence of a value.
* There is no due_amount column in the customers table according to the structure provided, so any WHERE clause referencing due_amount is irrelevant and incorrect. The due_amount is a derived column in the SELECT clause.
* The correct syntax to check for non-null values is IS NOT NULL, which is used in the WHERE clause of the correct answer.
References:
* The use of IS NOT NULL in the WHERE clause to filter out null values is documented in Oracle's SQL reference.
* NULL comparisons must be done with IS NULL or IS NOT NULL for correct logical evaluation in SQL.
質問 # 102
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTSand TIMEStables.
The PROD_IDcolumn is the foreign key in the SALEStable referencing the PRODUCTStable.
The CUST_IDand TIME_IDcolumns are also foreign keys in the SALEStable referencing the CUSTOMERS and TIMEStables, 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_SALEStable would get created and all the NOTNULLconstraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- B. The NEW_SALEStable would get created and all the FOREIGNKEYconstraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- C. The NEW_SALEStable would not get created because the column names in the CREATETABLE command and the SELECTclause do not match.
- D. The NEW_SALEStable would not get created because the DEFAULTvalue cannot be specified in the column definition.
正解：A
質問 # 103
Examine these SQL statements which execute successfully:
Which two statements are true after execution? (Choose two.)
- A. The foreign key constraint will be enabled and DEFERRED.
- B. The foreign key constraint will be enabled and IMMEDIATE.
- C. The primary key constraint will be enabled and IMMEDIATE.
- D. The primary key constraint will be enabled and DEFERRED.
- E. The foreign key constraint will be disabled.
正解：B、D
解説：
Explanation/Reference:
質問 # 104
Which three are true about granting object privileges on tables, views, and sequences?
- A. DELETE can be granted on tables, views, and sequences.
- B. INSERT can be granted on tables, views, and sequences.
- C. UPDATE can be granted only on tables and views.
- D. REFERENCES can be granted only on tables and views.
- E. ALTER can be granted only on tables and sequences.
- F. SELECT can be granted only on tables and views.
正解：C、D、E
質問 # 105
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. LEFT OUTER JOIN
- C. RIGHT OUTER JOIN
- D. FULL OUTER JOIN
- E. JOIN
正解：C、E
質問 # 106
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 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?
- A. The COMPONENT_IDcolumn must be of CHAR data type.
- B. The COMPONENT_IDmust be of VARCHAR2 data type.
- C. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
- D. The EXECUTION_DATETIME must be of TIMESTAMP data type.
- E. The COMPONENT_IDmust be of ROWID data type.
- F. The EXECUTION_DATETIME must be of DATE data type.
正解：A、F
質問 # 107
View the exhibit and examine the description of the PRODUCT_INFORMATIONtable.
Which SQL statement would retrieve from the table the number of products having LIST_PRICEas NULL?
- 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 is 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 i= NULL
正解：C
質問 # 108
View the Exhibit and examine the structure of the PRODUCTtable.
Which two tasks would require subqueries? (Choose two.)
- A. display the number of products whose list prices are more than the average list price
- B. display the total number of products supplied by supplier 102 and have product status as 'OBSOLETE'
- C. display all suppliers whose list price is more than 1000
- D. display the minimum list price for each product status
- E. display all products whose minimum list price is more than the average list price of products having the status 'orderable'
正解：A、E
質問 # 109
Which two statements are true about the rules of precedence for operators? (Choose two.)
- A. The + binary operator has the highest precedence in an expression in a SQL statement
- B. Arithmetic operators with equal precedence area evaluated from left to right within an expression
- C. Multiple parentheses can be used to override the default precedence of operators in an expression
- D. NULLS influence the precedence of operators in an expression
- E. The concatenation operator | | is always evaluated before addition and subtraction in an expression
正解：A、C
質問 # 110
View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom.
Which SQL statement would you execute to fulfill this requirement?
- A. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date"FROM product_informationWHERE warranty_period > SYSDATE
- B. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date"FROM product_informationORDER BY SYSDATE
- C. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date"FROM product_informationORDER BY SYSDATE-warranty_period
- D. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date"FROM product_informationORDER BY SYSDATE+warranty_period
正解：D
質問 # 111
Examine the description of the BRICKS table;
Examine the description of the BRICKS_STAGE table;
Which two queries execute successfully?
- A. select * from bricks
MINUS
select * from bricks_stage; - B. SELECT brick_id,shape FROM bricks
MINUS
SELECT WEIGHT,COLOR from bricks_stage; - C. SELECT shape,color FROM bricks
MINUS
SELECT WEIGHT,color FROM bricks_stage; - D. SELECT shape,color,weight from bricks
MINUS
SELECT * FROM bricks_stage; - E. SELECT shape,color FROM bricks
MINUS
SELECT color,shape FROM bricks_stage;
正解：A
解説：
In Oracle SQL, when using the set operators like MINUS, the number of columns and their data types in the SELECT statements must match in sequence.
A). This query will not execute successfully because the SELECT * FROM bricks_stage will return all columns from the BRICKS_STAGE table, which are WEIGHT, SHAPE, and COLOR, but the first SELECT statement specifies only SHAPE and COLOR. The order and number of columns must match.
B). This query will not execute successfully. The SELECT statements have a different number of columns, and the data types of the columns in the same positions do not match between the two queries. The first column in the first SELECT is SHAPE (VARCHAR2), and in the second SELECT, it is WEIGHT (NUMBER).
C). This query will execute successfully. The SELECT * from both tables will ensure that the number of columns and their data types are the same, as SELECT * selects all columns from the table. As long as the two tables have the same column order and data types for those columns, the query will execute.
D). This query will not execute successfully. Even though the columns are of the same data types, their order in the SELECT statements must match for the set operator to work. The order of SHAPE and COLOR is switched between the two queries.
E). This query will not execute successfully. The number of columns in the SELECT statements is the same, but their data types do not match between the two queries. BRICK_ID (NUMBER) in the first query does not match WEIGHT (NUMBER) in the second, and SHAPE (VARCHAR2) does not match COLOR (VARCHAR2).
References:
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Combining Queries with Set Operators"
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "MINUS"
質問 # 112
Which two statements are true regarding the USING and ON clauses in table joins?
- A. A maximum of one pair of columns can be joined between two tables using the ON clause.
- B. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
- C. The ON clause can be used to join tables on columns that have different names but compatible data types.
- D. Both USING and ON clauses can be used for equijoins and nonequijoins.
正解：B、C
質問 # 113
Evaluate the following query
What is the correct output of the above query?
- A. +25-00, +54-02, +00 11:12:10.123457
- B. +00-300, +54-02, +00 11:12:10.123457
- C. +00-300, +00-650, +00 11:12:10.123457
- D. +25-00, +00-650, +00 11:12:10.123457
正解：A
質問 # 114
......
GoShiken 1z1-071試験練習テスト問題：https://www.goshiken.com/Oracle/1z1-071-mondaishu.html
1z1-071試験問題解答：https://drive.google.com/open?id=1VWDR4-gMjgmEltJdUQ1_KLL1ZVV3FYLE