Choose the best answer.

Examine the description of the EMPLOYEES table:

Name  Null  Type

----------------------------------------------------------

EMP_ID  NOT NUL  NUMBER

EMP_NAME

VARCHAR2 (40)

DEPT_ID

NUMBER(2)

SALARY

NUMBER(8,2)

JOIN_DATE

DATE

Which query is valid? 

Choose the best answer

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.

Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the

second?

Choose the best answer

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type

DATE

You want to display the date of the first Monday after the completion of six months since hiring

The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day

of the week

Which query can be used?

Choose the best answer

Examine the description of the PRODUCT_INFORMATION table:

Name  NULL?  Type

------------------------------------------------------------------------

PROD_ID  NOT NULL  NUMBER(2)

PROD_NANE  VARCRAR2 (10)

LIST_PRICE  NUMBER(6,2)

Which query retrieves the number of products with a null list price?

What is true about non-equijoin statement performance?

Choose the best answer.

Examine this query:

SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;

What is returned upon execution?

Choose the best answer

Examine the description of the CUSTOMERS table:

Name  Null?  Type

-----------------------------------------------------------------------------------------

CUST_ID  Not NULL  VARCHAR2(6)

FIRST_NAME  VARCHAR2(50)

LAST_NAME  Not NULL  VARCHAR2(50)

ADDRRESS  VARCHAR2(50)

CITY  VARCHAR2(25)

You want to display details of all customers who reside in cities starting with the letter D followed by at

least two character

Which query can be used?

Examine the description or the CUSTOMERS table:

Name                                 Null?                                                   Type

CUST_ID                           NOT NULL                                       NUMBER

CUST_FIRST_NAM          NOT NULL                                        VARCHAR2(20)

CUST_LAST_NAME         NOT NULL                                     VARCHAR2(30)

CUST_INCOME_LEVEL                                                            VARCHAR2(30)

CUST_CREDIT_LIMIT                                                                 NUMBER

For Customers whose income level has a value, you want to display the first name and due amount as 5%

of their credit limit. Customers whose due amount is null should not be displayed.

Which query should be used?

You want to write a query that prompts for two column names and the WHERE condition each time It is

executed in a session but only prompts for the table name the first time it is executed. The variables used

in your query are never undefined in your session. Which query can be used?

Examine the description of the EMPLOYEES table:

Name  Null  Type

EMP_ID  NOT NULL NUMBER

EMP_NAME  VARCHAR2 (10)

DEPT_ID  NUMBER (2)

SALARY  NUMBER(8,2)

JOIN_DATE  DATE

NLS_DATE_FORMAT is set to DD-MON-YY.

Which query requires explicit data type conversion?

Examine the description of the EMPLOYEES table:

Name                        Null?                                 Type

EMP_ID                  NOT NULL                       NUMBER

EMPNAME                                                     VARCHAR2 (40)

DEPT_ID                                                            NUMBER(2)

SALARY                                                             NUNGER(B,2)

JOIN_DATE                                                    DATE

Which query is valid?

Examine the description of the SALES1 table:

Name  Null  Type

SALES_ID  NOT NULL NUMBER

STORE_ID  NOT NULL NUMBER

ITEMS_ID  NUMBER

QUANTITY  NUMBER

SALES_DATE  DATE

SALES2 is a table with the same description as SALES1,

Some sales data is duplicated In both tables.

You want to display the rows from the SALES1 table which are not present in the SALIES2 table.

Which set operator generates the required output

Which statement is true about aggregate functions?

Which statement is true about TRUNCATE and DELETE?

Examine the description or the BOOKS_TRANSACTIONS table:

Name                                 Null?                                            Type

CUST_ID                           NOT Null                                      NUMBER

CUST_FIRST_NAME       NOT Null                              VARCHAR2 (20)

CUST_LAST_NAME                                                         VARCHAR2 (30)

CUST_CREDIT_LIMIT                                                          NUMBER

FOR customers whose income level has a value, you want to display the first name and due amount as

5% of their

credit limit. Customers whose due amount is null should not be displayed.

Which query should be used?

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro

motion time interval.

Examine this SQL statement:

SELECT prod id

FROM costs

WHERE promo id IN

(SELECT promo id

FROM promotions

WHERE promo_cost < ALL

(SELECT MAX (promo cost)

FROM promotions

GROUP BY (promo_end date-promo_begin_date)) );

What will be the result?

Which is the default column or columns for sorting output from compound queries using SET operators

such as INTERSECT in a SQL statement?

You execute this query:

SELECT TO CHAR (NEXT_DAY(LAST_DAY(SYSDATE),’MON’ ),’ dd“Monday for” fmMonth rrr’) FROM

DUAL;

What is the result?

Which statement is true about the INTERSECT operator used in compound queries?

Which statement is true regarding the SESSION_PRIVS dictionary view?