Sql · Dates
Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Captured at a specific year
You've been asked to find all Codémons captured in 2022. Which SQL query would correctly retrieve this information?
Options
• ```SELECT * FROM codemon WHERE capture_date LIKE '2022%';```
• ```SELECT * FROM codemon WHERE capture_date = 2022;```
• ```SELECT * FROM codemon WHERE capture_date IS '2022';```
• ```SELECT * FROM codemon WHERE capture_date > '2022-01-01' AND capture_date < '2023-01-01';```
Hint : Think about how dates are stored in the database and which pattern matching technique would find all dates starting with '2022'.
Exercise 2 — Recently Captured Codémons
Which SQL query will find all Codémons captured in 2023?
Options
• ```SELECT * FROM codemon WHERE capture_date >= '2023-01-01';```
• ```SELECT * FROM codemon WHERE capture_date LIKE '2023%';```
• ```SELECT * FROM codemon WHERE year(capture_date) = 2023;```
• ```SELECT * FROM codemon WHERE capture_date = 2023;```
Hint : Think about how date strings are formatted and which pattern matching operation would correctly identify dates within a specific year.
Exercise 3 — Finding Recent Battles
Complete the SQL query to find all Codémons that had their last battle in June 2023.
SELECT name, type, last_battle_date
FROM codemon
WHERE last_battle_date ____ '2023-06-____';
Options: LIKE = % > 01% 30%
Hint : To find dates in a specific month and year, you need to use a pattern matching technique that will match all days within that month.
Exercise 4 — Sorting by Capture Date
Which query will list Codémons in order from most recently captured to oldest captured?
Options
• ```SELECT * FROM codemon ORDER BY capture_date DESC;```
• ```SELECT * FROM codemon ORDER BY capture_date ASC;```
• ```SELECT * FROM codemon SORT BY capture_date DESC;```
• ```SELECT * FROM codemon WHERE capture_date ORDER BY DESC;```
Hint : Consider which direction of sorting (ascending or descending) would show the most recent dates first, and the correct syntax for sorting in SQL.
Exercise 5 — Finding Evolved Codémons
Complete the SQL query to find all Codémons that have evolved (have a non-NULL evolution date).
SELECT name, type, evolution_date
FROM codemon
WHERE evolution_date ____ ____
Options: IS NOT NULL NOT NULL IS NULL
Hint : Think about the correct syntax for checking if a value is not NULL in SQL.
Exercise 6 — Winter Captures
Which query will find all Codémons captured in December 2022?
Options
• ```SELECT * FROM codemon WHERE capture_date LIKE '2022-12%';```
• ```SELECT * FROM codemon WHERE capture_date BETWEEN '2022-12-01' OR '2022-12-31';```
• ```SELECT * FROM codemon WHERE capture_date = '2022-12';```
• ```SELECT * FROM codemon WHERE capture_date CONTAINS '2022-12';```
Hint : Consider which SQL operator would best match the pattern for dates in a specific month and year.
Exercise 7 — Date Conditions and Their Meanings
Match each SQL condition with what it would find in our Codémon database:
Items to match
• capture_date > '2023-01-01'
• evolution_date IS NULL
• capture_date LIKE '%-10-%'
• training_date = last_battle_date
Definitions
• Codémons captured after January 1, 2023
• Codémons that haven't evolved yet
• Codémons captured in October of any year
• Codémons trained on the same day as their last battle
Exercise 8 — Training Before Battling
Complete the SQL query to find all Codémons whose last training date was earlier than their last battle date.
SELECT name, training_date, last_battle_date
FROM codemon
WHERE training_date ____ last_battle_date;
Options: < > = >= LIKE !=
Hint : Consider which comparison operator would find dates that come before other dates.
Exercise 9 — Recent Evolution First
Which query will list only evolved Codémons, with the most recently evolved ones first?
Options
• ```SELECT * FROM codemon WHERE evolution_date IS NOT NULL ORDER BY evolution_date DESC;```
• ```SELECT * FROM codemon WHERE evolution_date IS NOT NULL ORDER BY evolution_date ASC;```
• ```SELECT * FROM codemon ORDER BY evolution_date DESC;```
• ```SELECT * FROM codemon WHERE evolution_date >< NULL ORDER BY evolution_date DESC;```
Hint : Think about which filter condition would exclude Codémons that haven't evolved, and which sort order would show the most recent dates first.
Exercise 10 — Spring Captures
Complete the SQL query to find all Codémons captured in the first quarter of 2023 (January through March).
SELECT name, capture_date
FROM codemon
WHERE capture_date ____ '2023-01-01' AND '2023-03-31';
Options: BETWEEN WITHIN RANGE IN LIKE FROM
Hint : Consider which SQL operator is used to find values within a range, including the endpoints.
Exercise 11 — Recent Battles and Evolution
Which query will find Codémons that have evolved and had a battle in June 2023?
Options
• ```SELECT * FROM codemon WHERE evolution_date IS NOT NULL AND last_battle_date LIKE '2023-06%';```
• ```SELECT * FROM codemon WHERE evolution_date != NULL AND last_battle_date LIKE '2023-06%';```
• ```SELECT * FROM codemon WHERE evolution_date IS NOT NULL OR last_battle_date LIKE '2023-06%';```
• ```SELECT * FROM codemon WHERE evolution_date = '' AND last_battle_date LIKE '2023-06%';```
Hint : Think about the correct operator to check for non-NULL values and how to combine multiple conditions to require that both are true.
Exercise 12 — Date Queries and Their Results
Match each SQL query with the result it would return from our Codémon database:
Items to match
• WHERE capture_date LIKE '2022%' ORDER BY name;
• WHERE evolution_date > capture_date;
• WHERE last_battle_date > training_date;
• WHERE capture_date LIKE '%-10-31';
Definitions
• Names of all Codémons captured in 2022, alphabetically
• Codémons that evolved after they were captured
• Codémons that battled after their most recent training
• Codémons captured on Halloween (October 31st) of any year
Exercise 13 — Quick Evolution
Complete the SQL query to find Codémons that evolved within 3 months of being captured (assuming evolution_date is not NULL).
SELECT *
FROM codemon
SELECT name, capture_date, evolution_date
FROM codemon
WHERE evolution_date IS NOT NULL
AND ____ BETWEEN capture_date
AND date(capture_date, '+3 ____');
Options: evolution_date training_date months years days last_battle_date
Hint : Think about which date field needs to be compared and what time unit would represent a 3-month period after the capture date.
Exercise 14 — Find Untrained Codémons
Which query will find Codémons that haven't been trained since their last battle?
Options
• ```SELECT * FROM codemon WHERE training_date < last_battle_date;```
• ```SELECT * FROM codemon WHERE training_date > last_battle_date;```
• ```SELECT * FROM codemon WHERE training_date = last_battle_date;```
• ```SELECT * FROM codemon WHERE training_date BETWEEN capture_date AND last_battle_date;```
Hint : Consider which date comparison would indicate that the training occurred before the last battle.
Exercise 15 — Most Recent Activity
Complete the SQL query to find all Codémons that had a battle after their training (their last battle was more recent than their last training).
SELECT name, last_battle_date, training_date
FROM codemon
WHERE last_battle_date ____ training_date
ORDER BY last_battle_date ____
Options: > < = DESC ASC !=
Hint : Think about which comparison operator would identify dates that come after other dates, and which sort order would show the most recent dates first.
Exercise 16 — Finding Holiday Captures
Which query will find Codémons captured on Christmas Day (December 25th) of any year?
Options
• ```SELECT * FROM codemon WHERE capture_date LIKE '%-12-25';```
• ```SELECT * FROM codemon WHERE capture_date LIKE '12-25-%';```
• ```SELECT * FROM codemon WHERE capture_date = '12-25';```
• ```SELECT * FROM codemon WHERE MONTH(capture_date) = 25 AND DAY(capture_date) = 12;```
Hint : Consider the format of the date values and which pattern matching expression would identify December 25th regardless of the year.
Exercise 17 — Date LIKE Patterns
Match each LIKE pattern with what it would find in our date fields:
Items to match
• LIKE '2023-%'
• LIKE '%-06-%'
• LIKE '%-%-15'
• LIKE '2022-12%'
Definitions
• Dates from any month in 2023
• Dates from June in any year
• Dates on the 15th day of any month
• Dates from December 2022
Exercise 18 — Early 2023 Evolution
Complete the SQL query to find Codémons that evolved in the first quarter of 2023 (January through March) and are at least level 20.
SELECT name, evolution_date, level
FROM codemon
WHERE evolution_date BETWEEN '2023-01-01' AND '2023-03-31'
____ level ____ 20;
Options: AND OR >= <= = <
Hint : Consider which logical operator combines multiple conditions requiring both to be true, and which comparison operator checks for values greater than or equal to a threshold.
Exercise 19 — Battle History Sorting
Which query will list Codémons with the earliest battles first (oldest battle dates at the top)?
Options
• ```SELECT * FROM codemon ORDER BY last_battle_date ASC;```
• ```SELECT * FROM codemon ORDER BY last_battle_date DESC;```
• ```SELECT * FROM codemon ORDER BY capture_date ASC;```
• ```SELECT * FROM codemon WHERE last_battle_date IS NOT NULL;```
Hint : Consider which sort direction (ASC or DESC) would place the earliest dates at the top of the results.
Exercise 20 — October Captures
Complete the SQL query to find Codémons that were captured in October of any year.
SELECT name, capture_date
FROM codemon
WHERE capture_date LIKE ____
Options: '%-10-%' '%10%' '2022-10%' '%-10' '10-%' 'October%'
Hint : Think about the format of the date strings (YYYY-MM-DD) and which pattern would match dates from the 10th month of any year.
Exercise 21 — Finding Seasonal Captures
Which query would find all Codémons captured during summer months (June, July, August) of any year?
Options
• ```SELECT * FROM codemon WHERE capture_date LIKE '%-06-%' OR capture_date LIKE '%-07-%' OR capture_date LIKE '%-08-%';```
• ```SELECT * FROM codemon WHERE capture_date LIKE '%summer%';```
• ```SELECT * FROM codemon WHERE capture_date BETWEEN '2023-06-01' AND '2023-08-31';```
• ```SELECT * FROM codemon WHERE capture_date IN ('June', 'July', 'August');```
Hint : Consider how to use pattern matching to find dates from specific months regardless of the year, using the standard date format YYYY-MM-DD.
Exercise 22 — Special Dragon Challenge
Complete the SQL query to find dragon-type Codémons captured in 2022 that have evolved and had a battle in 2023.
SELECT name, capture_date, evolution_date, last_battle_date
FROM codemon
WHERE type = ____
AND capture_date LIKE '2022%'
AND evolution_date IS NOT NULL
AND last_battle_date LIKE ____
ORDER BY last_battle_date DESC;
Options: 'dragon' 'electric' 'ghost' '2023%' '2022%' '%-%-2023'
Hint : Consider which type we're looking for and which pattern would match dates from any month in 2023.
Exercise 23 — Multi-Column Date Sorting
Which query will list Codémons first by their evolution status (evolved ones first), then by their capture date (newest first)?
Options
• ```SELECT * FROM codemon ORDER BY evolution_date IS NULL, capture_date DESC;```
• ```SELECT * FROM codemon ORDER BY evolution_date DESC, capture_date DESC;```
• ```SELECT * FROM codemon ORDER BY evolution_date IS NOT NULL DESC, capture_date DESC;```
• ```SELECT * FROM codemon ORDER BY evolution_date ASC, capture_date DESC;```
Hint : Consider how to first order by whether a Codémon has evolved, and then by capture date in descending order.
Exercise 24 — Before and After Dates
Complete the SQL query to find Codémons captured before 2023 that were trained in June 2023.
SELECT name, capture_date, training_date
FROM codemon
WHERE capture_date < ____
AND training_date LIKE ____
Options: '2023-01-01' '2023-06-01' '2022-12-31' '2023-06%' '%-06-%' '2023%'
Hint : For the first blank, you need a date that represents the start of 2023. For the second blank, you need a pattern that matches any day in June 2023.
Exercise 25 — Filtering by Year with YEAR()
Which query returns all Codémons that were captured in 2022?
Options
• ```SELECT * FROM codemon WHERE YEAR(capture_date) = 2022;```
• ```SELECT * FROM codemon WHERE capture_date LIKE '2022';```
• ```SELECT * FROM codemon WHERE capture_date BETWEEN '2022-01-01' AND '2022-12-30';```
• ```SELECT * FROM codemon WHERE capture_date < '2023-01-01';```
Hint : The YEAR() function extracts just the year part of a date. This makes it easy to filter all dates from 2022, without worrying about months or days.