[Nov 20, 2021] New Real 1z1-071 Exam Dumps Questions
Pass Your 1z1-071 Exam Easily with Accurate Oracle Database SQL PDF Questions
Oracle 1z0-071 Practice Test Questions, Oracle 1z0-071 Exam Practice Test Questions
The Oracle 1Z0-071 exam is required for obtaining several certifications provided by the Oracle University, namely Oracle PL/SQL Developer Certified Associate, Oracle Database 12c Administrator Certified Associate, and Oracle Database SQL Certified Associate. This test validates the candidates’ competency in Oracle SQL as well as PL/SQL technologies.
NEW QUESTION 76
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
-------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC".
Which query gives the required output?
- A. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';
- B. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))LIKE 'Mc%';
- C. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =INITCAP 'MC%';
- D. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)LIKE INITCAP ('MC%');
Answer: B
NEW QUESTION 77
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 the VALUES clause is not required with the subquery.
- B. The statement will execute and a new row will be inserted in the SALES table.
- C. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
- D. The statement will fail because a subquery cannot be used in a VALUES clause.
Answer: B
NEW QUESTION 78
Examine the structure of the PROGRAMStable:
Which two SQL statements would execute successfully?
SELECT NVL(ADD_MONTHS(END_DATE,1)SYSDATE)
- A. FROM programs;
SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') - B. FROM programs;
- C. FROM programs;
SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start-date,end_date)),'Ongoing') - D. FROM programs;
SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))
Answer: A,B
NEW QUESTION 79
These are the steps for a correlated subquery, listed in random order:
The WHERE clause of the outer query is evaluated.
A candidate row is fetched from the table specified in the outer query.
This is repeated for the subsequent rows of the table, until all the rows are processed.
Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?
- A. 4, 2, 1, 3
- B. 2, 4, 1, 3
- C. 2, 1, 4, 3
- D. 4, 1, 2, 3
Answer: B
Explanation:
http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html
NEW QUESTION 80
Examine the structure of the BOOKS_ TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?
- A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
- B. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
- C. It displays details for only members A101and A102 who have borrowed before today with RM as TRANSACTION_TYPE.
- D. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
Answer: A
NEW QUESTION 81
Which three statements are true about Data Manipulation Language (DML)?
- A. DELETE statements can remove multiple rows based on multiple conditions.
- B. INSERT statements can insert NULLS explicitly into a column.
- C. UPDATE statements can have different subqueries to specify the values for each updated column.
- D. INSERT INTO...SELECT...FROM statements automatically commit.
- E. DML statements require a primary key be defined on a table.
Answer: A,B,C
NEW QUESTION 82
View the exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement?
- A. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
- B. listing of customers who do not have a credit limit and were born before 1980
- C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
- D. finding the number of customers, in each city, who's marital status is 'married'.
- E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: A,E
NEW QUESTION 83
Which three statements are true regarding subqueries? (Choose three.)
- A. Main query and subquery can get data from different tables.
- B. Main query and subquery must get data from the same tables.
- C. Subqueries can contain GROUP BY and ORDER BY clauses.
- D. Multiple columns or expressions can be compared between the main query and subquery.
- E. Only one column or expression can be compared between the main query and subquery.
- F. Subqueries can contain ORDER BY but not the GROUP BY clause.
Answer: A,C,D
Explanation:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html
NEW QUESTION 84
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? (Choose two.)
- A. ORDER BY DEPT_ID;
- B. ORDER BY 'b';
- C. ORDER BY DEPT_NAME;
- D. ORDER BY 3;
Answer: A,D
NEW QUESTION 85
View the Exhibits and examine PRODUCTSand SALES tables.
Exhibit 1
Exhibit 2
You issue the following query to display product name the number of times the product has been sold:
What happens when the above statement is executed?
- A. The statement produces an error because ITEM_CNTcannot be displayed in the outer query.
- B. The statement executes successfully and produces the required output.
- C. The statement produces an error because the GROUP BYclause cannot be used in a subquery in the FROMclause.
- D. The statement produces an error because a subquery in the FROMclause and outer-joins cannot be used together.
Answer: B
NEW QUESTION 86
Examine this statement:
SELECT1 AS id,' John' AS first_name, NULL AS commission FROM dual
INTERSECT
SELECT 1,'John' null FROM dual ORDER BY 3;
What is returned upon execution?[
- A. 1 ROW
- B. 0 rows
- C. 2 rows
- D. An error
Answer: A
NEW QUESTION 87
View the Exhibit and examine the description of the ORDERS table.
Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)
- A. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
- B. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
- C. WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21
2003','MON DD YYYY') ) - D. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
Answer: B,D
NEW QUESTION 88
Which three statements execute successfully?
A)
B)
C)
D)
E)
F)
G)
- A. Option D
- B. Option B
- C. Option F
- D. Option E
- E. Option G
- F. Option A
- G. Option C
Answer: E,F,G
NEW QUESTION 89
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column
is the
PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?
- A. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
- B. The NEW_IDRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
- C. The NEW_IDRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
- D. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
Answer: B
NEW QUESTION 90
Examine the structure of the INVOICE table.
NameNull?Type
--------------------------------------------------
INV_NONOT NULLNUMBER(3)
INV_DATEDATE
INV_AMTNUMBER(10,2)
Which two SQL statements would execute successfully?
- A. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
- B. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;
- C. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
- D. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
Answer: C,D
NEW QUESTION 91
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:
Reference:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm
NEW QUESTION 92
......
1z1-071 Certification Exam Dumps Questions in here: https://drive.google.com/open?id=1X-dg_lBaXfu2l9Bhjq4MIwQxkFmoFQ3i
Updated 1z1-071 Exam Practice Test Questions: https://www.ipassleader.com/Oracle/1z1-071-practice-exam-dumps.html