Views in SQL

# CHAPTER 19

Views in SQL #

1. Introduction #

2. Learning Objectives #

• Define what a View is in Relational Databases.

• Encapsulate a complex query using CREATE VIEW.

• Query a View exactly as if it were a physical table.

• Utilize Views for security to restrict data access.

• Modify and Drop existing Views.

3. What is a View? (The Virtual Table) #

4. Creating a View (CREATE VIEW) #

-- DDL Command to create the virtual table
CREATE VIEW active_customer_report AS
SELECT 
    u.id, 
    u.name, 
    u.email, 
    COUNT(o.id) AS total_orders,
    a.city
FROM users u
INNER JOIN addresses a ON u.id = a.user_id
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'Active'
GROUP BY u.id, u.name, u.email, a.city;

*(Success! The View is saved in the database permanently).*

5. Querying the View #

-- Query the virtual table!
SELECT * FROM active_customer_report;

-- You can even filter and sort the View!
SELECT * FROM active_customer_report 
WHERE total_orders > 5 
ORDER BY city;

6. Views for Database Security #

The Solution: Create a restricted View!

-- Create a View that deliberately EXCLUDES the salary column
CREATE VIEW public_directory AS
SELECT id, name, department 
FROM employees;

*(Now, you give the IT developer permission to query the public_directory View, while completely denying them access to the base employees table. The sensitive data is mathematically walled off!)*

7. Updating and Dropping Views #

-- Recreate the view with updated logic
CREATE OR REPLACE VIEW public_directory AS
SELECT id, name, department, phone_number
FROM employees;

-- Delete the view permanently
DROP VIEW public_directory;

8. Mini Project: The Reporting Dashboard #

1. 1. CREATE VIEW dailyrevenue AS ...

1. 2. CREATE VIEW lowstockalerts AS ...

1. 3. CREATE VIEW topperformingstaff AS ...

The Node.js developer now simply writes: SELECT * FROM dailyrevenue; The backend code is perfectly clean, and the Database Administrator retains full control over the complex logic!

9. Common Mistakes #

• Thinking Views Improve Performance: A common beginner myth is that Views make queries run faster. They do not. Because a View does not physically store data, querying a View simply executes the underlying JOINs in real-time. (Note: Advanced databases support *Materialized Views*, which DO physically store the data for massive speed boosts, but that is an advanced topic).

• Trying to INSERT into complex Views: While you can technically execute INSERT INTO or UPDATE on a simple 1-table View, if the View contains JOINs or GROUP BY math, it becomes "Read-Only". You cannot INSERT into a mathematical average.

10. Best Practices #

• Naming Conventions: It is an industry standard to prefix or suffix View names to distinguish them from actual physical tables.

11. Exercises #

1. 1. Does a standard SQL View physically duplicate and store data on the server's hard drive?

1. 2. What DDL command is used to permanently delete a View from the database schema?

12. SQL Challenges #

CREATE VIEW vw_expensive_products AS
SELECT product_name, price FROM products
WHERE price > 1000;

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the mechanical difference between a Standard View and a Materialized View (conceptually). Why would a company generating a massive 50-table analytics report choose a Materialized View?

• Q: A junior developer attempts to execute an UPDATE statement against a View that was generated using a GROUP BY clause. Explain why the database engine rejects this operation.

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #