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 customers table.
Using the customers table, you need to generate
a report that shows the average credit limit for
customers in Washington and NEW YORK.
Which SQL statement would produce the required result?

1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D



Correct Answer : Get Lastest Questions and Answer :


Explanation: The Oracle/PLSQL AVG function returns the average value of an expression.

SYNTAX

The syntax for the Oracle/PLSQL AVG function is:

SELECT AVG( expression )
FROM tables
WHERE conditions;
The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.
SYNTAX : The syntax for the Oracle/PLSQL NVL function is:
NVL( string1, replace_with )
Parameters or Arguments

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.






Question : View the Exhibit and examine the data in the employees table:
You want to display all the employee names and their corresponding manager names.
Evaluate the given query:
Which join option can be used in the blank in the above query to get the required output?


1. INNER JOIN
2. FULL OUTER JOIN
3. Access Mostly Uused Products by 50000+ Subscribers
4. RIGHT OUTER JOIN


Correct Answer : Get Lastest Questions and Answer : Exp: What is the difference between a left outer join and a right outer join? It is best to illustrate the differences between left outer joins and right outer joins by use of an example. Here we have 2 tables that we will use for our example:
Employee Location
EmpID EmpName
13 Jason
8 Alex
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 San Jose
8 Los Angeles
3 Pune, India
17 Chennai, India
39 Bangalore, India
For the purpose of our example, it is important to note that the very last employee in the Employee table (Johnson, who has an ID of 25) is not in the Location table. Also, no one from the Employee table is from Bangalore (the employee with ID 39 is not in the Employee table). These facts will be significant in the discussion that follows. A left outer join : Using the tables above, here is what the SQL for a left outer join would look like:
select * from employee left outer join location on employee.empID = location.empID;
In the SQL above, we are joining on the condition that the employee ID's match in the tables Employee and Location. So, we will be essentially combining 2 tables into 1, based on the condition that the employee ID's match. Note that we can get rid of the "outer" in left outer join, which will give us the SQL below. This is equivalent to what we have above. select * from employee left join location on employee.empID = location.empID;
What do left and right mean? A left outer join retains all of the rows of the "left" table, regardless of whether there is a row that matches on the "right" table. What are the "left" and "right" tables? That's easy - the "left" table is simply the table that comes first in the join statement - in this case it is the Employee table, it's called the "left" table because it appears to the left of the keyword "join". So, the "right" table in this case would be Location. The SQL above will give us the result set shown below.
Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Jason 13 San Jose
8 Alex 8 Los Angeles
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
25 Johnson NULL NULL
As you can see from the result set, all of the rows from the "left" table (Employee) are returned when we do a left outer join. The last row of the Employee table (which contains the "Johson" entry) is displayed in the results even though there is no matching row in the Location table. As you can see, the non-matching columns in the last row are filled with a "NULL". So, we have "NULL" as the entry wherever there is no match.




Question : Evaluate the following query:
What would be the outcome of the above query?

1. It produces an error because flower braces have been used.
2. It produces an error because the data types are not matching.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It executes successfully and displays the literal "{'s start date was \} * for each row in the output.


Correct Answer : Get Lastest Questions and Answer :
Exp: So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character-enclosing symbols could have been anything other than single quotation marks. Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or (angle brackets). Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character
The syntax of the alternative quote operator is as follows: q'delimiter'character literal which may include the single quotes delimiter' where delimiter can be
any character or bracket. Alternative Quote (q) Operator Specify your own quotation mark delimiter. Select any delimiter. Increase readability and usability.
SELECT department_name || q'[ Department's Manager Id: ]' || manager_id AS "Department and Manager" FROM departments; Alternative Quote (q) Operator
Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter. You can choose any convenient delimiter, single-byte or multi byte, or any of the following
character pairs: [ ], { }, ( ), In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string

Related Questions

---

Question : View the Exhibit and examine the structure of the products table.
What would be the outcome if all the parentheses are removed from the above statement?

1. It produces a syntax error.
2. The result remains unchanged.
3. The total price value would be lower than the correct value.
4. The total price value would be higher than the correct value.


Question : Examine the data in the PROMO_BEGIN_DATE column of the promotions table:

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


Question : You want to display the date for the first Monday of the next month and issue the following
command:

SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;

What is the outcome?



1. It executes successfully and returns the correct result.
2. It executes successfully but does not return the correct result.
3. It generates an error because TO_CHAR should be replaced with TO_DATE.
4. It generates an error because rrrr should be replaced by rr in the format string.
5. It generates an error because fm and double quotation marks should not be used in the format string.


Question : View the Exhibit and examine the structure of the promotions table.
You need to generate a report of all promos from the promotions table based on the following
conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which where clause would give the required result?

1. WHERE promo_name NOT LIKE '%T' AND promo_name NOT LIKE 'N' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'
2. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'
3. WHERE promo_name NOT LIKE '%T' AND promo_name NOT LIKE '%N' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'
4. WHERE promo_name NOT LIKE '%T%' AND promo_name NOT LIKE '%N%' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'



Question : Examine the structure of the employees table.


1. A,C
2. B,E
3. D,E
4. B,F
5. B,D




Question : Using the customers table, you need to generate a report that shows % of each credit amount in
each income level. The report should NOT show any repeated credit amounts in each income
level. Which query would give the required result?

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