Nearly every SQL question on the paper begins the same way: write a query to display… The examiner has decided which columns they want and which rows they want, and your job is to say that in SQL. This page drills exactly that shape.
1Try it before you open it
Every question below hides its answer behind a Show the answer strip. Write your query on paper first, then open it. Reading the solutions straight through feels productive and teaches almost nothing — the marks come from having produced the query yourself.
📝All questions use the same three tables
emp, dept and item are set up on the previous page. Keep it open in another tab if you cannot yet picture the columns; a real paper prints the table right above the questions for the same reason.
2Every column, every row
The star means all columns, in the order the table was created. It is the right answer when the question says “display all details” or “display the entire table”.
1
Display all details of the employees earning more than 30000.
“All details” is the phrase that licenses *. Note that Arun Thapa on exactly 30000 would not appear — > excludes the boundary.
3Choosing the columns
When the question names the columns, list them in the order asked. The resultset uses that order, not the table’s.
2
Display the name and salary of every clerk.
1 mark
▶Show the answer
MySQL command line client
mysql> select name, salary from emp where job = 'Clerk';
+---------------+----------+
| name | salary |
+---------------+----------+
| Bina Sharma | 26000.00 |
| Gopal Das | 24000.00 |
| Ishan Pradhan | 25500.00 |
| Lata Gurung | 27500.00 |
+---------------+----------+
4 rows in set (0.00 sec)
The text 'Clerk' must be in quotes. Without them MySQL reads it as a column name — a mistake worth its own question on the Find the Error page.
3
Display the name and job of employees who live in Gangtok.
1 mark
▶Show the answer
MySQL command line client
mysql> select name, job from emp where city = 'Gangtok';
+------------+---------+
| name | job |
+------------+---------+
| Arun Thapa | Manager |
| Deepa Nair | Manager |
| Hema Subba | Analyst |
+------------+---------+
3 rows in set (0.00 sec)
4Two conditions at once
and needs both sides true; or needs either. Read the question slowly — English “and” is not always SQL and. “Employees in Gangtok and Namchi” means a person in one or the other, because nobody lives in two cities at once.
4
Display the name and salary of employees of department 10 who earn more than 30000.
2 marks
▶Show the answer
MySQL command line client
mysql> select name, salary from emp where deptno = 10 and salary > 30000;
+-------------+----------+
| name | salary |
+-------------+----------+
| Arun Thapa | 58000.00 |
| Chandan Rai | 31000.00 |
+-------------+----------+
2 rows in set (0.00 sec)
Both conditions apply to the same row, so this really is and.
5
Display the name and city of employees living in Gangtok or Namchi.
2 marks
▶Show the answer
MySQL command line client
mysql> select name, city from emp where city = 'Gangtok' or city = 'Namchi';
+-------------+---------+
| name | city |
+-------------+---------+
| Arun Thapa | Gangtok |
| Deepa Nair | Gangtok |
| Farida Khan | Namchi |
| Hema Subba | Gangtok |
| Lata Gurung | Namchi |
+-------------+---------+
5 rows in set (0.00 sec)
Writing where city = 'Gangtok' and city = 'Namchi' returns nothing at all — no single row can satisfy both. The tidier form of this query, using in, is on the next page.
5Removing the repeats
6
Display the different jobs held in the company, without repeats.
1 mark
▶Show the answer
MySQL command line client
mysql> select distinct job from emp;
+----------+
| job |
+----------+
| Manager |
| Clerk |
| Salesman |
| Analyst |
+----------+
4 rows in set (0.01 sec)
Twelve employees, four distinct jobs. The giveaway words in a question are “different”, “distinct” and “unique”.
6Where the marks actually go
Quotes round text
'Clerk' is a value; Clerk is a column name. Numbers take no quotes.
Column order
Ask for name and salary, answer with name then salary.
> versus >=
“more than 30000” excludes 30000; “at least” includes it.
The semicolon
Every statement ends with one. Cheap to write, cheap to lose.
7Recap
select chooses the columns, where chooses the rows, and distinct removes repeats from what is left. Everything on the next two pages is a richer where.
✏️ Quick Check
A question says 'display all details of employees earning at least 30000'. Which is right?
✏️ Quick Check
Why does 'where city = 'Gangtok' and city = 'Namchi'' return no rows?