Oracle Database: SQL Fundamentals I (1Z0-051) Free Practice Test

Question 1

Examine the structure of the SHIPMENTS table:

You want to generate a report that displays the PO_ID and the penalty amount to be paid if
the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:

Which statement is true regarding the above commands?

A. Both execute successfully and give correct results.

B. Only the second query executes successfully but gives a wrong result.

C. Only the first query executes successfully and gives the correct result.

D. Only the first query executes successfully but gives a wrong result.

E. Only the second query executes successfully and gives the correct result.

Correct Answer: C

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

Question 2

Which two statements are true regarding the USING clause in table joins?(Choose two.)

A. It can be used to join tables that have columns with the same name and compatible data types.

B. It can be used to join a maximum of three tables.

C. It can be used to access data from tables through equijoins as well as nonequijoins.

D. It can be used to restrict the number of columns used in a NATURAL join.

Correct Answer: A,D

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

Question 3

Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

A. Both USING and ON clause can be used for equijoins and nonequijoins

B. A maximum of one pair of columns can be joined between two tables using the ON clause

C. The ON clause can be used to join tables on columns that have different names but compatible data types

D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause

Correct Answer: C,D

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

Question 4

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which MERGE statement is valid?

A. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);

B. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET

C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET

D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);

E. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET

F. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET

G. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);

H. name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);

Correct Answer: C

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

Question 5

SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?

A. prefix the column in the USING clause with the table alias

B. remove the table alias prefix from the WHERE clause

C. prefix the column in the ORDER BY clause with the table alias

D. replace the condition
"d.department_id NOT IN (10,40)"
in the WHERE clause with
"d.department_id <> 10 AND d.department_id <> 40"

E. remove the table alias from the SELECT clause

F. remove the ORDER BY clause

Correct Answer: B

Question 6

See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

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:
Exhibit:

What would be the outcome?

A. It executes successfully and gives the required result

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 generates an error because NULL cannot be specified as a return value

Correct Answer: A

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

Question 7

You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?

A. It gives an error because the GROUP BY expression is not valid.

B. It executes successfully and gives the correct output.

C. It gives an error because the HAVING clause is not valid.

D. It executes successfully but does not give the correct output.

Correct Answer: C

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

Question 8

The SQL statements executed in a user session as follows: Exhibit:

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

A. Both the DELETE statements and the UPDATE statement are rolled back

B. Only the seconds DELETE statement is rolled back

C. No SQL statements are rolled back

D. Only the DELETE statements are rolled back

E. The rollback generates an error

Correct Answer: C,E

Question 9

Which object privileges can be granted on a view?

A. DELETE, INSERT, SELECT, UPDATE

B. ALTER, DELETE, INSERT, SELECT

C. none

D. DELETE, INSERT,SELECT

Correct Answer: A

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

Question 10

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)

A. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION;

B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;

C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;

D. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;

Correct Answer: B,D

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

Question 11

Examine the structure of the CUSTOMERS table:

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result? (Choose two.)

A. right outer-join with self-join

B. full outer-join with self-join

C. self-join

D. subquery

E. left outer-join with self-join

Correct Answer: C,D

Question 12

Which two are true about aggregate functions? (Choose two.)

A. You can use aggregate functions in any clause of a SELECT statement.

B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.

C. You can use aggregate functions on a table, only by grouping the whole table as one single group.

D. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

E. You cannot group the rows of a table by more than one column while using aggregate functions.

F. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

Correct Answer: A,F

Question 13

View the Exhibit and examine the structure and data in the INVOICE table.

Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.)

A. inv_date > '01-02-2008' : uses implicit conversion

B. inv_date = '15-february-2008' : uses implicit conversion

C. inv_no BETWEEN '101' AND '110' : uses implicit conversion

D. CONCAT(inv_amt,inv_date) : requires explicit conversion

E. inv_amt ='0255982' : requires explicit conversion

Correct Answer: B,C

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

Question 14

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

A. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause

B. Numeric values are displayed from the maximum to the minimum value if they have decimal positions

C. In a character sort, the values are case-sensitive

D. NULL values are not considered at all by the sort operation

Correct Answer: C

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

Welcome to TestSimulate

Oracle Database: SQL Fundamentals I (1Z0-051) Free Practice Test