[Jan 02, 2022] 1z1-071 Exam Dumps PDF Guaranteed Success with Accurate & Updated Questions
Pass 1z1-071 Exam - Real Test Engine PDF with 305 Questions
NEW QUESTION 23
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTSand TIMEStables.
The PROD_IDcolumn is the foreign key in the SALEStable referencing the PRODUCTStable.
The CUST_IDand TIME_IDcolumns are also foreign keys in the SALEStable referencing the CUSTOMERS and TIMEStables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true?
- A. The NEW_SALEStable would get created and all the NOTNULLconstraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- B. The NEW_SALEStable would not get created because the DEFAULTvalue cannot be specified in the column definition.
- C. The NEW_SALEStable would get created and all the FOREIGNKEYconstraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- D. The NEW_SALEStable would not get created because the column names in the CREATETABLE command and the SELECTclause do not match.
Answer: A
NEW QUESTION 24
Which statement is true about the Oracle SQL, DELETEand TRUNCATEstatements?
- A. DELETEand TRUNCATEstatements remove all indexes for the tables on which they are performed.
- B. DELETEbut not TRUNCATEstatement can be used to selectively remove rows from a table.
- C. DELTEand TRUNCATEstatements can have a rollback done to restore data into a table.
- D. DELETE but not TRUNCATEstatement can be used to remove data from selective columns and rows of a table.
Answer: B
NEW QUESTION 25
What is true about non-equijoin statement performance?
- A. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
- B. The BETWEEN condition always performs less well than using the >= and <= conditions.
- C. Table aliases can improve performance.
- D. The BETWEEN condition always performs better than using the >= and <= conditions.
- E. The join syntax used makes no difference to performance.
Answer: C
Explanation:
Explanation/Reference: https://www.academia.edu/17342225/SQL_notes
NEW QUESTION 26
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. SYSDATE cannot be used with the CHECK constraint.
- C. The CHECK constraint cannot be placed on columns having the DATE data type.
- D. The BETWEEN clause cannot be used for the CHECK constraint.
Answer: B
NEW QUESTION 27
Which statement is true regarding the default behavior of the ORDER BY clause?
- A. Numeric values are displayed in descending order if they have decimal positions.
- B. Column aliases can be used in the ORDER BY clause.
- C. NULLs are not included in the sort operation.
- D. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.
- E. In a character sort, the values are case-sensitive.
Answer: E
Explanation:
The ORDER BY clause performs case sensitive sorting with character values.
NEW QUESTION 28
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)
- A. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
- B. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
- D. The values in all columns would be returned in descending order.
- E. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
Answer: A,C
NEW QUESTION 29
Which two statements are true regarding a SAVEPOINT? (Choose two.)
- A. Rolling back to a SAVEPOINTcan undo a CREATE INDEX statement
- B. Only one SAVEPOINTmay be issued in a transaction
- C. Rolling back to a SAVEPOINTcan undo a DELETEstatement
- D. A SAVEPOINTdoes not issue a COMMIT
- E. Rolling back to a SAVEPOINT can undo a TRUNCATEstatement
Answer: C,D
NEW QUESTION 30
View the Exhibit and examine the structure in the EMPLOYEES tables.
Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?
- A. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
- B. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
- C. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
- D. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
Answer: A
NEW QUESTION 31
Examine the description of the EMPLOYEES table:
Which two queries return rows for employees whose manager works in a different department?
- A. SELECT emp. *
FROM employees emp
JOIN employees mgr
ON emp. manager_ id = mgr. employee_ id
AND emp. department_ id<> mgr.department_ id; - B. SELECT emp.*
FROM employees emp
LEFT JOIN employees mgr
ON emp.manager_ id = mgr.employee_ id
AND emp. department id < > mgr. department_ id; - C. SELECT emp.*
FROM employees emp
WHERE NOT EXISTS (
SELECT NULL
FROM employees mgr
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id<>mgr.department_id
); - D. SELECT emp. *
FROM employees emp
RIGHT JOIN employees mgr
ON emp.manager_ id = mgr. employee id
AND emp. department id <> mgr.department_ id
WHERE emp. employee_ id IS NOT NULL; - E. SELECT emp. *
FROM employees emp
WHERE manager_ id NOT IN (
SELECT mgr.employee_ id
FROM employees mgr
WHERE emp. department_ id < > mgr.department_ id
);
Answer: A,D
NEW QUESTION 32
View the Exhibit and examine the structure of CUSTOMERStable.
Using the CUSTOMERStable, you need to generate a report that shows an increase in the credit limit by
15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
- A. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
- B. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"
FROM customers; - C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"
FROM customers; - D. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers;
Answer: D
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,
ename VARCHAR2(15),
salary NUMBER (8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));
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_PK?
- A. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
- B. It would be automatically enabled and immediate.
- C. It would be automatically enabled and deferred.
- D. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
Answer: D
NEW QUESTION 34
View the exhibit and examine the structure of the CUSTOMERStable.
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 number of customers, in each city, who's marital status is 'married'.
- D. finding the average credit limit of male customers residing in 'Tokyo'or 'Sydney'
- 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 35
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 executes successfully and gives the required result.
- C. It generates an error because multiple conditions cannot be specified for the WHEN clause.
- D. It generates an error because CASE cannot be used with group functions.
Answer: B
Explanation:
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 36
Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.
- A. ORDER BY CUSTNO
- B. ORDER BY"CUST NO"
- C. ORDER BY "Last Name"
- D. ORDERBY2, 1
- E. ORDER BY 2, cust_id
Answer: C,D,E
NEW QUESTION 37
Which statement is true about transactions?
- A. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
- B. Each Data Definition Language (DDL) statement executed forms a single transaction.
- C. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
- D. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
Answer: B
Explanation:
References:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038
NEW QUESTION 38
The SQL statements executed in a user session are as follows:
Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
- A. Only the DELETE statements are rolled back.
- B. The rollback generates an error.
- C. No SQL statements are rolled back.
- D. Only the second DELETE statement is rolled back.
- E. Both the DELETE statements and the UPDATE statement are rolled back.
Answer: B,C
NEW QUESTION 39
......
Passing this Oracle exam means that you are on the way of getting the following credentials:
- Oracle Database 12c Administrator Certified Associate;
- Oracle Database SQL Certified Associate
Completing 1Z0-071 is enough to obtain the first certificate of the associate level, while for gaining the second one in the Oracle Database 12c Administrator track, passing the 1Z0-062 exam is a necessity. Anyway, having any of these certifications on your CV will show your employer your ability to apply SQL knowledge for the Oracle Database server maintenance.
Who should take the Oracle 1Z0-071: Oracle Database SQL Exam
This course is for software administrators, database designers, and system operators.
Get New 1z1-071 Certification Practice Test Questions Exam Dumps: https://www.exams-boost.com/1z1-071-valid-materials.html
Real 1z1-071 Exam Dumps Questions Valid 1z1-071 Dumps PDF: https://drive.google.com/open?id=1Bg1fz2pcRETaNkCAaBYoZH22BKwv6mD8