Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
Which two statements are true regarding single row functions? (Choose two.)
A. They can be nested only to two levels
B. They always return a single result row for every row of a queried table
C. Arguments can only be column values or constant
D. They can return a data type value different from the one that is referenced
E. They accept only a single argument
Answer: B,D
Explanation:
A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution. Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.
Q2. - (Topic 2)
A data manipulation language statement _____.
A. completes a transaction on a table
B. modifies the structure and data in a table
C. modifies the data but not the structure of a table
D. modifies the structure but not the data of a table
Answer: C
Explanation:
modifies the data but not the structure of a table
Incorrect Answer:
ADML does not complete a transaction
BDDL modifies the structure and data in the table
DDML does not modified table structure.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-3
Q3. - (Topic 2)
EMPLOYEES and DEPARTMENTS data: EMPLOYEES
DEPARTMENTS
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key.
Evaluate this UPDATE statement.
UPDATE employees SET mgr_id = (SELECT mgr_id FROMemployees WHERE dept_id= (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary
FROM employees
WHERE emp_name = 'Smith')
WHERE job_id = 'IT_ADMIN'
What happens when the statement is executed?
A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105.
B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105.
C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105.
D. The statement fails because there is more than one row matching the employee name Smith.
E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table.
F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.
Answer: D
Explanation:
'=' is use in the statement and sub query will return more than one row.
Employees table has 2 row matching the employee name Smith.
The update statement will fail.
Incorrect Answers :
A. The Update statement will fail no update was done.
B. The update statement will fail no update was done.
C. The update statement will fail no update was done.
E. The update statement will fail but not due to job_it='IT_ADMIN'
F. The update statement will fail but not due to department_id='Administration'
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12
Q4. - (Topic 2)
Evaluate the following query:
SELECT INTERVAL '300' MONTH,
INTERVAL '54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?
A. +25-00 , +54-02, +00 11:12:10.123457
B. +00-300, +54-02, +00 11:12:10.123457
C. +25-00 , +00-650, +00 11:12:10.123457
D. +00-300 , +00-650, +00 11:12:10.123457
Answer: A
Explanation:
Datetime Data Types You can use several datetime data types: INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days, hours, minutes, and seconds
Q5. - (Topic 2)
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
Answer: A
Explanation:
But why the answer is not "C" ? Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling. If you wanted to keep the minimum value you would need to specify it in the sequence creation. sequence Is the name of the sequence generator INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.) MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending sequence (This is the default option.) MINVALUE n Specifies the minimum sequence value NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is the default option.) CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)
Q6. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A.
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000'
B.
SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98'
C.
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
D.
SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
Answer: A,B
Q7. - (Topic 2)
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
Q8. - (Topic 2)
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)
A. finding the remainder of a division
B. adding a number to a date for a resultant date value
C. comparing two expressions to check whether they are equal
D. checking whether a specified character exists in a given string
E. removing trailing, leading, and embedded characters from a character string
Answer: A,C,D
Q9. - (Topic 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?
A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
Answer: B
Explanation: Use TO_NUMBER on the prod_price column to convert from char to number
to be able to multiply it with 0.25. Then use the TO_CHAR function (with
formatting'$99,999.00') to convert the number back to char.
Incorrect:
Not C: Use the formatting'$99,999.00' with the TO_CHAR function, not with the
TO_NUMBER function.
Note:
*
Using the TO_CHAR Function The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]), The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single
*
Syntax of Explicit Data Type Conversion Functions TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1
Q10. - (Topic 2)
Examine the structure of the PROMOS table:
You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)
A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos;
B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
D. SELECT promo_name, DECODE(promo_end_date
-promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM
promos;
E. SELECT
promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',
promo_end_date - promo_start_date)
FROM promos;
Answer: B,C,D
Q11. - (Topic 1)
Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)
A. addition
B. subtraction
C. raising to a power
D. finding the quotient
E. finding the lowest value
Answer: A,C,E
Q12. - (Topic 2)
Which four are valid Oracle constraint types? (Choose four.)
A. CASCADE
B. UNIQUE
C. NONUNIQUE
D. CHECK
E. PRIMARY KEY
F. CONSTANT
G. NOT NULL
Answer: B,D,E,G
Explanation:
Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and Unique Incorrect Answer: AIs not Oracle constraint CIs not Oracle constraint FIs not Oracle constraint Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-3
Q13. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.
Which query would give you the required output?
A.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET'
B.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');
C.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
D.
SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');
Answer: C
Q14. - (Topic 1)
Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. The ON clause can be used to join tables on columns that have different names but compatible data types
B. A maximum of one pair of columns can be joined between two tables using the ON clause
C. Both USING and ON clause can be used for equijoins and nonequijoins
D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause
Answer: A,D
Explanation:
Creating Joins with the USING Clause If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin. Use the USING clause to match only one column when more than one column matches. The NATURAL JOIN and USING clauses are mutually exclusive Using Table Aliases with the USING clause When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING clause. The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement. Creating Joins with the ON Clause The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify arbitrary conditions or specify columns to join. – ANSWER C The join condition is separated from other search conditions. ANSWER D
The ON clause makes code easy to understand.
Q15. - (Topic 1)
Which three statements/commands would cause a transaction to end? (Choose three.)
A. COMMIT
B. SELECT
C. CREATE
D. ROLLBACK
E. SAVEPOINT
Answer: A,C,D