Review Questions – Chapter 1

                                         Review Questions – Chapter 1

1.	Check 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
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.
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.
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.   ||
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
6.	What is the default display length of the DATE datatype column? A.    8 B.    9 C.    19 D.   6
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.
8.	Which clause in a query limits the rows selected? A.    ORDER BY B.    WHERE C.    SELECT D.   FROM
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.
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
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.
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.    >=
13.	The following are clauses of the SELECT statement: 1.    WHERE 2.    FROM 3.    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.
14.	Which statement searches for PRODUCT_ID values that begin with DI_ from the ORDERS table? SELECT * FROM ORDERS WHERE PRODUCT_ID = 'DI%'; SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_' ESCAPE '\'; SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\'; SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_' ESCAPE '\'; SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_%' ESCAPE '\';
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? 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; 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; 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; 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;
16.	Which special character is used to query all the columns from the table without listing each column by name? A.    % B.    & C.    @ D.   *
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.
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? SELECT first_name last_name salary FROM   employees ORDER BY last_name WHERE  department_id = 40; SELECT first_name, last_name, salary FROM   employees ORDER BY last_name ASC WHERE  department_id = 40; SELECT first_name last_name salary FROM  employees WHERE  department_id = 40 ORDER BY last_name ASC; SELECT first_name, last_name, salary FROM   employees WHERE  department_id = 40 ORDER BY last_name; SELECT first_name, last_name, salary FROM   TABLE employees WHERE  department_id IS 40 ORDER BY last_name ASC;
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.
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

 Answers

1.	C. 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.
2.	A. 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.
3.	B. 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.
4.	A, C. 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.
5.	B. 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.
6.	B. 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.
7.	D. 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.
8.	B. 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.
9.	B. 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.
10.	D. 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'.
11.	C. Column alias names cannot be used in the WHERE clause. They can be used in the ORDER BY clause.
12.	A. The IN operator can be used. You can write the WHERE clause as WHERE empno IN (7782, 7876);
13.	B. 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.
14.	C. 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.
15.	C. A CASE expression begins with the keyword CASE and ends with keyword END.
16.	D. An asterisk (*) is used to denote all columns in a table.
17.	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.	D. 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.
19.	D. 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.
20.	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.