Retrieving Data with SELECT practice questions

Worked examples for Retrieving Data with SELECT

1. Question 1

   The `EMPLOYEES` table contains this row: ``` EMP_ID FIRST_NAME LAST_NAME SALARY COMMISSION ------ ---------- --------- ------ ---------- 103 Dave Novak 4800 (null) ``` What value does the following query return? ```sql SELECT commission * 0 + salary AS "Adjusted Pay" FROM employees WHERE emp_id = 103 ```

   1. A. NULLCorrect answer

      COMMISSION is null for this row, so COMMISSION * 0 is null, and null + SALARY is null as well — if any operand of an arithmetic operator is null, the result is null. The query returns one row whose single column is null.

   2. B. 4800

      Assumes that multiplying by zero forces the null to 0, leaving 0 + 4800. Zero is not special: null times any number is still null, and adding salary to a null operand keeps the result null.

   3. C. 0

      Assumes Oracle substitutes zero for a null number in arithmetic, so the whole expression collapses to zero. Oracle performs no such substitution; obtaining a number requires an explicit NVL or COALESCE around the null operand.

   4. D. The statement fails with ORA-00932: inconsistent datatypes

      Assumes a null column value is untyped and therefore cannot take part in arithmetic. Null is a legal value of the NUMBER column COMMISSION, so the expression is type-correct and executes — it simply evaluates to null.

   Explanation

   Null in Oracle means the value is unavailable, not zero, and it propagates through every arithmetic operator: if any operand of +, -, *, or / is null, the entire expression evaluates to null. Multiplying by zero is no exception, because the multiplication cannot be resolved without knowing the unknown operand, and the surrounding addition then inherits that null. Only an explicit conversion such as NVL(commission, 0) turns the missing value into a number that arithmetic can consume.

2. Question 2

   The EMPLOYEES table has columns EMP_ID (primary key), FIRST_NAME, LAST_NAME, SALARY, COMMISSION, MANAGER_ID, DEPT_ID and HIRE_DATE. DEPT_ID is a foreign key to DEPARTMENTS (DEPT_ID), and a department may exist without any employees. You must produce a single-column result set that lists each department id appearing in EMPLOYEES exactly once, with no other columns and no repeated department id. Which query does that?

   1. A. SELECT DISTINCT dept_id FROM employees;Correct answer

      The select list is exactly one expression, so DISTINCT collapses rows with matching DEPT_ID into one copy each, and only department ids that actually occur in EMPLOYEES appear (SELECT: 'DISTINCT | UNIQUE ... returns only one copy of each set of duplicate rows').

   2. B. SELECT DISTINCT dept_id, last_name FROM employees;

      Treats DISTINCT as de-duplicating on DEPT_ID alone while LAST_NAME rides along. DISTINCT compares the whole select list, so each (dept_id, last_name) pair is unique and every employee row survives — the result is two columns with DEPT_ID repeated.

   3. C. SELECT DISTINCT(dept_id), manager_id FROM employees;

      Reads DISTINCT as a function whose parentheses restrict it to DEPT_ID. DISTINCT is a keyword, not a function: the parentheses are just a redundant grouping around the expression, so this is identical to SELECT DISTINCT dept_id, manager_id and returns unique (dept_id, manager_id) pairs — two columns, with DEPT_ID repeating across different managers.

   4. D. SELECT DISTINCT dept_id FROM departments;

      Assumes the parent table is an equivalent source for 'department ids that appear in EMPLOYEES'. DEPT_ID is the primary key of DEPARTMENTS, so DISTINCT removes nothing there, and the result also includes departments that employ nobody.

   Explanation

   DISTINCT is a keyword that qualifies the entire select list, not an operator or function bound to the column that follows it: Oracle returns one copy of each set of rows whose values match for every expression selected. Adding a second column therefore widens the de-duplication key rather than leaving it alone, and wrapping the first column in parentheses changes nothing because those parentheses only group an expression. Restricting the select list to the single column being de-duplicated — and sourcing it from the table whose rows define membership — is what yields one row per department id.

