Oracle 1Z0-071 Quiz 59 Topic 1 Questions 291-295

Question: 1

Examine the structure of the MEMBERS table:

q1_1Z0-071

You want to display details of all members who reside in states starting with the letter A followed by exactly one character.

Which SQL statement must you execute?

ASELECT * FROM MEMBERS WHERE state LIKE '%A_';

BSELECT * FROM MEMBERS WHERE state LIKE 'A_';

CSELECT * FROM MEMBERS WHERE state LIKE 'A_%';

DSELECT * FROM MEMBERS WHERE state LIKE 'A%';

Show Answer

Question: 2

You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.

Which query will provide the required result?

ASELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES;

BSELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

CSELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;

DSELECT prod_id, cust_id, amount_soldFROM salesORDER BY amount_soldFETCH FIRST 5 PERCENT ROWS ONLY;

Show Answer

Question: 3

Examine the structure of the MEMBERS table:

NameNull?Type

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

MEMBER_IDNOT NULLVARCHAR2 (6)

FIRST_NAMEVARCHAR2 (50)

LAST_NAMENOT NULLVARCHAR2 (50)

ADDRESSVARCHAR2 (50)

You execute the SQL statement:

SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;

What is the outcome?

AIt fails because the alias name specified after the column names is invalid.

BIt fails because the space specified in single quotation marks after the first two column names is invalid.

CIt executes successfully and displays the column details in a single column with only the alias column heading.

DIt executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Show Answer

Question: 4

You issue the following command to drop the PRODUCTS table:

SQL > DROP TABLE products;

Which three statements are true about the implication of this command? (Choose three.)

AAll data along with the table structure is deleted.

BA pending transaction in the session is committed.

CAll indexes on the table remain but they are invalidated.

DAll views and synonyms on the table remain but they are invalidated.

EAll data in the table is deleted but the table structure remains.

Show Answer

Question: 5

You execute the following commands:

SQL > DEFINE hiredate = '01-APR-2011'

SQL >SELECT employee_id, first_name, salary

FROM employees

WHERE hire_date > '&hiredate'

AND manager_id > &mgr_id;

For which substitution variables are you prompted for the input?

Anone, because no input required

Bboth the substitution variables ''hiredate' and 'mgr_id'.

Conly hiredate'

Donly 'mgr_id'

Show Answer

Oracle 1Z0-071 Quiz:59 Topic:1 Questions:291-295