EXTRACT and YEAR/MONTH/DAY in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to extract the year from the date column.

MySQL

SELECT EXTRACT([1] FROM order_date) AS order_year FROM orders;

Drag options to blanks, or click blank then click option'

AHOUR

BMONTH

CYEAR

DDAY

Attempts:

3 left

💡 Hint

Common Mistakes

Using MONTH or DAY instead of YEAR.

Forgetting the FROM keyword.

The EXTRACT function with YEAR extracts the year part from a date.

2fill in blank

medium

Complete the code to extract the month from the date column.

MySQL

SELECT EXTRACT([1] FROM shipment_date) AS shipment_month FROM shipments;

Drag options to blanks, or click blank then click option'

ADAY

BMONTH

CYEAR

DMINUTE

Attempts:

3 left

💡 Hint

Common Mistakes

Using YEAR or DAY instead of MONTH.

Missing the FROM keyword.

Use EXTRACT(MONTH FROM ...) to get the month number from a date.

3fill in blank

hard

Fix the error in the code to extract the day from the date column.

MySQL

SELECT EXTRACT([1] FROM shipment_date) AS day_extracted FROM shipments;

Drag options to blanks, or click blank then click option'

ADAY

BFROM DAY

CDAY FROM

DMONTH FROM

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out the FROM keyword.

Putting FROM before the unit.

The correct syntax is EXTRACT(DAY FROM date_column). The FROM keyword must come after the unit.

4fill in blank

hard

Fill both blanks to extract the year and month from the date column.

MySQL

SELECT EXTRACT([1] FROM order_date) AS year, EXTRACT([2] FROM order_date) AS month FROM orders;

Drag options to blanks, or click blank then click option'

AYEAR

BDAY

CMONTH

DHOUR

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping YEAR and MONTH.

Using DAY or HOUR instead of MONTH.

Use YEAR to extract the year and MONTH to extract the month from a date.

5fill in blank

hard

Fill all three blanks to extract year, month, and day from the date column.

MySQL

SELECT EXTRACT([1] FROM event_date) AS year, EXTRACT([2] FROM event_date) AS month, EXTRACT([3] FROM event_date) AS day FROM events;

Drag options to blanks, or click blank then click option'

ADAY

BMONTH

CYEAR

DHOUR

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the order of YEAR, MONTH, and DAY.

Using HOUR instead of DAY.

Use YEAR, MONTH, and DAY in that order to extract each part from the date.

Practice

1. What does the MySQL function EXTRACT(YEAR FROM '2023-07-15') return?

easy

A. 07

B. '2023-07-15'

C. 15

D. 2023

Solution

1. Step 1: Understand EXTRACT function usage

   The EXTRACT function retrieves a specific part of a date, here the YEAR part.

2. Step 2: Apply EXTRACT to the given date

   Extracting YEAR from '2023-07-15' returns 2023 as a number.

3. Final Answer:

   2023 -> Option D

4. Quick Check:

   EXTRACT(YEAR FROM date) = year number [OK]

Hint: EXTRACT(YEAR FROM date) gives the year number [OK]

Common Mistakes:

• Confusing YEAR with MONTH or DAY
• Expecting a string instead of a number
• Using EXTRACT without FROM keyword

2. Which of the following is the correct syntax to extract the month from a date column named order_date in MySQL?

easy

A. EXTRACT(MONTH FROM order_date)

B. EXTRACT(order_date FROM MONTH)

C. EXTRACT(MONTH order_date)

D. EXTRACT(MONTH, order_date)

Solution

1. Step 1: Recall EXTRACT syntax

   The correct syntax is EXTRACT(part FROM date), where part is YEAR, MONTH, or DAY.

2. Step 2: Match syntax to options

   EXTRACT(MONTH FROM order_date) uses EXTRACT(MONTH FROM order_date), which is correct syntax.

3. Final Answer:

   EXTRACT(MONTH FROM order_date) -> Option A

4. Quick Check:

   EXTRACT(part FROM date) syntax is correct [OK]

Hint: Use EXTRACT(part FROM date) syntax exactly [OK]

Common Mistakes:

• Omitting FROM keyword
• Swapping arguments order
• Using commas instead of FROM

3. Given the table events with a column event_date storing dates, what will this query return?

SELECT EXTRACT(DAY FROM event_date) AS day FROM events WHERE EXTRACT(MONTH FROM event_date) = 12;

medium

A. Syntax error due to wrong EXTRACT usage

B. All days of events that happened in December

C. All years of events that happened in December

D. All months of events that happened in December

Solution

1. Step 1: Understand the WHERE clause

   The WHERE clause filters rows where the month part of event_date is 12 (December).

2. Step 2: Understand the SELECT clause

   The SELECT clause extracts the day part from event_date for those filtered rows.

3. Final Answer:

   All days of events that happened in December -> Option B

4. Quick Check:

   EXTRACT(MONTH) filters December, EXTRACT(DAY) selects day [OK]

Hint: Filter by EXTRACT(MONTH), select EXTRACT(DAY) for days [OK]

Common Mistakes:

• Confusing day and month extraction
• Thinking EXTRACT returns strings
• Assuming syntax error without checking carefully

4. Identify the error in this query:

SELECT EXTRACT(YEAR order_date) FROM orders;

medium

A. EXTRACT cannot be used with YEAR

B. order_date should be in quotes

C. Missing FROM keyword after YEAR

D. No error, query is correct

Solution

1. Step 1: Check EXTRACT syntax

   EXTRACT requires the format EXTRACT(part FROM date), so FROM keyword is mandatory.

2. Step 2: Identify missing FROM keyword

   The query misses FROM between YEAR and order_date, causing syntax error.

3. Final Answer:

   Missing FROM keyword after YEAR -> Option C

4. Quick Check:

   EXTRACT(part FROM date) needs FROM [OK]

Hint: Always include FROM between part and date in EXTRACT [OK]

Common Mistakes:

• Omitting FROM keyword
• Putting column name in quotes
• Thinking EXTRACT can't use YEAR

5. You want to count how many orders were placed each year and month from the orders table with a created_at datetime column. Which query correctly groups by year and month using EXTRACT?

hard

A. SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

B. SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

C. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

D. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month;

Solution

1. Step 1: Understand grouping by year and month

   We need to extract year and month parts from created_at to group orders by these parts.

2. Step 2: Check EXTRACT syntax correctness

   SELECT EXTRACT(YEAR FROM created_at) AS year, EXTRACT(MONTH FROM created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses correct EXTRACT syntax for both year and month and groups by aliases year and month.

3. Step 3: Verify other options

   SELECT YEAR(created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; uses YEAR() and MONTH() functions which also work but question asks for EXTRACT usage. SELECT EXTRACT(YEAR, created_at) AS year, EXTRACT(MONTH, created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; has wrong EXTRACT syntax. SELECT EXTRACT(YEAR FROM created_at) AS year, MONTH(created_at) AS month, COUNT(*) FROM orders GROUP BY year, month; mixes EXTRACT and MONTH() inconsistently.

4. Final Answer:

   Option A query with correct EXTRACT syntax and grouping -> Option A

5. Quick Check:

   Use EXTRACT(part FROM date) consistently for grouping [OK]

Hint: Use EXTRACT(part FROM date) for both year and month [OK]

Common Mistakes:

• Using wrong EXTRACT syntax with commas
• Mixing EXTRACT and other date functions inconsistently
• Not grouping by extracted aliases