SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | VIEWS

A view in SQL is a virtual table based on the result-set of an SQL statement. A view consists of rows and columns, just like a regular table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Here are some key points about views:

Benefits of Views:

  1. Simplification: They can simplify complex queries. Instead of writing a complex query every time, you can create a view and select data from that view.
  2. Security: Views can be used to restrict the data that a user can see.
  3. Logical Abstraction: They can present a different representation of the data, different from the underlying table structure.

Creating Views:

To create a view, you use the CREATE VIEW statement. For example:

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

Example:

Suppose you have a table named "Employees" and you want a view that only shows the first name and last name:

CREATE VIEW EmployeeNames AS
SELECT first_name, last_name
FROM Employees;

You can then query the view as you would with a normal table:

SELECT * FROM EmployeeNames;

Updating Views:

To update a view, you can use the CREATE OR REPLACE VIEW statement:

CREATE OR REPLACE VIEW view_name AS
SELECT columns FROM table_name WHERE condition;

Deleting Views:

To delete a view, you use the DROP VIEW statement:

DROP VIEW view_name;

Limitations:

  1. Not all views are updatable. There are specific requirements for a view to be updatable, for example, it should not contain any aggregate functions, DISTINCT keyword, etc.
  2. The performance might be an issue with complex views, especially when they're nested, as every time you query a view, the underlying SQL statement runs.

Practical Uses:

  1. Restrict Access to Rows/Columns: If you have a table with sensitive data, you can create a view that excludes the sensitive columns and grant users access to the view, not the underlying table.
  2. Joining Multiple Tables: If you often join multiple tables in a specific way, you can create a view that represents the joined result, making future queries simpler.
  3. Historical or Archived Data: Sometimes, you might archive older data into separate tables. A view can unify the main table and the archive table, making them appear as one continuous set of data.

In summary, views are a powerful feature in SQL databases that offer a flexible and secure way to present data in different formats without altering the actual underlying tables.

  1. How to Create Views in SQL:

    • Description: Creating views in SQL allows you to store a predefined query and use it as if it were a table.
    • Code:
      -- Creating a simple view
CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;

  2. Modifying and Dropping Views in SQL:

    • Description: Modifying and dropping views can be done to update the underlying query or remove the view altogether.
    • Code:
      -- Modifying a view
ALTER VIEW employee_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 20;

-- Dropping a view
DROP VIEW IF EXISTS employee_view;

  3. Views and JOIN Operations in SQL:

    • Description: Views can be used to encapsulate complex JOIN operations for easier use.
    • Code:
      -- Creating a view with JOIN
CREATE VIEW employee_department_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

  4. Views and GROUP BY in SQL:

    • Description: Views can also encapsulate GROUP BY operations for aggregating data.
    • Code:
      -- Creating a view with GROUP BY
CREATE VIEW department_salary_summary AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

  5. Creating Indexed Views in SQL:

    • Description: Indexed views store the result set physically, providing performance benefits for certain queries.
    • Code:
      -- Creating an indexed view
CREATE VIEW indexed_employee_view WITH SCHEMABINDING AS
SELECT employee_id, first_name, last_name
FROM dbo.employees;

-- Creating an index on the view
CREATE UNIQUE CLUSTERED INDEX idx_employee_id
ON indexed_employee_view (employee_id);

  6. Views with Calculated Columns in SQL:

    • Description: Views can include calculated columns based on expressions or operations.
    • Code:
      -- Creating a view with a calculated column
CREATE VIEW employee_full_name AS
SELECT employee_id, first_name, last_name, first_name + ' ' + last_name AS full_name
FROM employees;

  7. Views and Permissions in SQL Server:

    • Description: Granting or revoking permissions on views to control access to data.
    • Code:
      -- Granting SELECT permission on a view
GRANT SELECT ON employee_view TO user1;

-- Revoking permission
REVOKE SELECT ON employee_view TO user1;

  8. Views and Subqueries in SQL:

    • Description: Views can contain subqueries to provide more complex and reusable data structures.
    • Code:
      -- Creating a view with a subquery
CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

  9. Materialized Views in SQL:

    • Description: Materialized views store the result set physically like tables, and their data is refreshed periodically.
    • Code:
      -- Creating a materialized view
CREATE MATERIALIZED VIEW mv_employee_salary AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 30;
Copyright © devanddep.com | All Rights Reserved