Latest [Jan 18, 2022] 100% Passing Guarantee - Brilliant 1z1-071 Exam Questions PDF
1z1-071 Certification – Valid Exam Dumps Questions Study Guide! (Updated 305 Questions)
NEW QUESTION 15
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_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' AND
SUBSTR(cust_first_name, -2, 1)='a'; - B. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)='a'; - C. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, -2, 1)='a'; - D. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
SUBSTR(cust_first_name, 1, -2)='a';
Answer: C
NEW QUESTION 16
View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
Evaluate the following MERGE statement:
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_MASTER table would contain the ORDER_IDs 1, 2 and 4.
- B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
- C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
- D. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
Answer: A
Explanation:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION 17
View the exhibit for the structure of the STUDENTand FACULTYtables.
STUDENT
Name Null? Type
------------------ ------------------- -------------
STUDENT_ID NOT NULL NUMBER(2)
STUDENT_NAME VARCHAR2(20)
FACULTY_ID VARCHAR2(2)
LOCATION_ID NUMBER(2)
FACULTY
Name Null? Type
- ----------------- ------------------- -------------
FACULTY_ID NOT NULL NUMBER(2)
FACULTY_NAME VARCHAR2(20)
LOCATION_ID NUMBER(2)
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?
- A. Only statement 2 executes successfully and gives the required result.
- B. Both statements 1 and 2 execute successfully and give the same required result.
- C. Both statements 1 and 2 execute successfully and give different results.
- D. Only statement 1 executes successfully and gives the required result.
Answer: D
NEW QUESTION 18
Which statement is true about transactions?
- A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
- B. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
- C. Each Data Definition Language (DDL) statement executed forms a single transaction.
- D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
Answer: C
Explanation:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038
NEW QUESTION 19
Which two statements are true about the COUNT function?
- A. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
- B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column
- C. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
- D. It can only be used for NUMBER data types.
- E. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.
Answer: A,B
NEW QUESTION 20
View the Exhibit and examine the structure of the ORDER_ITEMS table.
You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?
- A. SELECT order_id
FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)
FROM order_items
GROUP BY order_id) - B. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity)
GROUP BY order_id); - C. SELECT order_id
FROM order_items
GROUP BY order_id
HAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)) FROM order_items GROUP BY order_id); - D. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)
FROM order_items) GROUP BY order_id);
Answer: C
NEW QUESTION 21
Which three tasks can be performed by DDL statements?
- A. providing an alternative name for a table
- B. creating multiple savepoints to enable partial rollback of a transaction
- C. modifying a table to prevent data that violate certain conditions from being entered in a column
- D. preventing any data modification to a table
- E. preventing data retrieval from a table outside of office hours
Answer: A,C,D
NEW QUESTION 22
The CUSTOMERStable has a CUST_CREDIT_LIMITcolumn of data type NUMBER.
Which two queries execute successfully? (Choose two.)
- A. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not
- B. SELECT NVL (cust_credit_limit * .15, 'Not Available') FROM customers;
- C. SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;
- D. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
- E. Available') FROM customers;
SELECT NVL2 (cust_credit_limit * .15, 'Not Available') FROM customers;
Answer: D,E
NEW QUESTION 23
View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) "Review" FROM employees; The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the firsts Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.
Which statement is true regarding this query?
- A. The query would not execute because the NEXT_DAY function accepts a string as argument.
- B. The query would execute but the output would give review dates that are Sundays.
- C. The query would not execute because date functions cannot be nested.
- D. The query would execute to give the desired output.
Answer: B
NEW QUESTION 24
Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)
- A. The HAVING clause is used to exclude one or more aggregated results after grouping data.
- B. The HAVING clause conditions can have aggregate functions.
- C. The HAVING clause conditions can use aliases for the columns.
- D. WHERE and HAVING clauses cannot be used together in a SQL statement.
- E. The WHERE clause is used to exclude rows before the grouping of data.
Answer: A,B,E
NEW QUESTION 25
Which statement is true about aggregate functions?
- A. The MAX and MIN functions can be used on columns with character data types.
- B. Aggregate functions can be nested to any number of levels.
- C. The AVG function implicitly converts NULLS to zero.
- D. Aggregate functions can be used in any clause of a SELECT statement.
Answer: B
NEW QUESTION 26
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 INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'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 UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')
Answer: B,E
NEW QUESTION 27
Examine the structure of the CUSTOMERS table: (Choose two.)
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
- A. Self-join
- B. Full outer-join with self-join
- C. Subquery
- D. Left outer-join with self-join
- E. Right outer-join with self-join
Answer: A,C
NEW QUESTION 28
You execute the SQL statement:
What is the outcome?
- A. It It succeeds and CITYcan contain only 'SEATTLE'or null for all rows.
- B. It succeeds and an index is created for CITIZEN_ID.
- C. It fails because the condition for the CNAMESconstraint is not valid.
- D. It fails because the NOT NULL and DEFAULToptions cannot be combined for the same column.
Answer: D
NEW QUESTION 29
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
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 NATURAL JOIN clause is missing the USING clause.
- B. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
- C. The table prefix is missing for the column names in the SELECT clause.
- D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
Answer: D
Explanation:
Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)
NEW QUESTION 30
Examine the description of the CUSTOMERS 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. Option A
- B. Option B
- C. Option D
- D. Option C
- E. Option E
Answer: A
NEW QUESTION 31
Examine the structure of the DEPARTMENTS table.
You execute the following command:
Which two statements are true?
- A. Unique key constraints defined on the COUNTRY column are removed.
- B. Synonyms existing of the DEPARTMENTS table would have to be re-created.
- 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. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.
Answer: A,D
NEW QUESTION 32
Examine this SQL statement:
Identify three order by clauses, any one of which can complete the query successfully.
- A. ORDER BY "CUST NO"
- B. ORDER BY "Last Name"
- C. ORDER BY 2, cust_id
- D. ORDER BY CUST_NO
- E. ORDER BY 2, 1
Answer: B,C,E
NEW QUESTION 33
Evaluate the following SQL statements that are issued in the given order: CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
- A. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
- B. It would be automatically enabled and deferred.
- C. It would be automatically enabled and immediate.
- D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
Answer: B,C,D
NEW QUESTION 34
Which three are true about subqueries?
- A. A subquery cannot be used in the select list.
- B. =ANY can only evaluate the argument against a subcjuery if it returns two or more values.
- C. A subquery can be used in a WHERE clause.
- D. A subquery can be used in a HAVING clause.
- E. <ANY returns true if the argument is less than the highest value returned by the subquery.
- F. A subquery cannot be used in a FROM clause.
- G. < any returns true if the argument is less than the lowest value returned by the subquery.
Answer: C,D,E
NEW QUESTION 35
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 3;
- B. ORDER BY DEPT_ID;
- C. ORDER BY DEPT_NAME;
- D. ORDER BY 'b';
Answer: A,B
NEW QUESTION 36
Examine this query:
SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;
What is the result?
- A. 0
- B. 1
- C. 2
- D. 3
- E. 4
Answer: A
Explanation:
Explanation/Reference:
Reference https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm
NEW QUESTION 37
......
1z1-071 are Available for Instant Access: https://www.ipassleader.com/Oracle/1z1-071-practice-exam-dumps.html
1z1-071 Dumps 2022 - New Oracle 1z1-071 Exam Questions: https://drive.google.com/open?id=1lmwBrM2QwLzaLgxvK4maFjfDnd3GkQYr