Which two statements are true about sequences created in a single instance database?
(Choose two.)

A.

When the MAXVALUE limit for the sequence is reached, you can increase the
MAXVALUE limit by using the ALTER SEQUENCE statement.

B.

DELETE <sequencename> would remove a sequence from the database.

C.

The numbers generated by a sequence can be used only for one table.

D.

CURRVAL is used to refer to the last sequence number that has been generated.

E.

When a database instance shuts down abnormally, the sequence numbers that have
been cached but not used would be available once again when the database instance is
restarted.

Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

A.

SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;

B.

SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;

C.

SELECT NVL (MONTHS_BETWEEN (start_date, end_date), ‘Ongoing’) FROM
programs;

D.

SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), ‘Ongoing’)
FROM programs

View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

A.

The statement would not execute because the positional notation instead of the column
name should be used with the ORDER BY clause.

B.

The statement would execute successfully and display all the rows in the ascending
order of DEPARTMENT_ID.

C.

The statement would execute successfully but it will ignore the ORDER BY clause and
display the rows in random order.

D.

The statement would not execute because the ORDER BY clause should appear only at
the end of the SQL statement, that is, in the last SELECT statement.

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.

View the exhibit for the structure of the STUDENT and FACULTY tables.
STUDENT
NameNull?Type
-------------------------
STUDENT_IDNOT NULLNUMBER(2)
STUDENT_NAMEVARCHAR2(20)
FACULTY_IDVARCHAR2(2)
LOCATION_IDNUMBER(2)
FACULTY
NameNull?Type
-------------------------
FACULTY_IDNOT NULLNUMBER(2)
FACULTY_NAMEVARCHAR2(20)
LOCATION_IDNUMBER(2)
You need to display the faculty name followed by the number of students handled by the
faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?

A.

Only statement 2 executes successfully and gives the required result.

B.

Only statement 1 executes successfully and gives the required result.

C.

Both statements 1 and 2 execute successfully and give different results.

D.

Both statements 1 and 2 execute successfully and give the same required result.

A subquery is called a single-row subquery when _______.

A.

There is only one subquery in the outer query and the inner query returns one or more
values

B.

The inner query returns a single value to the outer query.

C.

The inner query uses an aggregating function and returns one or more values.

D.

The inner query returns one or more values and the outer query returns a single value.

You issued this command:
CHOOSE THREE
SQL > DROP TABLE employees;
Which three statements are true?

A.

Sequences used in the EMPLOYEES table become invalid.

B.

If there is an uncommitted transaction in the session, it is committed.

C.

All indexes and constraints defined on the table being dropped are also dropped.

D.

The space used by the EMPLOYEES table is always reclaimed immediately.

E.

The EMPLOYEES table can be recovered using the ROLLBACK command.

F.

The EMPLOYEES table may be moved to the recycle bin.

Which statement is true about SQL query processing in an Oracle database instance?
(Choose the best answer.)

A.

During parsing, a SQL statement containing literals in the WHERE clause that has been
executed by any session and which is cached in memory, is always reused for the current
execution.

B.

During executing, the oracle server may read data from storage if the required data is
not already in memory.

C.

During row source generation, rows that satisfy the query are retrieved from the
database and stored in memory.

D.

During optimization, execution plans are formulated based on the statistics gathered by
the database instance, and the lowest cost plan is selected for execution.