[2024年05月24日] 完全版には更新されたのはOracle PL/SQL Developer Certified Associate（1z1-071）認定サンプル問題
最新のOracle 1z1-071リアル試験問題集PDF
Oracle 1Z0-071試験の準備をするために、候補者はOracleの公式トレーニングコースを受講し、書籍や練習試験などの学習資料を使用し、OracleデータベースとSQLプログラミングを使用して実践的な経験を積むことができます。 Oracle 1Z0-071試験に合格すると、SQLプログラミングにおける候補者の習熟度とOracleデータベースを操作する能力が示されています。この認定は雇用主によって高く評価されており、雇用機会の増加と給与の増加につながる可能性があります。
質問 # 86
Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose two.)
- A. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- B. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
- C. The WHERE clause can be used to exclude rows before dividing them into groups.
- D. The HAVING clause can be used with aggregate functions in subqueries.
- E. The WHERE clause can be used to exclude rows after dividing them into groups.
正解：D、E
質問 # 87
Examine the description of the CUSTOMERS table:
You want to display details of all customers who reside in cities starting with the letter D followed by at least two character.
Which query can be used?
- A. SELECT * FROM customers WHERE city LIKE'D %';
- B. SELECT * FROM customers WHERE city ='%D_';
- C. SELECT * FROM customers WHERE city LIKE'D_';
- D. SELECT * FROM customers WHERE city ='D_%';
正解：A
質問 # 88
You need to display the first names of all customers from the CUSTOMERS table that contain the character
'e' and have the character 'a' in the second last position.
Which query would give the required output?
- A. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')IS NOT NULL ANDSUBSTR(cust_first_name, 1, -2)='a';
- B. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name), -2)='a';
- C. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>'' ANDSUBSTR(cust_first_name, -2, 1)='a';
- D. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, -2, 1)='a';
正解：D
質問 # 89
Which two statements are true about a self join?
- A. The join key column must have an index.
- B. It must be an equijoin.
- C. It can be an inner join.
- D. It can be a left outer join.
- E. It must be a full outer jolin.
正解：A、C
質問 # 90
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL;
What is the outcome?
- A. It executes successfully and returns the correct result.
- B. It generates an error because rrrr should be replaced by rr in the format string.
- C. It generates an error because TO_CHAR should be replaced with TO_DATE.
- D. It generates an error because fm and double quotation marks should not be used in the format string.
- E. It executes successfully but does not return the correct result.
正解：A
解説：
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.
質問 # 91
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is 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?
- A. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
- B. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';
- C. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
- D. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
正解：B
質問 # 92
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
- A. INSERT INTO ordersVALUES(1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
- B. INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES (1, '10-mar-2007', 'direct', &customer_id, 1000).
- C. INSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);
- D. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' AND c.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
正解：A
質問 # 93
Which two statements are true about conditional INSERT ALL?
- A. It cannot have an ELSE clause.
- B. A single WHEN condition can be used for multiple INTO clauses.
- C. The total number of rows inserted is always equal to the number of rows returned by the subquery
- D. Each row returned by the subquery can be inserted into only a single target table.
- E. Each WHEN condition is tested for each row returned by the subquery.
正解：C、E
質問 # 94
View the Exhibit and examine the details of the ORDER_ITEMS table.
Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)
- A. Statement 1 would not return give the same output.
- B. Both the statements would give the same output.
- C. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.
- D. Statement 2 would return multiple rows of output.
- E. Statement 1 would return only one row of output.
正解：C、D、E
質問 # 95
Which three statements are true about the Oracle join and ANSI Join syntax?
- A. The SQL:1999 compliant ANSI join syntax supports natural joins.
- B. The Oracle join syntax supports creation of a Cartesian product of two tables.
- C. The Oracle join syntax supports natural joins.
- D. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
- E. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.
- F. The Oracle join syntax only supports right outer joins,
- G. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
正解：A、B、D
質問 # 96
View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?
- A. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (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 iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
- C. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_dateWHITH CHECK OPTION;
- D. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
正解：A
質問 # 97
View the exhibit and examine the description of the DEPARTMENTSand EMPLOYEEStables.
The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?
- A. The EMPLOYEESand DEPARTMENTStables have more than one column with the same column name and data type.
- B. The DEPARTMENTStable is not used before the EMPLOYEEStable in the FROMclause.
- C. The table prefix is missing for the column names in the SELECTclause.
- D. The NATURAL JOINclause is missing the USINGclause.
正解：A
解説：
Explanation/Reference:
Explanation:
Natural join needs only one column to be the same in each table. The EMPLOYEESand DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)
質問 # 98
Examine the structure proposed for the TRANSACTIONStable:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
- A. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
- B. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.
- C. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
- D. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
正解：A、B
質問 # 99
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. INTERSECT
- B. UNION
- C. SUBTRACT
- D. PLUS
- E. MINUS
正解：E
解説：
Explanation/Reference:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm
質問 # 100
Which two statements are true regarding non equijoins?
- A. The ON clause can be used.
- B. The USING clause can be used.
- C. The SQL:1999 compliant ANSI join syntax must be used.
- D. Table aliases must be used.
- E. The Oracle join syntax can be used.
正解：A、E
質問 # 101
......
Oracle 1z1-071試験は73問からなる選択式試験です。試験時間は105分で、合格には最低63%のスコアが必要です。試験はオンラインまたはテストセンターで受けることができます。試験料は245ドルであり、Oracleのウェブサイトを通じて申し込むことができます。
Oracle 1z1-071問題集で一発合格を目指すならこれ！：https://www.passtest.jp/Oracle/1z1-071-shiken.html
1z1-071練習テスト問題更新されたのは308問があります：https://drive.google.com/open?id=1-1EuHrgqzn1AUqgbLzQWkaKesONCDg1Q