Chapter 14: Views
A view is like a virtual table — it doesn’t store any data itself, but it looks and behaves exactly like a real table. It’s basically a saved SELECT query that you can treat as a table forever.
Views are super useful for:
- Simplifying complex queries
- Hiding sensitive columns
- Providing a clean interface to users
- Reusing the same logic multiple times
Let’s dive in step-by-step!
1. What is a View? (Simple analogy)
Imagine your coaching class has a big messy database with:
- students table (full_name, phone, email, date_of_birth, notes, fees_paid, etc.)
- courses table
- enrollments table
Now, the admin (you) wants a simple report: “Show me only student name, course name, fees, and enrollment date — nothing else!”
Instead of writing this long JOIN query every time:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT s.full_name, c.course_name, c.fees, e.enrollment_date FROM students s JOIN enrollments e ON s.student_id = e.student_id JOIN courses c ON e.course_id = c.course_id; |
You create a view once:
|
0 1 2 3 4 5 6 7 8 9 10 |
CREATE VIEW student_enrollment_report AS SELECT s.full_name, c.course_name, c.fees, e.enrollment_date FROM students s JOIN enrollments e ON s.student_id = e.student_id JOIN courses c ON e.course_id = c.course_id; |
Now, forever, you (or any user) can just write:
|
0 1 2 3 4 5 6 |
SELECT * FROM student_enrollment_report; |
It behaves exactly like a table, but behind the scenes it runs the saved SELECT!
2. CREATE VIEW – How to Make One
Basic Syntax:
|
0 1 2 3 4 5 6 7 |
CREATE VIEW view_name AS SELECT ... ; |
Example 1 – Simple view: Active students only
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE VIEW active_students AS SELECT student_id, full_name, email, phone, join_date FROM students WHERE is_active = TRUE; |
Now use it:
|
0 1 2 3 4 5 6 7 8 |
SELECT * FROM active_students; -- OR even SELECT full_name, email FROM active_students WHERE join_date > '2025-01-01'; |
Example 2 – Complex view with joins (our enrollment report) First make sure you have the tables from earlier chapters:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- (If you don't have them, quickly create courses and enrollments as in Chapter 9) CREATE VIEW student_course_details AS SELECT s.full_name AS 'Student Name', s.email, c.course_name AS 'Course', c.fees, e.enrollment_date AS 'Enrolled On', e.fees_paid, (c.fees - e.fees_paid) AS 'Balance Due' FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id LEFT JOIN courses c ON e.course_id = c.course_id WHERE s.is_active = TRUE; |
Now anyone can do:
|
0 1 2 3 4 5 6 |
SELECT * FROM student_course_details WHERE Balance Due > 0; |
3. Updatable Views – Can I INSERT/UPDATE/DELETE through a View?
Yes — but only simple views are updatable!
Rules for updatable views:
- Must be based on one table only (no JOINs)
- No aggregate functions (COUNT, SUM, etc.)
- No DISTINCT, GROUP BY, HAVING, UNION
- Must include the primary key of the base table
- No subqueries in SELECT list
Example – Updatable view
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE VIEW simple_student_info AS SELECT student_id, -- important: include PK full_name, email, phone, join_date FROM students; |
Now you can:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
UPDATE simple_student_info SET phone = '9999999999' WHERE student_id = 1; INSERT INTO simple_student_info (full_name, email, phone, join_date) VALUES ('New Student', 'new@example.com', '8888888888', CURDATE()); DELETE FROM simple_student_info WHERE student_id = 10; |
All these changes go directly to the students table!
Non-updatable view example (because of JOIN):
|
0 1 2 3 4 5 6 7 |
CREATE VIEW student_course_details AS ... (with JOINs); -- You CANNOT do INSERT/UPDATE/DELETE on this view |
4. DROP VIEW – Removing a View
Syntax:
|
0 1 2 3 4 5 6 |
DROP VIEW IF EXISTS view_name; |
Example:
|
0 1 2 3 4 5 6 |
DROP VIEW IF EXISTS active_students; |
Note: Dropping a view does NOT delete the underlying data — only the virtual table disappears.
5. Advantages of Views (Why professionals love them)
- Simplifies complex queries → Users don’t need to write long JOINs every time
- Security & Data Hiding → Show only certain columns (hide phone, salary, notes, etc.)
SQL012345678CREATE VIEW public_student_info ASSELECT student_id, full_name, email, join_dateFROM students;
- Reusability & Consistency → Same logic used everywhere — change once in view, affects all reports
- Abstraction → Users see a clean, logical structure (like “student_course_details”) instead of messy tables
- Performance (sometimes) → In MySQL 8.0+, materialized views (not standard views) can store data, but normal views are re-executed each time
- Easier maintenance → If table structure changes slightly, you fix the view once instead of changing 100 reports
That’s it for Chapter 14! 🎉 You now know how to create professional, clean, secure interfaces to your data using views!
Homework for today (do it right now – very important!) Run these commands and paste the output of the final SELECTs:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- 1. Create a view for active students CREATE VIEW active_students_view AS SELECT full_name, email, join_date FROM students WHERE is_active = TRUE; -- 2. Use the view SELECT * FROM active_students_view LIMIT 5; -- 3. Create an updatable view (simple one) CREATE VIEW basic_student_info AS SELECT student_id, full_name, email, phone FROM students; -- 4. Update through the view (change one phone) UPDATE basic_student_info SET phone = '7777777777' WHERE student_id = 1; -- 5. Check if change happened SELECT full_name, phone FROM basic_student_info WHERE student_id = 1; |
Paste the results of the last two SELECTs — I’ll check them!
