Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 2)
View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.
Which two queries give the correct result? (Choose two.)
A. SELECT promo_name, TO_CHAR(promo_end_date,'Day') ', '
TO_CHAR(promo_end_date,'Month') ' '
TO_CHAR(promo_end_date,'DD, YYYY') AS last_day
FROM promotions;
B. SELECT promo_name,TO_CHAR (promo_end_date,'fxDay') ', '
TO_CHAR(promo_end_date,'fxMonth') ' '
TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day
FROM promotions;
C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ', ' TRIM(TO_CHAR
(promo_end_date,'Month')) ' '
TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day
FROM promotions;
D. SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')','
TO_CHAR(promo_end_date,'fmMonth') ' '
TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day
FROM promotions;
Answer: C,D
Q2. - (Topic 1)
User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database.
Which SQL statement can Mary use to accomplish that task?
A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu;
B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu);
C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu;
D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER;
E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu;
F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS;
Answer: C
Explanation:
The general syntax to create a synonym is:
CREATE [PUBLIC] SYNONYM synonym FOR object;
Q3. - (Topic 2)
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the subquery.
Answer: C
Q4. - (Topic 1)
The PRODUCTS table has the following structure:
Evaluate the following two SQL statements:
Which statement is true regarding the outcome?
A. Both the statements execute and give the same result
B. Both the statements execute and give different results
C. Only the second SQL statement executes successfully
D. Only the first SQL statement executes successfully
Answer: B
Explanation:
Using the NVL2 Function The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax NVL2(expr1, expr2, expr3) In the syntax: expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null
Q5. - (Topic 2)
What are two reasons to create synonyms? (Choose two.)
A. You have too many tables.
B. Your tables names are too long.
C. Your tables have difficult names.
D. You want to work on your own tables.
E. You want to use another schema's tables.
F. You have too many columns in your tables.
Answer: B,C
Explanation:
Create a synonyms when the names of the tables are too long or the table names are difficult.
Q6. - (Topic 2)
View the Exhibit and examine the structure of the EMPLOYEES table.
Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:
ENAME HIREDATE
SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81
You want to generate a list of user IDs as follows: USERID
Smi17DEC80 All20FEB81 War22FEB81
You issue the following query:
SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-'))
"USERID"
FROM employees;
What is the outcome?
A. It executes successfully and gives the correct output.
B. It executes successfully but does not give the correct output.
C. It generates an error because the REPLACE function is not valid.
D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.
Answer: A
Explanation:
REPLACE(text, search_string,replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3,'9','85') from dual Query 2: select replace(sysdate, 'DEC','NOV') from dual
Q7. - (Topic 1)
View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where
CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?
A.
UPDATE customers SET cust_credit_limit = NULL WHERE CUST_INCOME_LEVEL = NULL;
B.
UPDATE customers SET cust_credit_limit = NULL WHERE cust_income_level IS NULL;
C.
UPDATE customers
SET cust_credit_limit = TO_NUMBER(NULL)
WHERE cust_income_level = TO_NUMBER(NULL);
D.
UPDATE customers
SET cust_credit_limit = TO_NUMBER(' ',9999)
WHERE cust_income_level IS NULL;
Answer: B
Q8. - (Topic 1)
Examine the structure of the INVOICE table: Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete') FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available') FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available') FROM invoice;
Answer: A,C
Explanation:
The NVL2 Function
The NVL2 function provides an enhancement to NVL but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not. 5-6 The NVL function\ If the first term is not null, the second parameter is returned, else the third parameter is returned. Recall that the NVL function is different since it returns the original term if it is not null. The NVL2 function takes three mandatory parameters. Its syntax is NVL2(original, ifnotnull, ifnull), where original represents the term being tested. Ifnotnull is returned if original is not null, and ifnull is returned if original is null. The data types of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG. They must either be of the same type, or it must be possible to convert ifnull to the type of the ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.
Q9. - (Topic 1)
Which two statements are true about constraints? (Choose two.)
A. The UNIQUE constraint does not permit a null value for the column.
B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.
C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D. The NOT NULL constraint ensures that null values are not permitted for the column.
Answer: B,D
Explanation:
B: A unique constraint can contain null values because null values cannot be compared to anything.
D: The NOT NULL constraint ensure that null value are not permitted for the column
Incorrect Answer: Astatement is not true Cstatement is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-9
Q10. - (Topic 1)
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO
columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)
A. CREATE INDEX ord_idx1
ON ord(ord_no);
B. CREATE INDEX ord_idx2
ON ord_items(ord_no);
C. CREATE INDEX ord_idx3
ON ord_items(item_no);
D. CREATE INDEX ord_idx4
ON ord,ord_items(ord_no, ord_date,qty);
Answer: B,C
Explanation: How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a
column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index
is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create
the FOREIGN KEY column index for a join in a query to improve the speed of retrieval.
Note: You can manually create a unique index, but it is recommended that you create a
unique constraint, which implicitly creates a unique index.
Q11. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)
A. Display the minimum list price for each product status.
B. Display all suppliers whose list price is less than 1000.
C. Display the number of products whose list price is more than the average list price.
D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.
Answer: C,E
Q12. - (Topic 1)
Here is the structure and data of the CUST_TRANS table: Exhibit:
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07'
C. SELECT transamt FROM cust_trans WHERE custno > '11'
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07'
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
Answer: A,C,D
Q13. - (Topic 2)
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers
whose last names start with A, B, or C, and credit limit is below 10, 000.
Evaluate the following two queries:
Which statement is true regarding the execution of the above queries?
A. Only the first query gives the correct result.
B. Only the second query gives the correct result.
C. Both execute successfully and give the same result.
D. Both execute successfully but do not give the required result.
Answer: A
Q14. - (Topic 1)
Which three statements are true regarding sub queries? (Choose three.)
A. Multiple columns or expressions can be compared between the main query and sub query
B. Main query and sub query can get data from different tables
C. Sub queries can contain GROUP BY and ORDER BY clauses
D. Main query and sub query must get data from the same tables
E. Sub queries can contain ORDER BY but not the GROUP BY clause
F. Only one column or expression can be compared between the main query and subqeury
Answer: A,B,C
Q15. - (Topic 1)
The STUDENT_GRADES table has these columns:
STUDENT_IDNUMBER(12)
SEMESTER_ENDDATE
GPANUMBER(4,3)
The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date.
Which statement accomplish this?
A.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
B.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa ASC
C.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
D.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
E.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
F.
SELECT student_id,semester_end,gpa FROM student_grades ORDER BY semester_end,gpa DESC
Answer: F