EXTRACT and YEAR/MONTH/DAY in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Extract Year from Date

Given a table orders with a column order_date of type DATE, what is the output of this query?

SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders WHERE order_id = 101;

MySQL

CREATE TABLE orders (order_id INT, order_date DATE);
INSERT INTO orders VALUES (101, '2023-07-15');

A 07

B 2023

C 15

D 2023-07-15

💡 Hint

EXTRACT(YEAR FROM date) returns the year part as a number.

The EXTRACT function with YEAR returns only the year part of the date, which is 2023 for '2023-07-15'.

❓ query_result

intermediate

Extract Month from Date

What will this query return for the order_date '2022-12-05' in the orders table?

SELECT EXTRACT(MONTH FROM order_date) AS order_month FROM orders WHERE order_id = 202;

MySQL

CREATE TABLE orders (order_id INT, order_date DATE);
INSERT INTO orders VALUES (202, '2022-12-05');

A 12

B 2022

C 2022-12-05

D 5

💡 Hint

EXTRACT(MONTH FROM date) returns the month number.

The EXTRACT function with MONTH returns the month part of the date, which is 12 for December.

📝 Syntax

advanced

Identify the Syntax Error in EXTRACT Usage

Which option contains a syntax error when trying to extract the day from a date column birth_date in table users?

MySQL

SELECT EXTRACT(DAY FROM birth_date) AS birth_day FROM users;

A SELECT EXTRACT(DAY OF birth_date) AS birth_day FROM users;

B SELECT EXTRACT(DAY FROM birth_date) AS birth_day FROM users;

C SELECT EXTRACT(DAY, birth_date) AS birth_day FROM users;

D SELECT EXTRACT(DAY FROM birth_date) birth_day FROM users;

💡 Hint

EXTRACT syntax requires the keyword FROM between the part and the date.

Option C uses a comma instead of the required 'FROM' keyword, causing a syntax error.

❓ optimization

advanced

Optimizing Date Filtering Using EXTRACT

You want to find all orders placed in March 2023 from the orders table with a order_date column. Which query is more efficient and why?

A SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023 AND EXTRACT(MONTH FROM order_date) = 3;

B SELECT * FROM orders WHERE MONTH(order_date) = 3 AND YEAR(order_date) = 2023;

C SELECT * FROM orders WHERE DATE_FORMAT(order_date, '%Y-%m') = '2023-03';

D SELECT * FROM orders WHERE order_date >= '2023-03-01' AND order_date < '2023-04-01';

💡 Hint

Using range conditions on the date column can use indexes better than functions on the column.

Option D uses a date range which allows MySQL to use indexes on order_date efficiently, unlike functions like EXTRACT or DATE_FORMAT which prevent index use.

🧠 Conceptual

expert

Understanding EXTRACT Behavior with NULL Dates

What is the result of this query when order_date is NULL in the orders table?

SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders WHERE order_id = 303;

MySQL

CREATE TABLE orders (order_id INT, order_date DATE);
INSERT INTO orders VALUES (303, NULL);

A NULL

B 0

C Error: Invalid date

D Empty result set

💡 Hint

EXTRACT returns NULL when the date value is NULL.

When the date is NULL, EXTRACT returns NULL instead of a number or error.