3. Question 3

   The `employees` table contains this row: ``` EMP_ID SALARY ------ ------ 100 9000 ``` What is the result of executing the following statement? ```sql SELECT 'Salary: ' || salary + 100 AS pay FROM employees WHERE emp_id = 100 ```

   1. A. The statement succeeds and returns Salary: 9100

      Assumes the arithmetic + binds tighter than ||, so that 'Salary: ' || (salary + 100) is evaluated. Binary +, - and || sit at the same precedence level in Oracle, so the leftmost operator wins and the concatenation happens first.

   2. B. The statement succeeds and returns Salary: 9000100

      Treats + as a string concatenation operator, as it is in some other SQL dialects, appending '100' to 'Salary: 9000'. In Oracle + is arithmetic only; the sole concatenation operator is ||.

   3. C. ORA-00932: inconsistent datatypes

      Assumes Oracle rejects a CHARACTER + NUMBER combination at parse time as a datatype mismatch. Oracle instead attempts implicit conversion of the character operand to NUMBER, so the failure is a conversion error raised while fetching the row, not a static datatype error.

   4. D. ORA-01722: invalid numberCorrect answer

      Equal precedence means left-to-right evaluation: ('Salary: ' || salary) is computed first, yielding the character value 'Salary: 9000'. The + then forces an implicit conversion of that string to NUMBER, which fails at run time with ORA-01722.

   Explanation

   Oracle gives the binary +, - and || operators the same precedence, so an expression mixing them is evaluated strictly left to right rather than doing the arithmetic first. Concatenating a character literal with a NUMBER column implicitly converts the number and produces a character value; applying an arithmetic operator to that character value then triggers an implicit character-to-number conversion, which fails because the string contains non-numeric text. Parenthesising the arithmetic is what makes the intended reading explicit.

4. Question 4

   The `employees` table has columns `emp_id`, `first_name` (VARCHAR2) and `salary` (NUMBER(8,2)). Employee 100 is named `Alice` and has a salary of 9000. Which query returns a single row whose only column holds exactly the text: ```text Alice's salary is 9000 ```

   1. A. SELECT first_name || q'['s salary is ]' || salary AS msg FROM employees WHERE emp_id = 100Correct answer

      The alternative quote operator takes [ as the opening quote delimiter and ] as its matching closer, and everything between them is taken literally — so the embedded apostrophe needs no doubling and the literal is exactly `'s salary is `. Concatenating the VARCHAR2 name, that literal, and the implicitly converted NUMBER 9000 yields `Alice's salary is 9000`.

   2. B. SELECT first_name || '''s salary is ''' || salary AS msg FROM employees WHERE emp_id = 100

      Inside an ordinary text literal a doubled '' emits one apostrophe, and this literal doubles the quote at BOTH ends of the phrase: the content is `'s salary is '`, so the result is `Alice's salary is '9000` with a stray apostrophe before the number. The misconception is that the whole embedded phrase must be wrapped in doubled quotes rather than only the apostrophe that is actually part of the text.

   3. C. SELECT first_name + q'['s salary is ]' + salary AS msg FROM employees WHERE emp_id = 100

      Treats + as a string-concatenation operator, as some other SQL dialects do. In Oracle + is strictly arithmetic, so the character operands are implicitly converted to NUMBER and the statement fails with ORA-01722 invalid number; || is Oracle's only concatenation operator.

   4. D. SELECT q'[first_name's salary is ]' || salary AS msg FROM employees WHERE emp_id = 100

      Places the column name inside the quoted text, assuming a column reference written in a literal is substituted with its value. Text between the quote delimiters is never evaluated, so this returns the constant `first_name's salary is 9000`.

   Explanation

   A single quote inside an ordinary text literal must be written as two single quotes; the alternative quote operator q'<delimiter>...<delimiter>' avoids that by taking every character between the chosen delimiters literally, and bracket-style delimiters such as [ ] pair opening with closing. Concatenation in Oracle is done only with ||, which implicitly converts a NUMBER operand to its default character form, so 9000 appends as the four characters 9000. Anything written inside a literal — including something that looks like a column name — is data, not an expression.

