SQL Interview Questions

Views in SQL are virtual tables that represent the result of a stored query. They do not store data themselves but provide a way to present and query data from one or more tables in a specific format. Views can simplify complex queries, enhance security by limiting data access, and help with database abstraction.

Key Characteristics of Views

1. Virtual Table: A view behaves like a table but does not store data physically. Instead, it dynamically retrieves data from the underlying tables whenever it is queried.
2. Stored Query: The definition of a view is a SQL query that determines what data the view will present.
3. Read-Only or Updatable: Views can be read-only, or they can be updatable, depending on the database system and how the view is defined.

Advantages of Using Views

1. Simplify Complex Queries: Views can encapsulate complex queries, making it easier for users to retrieve data without writing complex SQL.
2. Security: Views can restrict access to specific rows or columns, providing a way to control data visibility and enhance security.
3. Data Abstraction: Views can present a consistent, abstracted view of the data, hiding the underlying table structure and complexity.
4. Reusability: Once created, views can be reused in multiple queries and applications.

Creating and Using Views

Creating a View

The CREATE VIEW statement is used to define a new view.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

Suppose you have a students table and you want to create a view that shows only the names and grades of students who have passed.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    grade DECIMAL(4, 2)
);

-- Insert some sample data
INSERT INTO students (student_id, name, grade) VALUES (1, 'Alice', 85.5);
INSERT INTO students (student_id, name, grade) VALUES (2, 'Bob', 75.3);
INSERT INTO students (student_id, name, grade) VALUES (3, 'Charlie', 50.0);

-- Create a view for students who have passed
CREATE VIEW passed_students AS
SELECT name, grade
FROM students
WHERE grade >= 60;

Querying a View

Once the view is created, you can query it like a regular table.

SELECT * FROM passed_students;

Updating Data through a View

Some views are updatable, meaning you can perform INSERT, UPDATE, and DELETE operations on them, and the changes will be reflected in the underlying tables. However, this depends on the complexity of the view and the database system.

-- Update through view (if supported)
UPDATE passed_students
SET grade = 90
WHERE name = 'Alice';

Types of Views

1. Simple Views: Based on a single table and generally updatable.
2. Complex Views: Based on multiple tables with joins, aggregations, or subqueries. These are usually read-only.
3. Materialized Views: Physically store the query result and can be refreshed periodically. They improve performance for complex queries but require additional storage.

Example of a Complex View

CREATE VIEW student_summary AS
SELECT s.student_id, s.name, g.course, g.grade
FROM students s
JOIN grades g ON s.student_id = g.student_id
WHERE g.grade >= 60;

Managing Views

• Updating Views: Use the CREATE OR REPLACE VIEW statement to modify an existing view.

  CREATE OR REPLACE VIEW passed_students AS
  SELECT name, grade
  FROM students
  WHERE grade >= 70;

• Dropping Views: Use the DROP VIEW statement to remove a view.

  DROP VIEW passed_students;

Conclusion

Views in SQL are a powerful tool for simplifying data retrieval, enhancing security, and providing an abstracted layer over the underlying database schema. By encapsulating complex queries and controlling data access, views help maintain a clean and efficient database design. Understanding and leveraging views can significantly improve the manageability and usability of a database system.