Full 1z1-071 Practice Test and 323 unique questions with explanations waiting just for you!
Oracle PL/SQL Developer Certified Associate Dumps 1z1-071 Exam for Full Questions - Exam Study Guide
NEW QUESTION # 186
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
You executed this UPDATE statement:
Which statement is true regarding the execution? (Choose the best answer.)
- A. It would not execute because a SELECT statement cannot be used in place of a table name.
- B. It would not execute because two tables cannot be referenced in a single UPDATE statement.
- C. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
- D. It would execute and restrict modifications to the columns specified in the SELECT statement.
Answer: D
NEW QUESTION # 187
The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR.
Which two are true about data type conversions involving these columns in query expressions?
- A. CONCAT(qty_sold, invoice_date): requires explicit conversion
- B. qty_sold ='05549821 ': requires explicit conversion
- C. invoice_date = '15-march-2019': uses implicit conversion
- D. invoice_date> '01-02-2019': uses implicit conversion
- E. qty_sold BETWEEN '101' AND '110': uses implicit conversion
Answer: C,D
Explanation:
The statements regarding data type conversions and the treatment of literals in SQL expressions involve understanding implicit and explicit data conversions in Oracle SQL.
* Statement A is true as invoice_date > '01-02-2019' involves an implicit conversion of the string literal to a date type, based on the NLS_DATE_FORMAT setting, assuming the format matches.
* Statement E is true because, similarly to A, invoice_date = '15-march-2019' involves an implicit conversion where the string is automatically converted to a date type according to the Oracle NLS_DATE_FORMAT or an assumed default date format.
* Statements B, C, and D involve incorrect or misleading information:
* B (qty_sold = '05549821') is misleading and potentially incorrect as leading zeros in a numeric context do not typically require explicit conversion but the presence of spaces might suggest a need for trimming rather than numeric conversion.
* C (CONCAT(qty_sold, invoice_date)) would indeed require explicit conversion because CONCAT expects string types, and thus numerical and date values must be explicitly converted
* to strings before concatenation.
* D (qty_sold BETWEEN '101' AND '110') uses implicit conversion where the string literals '101' and '110' are implicitly converted to numbers if qty_sold is a numeric type.
NEW QUESTION # 188
View the Exhibit and examine the structure of the BOOKStable.
The BOOKS table contains details of 100 books.
Examine the commands executed and their outcome:
Which statement is true?
- A. Both ROLLBACKcommands restore the 100 rows that were deleted.
- B. Both ROLLBACKcommands restore the 101 rows that were deleted.
- C. The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.
- D. The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.
Answer: D
NEW QUESTION # 189
Evaluate these commands which execute successfully:
Which two statements are true about the ORD_ITEMStable and the ORD_SEQsequence?
- A. Any user inserting rows into table ORD_ITEMSmust have been granted access to sequence ORD_SEQ.
- B. Column ORD_NOgets the next number from sequence ORD_SEQwhenever a row is inserted into ORD_ITEMSand no explicit value is given for ORD_NO.
- C. If sequence ORD_SEQis dropped then the default value for column ORD_NOwill be NULL for rows inserted into ORD_ITEMS.
- D. Sequence ORD_SEQis guaranteed not to generate duplicate numbers.
- E. Sequence ORD_SEQcycles back to 1 after every 5000 numbers and can cycle 20 times.
Answer: A,D
NEW QUESTION # 190
Examine the description of the ORDERStable:
Examine the description of the INVOICEStable:
Examine this query:
Which three rows will it return? (Choose three.)
5 01-MAR-2019
- A.
- B. 1 <null>
- C. 2 <null>
- D. 3 <null>
- E. 4 01-FEB-2019
- F. 5 <null>
- G. 3 01-JAN-2019
Answer: A,D,F
NEW QUESTION # 191
Which statement adds a column called SALARYto the EMPLOYEEStable having 100 rows, which cannot contain null?
ALTER TABLE EMPLOYEES
- A. ADD SALARY NUMBER(8,2) NOT NULL;
- B. ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
ALTER TABLE EMPLOYEES - C. ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
ALTER TABLE EMPLOYEES - D. ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
ALTER TABLE EMPLOYEES
Answer: B
NEW QUESTION # 192
Which three statements are true? (Choose three.)
- A. Both USER_OBJECTSand CATviews provide the same information about all objects that are owned by the user.
- B. The USER_CONS_COLUMNSview should be queried to find the names of columns to which constraints apply.
- C. The data dictionary views consist of joins of dictionary base tables and user-defined tables.
- D. Views with the same name but different prefixes, such as DBA, ALLand USER, reference the same base tables from the data dictionary.
- E. The data dictionary is created and maintained by the database administrator.
- F. The usernames of all the users including database administrators are stored in the data dictionary.
Answer: B,D,F
Explanation:
Explanation
Explanation/Reference:
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm
NEW QUESTION # 193
You executed the following CREATE TABLE statement that resulted in an error:
SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename)); Identify two reasons for the error.
- A. Only one LONGcolumn can be used per table
- B. FOREIGN KEYdefined on the DEPT_IDcolumn must be at the table level only
- C. The PRIMARY KEYconstraint in the EMP_IDcolumn must have a name and must be defined at the table level only
- D. The FOREIGN KEYkeyword is missing in the constraint definition
- E. The NOT NULLconstraint on the ENAMEcolumn must be defined as the column level
Answer: A,E
NEW QUESTION # 194
View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
NameNull?Type
---------------------- -------------
EMPLOYEE_IDNOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAMENOT NULLVARCHAR2(25)
HIRE_DATENOT NULLDATE
JOB_IDNOT NULLVARCHAR2(10)
SALARYNUMBER(10,2)
COMMISSIONNUMBER(6,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_IDNUMBER(4)
DEPARTMENTS
NameNull?Type
---------------------- -------------
DEPARTMENT_IDNOT NULLNUMBER(4)
DEPARTMENT_NAMENOT NULLVARCHAR2(30)
MANAGER_IDNUMBER(6)
LOCATION_IDNUMBER(4)
You want to update EMPLOYEES table as follows:
You issue the following command:
SQL> UPDATE employees
SET department_id
(SELECT department_id
FROM departments
WHERE location_id = 2100),
(salary, commission)
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700;
What is outcome?
- A. It executes successfully but does not give the desired update
- B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
- C. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
- D. It executes successfully and gives the desired update
Answer: A
NEW QUESTION # 195
View the Exhibit and examine the data in the EMPLOYEES table.
Exhibit
You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:
What is the outcome?
- A. IT executes successfully and gives the correct output.
- B. It generates an error because the alias is not valid.
- C. It generates an error because the concatenation operator can be used to combine only two items.
- D. It executes successfully but does not give the correct output.
- E. It generates an error because the usage of the ROUNDfunction in the expression is not valid.
Answer: D
NEW QUESTION # 196
Examine the description of the EMPLOYEES table:
Which query requires explicit data type conversion?
- A. SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;
- B. SELECT join_date + '20' FROM employees;
- C. SELECT join_date || ' ' || salary FROM employees;
- D. SELECT join_date FROM employees WHERE join_date > '10-02-2018';
- E. SELECT salary + '120.50' FROM employees;
Answer: B
NEW QUESTION # 197
In which three situations does a new transaction always start? (Choose three.)
- A. when issuing a SELECT FOR UPDATEstatement after a CREATE TABLE AS SELECTstatement was issued in the same session
- B. when issuing a DML statement after a DML statement failed in the same session
- C. when issuing a CREATE TABLEstatement after a SELECTstatement was issued in the same session
- D. when issuing a TRUNCATEstatement after a SELECT statement was issued in the same session
- E. when issuing a CREATE INDEXstatement after a CREATE TABLEstatement completed successfully in the same session
- F. when issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session
Answer: A,E,F
NEW QUESTION # 198
Examine the description of EMPLOYEES table:
Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?
- A. SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;
- B. SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;
- C. SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;
- D. SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;
- E. SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,
- F. SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;
Answer: B,C,D
Explanation:
In Oracle Database 12c, when dealing with NULL values in arithmetic expressions, the functions NVL, NVL2
, and equivalent techniques using NULLIF or arithmetic operations with NULL can be crucial.
* Option A: SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;
* NULLIF(value1, value2) returns NULL if value1 is equal to value2, otherwise it returns value1.
In this case, if commission is NULL, it treats it as 0.
* This query correctly adds salary and commission (assuming 0 when commission is NULL) and checks if the total is at least 20000.
* Option B: SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >=
20000;
* NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL; otherwise, it returns expr3.
Here, if commission is not NULL, it uses commission, otherwise 0.
* This condition correctly calculates salary + commission (assuming commission as 0 if it is NULL) and checks if the total is at least 20000.
* Option D: SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;
* NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This query treats commission as 0 if it is NULL.
* It correctly sums salary and commission and compares the result against 20000.
Options C, E, and F contain either syntax errors or logical errors in handling NULLs and comparisons.
NEW QUESTION # 199
Examine the description of the PROMTIONS table:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used?
- A. SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY
1; - B. SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;
- C. SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:
- D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
- E. SELECT DISTINCT promo_category ||'has'|| promo_cost AS COSTS FROM promotions ORDER BY
1;
Answer: D,E
Explanation:
To display unique promotion costs in each promotion category, the correct queries that can be used are:
* C. SELECT DISTINCT promo_category ||' has '|| promo_cost AS COSTS FROM promotions ORDER BY 1;This query concatenates the promo_category with the literal ' has ' and promo_cost, giving a unique string for each combination of promo_category and promo_cost, which is what we are interested in when we want to list unique costs per category.
* D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;This query selects distinct combinations of promo_category and promo_cost, which is exactly what's required to display unique promotion costs in each category.
Options A, B, and E are incorrect:
* A is incorrect because it does not use the DISTINCT keyword to ensure uniqueness.
* B has incorrect syntax; the DISTINCT keyword should appear directly after SELECT and applies to all columns, not just one.
* E is syntactically incorrect and would not execute because the DISTINCT keyword is not used correctly.
It should not appear twice in the SELECT clause.
NEW QUESTION # 200
View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.
ORDERS_MASTER
ORDER_TOTAL
ORDER_ID
1 1000
2 2000
3 3000
4
MONTHLY_ORDERS
ORDER_TOTAL
ORDER_ID
2 2500
3
Evaluate the following MERGEstatement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?
- A. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.
- B. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.
- C. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.
- D. The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.
Answer: B
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION # 201
......
Oracle 1z1-071 exam is an essential certification for professionals who want to work with Oracle databases. 1z1-071 exam covers a wide range of topics related to SQL and Oracle databases and evaluates the candidate's ability to work with advanced SQL features. Oracle Database SQL certification is highly valued in the IT industry and provides a validation of the candidate's skills and knowledge in SQL and Oracle databases. Candidates can take the exam at any authorized testing center or online.
Authentic Best resources for 1z1-071 Online Practice Exam: https://www.dumpstillvalid.com/1z1-071-prep4sure-review.html
Get the superior quality 1z1-071 Dumps Questions from DumpStillValid: https://drive.google.com/open?id=1noAyvrFpgYLaHn-IU9j1FIZSWvJFXx3G
