Premium

Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)



Question : 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 given (left side) command:
What is the outcome?
: View the Exhibit and examine the structures of the employees and departments tables.
1. It executes successfully and gives the correct result.
2. It executes successfully but does not give the correct result.
3. It generates an error because a subquery cannot have a join condition in an update statement.
4. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.

Correct Answer : 2
Explanation:
- Set the employees’ salary in location_id 2100 to 1.1 times the average salary of their department.
- Set the employees’ commission in location_id 2100 to 1.5 times the average commission of their
These two conditions requires to update in location_id 2100 whereas
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700). Says update only in 2900 and 2700 so it won’t work

Because of this set clause:
(salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments WHERE departments.location_id IN(2900,2700,2100))
So salary and commission would be set to average of those from three locations whereas you need to use average only from their (2100) location






Question : You need to produce a report where each customer's credit limit has been incremented by $.
In the output, the customer's last name should have the heading Name and the incremented credit
limit should be labeled New credit Limit. The column headings should have only the first letter of
each word in uppercase.
Which statement would accomplish this requirement?
: You need to produce a report where each customer's credit limit has been incremented by $.
1. A
2. B
3. C
4. D

Correct Answer : 3

Explanation: A column alias:
- Renames a column heading
- Is useful with calculations
- Immediately follows the column name (There can also be the optional AS keyword between the
column name and the alias.)
- Requires double quotation marks if it contains spaces or special characters, or if it is case
sensitive.






Question : 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 given query:
Which statement is true regarding the above query?
: View the Exhibit and examine the data in the costs table.
1. It executes and produces the required result.
2. It produces an error because an expression cannot be used in the order by clause.
3. It produces an error because the DESC option cannot be used with an expression in the order by clause.
4. It produces an error because the expression in the ORDER by clause should also be specified in the SELECT clause.

Correct Answer : 1

Explanation:






Related Questions

Question : You want to display percent of the employees with the highest salaries in the EMPLOYEES
table. Which query will generate the required result?
 : You want to display percent of the employees with the highest salaries in the EMPLOYEES
1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D


Question : In the customers table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'Abigail'.
Evaluate the given query:
What would be the outcome?

 : In the customers table, the CUST_CITY column contains the value 'Paris' for the
1. Abigail PA
2. Abigail Pa
 3. Access Mostly Uused Products by 50000+ Subscribers
4. An error message


Question : View the Exhibit and evaluate the structure and data in the CUST_STATUS table.
You issue the given SQL statement:
Which statement is true regarding the execution of the above query?
 : View the Exhibit and evaluate the structure and data in the CUST_STATUS table.
1. It produces an error because the AMT_SPENT column contains a null value.
2. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It produces an error because the TO_NUMBER function must be used to convert the result of
the NULLIF function before it can be used by the NVL2 function.




Question : Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
 : Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
1. First normal form
2. Second normal form
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Fourth normal form



Question : Examine the types and examples of relationships that follow:
1. One-to-one a) Teacher to students
2. One-to-many b) Employees to Manager
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Many-to-many d) Customers to products
Which option indicates the correctly matched relationships?
: Examine the types and examples of relationships that follow:
1. 1-a, 2-b, 3-c, and 4-d
2. 1-c, 2-d, 3-a, and 4-b
 3. Access Mostly Uused Products by 50000+ Subscribers
4. 1-d, 2-b, 3-a, and 4-c


Question : You execute the givne sql commands:
For which substitution variables are you prompted for the input?
 : You execute the givne sql commands:
1. None, because no input required
2. Both the substitution variables 'hiredate' and 'mgr_id'
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Only 'mgr_id'