- 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.
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. How many employees in the table have Job = 'Clerk'?
- 3
- 5
- 2
- 4
Show answer
Answer: D. The clerks are Aman, Chirag, Eshan and Hema, so there are four.
-
2. SELECT EName FROM EMP WHERE EName LIKE '%a'; returns how many rows?
- 2
- 4
- 5
- 3
Show answer
Answer: D. Bela, Fatima and Hema end with a. Dolly, Gopal and the rest do not.
-
3. Which employee has the second-highest salary in EMP?
- Bela
- Fatima
- Dolly
- Gopal
Show answer
Answer: A. Fatima earns 52000 and Bela 45000, which is second. Dolly earns 38000.
-
4. SELECT DISTINCT DeptNo FROM EMP returns
- 8 rows
- 3 rows
- 2 rows
- 1 row
Show answer
Answer: B. The department numbers are 10, 20 and 30, so three different values appear.
-
5. What does SELECT EName, Salary + 2000 FROM EMP WHERE Job = 'Manager' do to the stored salaries?
- Raises them by 2000 in the table
- Deletes the managers' rows
- Only displays higher values
- Adds a new column for good
Show answer
Answer: C. SELECT only displays results. To change stored values you would need UPDATE.
Sign in to keep track of the lessons you finish.