NEW QUESTION 1
The COMMISSION column shows the monthly commission earned by the employee. Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

• A. listing the employees who earn the same amount of commission as employee 3
• B. finding the total commission earned by the employees in department 10
• C. finding the number of employees who earn a commission that is higher than the average commission of the company
• D. listing the departments whose average commission is more that 600
• E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
• F. listing the employees whose annual commission is more than 6000

Answer: AC

NEW QUESTION 2
Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?

• A. 46 and 45
• B. 46 and 45.93
• C. 50 and 45.93
• D. 50 and 45.9
• E. 45 and 45.93
• F. 45.95 and 45.93

Answer: C

Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left. TRUNC (45.936,2) will truncate value to 2 decimal The answer will be 50 and 45.93
Incorrect Answers :
A. Does not meet round and truncate functions
B. Does not meet round functions
D. Does not meet truncate functions
E. Does not meet round functions
F. Does not meet round functions
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Single-Row functions, p. 3-13

NEW QUESTION 3
Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100 FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

• A. No change is required to achieve the desired result
• B. SELECT ename, sal, 12*(sal+100) FROM emp;
• C. SELECT ename, sal, (12*sal)+100 FROM emp;
• D. SELECT ename, sal+100,*12 FROM emp;

Answer: B

Explanation:
to achieve the result you must add 100 to sal before multiply with 12. Select ename, sal, 12*(sal+100) from EMP;
Incorrect Answer: AMultiplication and division has priority over addition and subtraction in Operator precedence. CGive wrong results DWrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-11

NEW QUESTION 4
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which DELETE statement is valid?

• A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
• B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);
• C. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey');
• D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey');

Answer: C

Explanation:
The correct syntax for DELETE statement
DELETE [ FROM ] table
[ WHERE condition ];
Incorrect Answers :
A. '=' is use in the statement and sub query will return more than one row.
Error Ora-01427: single-row sub query returns more than one row.
B. Incorrect DELETE statement
D. Incorrect DELETE statement
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data,
p. 8-19

NEW QUESTION 5
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

• A. SELECT last_name, department_name, location_id FROM employees , departments ;
• B. SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
• C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
• D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

Answer: D

Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.
Incorrect Answer: Athere is no join B invalid syntax Cdoes not involve the join in the primary and foreign key
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8

NEW QUESTION 6
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('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL;
• B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;
• C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;
• D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year')) FROM DUAL;

Answer: C

Explanation:
Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines
.
The format model must be enclosed with single quotation marks and is case-sensitive.
.
The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.
.
The names of days and months in the output are automatically padded with blanks.
.
To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month
MM Two-digit value for the month
MON Three-letter abbreviation of the month
MONTH Full name of the month
YYYY Full year in numbers
YEAR Year spelled out (in English)

NEW QUESTION 7
SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?

• A. Only the synonym would be droppe
• B. The synonym would be dropped and the corresponding table would become invali
• C. The synonym would be dropped and the packages referring to the synonym would be droppe
• D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invali

Answer: A

Explanation:
A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

NEW QUESTION 8
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)

• A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith');
• B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith');
• C. INSERT INTO employees VALUES ( 1000, 'John', NULL);
• D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith');
• E. INSERT INTO employees (employee_id) VALUES (1000);
• F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' ');

Answer: CEF

Explanation: EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11

NEW QUESTION 9
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?

• A. CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, e_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB(200));
• B. CREATE TABLE 1_EMP (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);
• C. CREATE TABLE EMP_1 (emp_id NUMBER(4), emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT "ACTIVE", resume CLOB);
• D. CREATE TABLE EMP_1 (emp_id NUMBER, emp_name VARCHAR2(25), start_date DATE, emp_status VARCHAR2(10) DEFAULT 'ACTIVE', resume CLOB);

Answer: D

Explanation:
CLOB Character data (up to 4 GB)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of
decimal digits and scale is the number of digits to the right of the decimal point; precision
can range from 1 to 38, and scale can range from –84 to 127.)

NEW QUESTION 10
Which constraint can be defined only at the column level?

• A. UNIQUE
• B. NOT NULL
• C. CHECK
• D. PRIMARY KEY
• E. FOREIGN KEY

Answer: B

Explanation:
The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row.
Incorrect Answers A:The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level. C:The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column.
D:The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL. E:The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 11
Which describes the default behavior when you create a table?

• A. The table is accessible to all user
• B. Tables are created in the public schem
• C. Tables are created in your schem
• D. Tables are created in the DBA schem
• E. You must specify the schema when the table is create

Answer: C

Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Agrant the table privilege to PUBLIC Blogin as sysoper Dlogin as DBA or sysdba Eno such option is allow.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-9

NEW QUESTION 12
View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00.
Which two queries would give the required result? (Choose two.)

• A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
• B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date <='15-OCT-00';
• C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00'));
• D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00');

Answer: AD

NEW QUESTION 13
Which SQL statement returns a numeric value?

• A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
• B. SELECT ROUND(hire_date) FROM EMP;
• C. SELECT sysdate-hire_date FROM EMP;
• D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;

Answer: C

Explanation:
DATE value subtract DATE value will return numeric value.
Incorrect Answer: Adoes not return numeric value Bdoes not return numeric value Ddoes not return numeric value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-13

NEW QUESTION 14
Which statement is true regarding the COALESCE function?

• A. It can have a maximum of five expressions in a lis
• B. It returns the highest NOT NULL value in the list for all row
• C. It requires that all expressions in the list must be of the same data typ
• D. It requires that at least one of the expressions in the list must have a NOT NULL valu

Answer: C

Explanation:
The COALESCE Function The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,…,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2) COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)) The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter. To avoid an “ORA-00932: inconsistent data types” error, all not null parameters must have data types compatible with the first not null parameter.

NEW QUESTION 15
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.


Evaluate the following SQL statements:
Which statement is true regarding the output of the above query?

• A. It gives details of product IDs that have been sold irrespective of whether they had a promo or not
• B. It gives the details of promos for which there have been no sales
• C. It gives the details of promos for which there have been sales
• D. It gives details of all promos irrespective of whether they have resulted in a sale or not

Answer: D

NEW QUESTION 16
Examine the structure of the CUSTOMERS table:

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result? (Choose two.)

• A. self-join
• B. subquery
• C. full outer-join with self-join
• D. left outer-join with self-join
• E. right outer-join with self-join

Answer: AB

NEW QUESTION 17
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Examine the structure of PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list price and discounted list price for all those products whose list price fails below $10 after a discount of 25% is applied on it.
Exhibit:

The query generates an error.
What is the reason of generating error?

• A. The column alias should be put in uppercase and enclosed within double quotation marks in the WHERE clause
• B. The parenthesis should be added to enclose the entire expression
• C. The column alias should be replaced with the expression in the WHERE clause
• D. The double quotation marks should be removed from the column alias

Answer: C

Explanation: Note: You cannot use column alias in the WHERE clause.