1.
The EMP table has the following data:
EMPNO ENAME SAL COMM
---------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500 0
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
Consider the following two SQL statements:
1. SELECT empno, ename, sal, comm
FROM emp WHERE comm IN (0, NULL);
2. SELECT empno, ename, sal, comm
FROM emp WHERE comm = 0 OR comm IS NULL;
- [A]1 and 2 will produce the same result.
- [B]1 will error; 2 will work fine.
- [C]1 and 2 will produce different results.
- [D]1 and 2 will work but will not return any rows.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| In the first SQL, the comm IN (0, NULL) will be treated as comm = 0 OR comm = NULL. For all NULL comparisons, you should use IS NULL instead of = NULL. The first SQL will return only one row where comm = 0, whereas the second SQL will return all the rows that have comm = NULL as well as comm = 0. |
2.
Which two statements regarding substitution variables are true?
- [A]&variable is defined by SQL*Plus, and its value will be available for the duration of the session.
- [B]&&variable is defined by SQL*Plus, and its value will be available for the duration of the session.
- [C]&n (where n is a any integer) variables are defined by SQL*Plus when values are passed in as arguments to the script, and their values will be available for the duration of the session.
- [D]&&variable is defined by SQL*Plus, and its value will be available only for every reference to that variable in the current SQL.
- Answer & Explanation
- Report
Answer : [B, C]
Explanation :
Explanation :
| When a variable is preceded by double ampersands, SQL*Plus defines that variable. Similarly, when you pass values to a script using START script_name arguments, SQL*Plus defines those variables. Once a variable is defined, its value will be available for the duration of the session or until you use UNDEFINE variable. |
3.
What is wrong with the following statements submitted in SQL*Plus?
DEFINE V_DEPTNO = 20 SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = V_DeptNo;
- [A]Nothing is wrong. The query lists the employee name and salary of the employees who belong to department 20.
- [B]The DEFINE statement declaration is wrong.
- [C]The substitution variable is not preceded with the & character.
- [D]The substitution variable in the WHERE clause should be V_DEPTNO instead of V_DeptNo.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| The query will return an error, because the substitution variable is used without an ampersand (&) character. In this query, Oracle treats V_DEPTNO as another column name from the table and returns an error. Substitution variables are not case sensitive. |
4.
Column alias names cannot be used in which clause?
- [A]SELECT clause
- [B]WHERE clause
- [C]ORDER BY clause
- [D]None of the above
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| Column alias names cannot be used in the WHERE clause of the SQL statement. In the ORDER BY clause, you can use the column name or alias name, or you can indicate the column by its position in the SELECT clause. |
5.
When doing pattern matching using the LIKE operator, which character is used as the
default escape character by Oracle?
- [A]|
- [B]/
- [C]\
- [D]There is no default escape character in Oracle.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| There is no default escape character in Oracle for pattern matching. If your search includes pattern-matching characters such as _ or %, define an escape character using the ESCAPE keyword in the LIKE operator. |
|
|
||||||||||||||||||||||||||||
