Lecture on Views in SQL

Introduction to Views

• Definition: Views are virtual tables in SQL, not real tables.
  • Composed of fields and columns from one or more real tables.
  • Can be interacted with as if they were real tables.

Benefits of Using Views

• Avoids data duplication:
  • Changes in real tables automatically update views.
  • No need to update information in multiple places.
• Always up-to-date with changes in underlying real tables.

Example 1: Employee Attendance Sheet

• Task: Create an attendance sheet with first and last names of employees.
• Solution: Create a view consisting of first and last name columns from the employees table.
• SQL Commands:
  • CREATE VIEW employee_attendance AS SELECT first_name, last_name FROM employees;
  • To fetch data: SELECT * FROM employee_attendance;
  • Interact using SQL keywords like ORDER BY last_name ASCENDING.
  • To remove view: DROP VIEW employee_attendance;*

Example 2: Customer Emails

• Added a new email column to the customers table.
  • ALTER TABLE customers ADD COLUMN email VARCHAR(50);
• Updated customer emails:
  • Example: UPDATE customers SET email = '[email protected]' WHERE customer_ID = 1;
• Created a view for customer emails:
  • CREATE VIEW customer_emails AS SELECT email FROM customers;
  • Fetch emails with: SELECT * FROM customer_emails;
• Added a new customer (Pearl Krabs) to table and automatically updated view.
  • Insert example: INSERT INTO customers VALUES (5, 'Pearl', 'Krabs', NULL, '[email protected]');*

Key Characteristics of Views

• Virtual Table: Based on SQL query results, not actual stored data.
• Automatic Updates: Reflects changes made in real tables.
• Optimized for Readability: Helps in managing and accessing specific data sets without redundancy.

Summary

• Views facilitate efficient data management without the need for data redundancy across multiple tables.
• Ideal for presenting data in a specific format or for providing specific access to data.