Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)
Question : Which expressions do not return NULL values?
A. select ((99 + 44) * 5) + null from dual;
B. select 'HadoopExam '||null||'QuickTechie.com' from dual;
C. select null/0 from dual;
D. select null||'HadoopExam'||null as "QuickTechie.com" from dual;
1. A,C
2. B,D
3. Access Mostly Uused Products by 50000+ Subscribers
4. A,B
5. B,C
Correct Answer : Get Lastest Questions and Answer : The NULL operator in an SQL statement represents a value that is not known or is not applicable. Siebel CRM evaluates an expression that includes a NULL operator differently than it evaluates other operators. NULL is not a value. A comparison function does not operate correctly if a NULL operator exists in the comparison. For instance, if NULL = NULL is not TRUE. Note the following:
SQL and Siebel CRM provide special functions and grammar that support NULL, including the IS NULL operator and the IfNull function. A comparison, string concatenation, and Boolean operation include special behavior that handles a NULL operator.
You can set the type for a NULL operator similar to how you set the type for a value. An operand or result can be a NULL string, NULL number, NULL Boolean, and so on.
If one side of a comparison is:
NULL. The comparison returns a NULL of type Boolean.
Is not NULL. The comparison returns TRUE or FALSE. For example, 1>2 is FALSE, and 1 lt NULL is NULL.
If one side of an arithmetic operation is NULL, then the operation returns NULL of the appropriate type, except for a string concatenation. NULL adds no characters during a string concatenation operation. For example:
1 + 2 is 3
1 + NULL is NULL (of type Integer)
"Fred" + ", Smith" is "Fred, Smith"
"Fred" + NULL is "Fred"
B and D do not return null values since character expressions are not affected in the same way by null values as arithmetic expressions. B and D ignore the presence of null values in their expressions and return the remaining character literals.
Question : Which of the following will return all the columns from a table
1. select all from table;
2. select % from table;
3. Access Mostly Uused Products by 50000+ Subscribers
4. select *.* from table;
Correct Answer : Get Lastest Questions and Answer :
Explanation: SELECT ALL FIELDS FROM ONE TABLE
Let's look at how to use an Oracle SELECT query to select all fields from a table.
SELECT *
FROM homes
WHERE bathrooms >= 2
ORDER BY home_type ASC;
In this Oracle SELECT statement example, we've used * to signify that we wish to select all fields from the homes table where the number of bathrooms is greater than or equal to 2. The result set is sorted by home_type in ascending order.
Question : There are rows of data in the Total table. Consider the following SQL statement: SELECT ' * ' "Score" FROM total;
Select correct statement
1. 1 row returned, Score column contains value 12
2. 100 rows returned, Score column contains value 12 for all 100 rows
3. Access Mostly Uused Products by 50000+ Subscribers
4. 100 rows returned, Score column contains value 4*3 for all 100 rows
5. Error
Correct Answer : Get Lastest Questions and Answer :
Related Questions
Question : What is/are correct statement for the following query
SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;
1. You can not use Alias in Order by clause
2. You can not use Alias in JOIN clause
3. When you use Alias, use for all the tables
4. None of the above
Question : Which type of Join illustrated in the image. (Blue color is for selected data)
1. Right
2. Left
3. Inner
4. None of the above
Question : Select the equivalent statement of below query
SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 3000 AND 3500;
1. SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id > 3500;
2. SELECT *
FROM customers
WHERE customer_id = < 3000
OR customer_id > 3500;
3. SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id >= 3500;
4. None of the above
Question : Select correct sql statement
1. INSERT INTO suppliers
(supplier_id supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;
2. INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no || name
FROM customers
WHERE customer_id > 5000;
3. INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;
4. All of the above
Question : What result is returned by the following statement?
SELECT COUNT(*) FROM DUAL;
1. 0
2. 1
3. Access Mostly Uused Products by 50000+ Subscribers
4. infinite
Question : Select the correct statement ...
1. Group functions may only be used when a GROUP BY clause is present.
2. Group functions can operate on multiple rows at a time.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Group functions can execute multiple times within a single group.