Sql · Sorting and Limiting Results
Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Find Specific Codémons
Complete the query to find all Codémons that are either 'water' type OR have a power greater than 75.
SELECT name, type, power
FROM codemon
WHERE ____ = ____ ____ power > 75;
Options: type 'water' OR AND level ='water'
Hint : To find Codémons matching either of two conditions, you need to use the correct logical operator to combine them.
Exercise 2 — Finding the Oldest Codémon
Looking at the captured_date column, which query will find the Codémon that was captured the longest time ago (the oldest capture)?
Options
• ```SELECT name, captured_date FROM codemon ORDER BY captured_date ASC LIMIT 1;```
• ```SELECT name, captured_date FROM codemon ORDER BY captured_date DESC LIMIT 1;```
• ```SELECT name, captured_date FROM codemon ORDER BY captured_date;```
• ```SELECT name, captured_date FROM codemon WHERE captured_date IS NOT NULL;```
Hint : Think about how dates are ordered when sorted. Sorting by DESC will give you the most recent date first.
Exercise 3 — High-Level Fire or Water Codémons
Complete the query to find all Codémons that are either 'fire' OR 'water' type AND have a level of at least 15, sorted by their power in descending order.
SELECT name, type, level, power
FROM codemon
WHERE (type = 'fire' ____ type = 'water') ____ level ____ 15
ORDER BY power ____
Options: OR AND >= <= DESC ASC
Hint : Remember that parentheses help group conditions, and you need to use the correct comparison operator to check if a value is at least a certain number.
Exercise 4 — Finding the Second Most Powerful Codémon
Which query will return the name and power of the second most powerful Codémon?
Options
• ```SELECT name, power FROM codemon ORDER BY power DESC LIMIT 1 OFFSET 1;```
• ```SELECT name, power FROM codemon ORDER BY power DESC LIMIT 2;```
• ```SELECT name, power FROM codemon ORDER BY power ASC LIMIT 1 OFFSET 1;```
Hint : Think about how to sort the Codémons by power, and then how to skip the first result to get the second one.
Exercise 5 — Match Queries with Their Results
Match each SQL query with the result it would produce from our Codémon table.
Items to match
• SELECT name FROM codemon WHERE level > 15 ORDER BY name;
• SELECT name FROM codemon ORDER BY power DESC LIMIT 2;
• SELECT type FROM codemon WHERE power > 75 AND level < 20;
• SELECT name FROM codemon ORDER BY captured_date DESC LIMIT 1;
Definitions
• Florachu, Pyrochu, Rockachu, Sparkle
• Rockachu, Pyrochu
• fire
• Sparkle
Exercise 6 — Implementing Pagination
You're creating a Codémon listing page that shows 2 Codémons per page, sorted alphabetically by name. Complete the query for page 2 (the second set of results).
SELECT name, type, power
FROM codemon
____ BY name
____ 2 ____ 2
Options: ORDER SORT LIMIT TAKE OFFSET SKIP
Hint : To implement pagination, you need to order the results consistently, then use LIMIT to set how many per page, and OFFSET to skip the right number based on the page number.
Exercise 7 — Multi-Column Sorting
You want to list Codémons in a specific order: first grouped by their level (highest first), and within the same level, ordered by their names alphabetically. Which query accomplishes this?
Options
• ```SELECT name, level FROM codemon ORDER BY level DESC, name ASC;```
• ```SELECT name, level FROM codemon ORDER BY name ASC, level DESC;```
• ```SELECT name, level FROM codemon ORDER BY level ASC, name DESC;```
• ```SELECT name, level FROM codemon ORDER BY level DESC, name DESC;```
Hint : Think about which column should be the primary sort key, which should be secondary, and the sort direction for each.
Exercise 8 — Complex Filtering and Sorting
Complete the query to find Codémons that are NOT of type 'electric' AND were captured in 2023 after March, sorted by their power in descending order.
SELECT name, type, captured_date, power
FROM codemon
WHERE type ____ 'electric' ____ captured_date > '2023-03-31'
____ BY power ____
Options: != = AND OR ORDER SORT DESC ASC
Hint : You need to use the correct operator to exclude a specific type, combine conditions correctly, and specify the right sort direction.
Exercise 9 — Query Execution Order
In what order does SQL execute the following parts of a query? (We're talking about execution and not the order they appear)
1. SELECT
2. FROM
3. WHERE
4. ORDER BY
5. LIMIT
Options
• FROM → WHERE → SELECT → ORDER BY → LIMIT
• SELECT → FROM → WHERE → ORDER BY → LIMIT
• FROM → SELECT → WHERE → ORDER BY → LIMIT
• SELECT → FROM → ORDER BY → WHERE → LIMIT
Hint : Understanding the logical execution order (not the written order) is crucial for writing efficient queries and understanding how SQL processes data.
Exercise 10 — SQL Features and Their Uses
Match each SQL feature with the appropriate use case.
Items to match
• ORDER BY column DESC
• LIMIT X OFFSET Y
• WHERE column1 = X AND column2 > Y
• ORDER BY column1, column2
Definitions
• Finding the Codémon with the highest power
• Implementing pagination for a Codémon listing
• Finding Codémons of a specific type with level above a threshold
• Grouping Codémons by type, then sorting by power within each type
Exercise 11 — SQL Knowledge Test
What is the default behavior when sorting a column containing NULL values with ORDER BY in most SQL databases?
Options
• NULL values are placed at the end for ASC and at the beginning for DESC
• NULL values are always placed at the end regardless of sort direction
• NULL values are always placed at the beginning regardless of sort direction
• NULL values cause an error when using ORDER BY
Hint : Understanding how NULL values behave in ordering operations is important for writing queries that handle missing data correctly.
Exercise 12 — The Ultimate Codémon Query
You need to find the top 2 most powerful Codémons among those captured in the first half of 2023 (before July) that are either 'fire' or 'water' type. Complete the query.
SELECT name, type, power, captured_date
FROM codemon
WHERE captured_date < '2023-07-01'
AND (type = 'fire' OR type = 'water')
ORDER BY ____ ____
____ 2
Options: power level DESC ASC LIMIT
Hint : This complex query requires filtering by date, filtering by multiple types, sorting by the right column in the right direction, and limiting the results.
Exercise 13 — Predicting Query Results
What will be the result of the following query?
SELECT name FROM codemon
WHERE level > 15 AND power < 80
ORDER BY name DESC
LIMIT 2;
Options
• Sparkle, Florachu
• Florachu, Sparkle
• Pyrochu, Florachu
• Pyrochu, Sparkle
Hint : To answer this, analyze which Codémons meet the WHERE conditions, then apply the sorting direction (DESC means Z to A), and finally apply the LIMIT.
Exercise 14 — Understanding Query Components
Match each query component with its function in SQL.
Items to match
• SELECT column1, column2
• WHERE condition1 AND condition2
• ORDER BY column DESC
• LIMIT X OFFSET Y
Definitions
• Specifies which columns to include in the results
• Filters rows that meet multiple criteria
• Arranges results in highest-to-lowest order
• Returns X rows after skipping Y rows
Exercise 15 — Fix the Query
The following query is supposed to find the names of all plant-type Codémons with power greater than 65, sorted by their level in ascending order.
SELECT name, power
FROM codemon
WHERE type = ____ AND power ____ 65
____ level ASC;
Options: 'plant' > < ORDER BY SORT BY GROUP BY
Hint : Check for correct string formatting, comparison operator, and sorting syntax.
Exercise 16 — Understanding Boolean Logic
Which Codémons will be returned by this query?
SELECT name
FROM codemon
WHERE (type = 'water' OR type = 'fire')
AND level > 15 AND power < 80;
Options
• No Codémons match these criteria
• Only Aquachu
• Aquachu and Pyrochu
• All Codémons except Rockachu
Hint : Just read the query and try to apply it to every Codémon in the table.
Exercise 17 — Third Highest Level
Complete the query to find the Codémon with the third highest level. You do not know how many Codémons are in the table.
SELECT name, level
FROM codemon
____ BY level ____
____ 1 ____ 2
Options: ORDER SORT DESC ASC LIMIT MAX OFFSET
Hint : Think about how to sort the data to get levels in descending order, and then which combination of LIMIT and OFFSET will give you exactly the third row.
Exercise 18 — Filtering with Conditions
Which query finds all fire-type Codémons with power greater than 50?
Options
• ```SELECT * FROM codemon WHERE type = 'fire' AND power > 50;```
• ```SELECT * FROM codemon WHERE type = 'fire' OR power > 50;```
• ```SELECT * FROM codemon WHERE power > 50;```
• ```SELECT * FROM codemon WHERE type = 'fire' AND power < 50;```
Hint : This is easy peasy! But be careful with the AND and OR.
Exercise 19 — Identify SQL Errors
Match each incorrect SQL query with the type of error it contains.
Items to match
• SELECT * FROM codemon ORDER BY DESC level;
• SELECT * FROM codemon WHERE type = water;
• SELECT name power FROM codemon;
• SELECT * FROM codemon LIMIT 5, 10;
Definitions
• Incorrect order of keywords in ORDER BY clause
• Missing quotes around string value
• Missing comma between column names
• Incorrect syntax for LIMIT with OFFSET
Exercise 20 — Find the Middle Level
You have 5 Codémons in your table. Which query returns the Codémon with the level in the middle (after sorting all levels from lowest to highest)?
Options
• ```SELECT name, level FROM codemon ORDER BY level LIMIT 1 OFFSET 2;```
• ```SELECT name, level FROM codemon ORDER BY level LIMIT 1 OFFSET 1;```
• ```SELECT name, level FROM codemon ORDER BY level DESC LIMIT 1 OFFSET 0;```
• ```SELECT name, level FROM codemon ORDER BY name LIMIT 1 OFFSET 2;```
Hint : Try to remember how to sort lowest to highest and how OFFSET works.
Exercise 21 — Page 3 of Results
Complete the query to fetch page 3 of a paginated result set, where each page contains 2 Codémons, sorted by their capture date from newest to oldest.
SELECT name, captured_date
FROM codemon
____ BY captured_date ____
____ 2 ____ ____
Options: ORDER SORT DESC ASC LIMIT OFFSET 4
Hint : Remember, to find newer date, you need to sort by captured_date in descending order.
Exercise 22 — Find the Weakest Codémon
Which query returns the Codémon with the lowest power?
Options
• ```SELECT * FROM codemon ORDER BY power ASC LIMIT 1;```
• ```SELECT * FROM codemon ORDER BY power DESC LIMIT 1;```
• ```SELECT * FROM codemon WHERE power = 0;```
• ```SELECT * FROM codemon LIMIT 1;```
Hint : Remember the difference between ASC and DESC and how LIMIT works.
Exercise 23 — Match Queries to What They Do
Match each SQL query to what it does.
Items to match
• SELECT * FROM codemon WHERE NOT (type = 'water' OR type = 'fire');
• SELECT * FROM codemon ORDER BY power DESC LIMIT 1;
• SELECT * FROM codemon ORDER BY level ASC LIMIT 1;
• SELECT * FROM codemon WHERE type = 'plant' AND power >= 50;
Definitions
• Finds Codémons that are not water or fire type
• Finds the Codémon with the highest power
• Finds the Codémon with the lowest level
• Finds all plant-type Codémons with power 50 or more
Exercise 24 — Exclude Multiple Types
Complete the query to find all Codémons that are not 'electric' type AND not 'rock' type, sorted by their power in ascending order.
SELECT name, type, power
FROM codemon
WHERE type ____ 'electric' ____ type ____ 'rock'
____ BY power ____
Options: != != = AND OR ORDER SORT ASC
Hint : To exclude multiple values, you need to use the right combination of comparison operators and logical operators.
Exercise 25 — Empty Result Prediction
Given our Codémon table, which query will return an empty result set (no rows)?
Options
• ```SELECT * FROM codemon WHERE power > 85;```
• ```SELECT * FROM codemon WHERE level > 20;```
• ```SELECT * FROM codemon WHERE level != 10;```
• ```SELECT * FROM codemon WHERE type = 'plant';```
Hint : Examine the values in each column of the table to determine which condition would not be satisfied by any row.