Date and time types in MySQL - Mini Project: Build & Apply

Working with Date and Time Types in MySQL

📖 Scenario: You are managing a small library database. You need to store information about books and the dates when they were added to the library and when they were last borrowed.

🎯 Goal: Create a table to store book information including date and time fields, insert sample data, and query the data using date and time types.

📋 What You'll Learn

Create a table called books with columns id (integer), title (varchar), added_date (DATE), and last_borrowed (DATETIME).

Insert three books with exact titles and dates provided.

Write a query to select all books added after a specific date.

Write a query to select books last borrowed before a specific datetime.

💡 Why This Matters

🌍 Real World

Libraries, event planners, and many businesses use date and time fields to track when events happen or records are created.

💼 Career

Understanding how to store and query date and time data is essential for database administrators and backend developers.

1

Create the books table with date and datetime columns

Write a SQL statement to create a table called books with these columns: id as INT, title as VARCHAR(100), added_date as DATE, and last_borrowed as DATETIME.

MySQL

-- Your code here to create the books table with the specified columns

Need a hint?

Use CREATE TABLE with the exact column names and types.

2

Insert three books with exact dates

Insert these three rows into the books table: (1, 'The Great Gatsby', '2023-01-15', '2023-06-10 14:30:00'), (2, '1984', '2023-03-22', '2023-06-12 09:15:00'), and (3, 'To Kill a Mockingbird', '2023-05-05', '2023-06-11 16:45:00').

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  added_date DATE,
  last_borrowed DATETIME
);
-- Your code here to insert the three books with exact values

Need a hint?

Use INSERT INTO books (id, title, added_date, last_borrowed) VALUES with the exact rows.

3

Query books added after a specific date

Write a SQL query to select all columns from books where added_date is after '2023-02-01'. Use the exact condition added_date > '2023-02-01'.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  added_date DATE,
  last_borrowed DATETIME
);

INSERT INTO books (id, title, added_date, last_borrowed) VALUES
(1, 'The Great Gatsby', '2023-01-15', '2023-06-10 14:30:00'),
(2, '1984', '2023-03-22', '2023-06-12 09:15:00'),
(3, 'To Kill a Mockingbird', '2023-05-05', '2023-06-11 16:45:00');

-- Your code here to select books added after 2023-02-01

Need a hint?

Use SELECT * FROM books WHERE added_date > '2023-02-01'.

4

Query books last borrowed before a specific datetime

Write a SQL query to select all columns from books where last_borrowed is before '2023-06-12 00:00:00'. Use the exact condition last_borrowed < '2023-06-12 00:00:00'.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  added_date DATE,
  last_borrowed DATETIME
);

INSERT INTO books (id, title, added_date, last_borrowed) VALUES
(1, 'The Great Gatsby', '2023-01-15', '2023-06-10 14:30:00'),
(2, '1984', '2023-03-22', '2023-06-12 09:15:00'),
(3, 'To Kill a Mockingbird', '2023-05-05', '2023-06-11 16:45:00');

SELECT * FROM books WHERE added_date > '2023-02-01';

-- Your code here to select books last borrowed before 2023-06-12 00:00:00

Need a hint?

Use SELECT * FROM books WHERE last_borrowed < '2023-06-12 00:00:00'.