Real 1z0-071 Exam Questions are the Best Preparation Material
Practice on 2023 LATEST 1z0-071 Exam Updated 305 Questions
NEW QUESTION # 176
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 generates an error because fm and double quotation marks should not be used in the format string.
- B. It executes successfully and returns the correct result.
- C. It executes successfully but does not return the correct result.
- D. It generates an error because TO_CHAR should be replaced with TO_DATE.
- E. It generates an error because rrrr should be replaced by rr in the format string.
Answer: B
Explanation:
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.
NEW QUESTION # 177
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)
- A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
- B. During executing, the oracle server may read data from storage if the required data is not already in memory.
- C. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.
- D. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
Answer: B
NEW QUESTION # 178
See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONStable, you need to find out the average cost for all promos in the range $0-2000 and
$2000-5000 in category A.
You issue the following SQL statements:
What would be the outcome?
- A. It executes successfully and gives the required result.
- B. It generates an error because multiple conditions cannot be specified for the WHENclause.
- C. It generates an error because CASEcannot be used with group functions.
- D. It generates an error because NULLcannot be specified as a return value.
Answer: A
Explanation:
CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
NEW QUESTION # 179
Which three statements are true? (Choose three.)
- A. Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.
- B. The usernames of all users including database administrators are stored in the data dictionary.
- C. The data dictionary is created and maintained by the database administrator.
- D. Data dictionary views consist of joins of dictionary base tables and user-defined tables.
- E. The USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply.
- F. Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user.
Answer: A,B,E
Explanation:
Explanation
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm
NEW QUESTION # 180
Which three statements are true regarding the data types?
- A. Only one LONGcolumn can be used per table.
- B. A TIMESTAMPdata type column stores only time values with fractional seconds.
- C. The minimum column width that can be specified for a VARCHAR2data type column is one.
- D. The value for a CHARdata type column is blank-padded to the maximum defined column width.
- E. The BLOBdata type column is used to store binary data in an operating system file.
Answer: A,C,D
NEW QUESTION # 181
Examine the data in the ORD_ITEMS table:
Evaluate this query:
Which statement is true regarding the result?
- A. It returns an error because all the aggregate functions used in the HAVINGclause must be specified in the SELECT list.
- B. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
- C. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
- D. It returns an error because the HAVINGclause should be specified after the GROUP BYclause.
Answer: B
NEW QUESTION # 182
Examine the commands used to create DEPARTMENT_DETAILSand COURSE_DETAILS tables:
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?
- A. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);
- B. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);
- C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);
- D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);
Answer: A
NEW QUESTION # 183
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)
You executed this UPDATE statement:
Which statement is true regarding the execution?
- A. It would not execute because two tables cannot be referenced in a single UPDATE statement.
- B. It would execute and restrict modifications to the columns specified in the SELECT statement.
- C. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
- D. It would not execute because a SELECT statement cannot be used in place of a table name.
Answer: B
NEW QUESTION # 184
Examine this SQL statement:
Identify three order by clauses, any one of which can complete the query successfully.
- A. ORDER BY 2, cust_id
- B. ORDER BY CUST_NO
- C. ORDER BY "Last Name"
- D. ORDER BY "CUST NO"
- E. ORDER BY 2, 1
Answer: A,C,E
NEW QUESTION # 185
Which statement adds a column called SALARYto the EMPLOYEEStable having 100 rows, which cannot contain null?
- A. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) NOT NULL; - B. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL; - C. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL; - D. ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
Answer: B
NEW QUESTION # 186
View the Exhibit and examine the details of PRODUCT_INFORMATIONtable.
You have the requirement to display PRODUCT_NAMEfrom the table where the CATEGORY_IDcolumn has values 12or 13, and the SUPPLIER_IDcolumn has the value 102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?
- A. It would execute but the output would return no rows.
- B. It would execute and the output would display the desired result.
- C. It would not execute because the same column has been used in both sides of the ANDlogical operator to form the condition.
- D. It would not execute because the entire WHEREclause condition is not enclosed within the parentheses.
Answer: A
Explanation:
Explanation/Reference:
NEW QUESTION # 187
Examine the description of the CUSTOMERStable:
You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit is below 1000.
Examine this partial query:
Which two WHEREconditions give the required result? (Choose two.)
- A.
- B.
- C.
- D.
- E.
Answer: B,E
NEW QUESTION # 188
You execute the following commands:
For which substitution variables are you prompted for the input?
- A. Only 'hiredate'
- B. None, because no input required
- C. Both the substitution variables 'hiredate' and 'mgr_id\
- D. Only 'mgr_id'
Answer: C
NEW QUESTION # 189
View the Exhibit and examine the description for the SALES and CHANNELS tables.
You issued this SQL statement:
Which statement is true regarding the result? (Choose the best answer.)
- A. The statement will fail because a subquery cannot be used in a VALUES clause.
- B. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
- C. The statement will execute and a new row will be inserted in the SALES table.
- D. The statement will fail because the VALUES clause is not required with the subquery.
Answer: C
NEW QUESTION # 190
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 the output?
- A. ORDER BY DEPT_NAME;
- B. ORDER BY 3;
- C. ORDER BY DEPT_ID;
- D. ORDER BY 'b';
Answer: B,C
NEW QUESTION # 191
Examine this statement which executes successfully:
CREATE view emp80 AS
SELECT
FROM employees
WHERE department_ id = 80
WITH CHECK OPTION;
Which statement will violate the CHECK constraint?
- A. DELETE FROM emp80
WHERE department_ id = 90; - B. SELECT
FROM emp80
WHERE department_ id = 90; - C. SELECT
FROM emp80
WHERE department. id = 80; - D. UPDATE emp80
SET department. 1d =80;
WHERE department_ id =90;
Answer: D
NEW QUESTION # 192
Examine these statements:
CREATE TABLE alter_test (c1 VARCHAR2 (10) , c2 NUMBER (10);
INSERT INTO alter-test VALUES ('123', 123);
Which is true about modifying the columns in Alter_TEST?
Which is true about modifying the columns in ALTER_TEST?
- A. c2 can be changed to number (10) but c2 cannot be changed to varchab2 (10) .
- B. c2 can be changed to varchar2 (10) but el cannot be changed to number (10).
- C. c2 can be changed to number (5) but ci cannot be changed to varchar2 (5) .
- D. c1 can be changed to varchar2 (5) and c2 can be changed to number (13, 2) .
- E. c1 can be changed to number (10) and c2 can he changed to vahchar2 (10) .
Answer: D
NEW QUESTION # 193
View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have "Not Available" displayed.
Which SQL statement would produce the required result?
- A. SELECT NVL (TO_CHAR(cust_credit_limit*.15), 'Not Available') "NEW CREDIT" FROM customers
- B. SELECT TO_CHAR(NVL(cust_credit_limit*.15), 'Not Available')) "NEW CREDIT" FROM customers
- C. SELECT NVL (cust_credit_limit*.15, 'Not Available') "NEW CREDIT" FROM customers
- D. SELECT NVL (cust_credit_limit, 'Not Available')*.15 "NEW CREDIT" FROM customers
Answer: C
NEW QUESTION # 194
Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
- A. You can use column alias in the GROUP BY clause.
- B. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
- C. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
- D. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
- E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
Answer: C,E
NEW QUESTION # 195
......
Authentic 1z0-071 Exam Dumps PDF - Jul-2023 Updated: https://ensurepass.testkingfree.com/Oracle/1z0-071-practice-exam-dumps.html