• Practice
  • About 13 min
  • 5 practice questions

SQL output drills: predict the rows

Trace WHERE, AND, OR, IN, LIKE, BETWEEN and ORDER BY on a small table and get the exact output every time.

If something looks wrong, please tell us.

A favourite exam question shows a table and a query and asks for the output. There is no trick except being careful. Work in fixed steps: pick the rows, then sort them, then show the asked columns. This lesson gives you the routine and several drills with every row traced.

The routine

  • Step 1: Go row by row and apply the WHERE condition. Cross out rows that fail.
  • Step 2: Apply ORDER BY to the survivors (ascending unless DESC is given).
  • Step 3: Show only the columns named after SELECT, in that order.
  • Step 4: If DISTINCT is present, remove repeats at the end.

The practice table

EMP(EmpNo, EName, Job, Salary, DeptNo), all values invented for practice: (101, Aman, Clerk, 18000, 10), (102, Bela, Manager, 45000, 20), (103, Chirag, Clerk, 20000, 20), (104, Dolly, Analyst, 38000, 10), (105, Eshan, Clerk, 17500, 30), (106, Fatima, Manager, 52000, 10), (107, Gopal, Analyst, 36000, 30), (108, Hema, Clerk, 21000, 20).

Drills with full working

Drill 1. SELECT EName, Salary FROM EMP WHERE Salary >= 36000 AND DeptNo = 10; Rows with salary at least 36000: Bela (20), Dolly (10), Fatima (10), Gopal (30). Of these, department 10: Dolly and Fatima. Output: (Dolly, 38000), (Fatima, 52000).

Drill 2. SELECT EName FROM EMP WHERE DeptNo IN (20, 30) AND Salary < 21000; Department 20 or 30: Bela, Chirag, Eshan, Gopal, Hema. Salary below 21000: Chirag (20000) and Eshan (17500). Hema earns exactly 21000, which is not below it. Output: Chirag, Eshan.

Drill 3. SELECT EName FROM EMP WHERE EName LIKE '%a'; Names ending in a: Bela, Fatima, Hema. Output has 3 rows. Now LIKE '_o%' means the second letter is o: Dolly and Gopal.

Drill 4. SELECT EName, Salary FROM EMP WHERE Salary BETWEEN 20000 AND 40000 ORDER BY Salary DESC; BETWEEN includes both ends, so 20000 qualifies. Qualifying: Chirag 20000, Dolly 38000, Gopal 36000, Hema 21000. Sorted high to low: Dolly 38000, Gopal 36000, Hema 21000, Chirag 20000.

Drill 5. SELECT DISTINCT Job FROM EMP ORDER BY Job; The Job column has 8 values but only 3 different ones. Sorted alphabetically: Analyst, Clerk, Manager.

Drill 6. SELECT EName, Salary + 2000 FROM EMP WHERE Job = 'Manager'; Managers: Bela (45000) and Fatima (52000). The calculation shows the new values 47000 and 54000 but does not change the table, because SELECT never modifies stored data.

Drill 7. SELECT COUNT(*) FROM EMP WHERE DeptNo <> 10; Department 10 has Aman, Dolly and Fatima. The other 8 - 3 = 5 rows qualify. Output: 5.

Filter first, sort second, show columns last. BETWEEN includes both ends. SELECT never changes the table.

— Remember

Typical traps

  • Reading 'less than 21000' as 'up to and including 21000'.
  • Forgetting that AND needs both conditions, while OR needs only one.
  • Mixing up the positions in LIKE: '%a' ends with a; 'a%' starts with a; '_a%' has a as the second letter.
  • Counting duplicate values when DISTINCT is used.

Key takeaways

  • Trace row by row; do not guess.
  • IN (...) is a short way to write several OR conditions.
  • Check boundary values when the condition has < or > and when it has BETWEEN.

Check your understanding

  1. 1. How many employees in the table have Job = 'Clerk'?

    1. 3
    2. 5
    3. 2
    4. 4
    Show answer

    Answer: D. The clerks are Aman, Chirag, Eshan and Hema, so there are four.

  2. 2. SELECT EName FROM EMP WHERE EName LIKE '%a'; returns how many rows?

    1. 2
    2. 4
    3. 5
    4. 3
    Show answer

    Answer: D. Bela, Fatima and Hema end with a. Dolly, Gopal and the rest do not.

  3. 3. Which employee has the second-highest salary in EMP?

    1. Bela
    2. Fatima
    3. Dolly
    4. Gopal
    Show answer

    Answer: A. Fatima earns 52000 and Bela 45000, which is second. Dolly earns 38000.

  4. 4. SELECT DISTINCT DeptNo FROM EMP returns

    1. 8 rows
    2. 3 rows
    3. 2 rows
    4. 1 row
    Show answer

    Answer: B. The department numbers are 10, 20 and 30, so three different values appear.

  5. 5. What does SELECT EName, Salary + 2000 FROM EMP WHERE Job = 'Manager' do to the stored salaries?

    1. Raises them by 2000 in the table
    2. Deletes the managers' rows
    3. Only displays higher values
    4. Adds a new column for good
    Show answer

    Answer: C. SELECT only displays results. To change stored values you would need UPDATE.

Privacy · Terms · Refunds · Delivery