View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)


You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result?

A.

The values in the SALARY column would be returned in descending order for all
employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

B.

The values in the FIRST_NAME column would be returned in ascending order for all
employees having the same value in the DEPARTMENT_ID column.

C.

The values in the SALARY column would be returned in descending order for all
employees having the same value in the DEPARTMENT_ID column.

D.

The values in the all columns would be returned in descending order.

E.

The values in the FIRST_NAME column would be returned in descending order for all
employees having the same value in the DEPARTMENT_ID column.

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?

A.

After executing the ALTER TABLE command, you can add a new column called
ORDER_DATE to the ORDERS table.

B.

The ORDER_DATE column should be empty for the ALTER TABLE command to
execute succsessfully.

C.

ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

D.

The DESCRIBE command would still display the ORDER_DATE column.

Which three statements are true reading subquenes?

A.

A Main query can have many subqueries.

B.

A subquery can have more than one main query.

C.

The subquery and main query must retrieve date from the same table.

D.

The subquery and main query can retrieve data from different tables.

E.

Only one column or expression can be compared between the subquery and main
query.

F.

Multiple columns or expressions can be compared between the subquery and main
query.

You must display details of all users whose username contains the string 'ch_'. (Choose
the best answer.)
Which query generates the required output?

A.

SELECT * FROM users Where user_name LIKE '%ch_';

B.

SELECT * FROM usersWhere user_name LIKE '%ch_%'ESCAPE'%';

C.

SELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_';

D.

SELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\';

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

A.

All constraints can be defined at the column level and at the table level.

B.

A constraint can be disabled even if the constraint column contains data.

C.

A column with the UNIQUE constraint can contain NULLS.

D.

A foreign key column cannot contain NULLS.

E.

A constraint is enforced only for INSERT operations.

Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

A.

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE"
FROM BOOKS_TRANSACTIONS

B.

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE
FEE" FROM BOOKS_TRANSACTIONS

C.

SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM
BOOKS_TRANSACTIONS;

D.

SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE
FROM BOOKS_TRANSACTIONS

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

A.

Savepoints are effective only for COMMIT.

B.

Savepoints may be used to ROLLBACK.

C.

Savepoints can be used for only DML statements.

D.

Savepoints are effective for both COMMIT and ROLLBACK.

E.

Savepoints can be used for both DML and DDL statements.

Which two statements are true regarding the GROUP BY clause in a SQL statement?
(Choose two.)

A.

You can use column alias in the GROUP BY clause.

B.

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

C.

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

D.

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

E.

If the SELECT clause has an aggregate function, then those individual columns without
an aggregate function in the SELECT clause should be included in the GROUP BY cause.

Which statement is true regarding the default behaviour of the ORDER by clause?

A.

Numeric values are displayed in descending order if they have decimal positions.

B.

Only columns that are specified in the SELECT list can be used in the ORDER by
clause.

C.

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

D.

NULLs are not including in the sort operation

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

A.

Finding the lowest value

B.

Finding the quotient

C.

Raising to a power

D.

Subtraction

E.

Addition

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.


You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total
number of items in each order.
Which CREATE VIEW statement would create the views successfully?

A.

CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT
(i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY
o.order_id, o.order_date;

B.

CREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id,
o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items
iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;

C.

CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT
(i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id =
i.order_id)GROUP BY o.order_id, o.order_date;

D.

CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT
(i.line_item_id) ||"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id =
i.order_id)GROUP BY o.order_id, o.order_dateWHITH CHECK OPTION;

Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

A.

SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE
FROM BOOKS_TRANSACTIONS;

B.

SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM
BOOKS_TRANSACTIONS;

C.

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE
FEE" FROM BOOKS_TRANSACTIONS;

D.

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE"
FROM BOOKS_TRANSACTIONS;