[2026] 1z0-071 Actual Exam Dumps, 1z0-071 Practice Test [Q151-Q171]

[2026] 1z0-071 Actual Exam Dumps, 1z0-071 Practice Test

TestPassKing 1z0-071 dumps & Oracle PL/SQL Developer Certified Associate sure practice dumps

Oracle 1z0-071 exam is a computer-based exam that lasts for 120 minutes. 1z0-071 exam consists of 73 multiple-choice questions, and the passing score is 63%. 1z0-071 exam is available in different languages, including English, Japanese, Korean, Portuguese, and Chinese. It can be taken at any authorized Oracle testing center or online through Oracle's remote proctoring service.

The Oracle 1z0-071 exam is designed to test a candidate's ability to work with SQL in Oracle Database 12c. It is a valuable certification for individuals who want to enhance their skills and knowledge in SQL and Oracle Database administration. Oracle Database SQL certification is recognized globally and is highly valued by employers.

NEW QUESTION # 151
Evaluate the following statement:

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

A. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
B. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
C. The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.
D. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

Answer: B

Explanation:
http://psoug.org/definition/WHEN.htm

NEW QUESTION # 152
Examine this description of the PRODUCTStable:

Rows exist in this table with data in all the columns. You put the PRODUCTStable in read-only mode.
Which three commands execute successfully on PRODUCTS? (Choose three.)
DROP TABLE products;

A. CREATE INDEX price_idx ON products (price);
B. ALTER TABLE products DROP UNUSED COLUMNS;
C. TRUNCATE TABLE products;
D. ALTER TABLE products SET UNUSED (expiry_date);
E. ALTER TABLE products DROP COLUMN expiry_date;
F.

Answer: A,C,E

NEW QUESTION # 153
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
----------------
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
CUST NAME
------------------
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)

A. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)<>0;
B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)- cust_name,''),'*')
"CUST NAME"
INSTR(
FROM customers
WHERE INSTR(cust_name, ' ',-1,-2)<>0;
C. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)- INSTR(cust_name,' '),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0 ;
D. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0;

Answer: A,D

NEW QUESTION # 154
Which statement is true about aggregate functions?

A. Aggregate functions can be nested to any number of levels.
B. The AVG function implicitly converts NULLS to zero.
C. The MAX and MIN functions can be used on columns with character data types.
D. Aggregate functions can be used in any clause of a SELECT statement.

Answer: A

Explanation:
https://docs.oracle.com/database/121/SQLRF/functions003.htm

NEW QUESTION # 155
The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.
Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)
AS SELECT I prod_id,cust_id,time_id FROM sales.
Which statement is true regarding the above command?

A. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
B. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table
C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
D. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.

Answer: D

Explanation:
The statement true regarding the CREATE TABLE command:
* C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match: The SQL command tries to create a table with columns prod_id, cust_id, and order_date, but the SELECT statement specifies columns prod_id
, cust_id, and time_id. The mismatch in column names and the number of columns specified will prevent the table from being created.
Incorrect options:
* A: It is possible to specify a DEFAULT value in the column definition when creating a table with the CREATE TABLE AS SELECT syntax.
* B: Not all NOT NULL constraints (or any other constraints, for that matter) are automatically passed to the new table unless explicitly stated in the CREATE TABLE statement.
* D: FOREIGN KEY constraints are not automatically included when creating a table using the CREATE TABLE AS SELECT syntax; they would need to be added explicitly afterwards.

NEW QUESTION # 156
Examine the data in the INVOICEStable:

Examine the data in the CURRENCIEStable:

Which query returns the currencies in CURRENCIESthat are not present in INVOICES?

Answer: C

NEW QUESTION # 157
Examine the structure of the BOOKS_TRANSACTIONS table.

You want to update this table such that BOOK_ID is set to 'INVALID' for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:

Which condition must be used in the WHERE clause to perform the required update?

A. MEMBER_ID = '';
B. MEMBER_ID IS NULL;
C. MEMBER_ID = "";
D. MEMBER_ID = NULL;

