Oracle ComeBack

Question 1

Which two statements about the evaluation of clauses in a SELECT statement are true? 
(Choose two.)

Accepted Answer

The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.

Accepted Answer

The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.

Answer

The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.

Answer

The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.

Answer

The Oracle Server will evaluate a HAVING clause before a WHERE clause.

Question 2

What value is returned after executing the following statement? Take note that 01-JAN-2009 occurs on a Thursday.

SELECT NEXT_DAY('01-JAN-2009','wed') FROM DUAL;

Accepted Answer

07-JAN-2009

Answer

Wednesday

Answer

31-JAN-2009

Answer

None of the above

Question 3

Which SQL statement will query the EMPLOYEES table for FIRST_NAME, LAST_NAME, and SALARY of all employees in DEPARTMENT_ID 40 in the alphabetical order of last name?

Accepted Answer

SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;

Answer

SELECT first_name, last_name, salary FROM TABLE employees WHERE department_id IS 40 ORDER BY last_name ASC;

Answer

SELECT first_name last_name salary FROM employees WHERE department_id = 40 ORDER BY last_name ASC;

Answer

SELECT first_name last_name salary FROM employees ORDER BY last_name WHERE department_id = 40;

Answer

SELECT first_name, last_name, salary FROM employees ORDER BY last_name ASC WHERE department_id = 40;

Question 4

Which three statements/commands would cause a transaction to end?
(Choose three.)

Accepted Answer

ROLLBACK

Accepted Answer

CREATE

Accepted Answer

COMMIT

Answer

SELECT

Answer

SAVEPOINT

Question 5

Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)

Accepted Answer

The HAVING clause can be used with aggregate functions in subqueries.

Accepted Answer

The WHERE clause can be used to exclude rows before dividing them into groups.

Answer

The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.

Answer

The WHERE clause can be used to exclude rows after dividing them into groups.

Answer

The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

Question 6

SQL is a set-oriented language. Which of these features is a consequence of this?
(Choose the best answer.)

Accepted Answer

One statement can affect multiple rows.

Answer

Individual rows must have a unique identifier.

Answer

SQL statements can be placed within blocks of code in other languages, such as Java and PL/SQL.

Answer

Sets of users can be managed in groups.

Question 7

What is true of using group functions on columns that contain NULL values?

Accepted Answer

Group functions on columns ignore NULL values.

Answer

Group functions on columns cannot be accurately used on columns that contain NULL values.

Answer

Group functions on columns returning dates include NULL values.

Answer

Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.

Answer

Group functions on columns returning numbers include NULL values.

Question 8

Which actions are allowed on a table that is marked as read-only?

Accepted Answer

Dropping a table

Accepted Answer

Dropping a constraint

Accepted Answer

Dropping an index

Answer

Truncating a table

Answer

Inserting new data

Question 9

Which of the following statements will create a primary key for the CITY table with the columns STATE_CD and CITY_CD?

Accepted Answer

ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (STATE_CD, CITY_CD);

Accepted Answer

ALTER TABLE CITY ADD PRIMARY KEY (STATE_CD, CITY_CD);

Answer

CREATE CONSTRAINT PK_CITY PRIMARY KEY ON CITY (STATE_CD, CITY_CD);

Answer

ALTER TABLE CITY ADD PRIMARY KEY CONSTRAINT PK_CITY ON (STATE_CD, CITY_CD);

Answer

CREATE PRIMARY KEY ON CITY (STATE_CD, CITY_CD);

Question 10

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?

Which query will provide the required result?

Accepted Answer

SELECT prod_id, cust_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS WITH TIES;

Answer

SELECT prod_id, cust_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;

Answer

SELECT prod_id, cust_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;

Answer

SELECT prod_id, cust_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

Question 11

Consider this statement:
SELECT last_name, (SELECT count(*) FROM departments) FROM employees WHERE salary = (SELECT salary FROM employees);

What is wrong with it? 
(Choose the best answer.)

Accepted Answer

The statement will fail if the second query returns more than one row.

Answer

Nothing is wrong—the statement should run without error.

Answer

The statement will run but is extremely inefficient because of the need to run the second subquery once for every row in EMPLOYEES.

Answer

The statement will fail because the subquery in the SELECT list references a table that is not listed in the FROM clause.

Question 12

Which two functions could you use to strip leading characters from a character string?

Accepted Answer

SUBSTR

Accepted Answer

LTRIM

Answer

INSTR

Answer

STRIP

Answer

RTRIM

Question 13

If you issue this command: 
UPDATE employees SET salary=salary * 1.1;

what will be the result?
(Choose the best answer.)

Accepted Answer

Every row will have SALARY incremented by 10 percent, unless SALARY was NULL.

Answer

The statement will fail because there is no WHERE clause to restrict the rows affected.

Answer

The first row in the table will be updated.

Answer

There will be an error if any row has its SALARY column NULL.

Question 14

Examine the structure of the products table:

CREATE TABLE products(
 PRO_ID NUMBER(4) NOT NULL,
 PROD_NAME VARCHAR2(20),
 PROD_STATUS VARCHAR2(6),
 QTY_IN_HAND NUMBER(8,2),
 UNIT_PRICE NUMBER(10,2)
);

You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND. Which SQL statement gives the required output?

Accepted Answer

SELECT prod_name
FROM products
WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)
FROM products);

Answer

SELECT prod_name
FROM products
WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)
FROM products
GROUP BY prod_name);

Answer

SELECT prod_name
FROM products
GROUP BY prod_name
HAVING MAX (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)
FROM products)
GROUP BY prod_name;

Answer

SELECT prod_name
FROM products

Question 15

Which tasks can be performed using SQL functions that are built into Oracle database?
(Choose three.)

Accepted Answer

comparing two expressions to check whether they are equal

Accepted Answer

checking whether a specified character exists in a given string

Accepted Answer

finding the remainder of a division

Answer

removing trailing, leading, and embedded characters from a character string

Answer

adding a number to a date for a resultant date value

Question 16

Examine the structure of the PROMOS table:

CREATE TABLE promos(
 PROMO_ID NUMBER(3) NOT NULL,
 PROMO_NAME VARCHAR2(30),
 PROMO_START_DATE DATE NOT NULL,
 PROMO_END_DATE DATE NOT NULL
);

You want to display the list of promo names with the message ‘Same Day’ for promos that started and ended on the same day. Which query gives the correct output?

Accepted Answer

SELECT promo_name,
DECODE((NULLIF(promo_start_date, promo_end_date)), NULL, 'Same Day')
FROM promos;

Answer

SELECT promo_name, 
NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL, 'Same Day')
FROM promos;

Answer

SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day'
FROM promos;

Answer

SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day')
FROM promos;

Question 17

Which two statements about sub queries are true? 
(Choose two.)

Accepted Answer

When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

Accepted Answer

A sub query can retrieve zero or more rows.

Answer

A sub query should retrieve only one row.

Answer

Sub queries CANNOT be nested by more than two levels.

Answer

A sub query can be used only in SQL query statements.

Answer

A sub query CANNOT be used in an SQL query statement that uses group functions.

Question 18

In which three situations does a transaction complete?

Accepted Answer

When a ROLLBACK command is executed

Accepted Answer

When a data definition language (DDL) statement is executed

Accepted Answer

When a TRUNCATE statement is executed after the pending transaction

Answer

When a DELETE statement is executed

Answer

When a PL/SQL anonymous block is executed

Oracle ComeBack

Create a free account to finish this quiz

Questions in this quiz (18)

Information

Feedback

What do you think of the new look?