Oracle Database SQL - 1z0-071 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

Which statement falls to execute successfully?

A. SELECT *
FROM employees e
JOIN department d
WHERE e.department_id=d.department_id
AND d.department_id=90; B. SELECT *
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
AND d.department_id=90; C. SELECT *
FROM employees e
JOIN departments d
ON d.departments_id=90
WHERE e.department_id=d.department_id; D. SELECT *
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id=90;

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 2

Examine this query:
SELECT employee_id,first_name,salary
FROM employees
WHERE hire_date>'&1';
Which two methods should you use to prevent prompting for a hire date value when this query is executed?

A. Use the DEFINE command before executing the query. B. Execute the SET VERIFY OFF command before executing the query. C. Replace'&1' with'&&1' in the query. D. Execute the SET VERIFY ON command before executing the query. E. Use the UNDEFINE command before executing the query. F. Store the query in a script and pass the substitution value to the script when executing it.

Discussion 0

Correct Answer: A,B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 3

Which two queries execute successfully?

A. SELECT CO ALESCE(100, 'A' ) FROM DUAL; B. SELECT NULLIF(NULL, 100) FROM DUAL; C. SELECT COALESCE(100, NULL, 200) FROM DUAL; D. SELECT NULLIF(100, 'A') FROM DUAL; E. SELECT NULLIF(100, 100) FROM DUAL;

Discussion 0

Correct Answer: A,B,C,E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 4

Evaluate the following SQL statement
SQL>SELECT promo_id, prom _category FROM promotions
WHERE promo_category='Internet' ORDER BY promo_id
UNION
SELECT promo_id, promo_category FROM Pomotions
WHERE promo_category = 'TV'
UNION
SELECT promoid, promocategory FROM promotions WHERE promo category='Radio' Which statement is true regarding the outcome of the above query?

A. It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators. B. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement. C. It executes successfully and displays rows in the descend ignore of PROMO CATEGORY. D. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 5

Examine the description of the employees table:

Examine these requirements:
1- Display the last name, date of hire and the number of years of service for each employee.
2. If the employee has been employed 5 or more years but less than 10, display -5+ years of service".
3. If the employee has been employed 10 or more years but less than 15, display "10+ years of service".
4. If the employee has been employed 15 or more years, display "15-*- years of service".
5. If none of these conditions matches, display "<5 years of service".
6. Sort the results by the hire_date column.
Which statement satisfies all the requirements?

A.

B.

C.

D.

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 6

Examine the description of the ORDER_ITEMS table:

Examine this incomplete query:
SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY <clause>; Which two can replace <clause> so the query completes successfully?

A. product_id B. total_paid C. quantity D. quantity * unit_price E. quantity, unit_price

Discussion 0

Correct Answer: D,E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 7

Which three statements are true about indexes and their administration in an Oracle database?

A. A UNIQUE and non-unique index can be created on the same table column B. A descending index is a type of function-based index C. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table. D. A DROP INDEX statement always prevents updates to the table during the drop operation E. An index can be created as part of a CREATE TABLE statement. F. If a query filters on an indexed column then it will always be used during execution of the query

Discussion 0

Correct Answer: A,B,E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 8

You execute this query:
SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),'MON' ),' dd"Monday for" fmMonth rrr') FROM DUAL; What is the result?

A. It executes successfully but does not return any result. B. It returns the date for the first Monday of the next month. C. It returns the date for the last Monday of the current month. D. It generates an error.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 9

Examine the description of the CUSTOMERS table:
Which three statements will do an implicit conversion?

A. SELECT * FROM customers WHERE customer_id='0001'; B. SELECT * FROM customers WHERE insert_date=DATE'2019-01-01'; C. SELECT * FROM customers WHERE TO_CHAR(customer_id)='0001'; D. SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE'2019-01-01'; E. SELECT * FROM customers WHERE customer_id=0001; F. SELECT * FROM customers WHERE insert_date'01-JAN-19';

Discussion 0

Correct Answer: A,C,F Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 10

Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)?

A. Every relationship between the two tables must be implemented in a Join condition. B. Rows from unrelated tables cannot be joined. C. Implementing a relationship between two tables might require joining additional tables. D. An inner join relates rows within the same table. E. Relating data from a table with data from the same table is implemented with a self join.

Discussion 0

Correct Answer: C,E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 11

You issued this command: DROP TABLE hr. employees;
Which three statements are true?

A. All indexes defined on HR, EMPLOYEES are dropped. B. ALL constraints defined on HR, EMPLOYEES are dropped. C. Sequences used to populate columns in the HR. EMPLOYEES table are dropped. D. Synonyms for HR EMPLOYEES are dropped. E. The HR. EMPLOYEES table may be moved to the recycle bin. F. Views referencing HR, EMPLOYEES are dropped.

Discussion 0

Correct Answer: A,B,E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 12

Which three statements are true about multiple row subqueries?

A. They can contain GROUP BY clauses. B. They can contain HAVING clauses. C. They can return multiple columns. D. Two or more values are always returned from the subquery. E. They cannot contain subquery.

Discussion 0

Correct Answer: A,B,C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).