Premium

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



Question : View the Exhibit and examine the
structure of the SALES table.
The following query is written to retrieve all
those product IDs from the SALES table that have
more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
: View the Exhibit and examine the
1. It executes successfully and generates the required result.
2. It produces an error because count(*) should be specified in the SELECT clause also.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).


Correct Answer : Get Lastest Questions and Answer :
Exp: Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further restricting
the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for which the
specified condition is true.
The Oracle server performs the following steps when you use the HAVING clause:
1. Rows are grouped.
2. The group function is applied to the group.
3. Access Mostly Uused Products by 50000+ Subscribers
The HAVING clause can precede the GROUP BY clause, but it is recommended that you place
the GROUP BY clause first because it is more logical. Groups are formed and group functions are
calculated before the HAVING clause is applied to the groups in the SELECT list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.







Question : View the Exhibit and examine the structure of the customers table.
Using the customers table, you need to generate a report that shows an increase in the credit limit
by 15% for all customers. Customers whose credit limit has not been entered should have the
message "Not Available" displayed.
Which SQL statement would produce the required result?
: View the Exhibit and examine the structure of the customers table.
1. A
2. B
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D

Correct Answer : Get Lastest Questions and Answer :
Exp: : NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
NVL(commission_pct, 0)
NVL(hire_date, '01-JAN-97')
NVL(job_id, 'No Job Yet')




Question : View the Exhibit and examine the structure of the promotions table.
Evaluate the given SQL statement:
Which statement is true regarding the outcome of the above query?

: View the Exhibit and examine the structure of the promotions table.
1. It shows COST_REMARK for all the promos in the table.
2. It produces an error because the SUBQUERY gives an error.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It produces an error because SUBQUERIES cannot be used with the case expression.

Correct Answer : Get Lastest Questions and Answer :
Exp: In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. You cannot specify the literal NULL for every return_expr and the else_expr. In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise, Oracle returns null. Oracle Database uses short-circuit evaluation. That is, for a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr. Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr. For a searched CASE expression, the database evaluates each condition to determine whether it is true, and never evaluates a condition if the previous condition was true. For a simple CASE expression, the expr and all comparison_expr values must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. For both simple and searched CASE expressions, all of the return_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all return expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN ... THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression. Simple CASE Example : For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.
SELECT cust_last_name, CASE credit_limit WHEN 100 THEN 'Low' WHEN 5000 THEN 'High' ELSE 'Medium' END FROM customers;
CUST_LAST_NAME CASECR
-------------------- ------
Bogart Medium
Nolte Medium
Loren Medium
Gueney Medium
Searched CASE Example : The following statement finds the average salary of the employees in the sample table oe.employees, using $2000 as the lowest salary possible: SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary ELSE 2000 END) "Average Salary" FROM employees e;
Average Salary
--------------
6461.68224


Related Questions

Question :

:
1. A
2. B
3. C
4. D




Question : View the Exhibit and examine the data in the products table.
You need to display product names from the products table that belong to the 'software/other'
category with minimum prices as either S2000 or S4000 and no unit of measure.
You issue the given query:

Which statement is true regarding the above query?
:
1. It executes successfully but returns no result.
2. It executes successfully and returns the required result.

3. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

4. It generates an error because the condition specified for the prod category column is not valid.



Question : You need to display the first names of all customers from the
customers table that contain the character 'e' and have the character 'a' in
the second last position. Which query would give the required output?

 : You need to display the first names of all customers from the
1. A
2. B
3. C
4. D



Question : 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?

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

 : Examine the structure of the customers table:
1. A,C
2. A,B
3. D,E
4. B,F
5. B,D



Question : 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?

 : You need to generate a list of all customer last names with their credit limits from the customers
1. A,C
2. A,B
3. C,D
4. B,D
5. B,C






Question : You issue the following command to alter the country column in the departments table:
SQL> ALTER TABLE departments MODIFY (country DEFAULT 'USA');
Which statement is true?
 : You issue the following command to alter the country column in the departments table:
1. It produces an error because column definitions cannot be altered to add default values.
2. It executes successfully and all the rows that have a null value for the country column will be updated with the value 'USA'.

3. It executes successfully. The modification to add the default value takes effect only from subsequent insertions to the table.
4. It produces an error because the data type for the column is not specified.




Question : Which two statements are true regarding constraints?
A. A foreign key cannot contain null values.
B. A column with the unique constraint can contain null values.
C. A constraint is enforced only for the insert operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.
 : Which two statements are true regarding constraints?
1. A,B
2. C,D
3. D,E
4. B,D
5. A,C