A. SELECT, FROM
B. ORDER BY, WHERE
C. SELECT, WHERE
D. SELECT, ORDER BY
2. Choose the query that extracts the LAST_NAME, JOB_ID,
and SALARY values from the EMPLOYEES table for records having JOB_ID values
of either SA_REP or MK_MAN and having SALARY values
in the range of $1,000 to $4,000. The SELECT and FROM clauses are SELECT
LAST_NAME, JOB_ID, SALARY Y FROM EMPLOYEES:
A. WHERE JOB_ID IN ('SA_REP','MK_MAN') AND SALARY > 1000
AND SALARY < 4000;
B. WHERE JOB_ID IN ('SA_REP','MK_MAN') AND SALARY BETWEEN
1000 AND 4000;
C. WHERE JOB_ID LIKE 'SA_REP%' AND 'MK_MAN%' AND SALARY
> 1000 AND SALARY < 4000;
D. WHERE JOB_ID = 'SA_REP' AND SALARY BETWEEN 1000 AND
4000 OR JOB_ID='MK_MAN';
3. Which of the following WHERE clauses contains an error?
The SELECT and FROM clauses are SELECT * FROM EMPLOYEES:
A. WHERE HIRE_DATE IN ('02-JUN-2004');
B. WHERE SALARY IN ('1000','4000','2000');
C. WHERE JOB_ID IN (SA_REP,MK_MAN);
D. WHERE COMMISSION_PCT BETWEEN 0.1 AND 0.5;
4. Choose the WHERE clause that extracts the DEPARTMENT_NAME
values containing the character literal "er" from the DEPARTMENTS table.
The
SELECT and FROM clauses are SELECT DEPARTMENT_NAME FROM
DEPARTMENTS:
A. WHERE DEPARTMENT_NAME IN ('%e%r');
B. WHERE DEPARTMENT_NAME LIKE '%er%';
C. WHERE DEPARTMENT_NAME BETWEEN 'e' AND 'r';
D. WHERE DEPARTMENT_NAME CONTAINS 'e%r';
5. Which two of the following conditions are equivalent to each other?
A. WHERE COMMISSION_PCT IS NULL
B. WHERE COMMISSION_PCT = NULL
C. WHERE COMMISSION_PCT IN (NULL)
D. WHERE NOT(COMMISSION_PCT IS NOT NULL)
6. Which two of the following conditions are equivalent to each other?
A. WHERE SALARY <=5000 AND SALARY >=2000
B. WHERE SALARY IN (2000,3000,4000,5000)
C. WHERE SALARY BETWEEN 2000 AND 5000
D. WHERE SALARY > 2000 AND SALARY < 5000
E. WHERE SALARY >=2000 AND <=5000
---
Answers:
1. C.
The SELECT clause facilitates projection by specifying the list of columns to be projected from a table, while the WHERE clause facilitates selection by limiting the rows retrieved based on its conditions.
A, B, and D are incorrect.
The FROM clause specifies the source of the rows being projected and the ORDER BY clause is used for sorting the selected rows.
2. B.
The IN operator efficiently tests whether the JOB_ID for a particular row is either SA_REP or MK_MAN, while the BETWEEN operator efficiently measures whether an employee’s SALARY value falls within the required range.
A, C, and D are incorrect.
A and C exclude employees who earn a salary of $1,000 or $4,000, since these SALARY values are excluded by the inequality operators.
C also selects JOB_ID values like SA_REP% and MK_MAN%, potentially selecting incorrect JOB_ID values.
D is half right.
The first half returns the rows with JOB_ID equal to SA_REP having SALARY values between $1,000 and $4,000. However, the second part (the OR clause), correctly tests for JOB_ID equal to MK_MAN but ignores the SALARY condition.
3. C.
The character literals being compared to the JOB_ID column by the IN operator must be enclosed by single quotation marks.
A, B, and D are syntactically correct.
Notice that B does not require quotes around the numeric literals. Having them, however, does not cause an error.
4. B.
The LIKE operator tests the DEPARTMENT_NAME column of each row for values that contain the characters "er". The percentage symbols before and after the character literal indicate that any characters enclosing the "er" literal are permissible.
A and C are syntactically correct.
A uses the IN operator, which is used to test set membership.
C tests whether the alphabetic value of the DEPARTMENT_NAME column is between the letter "e" and the letter "r".
Finally, D uses the word "contains", which cannot be used in this context.
5. A and D.
The IS NULL operator correctly evaluates the COMMISSION_PCT
column for NULL values. D uses the NOT operator to negate the already negative
version of the IS NULL operator, IS NOT NULL. Two negatives
return a positive, and therefore A and D are equivalent.
B and C are incorrect.
NULL values cannot be tested by the equality operator or the IN operator.
6. A and C.
Each of these conditions tests for SALARY values in the range of $2,000 to $5,000.
B, D, and E are incorrect.
B excludes values like $2,500 from its set, D excludes the boundary values of $2,000 and $5,000, and E is illegal since it is missing the SALARY column name reference after the AND operator.
