Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice 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.
Correct Answer : 1
Explanation:
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?
1. A
2. B
3. C
4. D
Correct Answer : 1
Explanation: The SUBSTR(string, start position, number of characters) function accepts three parameters and
returns a string consisting of the number of characters extracted from the source string, beginning
at the specified start position:
substr('http://www.domain.com', 12, 6) = domain
The position at which the first character of the returned string begins.
When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the first
character.
When position is negative, then the function counts backward from the end of string.
substring_length
The length of the returned string. SUBSTR calculates lengths using characters as defined by the
input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode
complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
When you do not specify a value for this argument, then the function
The INSTR(source string, search item, [start position], [nth occurrence of search item]) function
returns a number that represents the position in the source string, beginning from the given start
position, where the nth occurrence of the search item begins:
instr('http://www.domain.com', '.', 1, 2) = 18
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
1. A,C
2. A,B
3. D,E
4. B,F
5. B,D
Correct Answer : 2
Explanation: Let's illustrate the need for a self join with an example. Suppose we have the following table - that is called employee. The employee table has 2 columns - one for the employee name (called employee_name), and one for the employee location (called employee_location): employee
employee_name employee_location
Joe New York
Sunil India
Alex Russia
Albert Canada
Jack New York
Now, suppose we want to find out which employees are from the same location as the employee named Joe. In this example, that location would be New York. Let's assume - for the sake of our example - that we can not just directly search the table for people who live in New York with a simple query like this (maybe because we don't want to hardcode the city name) in the SQL query:
SELECT employee_name FROM employee WHERE employee_location = "New York"
So, instead of a query like that what we could do is write a nested SQL query (basically a query within another query - which more commonly called a subquery) like this: SELECT employee_name FROM employee WHERE employee_location in ( SELECT employee_location FROM employee WHERE employee_name = "Joe")
Using a subquery for such a simple question is inefficient. Is there a more efficient and elegant solution to this problem?
It turns out that there is a more efficient solution - we can use something called a self join. A self join is basically when a table is joined to itself. The way you should visualize a self join for a given table is by imagining that a join is performed between two identical copies of that table. And that is exactly why it is called a self join - because of the fact that it's just the same table being joined to another copy of itself rather than being joined with a different table.
How does a self join work : Before we come up with a solution for this problem using a self join, we should go over some concepts so that you can fully understand how a self join works. This will also make the SQL in our self join tutorial a lot easier to understand, which you will see further below.
A self join must have aliases : In a self join we are joining the same table to itself by essentially creating two copies of that table. But, how do we distinguish between the two different copies of the table - because there is only one table name after all? Well, when we do a self join, the table names absolutely must use aliases otherwise the column names would be ambiguous. In other words, we would not know which table's columns are being referenced without using aliases for the two copies of the table. If you don't already know what an alias is, it's simply another name given to a table, and that name is then used in the SQL query to reference the table. So, we will just use the aliases e1 and e2 for the employee table when we do a self join.
Related Questions
Question : Which create table statement is valid?
1. A
2. B
3. C
4. D
Question : Which statement is true regarding the UNION operator?
1. By default, the output is not sorted.
2. Null values are not ignored during duplicate checking.
3. Names of all columns must be identical across all select statements.
4. The number of columns selected in all select statements need not be the same.
Question :
1. A,C
2. B,C
3. A,B
4. B,D
4. C,D
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?
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.
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?
1. A
2. B
3. C
4. D
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?
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.