Oracle Vengence

Question 1

The PRODUCTS table has these columns:

PRODUCT_ID NUMBER(4)
PRODUCT_NAME VARCHAR2(45)
PRICE NUMBER(8, 2)

Evaluate this SQL statement:

SELECT *
FROM PRODUCTS
ORDER BY price, product_name;

What is true about the SQL statement?

Accepted Answer

The results are sorted numerically and then alphabetically.

Answer

The results are not sorted.

Answer

The results are sorted numerically.

Answer

The results are sorted alphabetically.

Question 2

Examine the structure and data in the PRICE_LIST table:

Name . Null . Type
———————-
PROD_ID . NOT NULL . NUMBER(3)
PROD_PRICE . VARCHAR2(10)

PROD_ID PROD_PRICE
———————-
100 $234.55
101 $6,509.75
102 $1,234

You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.

Which SQL statement would give the required result?

Accepted Answer

SELECT TO_CHAR(TO_NUMBER(prod_price, ‘$99, 999.99’)* .25, ‘$99, 999.00’) FROM PRICE_LIST;

Answer

SELECT TO_CHAR(prod_price* .25, ‘$99, 999.99’) FROM PRICE_LIST;

Answer

SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, ‘$99, 999.00’) FROM PRICE_LIST;

Answer

SELECT TO_NUMBER(TO_NUMBER(prod_price, ‘$99, 999.99’)* .25, ‘$99, 999.00’) FROM PRICE_LIST;

Question 3

Which statement successfully adds a new column, ORDER_DATE, to the table ORDERS?

Accepted Answer

ALTER TABLE ORDERS ADD ORDER_DATE DATE;

Answer

ALTER TABLE ORDERS ADD COLUMN ORDER_DATE DATE;

Answer

ALTER TABLE ORDERS ADD ORDER_DATE (DATE);

Answer

ALTER TABLE ORDERS NEW COLUMN ORDER_DATE TYPE DATE;

Question 4

You want to display each project’s start date as the day, week, number, and year. Which statement will give output like the following?

Tuesday Week 23, 2008

Accepted Answer

SELECT proj_id, TO_CHAR(start_date, 'Day" Week" WW, YYYY') FROM projects;

Answer

SELECT proj_id, TO_CHAR(start_date, 'DOW Week WOY YYYY') FROM projects;

Answer

SELECT proj_id, TO_CHAR(start_date,'Day'||' Week'||' WOY, YYYY') FROM projects;

Answer

SELECT proj_id, TO_CHAR(start_date, 'Day Week# , YYYY') FROM projects;

Answer

You can’t calculate week numbers with Oracle.

Question 5

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr.

Which two SQL statements would give the required output?

(Choose two.)

Accepted Answer

SELECT SYSDATE – TO_DATE(’01-JANUARY-2007′) FROM DUAL;

Accepted Answer

SELECT SYSDATE – TO_DATE(’01/JANUARY/2007′) FROM DUAL;

Answer

SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2007′ FROM DUAL;

Answer

SELECT SYSDATE – ’01-JAN-2007′ FROM DUAL;

Answer

SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ’01/JANUARY/2007′ FROM DUAL;

Question 6

What is the default precision for fractional seconds in a TIMESTAMP datatype column?

Accepted Answer

6

Answer

0

Answer

9

Answer

2

Question 7

Examine this statement:

SELECT student_id, gpa FROM student_grades WHERE gpa > &&value; You run the statement once, and when prompted you enter a value of 2.0. A report is produced.

What happens when you run the statement a second time?

Accepted Answer

A report is produced that matches the first report produced.

Answer

An error is returned.

Answer

You are asked whether you want a new value or if you want to run the report based on the previous value.

Answer

You are prompted to enter a new value.

Question 8

he EMPLOYEES table has EMPLOYEE_ID, DEPARTMENT_ID, and FULL_NAME columns. The DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns.

Which two of the following queries return the department ID, name, and employee name, listing department names even if there is no employee assigned to that department?

Accepted Answer

SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees e ON (d.department_id = e.department_id);

Accepted Answer

SELECT department_id, department_name, full_name FROM departments NATURAL LEFT JOIN employees;

Answer

SELECT d.department_id, d.department_name, e.full_name FROM departments d NATURAL LEFT OUTER JOIN employees e;

Answer

SELECT d.department_id, d.department_name, e.full_name FROM departments d LEFT OUTER JOIN employees e USING (d.department_id);

Question 9

Which two operators are not allowed when using an outer join operator in the query?

Accepted Answer

IN

Accepted Answer

OR

Answer

AND

Answer

=

Question 10

The account table contains these columns:
ACCOUNT_ID NUMBER(12)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)

You need to create a single SELECT statement to accomplish these requirements:
- Display accounts that have a new balance that is less than the previous balance.
- Display accounts that have a finance charge that is less than $25.00.
- Display accounts that have no finance charge.

Evaluate this statement:
SELECT account_id
FROM account
WHERE new_balance < prev_balance
AND NVL(finance_charge, 0) < 25;

How many of the three requirements will this SELECT statement accomplish?

