1Z0-071 | Precise Oracle 1Z0-071 Exam Dumps Online

NEW QUESTION 1
Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.)
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 VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
• B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
• C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
• D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

Answer: C

NEW QUESTION 2
Examine the following query:
SQL> SELECT prod_id, amount_sold FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?

• A. It displays 5 percent of the products with the highest amount sold.
• B. It displays the first 5 percent of the rows from the SALES table.
• C. It displays 5 percent of the products with the lowest amount sold.
• D. It results in an error because the ORDER BY clause should be the last clause.

Answer: C

Explanation:
References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1

NEW QUESTION 3
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 table prefix is missing for the column names in the SELECT clause.
• B. The NATURAL JOIN clause is missing the USING clause.
• C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM 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 4
Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

• A. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
• B. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
• C. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
• D. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Answer: AB

NEW QUESTION 5
View the Exhibit and examine the structure of the EMPLOYEES table.
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_NAME of the managers and the second column would have LAST_NAME of the employees.

Which SQL statement would you execute?

• A. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eON m.employee_id = e.manager_id WHERE m.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.managerjd=100;
• C. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eON e.employee_id = m.manager_id WHERE m.manager_id=100;
• D. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id AND e.managerjd=100;

Answer: B

NEW QUESTION 6
View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. EMPLOYEES
NameNull?Type
---------------------- ------------- EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) HIRE_DATENOT NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(10,2) COMMISSIONNUMBER(6,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4) DEPARTMENTS
NameNull?Type
---------------------- -------------
DEPARTMENT_IDNOT NULLNUMBER(4) DEPARTMENT_NAMENOT NULLVARCHAR2(30) MANAGER_IDNUMBER(6) LOCATION_IDNUMBER(4)
You want to update EMPLOYEES table as follows: You issue the following command:
SQL> UPDATE employees SET department_id = (SELECT department_id FROM departments
WHERE location_id = 2100), (salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700; What is outcome?

• A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
• B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
• C. It executes successfully and gives the desired update
• D. It executes successfully but does not give the desired update

Answer: D

NEW QUESTION 7
Examine the structure of the EMPLOYEES table. NameNull?Type
---------------------- ------------ EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) EMAILNOT NULLVARCHAR2(25) PHONE NUMBERVARCHAR2(20) HIRE_DATENOT NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(8,2) COMMISSION_PCTNUMBER(2,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

• A. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
• B. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
• C. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
• D. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

Answer: B

NEW QUESTION 8
View the Exhibits and examine PRODUCTS and 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 executes successfully and produces the required output.
• B. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
• C. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
• D. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.

Answer: A

NEW QUESTION 9
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 row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
• D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Answer: B

NEW QUESTION 10
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

• A. SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• B. SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
• D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers

Answer: B

NEW QUESTION 11
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. (Choose all that apply.)

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN'
------------------------------------- SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result.

• A. UNION
• B. MINUS
• C. INTERSECT
• D. UNION ALL

Answer: AD

NEW QUESTION 12
Which statement is true regarding the default behavior of the ORDER BY clause?

• A. In a character sort, the values are case-sensitive.
• B. NULL values are not considered at all by the sort operation.
• C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
• D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

Answer: A

NEW QUESTION 13
View the exhibit for the structure of the STUDENT and FACULTY tables. STUDENT
NameNull?Type
-------------------------------------------------- STUDENT_IDNOT NULLNUMBER(2) STUDENT_NAMEVARCHAR2(20) FACULTY_IDVARCHAR2(2) LOCATION_IDNUMBER(2) FACULTY
NameNull?Type
-------------------------------------------------- FACULTY_IDNOT NULLNUMBER(2) FACULTY_NAMEVARCHAR2(20) LOCATION_IDNUMBER(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. Only statement 1 executes successfully and gives the required result.
• C. Both statements 1 and 2 execute successfully and give different results.
• D. Both statements 1 and 2 execute successfully and give the same required result.

Answer: B

NEW QUESTION 14
Which statement correctly grants a system privilege?

• A. GRANT CREATE VIEWON table1 TOuser1;
• B. GRANT ALTER TABLETO PUBLIC;
• C. GRANT CREATE TABLETO user1, user2;
• D. GRANT CREATE SESSIONTO ALL;

Answer: C

NEW QUESTION 15
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

• A. It produces an error because subqueries cannot be used with the CASE expression.
• B. It shows COST_REMARK for all the promos in the promo category ‘TV’.
• C. It shows COST_REMARK for all the promos in the table.
• D. It produces an error because the subquery gives an error.

Answer: C

NEW QUESTION 16
......