Q1. View the Exhibit and examine the description of SALES and PROMOTIONS tables.
You want to delete rows from the sales table, where the PROMO_NAME column in the promotions table has either blowout sale of everyday low prices as values.
Which three delete statements are valid?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B,C,D
Q2. View the Exhibit and examine the structures of the employees and departments tables.
You want to update the employees table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
What is the outcome?
A. It executes successfully and gives the correct result.
B. It executes successfully but does not give the correct result.
C. It generates an error because a subquery cannot have a join condition in an update statement.
D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.
Answer: B
Q3. You need to generate a list of all customer last names with their credit limits from the customers table.
Those customers who do not have a credit limit should appear last in the list.
Which two queries would achieve the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B,C
Explanation:
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting The default sort order is ascending:
. Numeric values are displayed with the lowest values first (for example, 1 to 999).
. Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95).
. Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).
. Null values are displayed last for ascending sequences and first for descending sequences.
-ANSWER B
. You can also sort by a column that is not in the SELECT list.
Q4. You want to display 5 percent of the employees with the highest salaries in the EMPLOYEES table.
Which query will generate the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q5. View the Exhibit and examine the data in the costs table.
You need to generate a report that displays the IDs of all products in the costs table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the order by clause.
C. It produces an error because the DESC option cannot be used with an expression in the order by clause.
D. It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.
Answer: A
Q6. Examine the data in the ename and hiredate columns of the employees table:
You want to generate a list of user IDs as follows: You issue the following query:
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. View the Exhibits and examine the structures of the products, sales, and customers tables.
You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for a customers in 'Tokyo'.
Which two queries give the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A,C
Q8. You issue the following command to alter the country column in the departments table: Which statement is true?
A. It produces an error because column definitions cannot be altered to add default values.
B. It executes successfully and all the rows that have a null value for the country column will be updated with the value 'USA'.
C. It executes successfully. The modification to add the default value takes effect only from subsequent insertions to the table.
D. It produces an error because the data type for the column is not specified.
Answer: B
Q9. 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? A. Option A
B. Option B
C. Option C
D. Option D
Answer: C,D
Q10. The customers table has the following structure:
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B