Accepted Answer

all of the requirements

Answer

one of the requirements

Answer

two of the requirements

Answer

none of the requirements

Question 11

What are the distinguishing characteristics of a scalar subquery?
(Choose two correct answers.)

Accepted Answer

A scalar subquery returns one column.

Accepted Answer

A scalar subquery returns one row.

Answer

A scalar subquery cannot be used as a correlated subquery.

Answer

A scalar subquery cannot be used in the SELECT LIST of the parent query.

Question 12

Which two statements are true regarding single row functions?

Accepted Answer

TRUNC: can be used with number and date values

Accepted Answer

SYSDATE: returns the database server current date and time

Answer

MOD: returns the quotient of a division

Answer

TRIM: can be used to remove all the occurrences of a character from a string

Answer

INSTR: can be used to find only the first occurrence of a character in a string

Question 13

When a COMMIT is issued, what will happen?
(Choose the best answer.)

Accepted Answer

LGWR writes the log buffer to disk.

Answer

All the change vectors that make up the transaction are written to disk.

Answer

DBWn writes the change blocks to disk

Answer

The undo data is deleted so that the changes can no longer be rolled back.

Question 14

You need to create a table with the following column specifications:

1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value 'active' if no data is entered
5. Resume (character large object [CLOB] data type),

which contains the resume submitted by the employee Which is the correct syntax to create this table?

Accepted Answer

CREATE TABLE EMP_1(
 emp_id NUMBER(4),
 emp_name VARCHAR2 (25),
 start_date DATE,
 e_status VARCHAR2(10) DEFAULT 'ACTIVE',
 resume CLOB
);

Answer

CREATE TABLE EMP_1(
 emp_id NUMBER(4),
 emp_name VARCHAR2 (25),
 start_date DATE,
 e_status VARCHAR2(10) DEFAULT 'ACTIVE',
 resume CLOB(200)
);

Answer

CREATE TABLE 1_EMP(
 emp_id NUMBER(4),
 emp_name VARCHAR2 (25),
 start_date DATE,
 e_status VARCHAR2(10) DEFAULT 'ACTIVE',
 resume CLOB
);

Answer

CREATE TABLE EMP_1(
 emp_id NUMBER(4),
 emp_name VARCHAR2 (25),
 start_date DATE,
 e_status VARCHAR2(10) DEFAULT "ACTIVE",
 resume CLOB
);

Question 15

The LRU algorithm is used to manage what part of the Oracle architecture?

Accepted Answer

The shared pool and database buffer cache portions of the SGA

Answer

The data file that stores the least amount of information and will need the least frequent backup

Answer

The tables that users rarely access so that they can be moved to a less active tablespace

Answer

Users who log on to the database infrequently and may be candidates for being dropped

Question 16

Which statement is true regarding the INTERSECT operator?

Accepted Answer

The number of columns and data types must be identical for all SELECT statements in the query

Answer

It ignores NULL values

Answer

The names of columns in all SELECT statements must be identical

Answer

Reversing the order of the intersected tables the result

Question 17

Which statement will remove the column UPDATE_DT from the table STATE?

Accepted Answer

ALTER TABLE STATE DROP COLUMN UPDATE_DT;

Answer

ALTER TABLE STATE REMOVE COLUMN UPDATE_DT;

Answer

DROP COLUMN UPDATE_DT FROM STATE;

Answer

ALTER TABLE STATE SET UNUSED COLUMN UPDATE_DT;

Answer

You cannot drop a column from the table.

Question 18

After describing the EMPLOYEES table, you discover that the SALARY column has a data type of NUMBER(8,2). Which SALARY values will not be permitted in this column?

(Choose the best answers.)

Accepted Answer

SALARY=12345678

Accepted Answer

SALARY=1234567.8

Answer

SALARY=123456.78

Answer

SALARY=123456

Answer

SALARY=12.34

Question 19

Examine the structure of the INVOICE table:

CREATE TABLE invoice(
 inv_no NUMBER(3) NOT NULL,
 inv_date DATE,
 inv_amt NUMBER(10, 2)
);

Which two SQL statements would execute successfully?
(Choose two.)

Accepted Answer

SELECT inv_no, NVL2(inv_date, ‘Pending’, ‘Incomplete’)
FROM invoice;

Accepted Answer

SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)
FROM invoice;

Answer

SELECT inv_no, NVL2(inv_amt, inv_date, ‘Not Available’)
FROM invoice;

Answer

SELECT inv_no, NVL2(inv_amt, inv_amt*.25, ‘Not Available’)
FROM invoice;``1

Question 20

If you suspect that undo generation is a performance issue, what can you do to reduce the amount of undo data generated?

(Choose the best answer.)

Accepted Answer

There is nothing you can do because all DML statements must generate undo.

Answer

Convert from use of rollback segments to automatic undo management.

Answer

Reduce the size of the undo segments.

Answer

Set the UNDO_MANAGEMENT parameter to NONE.

Oracle Vengence

Crea una cuenta gratis para terminar este test

Preguntas del test (20)

Información

Escríbenos

¿Qué te parece el nuevo diseño?