Views

 

SQL VIEWS

---

1. AIM

To study the concept of Views in SQL, understand their advantages, and perform operations such as creating, modifying, updating, and dropping views in MySQL.

---

2. THEORY

✔ What is a View?

A view is a virtual table created from one or more tables in a database.

• It does not store data physically.

• It stores only the query definition.

• Whenever a view is accessed, the query is executed dynamically.

• Views help in security, simplification, and customization of data.

---

✔ Key Properties

Concept	Description
Virtual Table	Looks like a table, but does not store data
Security	Restricts users from accessing sensitive columns
Simplification	Presents complex joins in a simplified way
Read-only or Updatable	Some views allow UPDATE/INSERT, others don’t
No Storage	Except for materialized views (not in MySQL)

---

3. VIEW OPERATIONS IN MYSQL

✔ Create a View

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

✔ View the View Structure

DESCRIBE view_name;

✔ Display View Content

SELECT * FROM view_name;

✔ Modify (Replace) a View

CREATE OR REPLACE VIEW view_name AS
SELECT ...

✔ Drop a View

DROP VIEW view_name;

---

4. EXAMPLE DATABASE FOR LAB

Create a sample table:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept VARCHAR(50),
    salary INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'Finance', 60000),
(3, 'Charlie', 'IT', 70000),
(4, 'David', 'IT', 55000),
(5, 'Eve', 'HR', 65000);

---

5. VIEW EXAMPLES WITH OUTPUTS

---

Experiment 1: Simple View

Create a view showing only selected columns

CREATE VIEW emp_basic AS
SELECT emp_id, emp_name, dept
FROM employees;

View data:

SELECT * FROM emp_basic;

Output

emp_id	emp_name	dept
1	Alice	HR
2	Bob	Finance
3	Charlie	IT
4	David	IT
5	Eve	HR

---

Experiment 2: Conditional View

CREATE VIEW high_salary AS
SELECT emp_name, salary
FROM employees
WHERE salary > 60000;

Output

emp_name	salary
Charlie	70000

---

Experiment 3: View on Multiple Tables

Suppose you have:

CREATE TABLE departments (
    dept VARCHAR(50),
    location VARCHAR(50)
);

INSERT INTO departments VALUES
('HR', 'Building A'),
('Finance', 'Building B'),
('IT', 'Building C');

Join view:

CREATE VIEW emp_dept_info AS
SELECT e.emp_name, e.salary, d.location
FROM employees e
JOIN departments d ON e.dept = d.dept;

---

Experiment 4: Updating Through a View

View:

CREATE VIEW it_staff AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE dept = 'IT';

Update using the view:

UPDATE it_staff
SET salary = salary + 5000
WHERE emp_id = 3;

✔ Allowed because:

• Single base table

• No GROUP BY

• No DISTINCT

---

Experiment 5: Create or Replace View

CREATE OR REPLACE VIEW high_salary AS
SELECT emp_name, salary, dept
FROM employees
WHERE salary > 55000;

---

Experiment 6: Dropping a View

DROP VIEW high_salary;

---

6. TYPES OF VIEWS

Type	Description
Simple View	Based on one table, no functions
Complex View	Based on multiple tables, may use aggregate functions
Read-Only View	Not updatable due to joins or aggregations
Updatable View	Supports UPDATE/DELETE if MySQL rules allow

---

7. BENEFITS OF VIEWS

• Hide sensitive data

• Simplify complex queries

• Provide logical data independence

• Maintain consistent representation of data

• Useful for security and abstraction

---

8. LAB PRACTICE QUESTIONS

1. Create a view to display employees from the HR department.

2. Create a view to show employees earning between 50,000 and 60,000.

3. Create a join view displaying employee name, department, and location.

4. Update employee salary using a view.

5. Create a view that shows the highest salary in each department (GROUP BY).

6. Try updating a complex view and observe the error.

7. Drop a view and confirm it is removed.

---

9. RESULT

The concepts of Views were successfully implemented. Students created, modified, updated, and deleted views and understood their use in simplifying queries and enhancing database security.