AI InterviewSearch…Search problems, topics, or pages…Ctrl KGet started
Skip to content

02 Selecting and filtering / Checkpoint

Checkpoint — Selecting and filtering

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3
Quiz · 70% to clear12 q

This checkpoint covers the whole module: column and table aliases and where they may be used; calculated columns with arithmetic, decimal division and DIV, MOD, ROUND and CONCAT; the WHERE clause with comparisons on numbers, text and dates; AND, OR and NOT with precedence and three-valued logic; IN, BETWEEN and LIKE; and DISTINCT with COUNT(DISTINCT …).

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • Where may a column alias be used, and why not in WHERE?
  • What do 7 / 2, 7 DIV 2 and 7 % 2 return in MySQL, and what does NULL + 1 return?
  • Why does WHERE dept <> 'Sales' leave out the rows whose dept is NULL, and how do you include them?
  • Which binds first, AND or OR, and where do the parentheses go?
  • Is BETWEEN inclusive, and why is it risky on a DATETIME column?
  • What do % and _ match in LIKE, and how do you search for a literal %?
  • What happens to NOT IN when its list contains NULL?
  • How do COUNT(*), COUNT(col) and COUNT(DISTINCT col) differ?

The three queries are a price list with renamed and calculated columns, a filtered order list that needs parentheses, a range and a NULL check, and a set of distinct values found with a LIKE pattern.

Common questions

Why does WHERE category = 'books' OR category = 'games' AND price < 500 return expensive books?

Because AND binds tighter than OR, so it reads as category = 'books' OR (category = 'games' AND price < 500): every book qualifies whatever its price. Parenthesise the OR group: (category = 'books' OR category = 'games') AND price < 500.

What does SELECT 7 / 2, 7 DIV 2 return in MySQL?

3.5000 and 3. MySQL's / is always decimal division and DIV is integer division. PostgreSQL, SQLite and SQL Server would return 3 for 7 / 2, because there / between two integers truncates.

Why can a NOT IN list containing NULL return nothing?

x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL. The comparison with NULL is unknown for every row, so the AND is never true and WHERE keeps no rows. The same happens with a NOT IN subquery that returns a NULL.

AI InterviewGet started

SQL WHERE Quiz: Aliases, AND/OR, IN, BETWEEN, LIKE Practice

Test yourself: 12 questions and three queries on SQL aliases, calculated columns, WHERE, AND/OR precedence, IN, BETWEEN, LIKE wildcards and DISTINCT.

  • Course: SQL study plan
  • Module: Selecting and filtering
  • Kind: Checkpoint — cleared at 70%
  • Reading time: 25 min
  • Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Selecting and filtering is the checkpoint that closes the Selecting and filtering module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

This checkpoint covers the whole module: column and table aliases and where they may be used; calculated columns with arithmetic, decimal division and DIV, MOD, ROUND and CONCAT; the WHERE clause with comparisons on numbers, text and dates; AND, OR and NOT with precedence and three-valued logic; IN, BETWEEN and LIKE; and DISTINCT with COUNT(DISTINCT …).

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • Where may a column alias be used, and why not in WHERE?
  • What do 7 / 2, 7 DIV 2 and 7 % 2 return in MySQL, and what does NULL + 1 return?
  • Why does WHERE dept <> 'Sales' leave out the rows whose dept is NULL, and how do you include them?
  • Which binds first, AND or OR, and where do the parentheses go?
  • Is BETWEEN inclusive, and why is it risky on a DATETIME column?
  • What do % and _ match in LIKE, and how do you search for a literal %?
  • What happens to NOT IN when its list contains NULL?
  • How do COUNT(*), COUNT(col) and COUNT(DISTINCT col) differ?

The three queries are a price list with renamed and calculated columns, a filtered order list that needs parentheses, a range and a NULL check, and a set of distinct values found with a LIKE pattern.

Common questions

Why does WHERE category = 'books' OR category = 'games' AND price < 500 return expensive books?

Because AND binds tighter than OR, so it reads as category = 'books' OR (category = 'games' AND price < 500): every book qualifies whatever its price. Parenthesise the OR group: (category = 'books' OR category = 'games') AND price < 500.

What does SELECT 7 / 2, 7 DIV 2 return in MySQL?

3.5000 and 3. MySQL's / is always decimal division and DIV is integer division. PostgreSQL, SQLite and SQL Server would return 3 for 7 / 2, because there / between two integers truncates.

Why can a NOT IN list containing NULL return nothing?

x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL. The comparison with NULL is unknown for every row, so the AND is never true and WHERE keeps no rows. The same happens with a NOT IN subquery that returns a NULL.

Exercises

A discounted price list

The table products (discount_pct is NULL when no discount is set):

product_idnamepricediscount_pct
1Kettle199910
2Toaster25000
3Mixer449915

Return id (product_id), product (name), list_price (price) and sale_price: the price less its discount percentage, rounded to 2 places. A NULL discount gives a NULL sale_price. Order by sale_price descending (NULLs last, which is MySQL's descending order), ties by product_id.

Output: id | product | list_price | sale_price.

id | product | list_price | sale_price
3 | Mixer | 4499 | 3824.15
2 | Toaster | 2500 | 2500
1 | Kettle | 1999 | 1799.1

Orders to review

The table orders:

order_idstatusamountcountrycoupon
1pending12000INNULL
2pending3000USNULL
3shipped25000INNULL
4on_hold15000INSAVE10
5on_hold9999INNULL

An order needs review when all three hold: its status is pending or on_hold; its amount is between 10000 and 50000 inclusive or it ships outside India (country is not IN); and no coupon was used (coupon is NULL). Return order_id, status and amount of those orders, ordered by order_id.

Output: order_id | status | amount.

order_id | status | amount
1 | pending | 12000
2 | pending | 3000

Departments with engineers

The table employees (dept may be NULL):

emp_idnamejob_titledept
1AshaSoftware EngineerPlatform
2RaviSenior EngineerPlatform
3MeeraData EngineerAnalytics
4KabirAnalystAnalytics
5DevQA EngineerNULL

Return each department that has at least one employee whose job_title contains the text Engineer, once each, in alphabetical order. Leave out a NULL department.

Output: one column dept.

dept
Analytics
Platform

In this module: Selecting and filtering

← DISTINCT — removing duplicate rows · ORDER BY — putting rows in order →