1z0-071 Practice Test

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.

Which two statements are true regarding the SQL GROUP BY clause?

A.

You can use a column alias in the GROUP BY clause.

B.

Using the WHERE clause after the GROUP BY clause excludes rows after creating
groups.

C.

The GROUP BY clause is mandatory if you are using an aggregating function in the
SELECT clause.

D.

Using the WHERE clause before the GROUP BY clause excludes rows before creating
groups.

E.

If the SELECT clause has an aggregating function, then columns without an aggregating
function in the SELECT clause should be included in the GROUP BY clause.

View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best
answer.)

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_idFROM order_itemsGROUP BY order_idHAVING
SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))FROM order_items
GROUP BY order_id);

B.

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX
(SUM(unit_price*quantity)FROM order_items) GROUP BY order_id);

C.

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) =
MAX(unit_price*quantity)GROUP BY order_id);

D.

SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT
MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column
is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

A.

 The NEW_ODRDERS table would not get created because the DEFAULT value cannot
be specified in the column definition.

B.

The NEW_ODRDERS table would get created and only the NOT NULL constraint
defined on the specified columns would be passed to the new table.

C.

The NEW_ODRDERS table would not get created because the column names in the
CREATE TABLE command and the SELECT clause do not match.

D.

The NEW_ODRDERS table would get created and all the constraints defined on the
specified columns in the ORDERS table would be passed to the new table.

Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow
for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME
PROJECT_TASK
Which two statements are true in this scenario?

A.

The ERD must have a 1:M relationship between the STUDENTS and PROJECTS
entities.

B.

The ERD must have a M:M relationship between the STUDENTS and PROJECTS
entities that must be resolved into 1:M relationships.

C.

STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the
PROJECTS entity.

D.

PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the
STUDENTS entity.

E.

An associative table must be created with a composite key of STUDENT_ID and
PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.