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 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'
Correct Answer : 2
Explanation: The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. LIKE calculates strings using characters as defined by the input character set. LIKEC uses Unicode complete characters. LIKE2 uses UCS2 code points. LIKE4 uses UCS4 code points.
Case is significant in all conditions comparing character expressions that the LIKE condition and the equality (=) operators. You can use the UPPER function to perform a case-insensitive match, as in this condition:
UPPER(last_name) LIKE 'SM%' Pattern Matching on Indexed Columns
When you use LIKE to search an indexed column for a pattern, Oracle can use the index to improve performance of a query if the leading character in the pattern is not % or _. In this case, Oracle can scan the index by this leading character. If the first character in the pattern is % or _, then the index cannot improve performance because Oracle cannot scan the index.
LIKE Condition: General Examples
This condition is true for all last_name values beginning with Ma:
last_name LIKE 'Ma%' char1 is a character expression, such as a character column, called the search value.
char2 is a character expression, usually a literal, called the pattern.
esc_char is a character expression, usually a literal, called the escape character.
The LIKE condition is the best choice in almost all situations. Use the following guidelines to determine whether any of the variations would be helpful in your environment:
Use LIKE2 to process strings using UCS-2 semantics. LIKE2 treats a Unicode supplementary character as two characters.
Use LIKE4 to process strings using UCS-4 semantics. LIKE4 treats a Unicode supplementary character as one character.
Use LIKEC to process strings using Unicode complete character semantics. LIKEC treats a composite character as one character.
If esc_char is not specified, then there is no default escape character. If any of char1, char2, or esc_char is null, then the result is unknown. Otherwise, the escape character, if specified, must be a character string of length 1.
All of the character expressions (char1, char2, and esc_char) can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. If they differ, then Oracle converts all of them to the datatype of char1.
The pattern can contain special pattern-matching characters:
An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.
A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern '%' cannot match a null.
Question : Examine the structure of the employees table.
1. A,C
2. B,E
3. D,E
4. B,F
5. B,D
Correct Answer : 5
Explanation: WHAT IS A SUBQUERY IN ORACLE?
In Oracle, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.
WHERE CLAUSE
Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.
For example:
SELECT *
FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
FROM all_tab_columns cols
WHERE cols.column_name = 'SUPPLIER_ID');
Limitations
Oracle allows up to 255 levels of subqueries in the WHERE clause.
FROM CLAUSE : A subquery can also be found in the FROM clause. These are called inline views.
For example:
SELECT suppliers.name, subquery1.total_amt FROM suppliers,
(SELECT supplier_id, SUM(orders.amount) AS total_amt FROM orders GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;
In this example, we've created a subquery in the FROM clause as follows:
(SELECT supplier_id, SUM(orders.amount) AS total_amt FROM orders GROUP BY supplier_id) subquery1
This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.
Limitations
Oracle allows an unlimited number of subqueries in the FROM clause.
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
Correct Answer : 3
Explanation:The syntax for the Oracle DISTINCT clause is:
SELECT DISTINCT expressions FROM tables WHERE conditions;
Parameters or Arguments : expressions are the columns or calculations that you wish to retrieve. tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. conditions are conditions that must be met for the records to be selected.
NOTE : When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.
When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.
In Oracle, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value. EXAMPLE - WITH SINGLE EXPRESSION
Let's look at the simplest Oracle DISTINCT clause example. We can use the Oracle DISTINCT clause to return a single field that removes the duplicates from the result set. For example:
SELECT DISTINCT state FROM customers WHERE last_name = 'Smith';
This Oracle DISTINCT example would return all unique state values from the customers table where the customer's last_name is 'Smith'.
Duplicate Rows : Unless you indicate otherwise, SQL displays the results of a query without eliminating the
duplicate rows.
To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword.
You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all , the selected columns, and the result is every distinct combination of the columns.
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?
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
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?
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?
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.
1. A,B
2. C,D
3. D,E
4. B,D
5. A,C