Which three statements are true regarding group functions? (Choose three.)

A.

They can be used on columns or expressions.

B.

They can be passed as an argument to another group function.

C.

They can be used only with a SQL statement that has the GROUP BY clause.

D.

They can be used on only one column in the SELECT clause of a SQL statement.

E.

They can be used along with the single-row function in the SELECT clause of a SQL
statement.

You must create a SALES table with these column specifications and data types: (Choose
the best answer.)
SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is
specified
Which statement would create the table?

A.

CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID
NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT
SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");

B.

CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID
NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT 'SYSDATE',PAYMENT VARCHAR2(30) DEFAULT CASH);

C.

CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID
NUMBER (4),qty NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT
SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");

D.

Create Table sales(salesid NUMBER (4),Storeid NUMBER (4),Itemid NUMBER (4),QTY
NUMBER DEFAULT 1,Slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30)
DEFAULT 'CASH');

You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand
Seven’.
Which SQL statement would give the required result?

A.

SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)FROM
DUAL

B.

 SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)FROM DUAL

C.

SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’)FROM DUAL

D.

SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’))FROM
DUAL

Which two are the minimal requirements for a self-join? (Choose two.)

A.

Only equijoin conditions may be used in the query.

B.

Outer joins must not be used in the query.

C.

There must be a condition on which the self-join is performed.

D.

No other condition except the self-join may be specified.

E.

The table used for the self-join must have two different alias names in the query.

Evaluate this ALTER TABLE statement: (Choose the best answer.)
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?

A.

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

B.

The ORDER_DATE column must be empty for the ALTER TABLE command to execute
successfully.

C.

 ROLLBACK can be used to restore the ORDER_DATE column.

D.

 The DESCRIBE command would still display the ORDER_DATE column.

View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)

You must display product names from the PRODUCTS table that belong to the
'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of
measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price =
4000) AND prod_unit_of_measure <> ' ';
Which statement is true?

A.

It executes successfully but returns no result.

B.

It executes successfully and returns the required result.

C.

It generates an error because the condition specified for PROD_UNIT_OF_MEASURE
is not valid.

D.

 It generates an error because the condition specified for the PROD_CATEGORY
column is not valid.

In which three situations does a transaction complete?

A.

when a PL/SQL anonymous block is executed

B.

when a DELETE statement is executed

C.

when a ROLLBACK command is executed

D.

when a data definition language (DDL) statement is executed

E.

when a TRUNCATE statement is executed after the pending transaction

Which three statements are true regarding single-row functions? (Choose three.)

A.

The data type returned, can be different from the data type of the argument that is
referenced.

B.

They can return multiple values of more than one data type.

C.

 They can accept only one argument.

D.

They can be nested up to only two levels.

E.

They can be used in SELECT, WHERE, and ORDER BY clauses.

F.

They can accept column names, expressions, variable names, or a user-supplied
constants as arguments.

Which statement is true about an inner join specified in the WHERE clause of a query?

A.

It must have primary-key and foreign-key constraints defined on the columns used in the
join condition.

B.

It requires the column names to be the same in all tables used for the join conditions.

C.

It is applicable for equijoin and nonequijoin conditions.

D.

It is applicable for only equijoin conditions.

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

A.

A subquery can appear on either side of a comparison operator.

B.

Only two subqueries can be placed at one level.

C.

A subquery can retrieve zero or more rows.

D.

A subquery can be used only in SQL query statements.

E.

There is no limit on the number of subquery levels in the WHERE clause of a SELECT
statement.

Which three statements are true regarding the data types?

A.

The minimum column width that can be specified for a VARCHAR2 data type column is
one.

B.

Only one LONG column can be used per table.

C.

A TIMESTAMP data type column stores only time values with fractional seconds.

D.

The BLOB data type column is used to store binary data in an operating system file.

E.

The value for a CHAR data type column is blank-padded to the maximum defined
column width.

The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the
following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?

A.

PUBLIC should be replaced with specific usernames.

B.

ALL should be replaced with a list of specific privileges.

C.

WITH GRANT OPTION should be added to the statement.

D.

Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.