SQL Dates & Views

DATE stores calendar days. DATETIME or TIMESTAMP adds time. Use standard formats 'YYYY-MM-DD' in queries for clarity.

Dates — store and query time

DATE stores calendar days. DATETIME or TIMESTAMP adds time. Use standard formats 'YYYY-MM-DD' in queries for clarity.

Real-life example: Dates are exam day on the calendar — query 'enrollments after 2025-01-01' like flipping to January in a planner.

Insert and filter by date

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enrolled_at DATE,
  score DECIMAL(5, 2)
);

INSERT INTO enrollments (enrollment_id, student_id, course_id, enrolled_at, score)
VALUES (1, 101, 1, '2025-08-15', 88.5);

SELECT * FROM enrollments
WHERE enrolled_at BETWEEN '2025-08-01' AND '2025-08-31';

Views — saved virtual tables

A VIEW is a stored SELECT. Users query the view name like a table. It simplifies reports and hides complex joins.

Real-life example: A view is a pre-made dashboard — 'student_report_card' always shows name + course + score without rewriting the JOIN.

Create and query a view

CREATE VIEW student_scores AS
SELECT
  s.full_name,
  c.course_name,
  e.score,
  e.enrolled_at
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

SELECT * FROM student_scores WHERE score >= 85;