5. Question 5

   Every employee receives a flat allowance of 100 on top of the monthly `salary` stored in `EMPLOYEES`, and the annual figure is that combined monthly amount multiplied by 12. Which query returns each employee's last name together with that annual figure under the exact column heading `Annual Pay`?

   1. A. SELECT last_name, salary + 100 * 12 AS "Annual Pay" FROM employees

      Reads the expression left to right and assumes the addition happens first. Because * outranks +, Oracle multiplies 100 by 12 and then adds, yielding salary + 1200 (10200 for Alice's 9000) instead of (salary + 100) * 12.

   2. B. SELECT last_name, (salary + 100) * 12 AS "Annual Pay" FROM employeesCorrect answer

      Parentheses force the addition ahead of the multiplication, so the monthly total (salary + 100) is annualized as required, and the double-quoted alias preserves the space and mixed case in the heading Annual Pay. Alice returns 109200.

   3. C. SELECT last_name, salary * 12 + 100 AS "Annual Pay" FROM employees

      Annualizes the salary but then adds the allowance only once, so the allowance is not multiplied by 12. For Alice this returns 108100 rather than 109200.

   4. D. SELECT last_name, salary * (12 + 100) AS "Annual Pay" FROM employees

      Puts the parentheses around the wrong pair of operands, multiplying the salary by 112 (1008000 for Alice) instead of adding the allowance before annualizing.

   Explanation

   Within a SELECT list Oracle applies standard arithmetic precedence — unary minus first, then * and /, then + and - — with equal-precedence operators evaluated left to right, so an addition that must happen before a multiplication has to be parenthesized, and the parentheses must enclose exactly the operands being added. A column alias is a label attached to the finished select-list item and is not evaluated as part of the expression; enclosing it in double quotation marks is what preserves spaces and mixed case in the reported heading.

6. Question 6

   The `departments` table contains this row: ```text DEPT_ID DEPT_NAME LOCATION ------- --------- -------- 30 Sales Chicago ``` Which query returns a single row whose only column holds exactly this text? ```text Sales's HQ is 'Chicago' ```

   1. A. SELECT dept_name || q'{''s HQ is ''}' || location || q'{''}' AS label FROM departments WHERE dept_id = 30

      Applies ordinary-literal escaping inside an alternative-quoted literal. Between the q-operator delimiters every character stands for itself, so each doubled quote survives as two quotes and the result is Sales''s HQ is ''Chicago'' — doubling is an escape only inside a normal '...' literal.

   2. B. SELECT dept_name || '''s HQ is ''' || location || ''' AS label' FROM departments WHERE dept_id = 30

      Miscounts the doubling rule in the final literal: in ''' AS label' the leading pair is consumed as one escaped quote, so the literal does not end until after label and swallows the alias clause. The expression returns Sales's HQ is 'Chicago' AS label, and the query has no column alias at all.

   3. C. SELECT dept_name || q'{'s HQ is '}' || location || '''' AS label FROM departments WHERE dept_id = 30Correct answer

      q'{'s HQ is '}' uses { as the opening delimiter, so the literal ends at the matching }' and its value is the raw text 's HQ is ' with no doubling required. The trailing '''' is an ordinary literal whose two inner quotes escape to one quote character, giving Sales || 's HQ is ' || Chicago || ' = Sales's HQ is 'Chicago'.

   4. D. SELECT dept_name || q'{'s HQ is '}' || location || q'{}' AS label FROM departments WHERE dept_id = 30

      Treats the quotation mark that terminates a q-literal as part of its value. In q'{}' the } ends the text and the following quote is only syntax, so the literal is empty and contributes nothing; the output stops at Sales's HQ is 'Chicago with no closing quote.

   Explanation

   Oracle offers two ways to put a quotation mark in a character literal. In an ordinary literal the quote must be doubled, so '''' denotes a single ' and '''s HQ is ''' denotes 's HQ is '. In an alternative-quoted literal q'<delimiter>text<delimiter>' the text between the delimiters is taken exactly as written — quotes are not doubled there — and when the opening delimiter is one of ( { [ <, the literal ends at the matching closer immediately followed by a quotation mark, which is syntax rather than data. Building the target string therefore means using raw quotes inside the q-literal and doubled quotes in the ordinary one.

7. Question 7

   The `EMPLOYEES` table contains this row: ``` EMP_ID FIRST_NAME LAST_NAME SALARY COMMISSION ------ ---------- --------- ------- ---------- 107 Heidi Rossi 6700 (null) ``` What value does each column of the following query return for that row? ```sql SELECT last_name || '-' || commission AS tag, salary * commission AS bonus FROM employees WHERE emp_id = 107 ```

   1. A. NULL, NULL

      Assumes the concatenation operator propagates NULL the way arithmetic does. It does not: Oracle treats a null character operand of || as a zero-length string, so the surviving text is still returned.

   2. B. Rossi-, NULLCorrect answer

      Concatenating a null with a character string returns the string, so 'Rossi' || '-' || NULL is 'Rossi-' (the hyphen literal survives); any arithmetic operator with a null operand yields null, so salary * commission is NULL.

   3. C. Rossi-, 0

      Treats a null number as zero in arithmetic. Oracle never substitutes 0 for NULL — salary * NULL is NULL, not 6700 * 0.

   4. D. Rossi-NULL, NULL

      Confuses how a null is *displayed* with what it *is*. A null column has no value to concatenate — it contributes nothing, rather than the four characters N, U, L, L.

   Explanation

   The two operators in this SELECT list handle a null operand by opposite rules. Any arithmetic operator applied to a null returns null, so multiplying a salary by a null commission gives null rather than zero. Concatenation is the documented exception: Oracle treats a null character operand as a zero-length string, so a null simply drops out of the concatenation and the adjacent literals are still returned.

8. Question 8

   You must return a single row whose only column is headed exactly `Note` and whose value is exactly this text, apostrophes included: ``` It's 100% 'done' ``` Which query does that?

   1. A. SELECT 'It''s 100% ' + '''done''' AS "Note" FROM dual;

      Treats + as a string concatenation operator. In Oracle + is arithmetic only: it forces an implicit conversion of both character operands to NUMBER, and 'It''s 100% ' is not a valid number, so the statement fails with ORA-01722. || is the only concatenation operator.

   2. B. SELECT q'!It's 100% 'done'!' AS "Note" FROM dual;Correct answer

      ! is a valid non-paired q-quote delimiter, so the literal runs to the first ! immediately followed by a single quote — the one at the end. Every apostrophe inside is ordinary text and needs no doubling, and the double-quoted alias makes the heading exactly Note.

   3. C. SELECT q'!It's 100% 'done'!' AS 'Note' FROM dual;

      Assumes a column alias may be enclosed in single quotes. Single quotes delimit text literals; an alias that needs quoting must use double quotation marks, so the parser fails here with ORA-00923 (FROM keyword not found where expected) even though the q-literal itself is well formed.

   4. D. SELECT q'!It''s 100% 'done'!' AS "Note" FROM dual;

      Assumes apostrophes must still be doubled inside a q-quoted literal. The q operator suspends the special meaning of the single quote entirely, so both characters are kept literally and the value returned is It''s 100% 'done' — two apostrophes, not one.

   Explanation

   The alternative quote operator q'<d> ... <d>' lets any single-byte character except a space, tab, or newline act as the delimiter, and inside that literal the single quote loses its special meaning — so an embedded apostrophe is written once, not doubled, and doubling it stores two characters. Independently, only || concatenates in Oracle; + coerces character operands to NUMBER. A column alias that must preserve exact spelling or case is enclosed in double quotation marks, never single ones, which are reserved for text literals.