Use the best ways of preparing for 1z1-071 Exam Dumps with 2Pass4sure Oracle 1z1-071 dump PDF [2021]
Oracle 1z1-071 exam candidates will surely pass the Exam if they consider the 1z1-071 dumps learning material presented by 2Pass4sure.
NEW QUESTION 50
Which three statements are true about multiple row subqueries? (Choose three.)
- A. They can contain GROUP BY clauses.
- B. They can contain HAVINGclauses.
- C. They cannot contain a subquery.
- D. Two or more values are always returned from the subquery.
- E. They can return multiple columns.
Answer: A,B,E
NEW QUESTION 51
Examine the structure of the BOOKS_TRANSACTIONStable:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
- A. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
- B. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
- C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
- D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
Answer: D
NEW QUESTION 52
Which two statements are true about a self join?
- A. It must be a full outer jolin.
- B. It can be an inner join.
- C. It must be an equijoin.
- D. It can be a left outer join.
- E. The join key column must have an index.
Answer: B,E
NEW QUESTION 53
You execute these commands:
CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));
INSERT INTO customers VALUES (1,'Custmoer1 ');
SAVEPOINT post insert;
INSERT INTO customers VALUES (2, 'Customer2 ');
<TODO>
SELECTCOUNT (*) FROM customers;
Which two, used independently, can replace <TODO> so the query retums 1?
- A. COMMIT;
- B. ROLIBACK TO SAVEPOINT post_ insert;
- C. ROLLEBACK TO post_ insert;
- D. ROLLBACK;
- E. CONOIT TO SAVEPOINT post_ insert;
Answer: B,C
NEW QUESTION 54
What is true about non-equijoin statement performance?
- A. Table aliases can improve performance.
- B. The BETWEEN condition always performs less well than using the >= and <= conditions.
- C. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
- D. The BETWEEN condition always performs better than using the >= and <= conditions.
- E. The join syntax used makes no difference to performance.
Answer: A
NEW QUESTION 55
Which statement is true regarding the INTERSECT operator?
- A. It ignores NULLs.
- B. The number of columns and data types must be identical for all SELECT statements in the query.
INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query. - C. The names of columns in all SELECT statements must be identical.
- D. Reversing the order of the intersected tables alters the result.
Answer: B
NEW QUESTION 56
Which three statements are true about multiple row subqueries?
- A. They can contain GROUP BY clauses.
- B. They can contain HAVING clauses.
- C. Two or more values are always returned from the subquery.
- D. They cannot contain subquery.
- E. They can return multiple columns.
Answer: A,B,C
NEW QUESTION 57
Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?
- A. Select deptno. no LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employee-list FROM emp ORDER BY emp
- B. Select deptno. LISTAGG(ename, ',') WITHIN GROUP AS employee-list
FROM emp
GROUP BY emp - C. Select deptno. LISTAGG(ename, ',') WITHIN GROUP AS employee-list
FROM emp
ORDER BY ename; - D. Select deptno. no LISTAGG(ename, ',') WITHIN GROUP (GROUP BY deptno) AS employee-list FROM emp ORDER BY emp
Answer: A
NEW QUESTION 58
Examine the data in the CUST_NAME column of the CUSTOMERS table.
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
Which two queries give the required output?
- A. SELECT LPAD(SUBSTR(cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',1,-2)<>0;
- B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',-1,2)<>0;
- C. SELECT LPAD(SUBSTR(cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;
- D. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;
Answer: B,D
NEW QUESTION 59
Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER (4) );
CREATE TABLE ord_items
(ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );
The above command fails when executed. What could be the reason?
- A. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
- B. The BETWEEN clause cannot be used for the CHECK constraint.
- C. The CHECK constraint cannot be placed on columns having the DATE data type.
- D. SYSDATE cannot be used with the CHECK constraint.
Answer: D
NEW QUESTION 60
See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONS table,
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 generates an error because NULL cannot be specified as a return value.
- B. It generates an error because CASE cannot be used with group functions.
- C. It generates an error because multiple conditions cannot be specified for the WHEN clause.
- D. It executes successfully and gives the required result.
Answer: D
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 61
View the Exhibit and examine the data in the PRODUCTS table.
You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
Which statement is true?
- A. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.
- B. It executes successfully but returns no result.
- C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
- D. It executes successfully and returns the required result.
Answer: B
NEW QUESTION 62
Which statement is true regarding the INTERSECT operator?
- A. It ignores NULLs.
- B. The names of columns in all SELECT statements must be identical.
- C. Reversing the order of the intersected tables alters the result.
- D. The number of columns and data types must be identical for all SELECT statements in the query.
Answer: D
Explanation:
INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
NEW QUESTION 63
Examine the description of the CUSTOMERS table:
You want to display details of all customers who reside in cities starting with the letter D followed by at least two character.
Which query can be used?
- A. SELECT * FROM customers WHERE city LIKE'D %';
- B. SELECT * FROM customers WHERE city ='%D_';
- C. SELECT * FROM customers WHERE city LIKE'D_';
- D. SELECT * FROM customers WHERE city ='D_%';
Answer: A
NEW QUESTION 64
You must create a table for a banking application.
One of the columns in the table has these requirements:
A column to store the duration of a short team loan
The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
The maximum loan period is 30 days.
Interest must be calculated based on the number of days for which the loan remains unpaid.
Which data type would you use?
- A. NUMBER
- B. TIMESTAMP
- C. INTERVAL YEAR TO MONTH
- D. DATE
- E. INTERVAL DAY TO SECOND
Answer: E
NEW QUESTION 65
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_date, 'Pending', 'Incomplete')FROM invoice;
- C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
- D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;
Answer: B,C
NEW QUESTION 66
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 executes successfully and returns the correct result.
- B. It executes successfully but does not return the correct result.
- C. It generates an error because TO_CHAR should be replaced with TO_DATE.
- D. It generates an error because fm and double quotation marks should not be used in the format string.
- E. It generates an error because rrrr should be replaced by rr in the format string.
Answer: A
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 67
View the exhibit and examine the structure of the EMPLOYEEStable.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAMEof the managers and the second column would have LAST_NAMEof the employees.
Which SQL statement would you execute?
- A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id and AND e.manager_id = 100 - B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id = 100; - C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id = 100; - D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id = 100;
Answer: B
NEW QUESTION 68
Evaluate the following query
What is the correct output of the above query?
- A. +00-300, +54-02, +00 11:12:10.123457
- B. +00-300, +00-650, +00 11:12:10.123457
- C. +25-00, +54-02, +00 11:12:10.123457
- D. +25-00, +00-650, +00 11:12:10.123457
Answer: C
NEW QUESTION 69
Examine the SQL statement used to create the TRANSACTION table.
SQL > CREATE TABLE transaction
(trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE,
End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?
- A. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')
- B. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
- C. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
- D. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
Answer: B
NEW QUESTION 70
Which statement is true regarding the INTERSECToperator?
- A. Reversing the order of the intersected tables alters the result.
- B. The names of columns in all SELECTstatements must be identical.
- C. It ignores NULLvalues.
- D. The number of columns and data types must be identical for all SELECTstatements in the query.
Answer: C
NEW QUESTION 71
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. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
- B. finding the number of customers, in each city, who's marital status is 'married'.
- C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
- D. listing of customers who do not have a credit limit and were born before 1980
- E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
Answer: A,E
NEW QUESTION 72
Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)
- A. The CUST_CREDIT_LIMIT column would be able to store decimal values.
- B. The TRANS_VALIDITY column would have a maximum size of one character.
- C. The CUST_STATUS column would store exactly one character.
- D. The TRANS_VALIDITY column would give an error.
- E. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
- F. The CUST_STATUS column would give an error.
Answer: C,D
NEW QUESTION 73
......
Full 1z1-071 Practice Test and 305 unique questions with explanations waiting just for you, get it now: https://drive.google.com/open?id=17B370ixuIFPYQbScSlKoLekKVhL6LRA0
Accurate & Verified Answers As Seen in the Real Exam here: https://www.2pass4sure.com/Oracle-PL-SQL-Developer-Certified-Associate/1z1-071-actual-exam-braindumps.html