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

Question 1

Evaluate the following SELECTstatement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition,
r_constraint_name, delete_rule, status,
FROM user_constraints
WHERE table_name = 'ORDERS';

Which two statements are true about the output? (Choose two.)

A. The STATUS column indicates whether the table is currently in use. B. In the second column, indicates a check constraint. C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint. D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Discussion 0

Correct Answer: B,D Vote an answer

Question 2

View the Exhibit and examine the structure of the PRODUCTS table.

You must display the category with the maximum number of items.
You issue this query:

What is the result?

A. It executes successfully and gives the correct output. B. It executes successfully but does not give the correct output. C. It generates an error because = is not valid and should be replaced by the IN operator. D. It generate an error because the subquery does not have a GROUP BY clause.

Discussion 0

Correct Answer: D Vote an answer

Question 3

Which two are true about the data dictionary? (Choose two.)

A. The SYS user owns all base tables and user-accessible views in the data dictionary. B. Base tables in the data dictionary have the prefix DBA_. C. The data dictionary is constantly updated to reflect changes to database objects, permissions, and data. D. All users have permissions to access all information in the data dictionary by default. E. All user actions are recorded in the data dictionary.

Discussion 0

Correct Answer: A,B Vote an answer

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

Question 4

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

A. Addition B. Finding the quotient C. Subtraction D. Raising to a power E. Finding the lowest value

Discussion 0

Correct Answer: A,D,E Vote an answer

Question 5

Examine the data in the INVOICEStable:

Examine the data in the CURRENCIEStable:

Which query returns the currencies in CURRENCIESthat are not present in INVOICES?

A.

B.

C.

D.

Discussion 0

Correct Answer: D Vote an answer

Question 6

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 VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10')) B. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014') C. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10)) D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

Discussion 0

Correct Answer: B Vote an answer

Question 7

Examine the description of the CUSTOMERStable:

CUSTNOis the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result? (Choose two.)

A. RIGHT OUTER JOIN with self join B. LEFT OUTER JOIN with self join C. FULL OUTER JOIN with self join D. self join E. subquery

Discussion 0

Correct Answer: D,E Vote an answer

Question 8

Which two statements are true regarding subqueries? (Choose two.)

A. A subquery can retrieve zero or more rows. B. A subquery can appear on either side of a comparison operator. C. Only two subqueries can be placed at one level. D. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement. E. A subquery can be used only in SQL query statements.

Discussion 0

Correct Answer: A,B Vote an answer

Question 9

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

A. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC). B. A TIMESTAMP data type column contains information about year, month, and day. C. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC). D. The CURRENT_TIMESTAMP function returns data without time zone information. E. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row.

Discussion 0

Correct Answer: A,C,D Vote an answer

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

Question 10

View the Exhibit and examine the structure of the ORDER_ITEMS table.

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?

A. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id; B. SELECT order_id
FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id) C. SELECT order_id
FROM order_items
GROUP BY order_id
HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id); D. SELECT order_id
FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity)
GROUP BY order_id;

Discussion 0

Correct Answer: C Vote an answer