OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
Review Questions
1. You issue the following query:
SELECT salary "Employee Salary"
FROM employees;
How will the column heading appear in the result?
A. EMPLOYEE SALARY
B. EMPLOYEE_SALARY
C. Employee Salary
D. employee_salary
Column alias names enclosed in quotation marks will appear as typed.
Spaces and mixed case appear in the column alias name only when
the alias is enclosed in double quotation marks.
Ans: C
2. The EMP table is defined as follows:
--------------------------
EMP Table
--------------------------
Column Datatype Length
--------------------------
EMPNO NUMBER 4
ENAME VARCHAR2 30
SALARY NUMBER 14,2
COMM NUMBER 10,2
DEPTNO NUMBER 2
--------------------------
You perform the following two queries:
SELECT empno enumber, ename
FROM emp ORDER BY 1;
SELECT empno, ename
FROM emp ORDER BY empno ASC;
Which of the following is true?
A. Statements 1 and 2 will produce the same result.
B. Statement 1 will execute; statement 2 will return an error.
C. Statement 2 will execute; statement 1 will return an error.
D. Statements 1 and 2 will execute but produce different results.
---
Statements 1 and 2 will produce the same result. You can use the column name,
column alias, or column position in the ORDER BY clause. The default sort
order is ascending. For a descending sort, you must explicitly specify
that order with the DESC keyword.
Ans: A
3. You issue the following SELECT statement on the EMP table shown in question 2.
SELECT (200+((salary*0.1)/2)) FROM emp;
What will happen to the result if all of the parentheses are removed?
A. No difference, because the answer will always be NULL.
B. No difference, because the result will be the same.
C. The result will be higher.
D. The result will be lower.
----
3.
In the arithmetic evaluation, multiplication and division have precedence
over addition and subtraction. Even if you do not include the parentheses,
salary*0.1 will be evaluated first. The result is then divided by 2,
and its result is added to 200.
Ans: B
4. In the following SELECT statement, which component is a literal?
(Choose all that apply.)
SELECT 'Employee Name: ' || ename
FROM emp where deptno = 10;
A. 10
B. ename
C. Employee Name:
D. ||
---
Character literals in the SQL statement are enclosed in single quotation marks.
Literals are concatenated using ||. Employee Name: is a character literal,
and 10 is a numeric literal.
Ans: A, C
5. When you try to save 34567.2255 into a column defined as NUMBER(7,2)
what value is actually saved?
A. 34567.00
B. 34567.23
C. 34567.22
D. 3456.22
-----
Since the numeric column is defined with precision 7 and scale 2, you can have
five digits in the integer part and two digits after the decimal point. The digits
after the decimal are rounded.
Ans: B
6. What is the default display length of the DATE datatype column?
A. 8
B. 9
C. 19
D. 6
----
The default display format of the DATE column is DD-MON-YY, whose length
is 9. This is U.S. specific and will be different as user settings vary.
Ans: B
7. What will happen if you query the EMP table shown in question 2 with the following?
SELECT empno, DISTINCT ename, salary FROM emp;
A. EMPNO, unique values of ENAME and then SALARY are displayed.
B. EMPNO, unique values of the two columns, ENAME and SALARY, are displayed.
C. DISTINCT is not a valid keyword in SQL.
D. No values will be displayed because the statement will return an error.
------
DISTINCT is used to display a unique result row, and it should follow immediately
after the keyword SELECT. Uniqueness is identified across the row, not a single column.
Ans: D
8. Which clause in a query limits the rows selected?
A. ORDER BY
B. WHERE
C. SELECT
D. FROM
-----
The WHERE clause is used to limit the rows returned from a query. The WHERE
clause condition is evaluated, and rows are returned only if the result is TRUE.
The ORDER BY clause is used to display the result in certain order.
Ans: B
9. The following listing shows the records of the EMP table.
EMPNO ENAME SALARY COMM DEPTNO
--------- ---------- --------- --------- ---------
7369 SMITH 800 20
7499 ALLEN 1600 300 30
7521 WARD 1250 500 30
7566 JONES 2975 20
7654 MARTIN 1250 1400 30
7698 BLAKE 2850 30
7782 CLARK 2450 24500 10
7788 SCOTT 3000 20
7839 KING 5000 50000 10
7844 TURNER 1500 0 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 13000 10
When you issue the following query, which value will be displayed in the first row?
SELECT empno
FROM emp
WHERE deptno = 10
ORDER BY ename DESC;
A. MILLER
B. 7934
C. 7876
D. No rows will be returned because ename cannot be used in the ORDER BY clause.
------------
There are three records belonging to DEPTNO 10: EMPNO 7934 (MILLER), 7839 (KING),
and 7782 (CLARK). When you sort their names by descending order, MILLER is
the first row to display. You can use alias names and columns that are not in the
SELECT clause in the ORDER BY clause.
Ans: B
10. Refer to the listing of records in the EMP table in question 9.
How many rows will the following query return?
SELECT * FROM emp WHERE ename BETWEEN 'A' AND 'C'
A. 4
B. 2
C. A character column cannot be used in the BETWEEN operator.
D. 3
----
Here, a character column is compared against a string using the BETWEEN operator,
which is equivalent to ename >= 'A' AND ename <= 'C'. The name CLARK will
not be included in this query, because 'CLARK' is > 'C'.
Ans: D
11. Refer to the EMP table in question 2. When you issue the following query,
which line has an error?
SELECT empno "Enumber", ename "EmpName"
FROM emp
WHERE deptno = 10
AND "Enumber" = 7782
ORDER BY "Enumber";
A. 1
B. 5
C. 4
D. No error; the statement will finish successfully.
----
Column alias names cannot be used in the WHERE clause. They can be used in the ORDER BY clause.
Ans: C
12. You issue the following query:
SELECT empno, ename
FROM emp
WHERE empno = 7782 OR empno = 7876;
Which other operator can replace the OR condition in the WHERE clause?
A. IN
B. BETWEEN .. AND ..
C. LIKE
D. <=
E. >=
---
The IN operator can be used. You can write the WHERE clause as
WHERE empno IN (7782, 7876);
Ans: A
13. The following are clauses of the SELECT statement:
WHERE
FROM
ORDER BY
In which order should they appear in a query?
A. 1, 3, 2
B. 2, 1, 3
C. 2, 3, 1
D. The order of these clauses does not matter.
---
The FROM clause appears after the SELECT statement, followed by WHERE and
ORDER BY clauses. The FROM clause specifies the table names, the WHERE clause
limits the result set, and the ORDER BY clause sorts the result.
Ans: B
14. Which statement searches for PRODUCT_ID values that begin with DI_ from the ORDERS table?
A. SELECT * FROM ORDERS
WHERE PRODUCT_ID = 'DI%';
B. SELECT * FROM ORDERS
WHERE PRODUCT_ID LIKE 'DI_' ESCAPE '\';
C. SELECT * FROM ORDERS
WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\';
D. SELECT * FROM ORDERS
WHERE PRODUCT_ID LIKE 'DI\_' ESCAPE '\';
E. SELECT * FROM ORDERS
WHERE PRODUCT_ID LIKE 'DI_%' ESCAPE '\';
------
Since _ is a special pattern-matching character, you need to include the
ESCAPE clause in LIKE. The % character matches any number of characters including 0,
and _ matches a single character.
Ans: C
15. COUNTRY_NAME and REGION_ID are valid column names in the COUNTRIES table.
Which one of the following statements will execute without an error?
A.
SELECT country_name, region_id,
CASE region_id = 1 THEN 'Europe',
region_id = 2 THEN 'America',
region_id = 3 THEN 'Asia',
ELSE 'Other' END Continent
FROM countries;
B.
SELECT country_name, region_id,
CASE (region_id WHEN 1 THEN 'Europe',
WHEN 2 THEN 'America',
WHEN 3 THEN 'Asia',
ELSE 'Other') Continent
FROM countries;
C.
SELECT country_name, region_id,
CASE region_id WHEN 1 THEN 'Europe'
WHEN 2 THEN 'America'
WHEN 3 THEN 'Asia'
ELSE 'Other' END Continent
FROM countries;
D.
SELECT country_name, region_id,
CASE region_id WHEN 1 THEN 'Europe'
WHEN 2 THEN 'America'
WHEN 3 THEN 'Asia'
ELSE 'Other' Continent
FROM countries;
----
Ans: C. A CASE expression begins with the keyword CASE and ends with keyword END.
16. Which special character is used to query all the columns from the table without
listing each column by name?
A .%
B. &
C. @
D. *
------
Ans: D. An asterisk (*) is used to denote all columns in a table.
17. The EMPLOYEE table has the following data:
EMP_NAME HIRE_DATE SALARY
---------- --------- ----------
SMITH 17-DEC-90 800
ALLEN 20-FEB-91 1600
WARD 22-FEB-91 1250
JONES 02-APR-91 5975
WARDEN 28-SEP-91 1250
BLAKE 01-MAY-91 2850
What will be the value in the first row of the result set when the following query is executed?
SELECT hire_date FROM employee
ORDER BY salary, emp_name;
A. 02-APR-91
B. 17-DEC-90
C. 28-SEP-91
D. The query is invalid, because you cannot have a column in the
ORDER BY clause that is not part of the SELECT clause.
----
Ans: B. The default sorting order for numeric column is ascending. The columns
are sorted first by salary and then by name, so the row with the lowest salary is
displayed first. It is perfectly valid to use a column in the ORDER BY clause
that is not part of the SELECT clause.
18. Which SQL statement will query the EMPLOYEES table for FIRST_NAME, LAST_NAME,
and SALARY of all employees in DEPARTMENT_ID 40 in the alphabetical order of last name?
A.
SELECT first_name last_name salary
FROM employees
ORDER BY last_name
WHERE department_id = 40;
B.
SELECT first_name, last_name, salary
FROM employees
ORDER BY last_name ASC
WHERE department_id = 40;
C.
SELECT first_name last_name salary
FROM employees
WHERE department_id = 40
ORDER BY last_name ASC;
D.
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 40
ORDER BY last_name;
E.
SELECT first_name, last_name, salary
FROM TABLE employees
WHERE department_id IS 40
ORDER BY last_name ASC;
-------
In the SELECT clause, the column names should be separated by commas.
An alias name may be provided for each column with a space or using the keyword AS.
The FROM clause should appear after the SELECT clause. The WHERE clause appears
after the FROM clause. The ORDER BY clause comes after the WHERE clause.
Ans: D
19. 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 Oracle9i.
---
There is no default escape character in Oracle9i.
If your search includes pattern-matching characters such as _ or %, define an escape
character using the ESCAPE keyword in the LIKE operator.
Ans: D
20. Column alias names cannot be used in which clause?
A. SELECT clause
B. WHERE clause
C. ORDER BY clause
D. None of the above
------
Ans: B. 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 indicate the
column by its position in the SELECT clause.
No comments:
Post a Comment