Answer: B

NEW QUESTION # 158
Examine the data in the PRODUCTS table:

Examine these queries:
1. SELECT prod name, prod list
FROM products
WHERE prod 1ist NOT IN(1020) AND category _id=1;
2. SELECT prod name, | prod _ list
FROM products
WHERE prod list < > ANY (1020) AND category _id= 1;
SELECT prod name, prod _ list
FROM products
WHERE prod_ list <> ALL (10 20) AND category _ id= 1;
Which queries generate the same output?

A. 2 and 3
B. 1, 2 and 3
C. 1 and 2
D. 1 and 3

Answer: D

Explanation:
Based on the given PRODUCTS table and the SQL queries provided:
* Query 1: Excludes rows where prod_list is 10 or 20 and category_id is 1.
* Query 2: Includes rows where prod_list is neither 10 nor 20 and category_id is 1.
* Query 3: Excludes rows where prod_list is both 10 and 20 (which is not possible for a single value) and category_id is 1.
The correct answer is A, queries 1 and 3 will produce the same result. Both queries exclude rows where prod_list is 10 or 20 and include only rows from category_id 1. The NOT IN operator excludes the values within the list, and <> ALL operator ensures that prod_list is not equal to any of the values in the list, which effectively excludes the same set of rows.
Query 2, using the <> ANY operator, is incorrect because this operator will return true if prod_list is different from any of the values in the list, which is not the logic represented by the other two queries.

NEW QUESTION # 159
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 output?

A. ORDER BY 'b';
B. ORDER BY DEPT_ID;
C. ORDER BY 3;
D. ORDER BY DEPT_NAME;

Answer: B,C

NEW QUESTION # 160
Examine this statement:

What is returned upon execution?

A. 1 row
B. 0 rows
C. 2 rows
D. an error

Answer: A

NEW QUESTION # 161
Examine the data in the CUST_NAME column of the CUSTOMERS table:
CUST_NAME
---------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You want to display the CUST_NAME values where the last name starts with Mc or MC.
Which two WHERE clauses give the required result?

A. WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%'
B. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE UPPER('MC%')
C. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) IN ('MC%','Mc%')
D. WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%' OR 'MC%'
E. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE 'Mc%'

Answer: A,B

Explanation:
To find customer names where the last name starts with Mc or MC, the correct queries are:
* A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'MC%'This query
* converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with 'MC', which will match both 'Mc' and 'MC'.
* E. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'This query checks if the substring of cust_name after the first space starts with 'Mc'. However, this query will only match last names that start with 'Mc' and not 'MC' unless the database is using a case-insensitive collation.
Options B, C, and D are incorrect:
* B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way.
* C is incorrect because INITCAP would not only capitalize the first letter of 'mc' or 'Mc' but would lowercase all other letters, which is not the intended action.
* D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with 'MC'.

NEW QUESTION # 162
In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine this result:
DATE
02-JAN-2020
Which statement returns this?

