1. Which query creates a projection of the DEPARTMENT_NAME
and LOCATION_ID columns from the DEPARTMENTS table?
(Choose the best answer.)
A. SELECT DISTINCT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS;
B. SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS;
C. SELECT DEPT_NAME, LOC_ID FROM DEPT;
D. SELECT DEPARTMENT_NAME AS “LOCATION_ID” FROM DEPARTMENTS;
2. After describing the EMPLOYEES table, you discover
that the SALARY column has a data type of NUMBER(8,2). Which SALARY value(s)
will not be permitted in this column?
(Choose all that apply.)
A. SALARY=12345678
B. SALARY=123456.78
C. SALARY=12345.678
D. SALARY=123456
E. SALARY=12.34
3. After describing the JOB_HISTORY table, you discover
that the START_DATE and END_DATE columns have a data type of DATE. Consider
the expression END_DATESTART_ DATE.
(Choose two correct statements.)
A. A value of DATE data type is returned.
B. A value of type NUMBER is returned.
C. A value of type VARCHAR2 is returned.
D. The expression is invalid since arithmetic cannot
be performed on columns with DATE data types.
E. The expression represents the days between the END_DATE
and START_DATE less one day.
4. The DEPARTMENTS table contains a DEPARTMENT_NAME
column with data type VARCHAR2(30).
(Choose two true statements about this column.)
A. This column can store character data up to a maximum
of 30 characters.
B. This column must store character data that is at least
30 characters long.
C. The VARCHAR2 data type is replaced by the CHAR data
type.
D. This column can store data in a column with data type
VARCHAR2(50) provided that the contents are at most 30 characters long.
5. Which statement reports on unique JOB_ID values
from the EMPLOYEES table?
(Choose all that apply.)
A. SELECT JOB_ID FROM EMPLOYEES;
B. SELECT UNIQUE JOB_ID FROM EMPLOYEES;
C. SELECT DISTINCT JOB_ID, EMPLOYEE_ID FROM EMPLOYEES;
D. SELECT DISTINCT JOB_ID FROM EMPLOYEES;
6. Choose the two illegal statements. The two correct statements produce identical results. The two illegal statements will cause an error to be raised:
A. SELECT DEPARTMENT_ID|| ' represents the '|| DEPARTMENT_NAME||'
Department' as "Department Info" FROM DEPARTMENTS;
B. SELECT DEPARTMENT_ID|| ' represents the || DEPARTMENT_NAME||'
Department' as "Department Info" FROM DEPARTMENTS;
C. select department_id|| ' represents the '||department_name||
' Department' "Department Info" from departments;
D. SELECT DEPARTMENT_ID represents the DEPARTMENT_NAME
Department as "Department Info" FROM DEPARTMENTS;
7. Which expressions do not return NULL values?
(Choose all that apply.)
A. select ((10 + 20) * 50) + null from dual;
B. select 'this is a '||null||'test with nulls' from
dual;
C. select null/0 from dual;
D. select null||'test'||null as “Test” from dual;
8. Choose the correct syntax to return all columns and rows of data from the EMPLOYEES table.
A. select all from employees;
B. select employee_id, first_name, last_name, first_name,
department_id from employees;
C. select % from employees;
D. select * from employees;
E. select *.* from employees;
9. The following character literal expression is selected
from the DUAL table: SELECT 'Coda""s favorite fetch toy is his orange ring'
FROM DUAL;
(Choose the result that is returned.)
A. An error would be returned due to the presence of two
adjacent quotes
B. Coda's favorite fetch toy is his orange ring
C. Coda""s favorite fetch toy is his orange ring
D. Coda""s favorite fetch toy is his orange ring'
10. There are four rows of data in the REGIONS table.
Consider the following SQL statement: SELECT '6 * 6' “Area” FROM REGIONS;
How many rows of results are returned and what value is returned by the
Area column?
(Choose the best answer.)
A. 1 row returned, Area column contains value 36
B. 4 rows returned, Area column contains value 36 for
all 4 rows
C. 1 row returned, Area column contains value 6 * 6
D. 4 rows returned, Area column contains value 6 * 6
for all 4 rows
E. A syntax error is returned.
---
Answers:
1. B. A projection is an intentional restriction of the columns returned from a table.
A, C, and D are incorrect.
A is eliminated since the question has nothing to do with
duplicates, distinctiveness, or uniqueness of data.
C incorrectly selects nonexistent columns called DEPT_NAME
and LOC_ID from a nonexistent table called DEPT.
D returns just one of the requested columns: DEPARTMENT_NAME.
Instead of additionally projecting the LOCATION_ID column from the
DEPARTMENTS table, it attempts to alias the DEPARTMENT_NAME
column as LOCATION_ID.
2. A. Columns with NUMBER(8,2) data type can store, at most, eight digits, of which, at most, six digits are to the left of the decimal point. Although A is the correct answer, note that since the question is phrased in the negative, these values are NOT allowed to be stored in such a column. A is not allowed because it contains eight whole number digits, but the data type is constrained to store six whole number digits and two fractional digits.
B, C, D, and E are incorrect, as they can legitimately be stored in this data type. C is allowed since the fractional portion is rounded to two decimal places.
D shows that numbers with no fractional part are legitimate values for this column, as long as the number of digits in the whole number portion does not exceed six digits.
3. B and E. The result of arithmetic between two date values represents a certain number of days.
A, C, and D are incorrect. It is a common mistake to expect the result of arithmetic between two date values to be a date as well, so A may seem plausible, but it is false.
4. A and D. The scale of the VARCHAR2 data type, specified
in brackets, determines its maximum capacity for storing character data
as mentioned by A. If a
data value that is at most 30 characters long is stored
in any data type, it can also be stored in this column as stated by D.
B and C are incorrect. B is incorrect because it is possible to store character data of any length up to 30 characters in this column.
C is false, since the CHAR data type exists in parallel with the VARCHAR2 data type.
5. D. Unique JOB_ID values are projected from the EMPLOYEES table by applying the DISTINCT keyword to just the JOB_ID column.
A, B, and C are incorrect, since A returns an unrestricted list of JOB_ID values including duplicates, B makes use of the UNIQUE keyword in the incorrect context, and C selects the distinct combination of JOB_ID and EMPLOYEE_ID values. This has the effect of returning all the rows from the EMPLOYEES table since the EMPLOYEE_ID column contains unique values for each employee record. Additionally, C returns two columns, which is not what was originally requested.
6. B and D. B and D represent the two illegal statements
that will return syntax errors if they are executed. This is a tricky question
because it asks for the
illegal statements and not the legal statements. B is
illegal because it is missing a single quote enclosing the character literal
"represents the".
D is illegal because it does not make use of single quotes to enclose its character literals.
A and C are incorrect, as they are the legal statements. A and C appear to be different since the case of the SQL statements are different and A uses the alias keyword AS, whereas C just leaves a space between the expression and the alias. Yet both A and C produce identical results.
7. B and D. 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.
A and C are incorrect. They return null values because any arithmetic expression that involves
8. D. An asterisk is the SQL operator that implies that all columns must be selected from a table.
A, B, C, and E are incorrect. A uses the ALL reserved word but is missing any column specification and will, therefore, generate an error.
B selects some columns but not all columns and, therefore, does not answer the question.
C and E make use of illegal selection operators.
9. B. The key to identifying the correct result lies in understanding the role of the single quotation marks. The entire literal is enclosed by a pair of quotes to avoid the generation of an error. The two adjacent quotes are necessary to delimit the single quote that appears in literal B.
A, C, and D are incorrect.
A is eliminated since no error is returned.
C inaccurately returns two adjacent quotes in the literal
expression and D returns a literal with all the quotes still present. The
Oracle server removes the
quotes used as character delimiters after processing
the literal.
10. D. The literal expression '6 * 6' is selected once for each row of data in the REGIONS table.
A, B, C, and E are incorrect.
A returns one row instead of four and calculates the product
6 * 6. The enclosing quote operators render 6 * 6 a character literal and
not a numeric literal that
can be calculated.
B correctly returns four rows but incorrectly evaluates the character literal as a numeric literal.
C incorrectly returns one row instead of four and E is incorrect, because the given SQL statement can be executed.