A. SELECT TO_ CHAR(TO_ DATE('29-10-2019') +INTERVAL '2'; MONTH + INTERVAL '5'; DAY - INTERVAL '86410' SECOND, ' DD-MON-YYYY') AS "date" FROM DUAL;
B. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '6' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;
C. SELECT To CHAR(TO _DATE('29-10-2019') + INTERVAL '2' NONTH + INTERVAL '5' DAY INEERVAL '120' SECOND, ' DD-MON-YYY) AS "date" FROM DUAL;
D. SELECT TO_ CHAR(TO_ DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY - INTERVAL '360' SECOND, ' DD-MON-YYYY') AS "date" FROM DUAL;
E. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '4' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;

Answer: D

Explanation:
To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.
Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts 360 seconds (which is 6 minutes), resulting in a time that is still within the same day.
Here's how the calculation in option B would work out:
Start date: 29-10-2019
Add 3 months: 29-01-2020
Add 7 days: 05-02-2020
Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.
However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.
But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.

NEW QUESTION # 163
The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.
You only want to display details of rows where START_DATEis within the last 25 months.which WHERE clause can be used?

A. WHERE TO_NUMBER(start_date - SYSDATE)<=25
B. WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25
C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25
D. WHERE ADD_MONTHS (start date , 25)<= SYSDATE

Answer: C

NEW QUESTION # 164
View the Exhibits and examine the structure of the costs and promotions tables.
You want to display prod_ids whose promotion cost is less than the highest cost PR0D_ID in a promotion time interval.
Examine this SQL statement:

What will be the result?

A. It executes successfully but does not give the required result.
B. It gives an error because the ROUP BY clause is not valid.
C. It executes successfully and gives the required result.
D. It gives an error because the all keyword is not valid.

Answer: C

NEW QUESTION # 165
Examine the description of the EMPLOYEES table:

Examine this query:

Which line produces an error?

A. Line 3
B. Line 5
C. Line 7
D. Line 8

Answer: A

Explanation:
In the provided SQL query, the issue arises from the alias 'a.avg_sal' which is defined in the subquery but is being referenced in the SELECT list of the outer query. This is not permitted in SQL as the scope of the alias defined in the subquery is only within that subquery.
Here is the breakdown of the code and the error:
* Line 1: Correct syntax for initiating a SELECT statement.
* Line 2: Refers to 'e.salary', which is a correct reference to the 'salary' column using alias 'e' for the employees table.
* Line 3: 'a.avg_sal' attempts to reference an alias that is defined in the subquery within the outer query, which is not allowed. This is because 'avg_sal' is defined in the subquery's SELECT list and cannot be referenced outside of it. The correct way to include the average salary from the subquery in the SELECT list of the main query would be to repeat the subquery or to use a join that includes the average salary.
* Line 5-7: The subquery itself is correctly formed; it computes the average salary for the same department.
* Line 8: The ORDER BY clause is properly referencing 'e.last_name', which is defined in the outer query.
Therefore, the error occurs at Line 3 where 'a.avg_sal' is not a valid reference in the SELECT list of the main query because it is defined in the subquery.
The rules of scope for aliases in subqueries are specified in the Oracle Database SQL Language Reference 12c documentation. Subquery aliases cannot be referenced outside their subquery.

NEW QUESTION # 166
Examine the structure of the DEPARTMENTS table.

You execute the following command:

Which two statements are true? (Choose two.)

A. Synonyms existing of the DEPARTMENTS table would have to be re-created.
B. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.
C. Views created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.
D. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.
E. Unique key constraints defined on the COUNTRY column are removed.

Answer: D,E

NEW QUESTION # 167
Which statements are true? (Choose all that apply.)

A. Views with the same name but different prefixes, such as DBA, ALLand USER, use the same base tables from the data dictionary.
B. The USER_CONS_COLUMNSview should be queried to find the names of the columns to which a constraint applies.
C. The data dictionary is created and maintained by the database administrator.
D. The data dictionary views consists of joins of dictionary base tables and user-defined tables.
E. The usernames of all the users including the database administrators are stored in the data dictionary.
F. Both USER_OBJECTSand CATviews provide the same information about all the objects that are owned by the user.

Answer: A,B,E

Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

NEW QUESTION # 168
View the Exhibit and examine the structure of the SALES table.

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 items.

Which statement is true regarding this SQL statement?

A. It produces an error because COUNT (*) should be specified in the SELECT clause also.
B. It produces an error because COUNT (*) should be only in the HAVING clause and not in the WHERE clause.
C. It executes successfully and generates the required result.
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).

Answer: B

NEW QUESTION # 169
Examine these two queries and their output:
SELECT deptno, dname FROM dept;

SELECT ename, job, deptno FROM emp ORDER BY deptno;

Now examine this query:
SELECT ename, dname
FROM emp CROSS JOIN dept WHERE job = 'MANAGER'
AND dept.deptno IN (10, 20) ;