Views - 96ankur/SQL GitHub Wiki

Views

• We can store a query in a view so that we don't have write that query again and again. Whenever we want to execute that complex query, then we can just execute SELECT statement on that view.#
• Creating a view

  CREATE VIEW sales_by_client AS
  SELECT c.client_id, 
  	   c.name,
         SUM(invoice_total) AS total_sale
  FROM clients c
  JOIN invoices i USING (client_id)
  GROUP BY client_id, name
  

• We can also use a view as a table.

  SELECT * 
  FROM sales_by_client  -- sales_by_client is a VIEW
  WHERE total_sales > 500
  

• A VIEW does not store data, data is store in table. It only provide a view to the underlying table.

Altering or Dropping Views

• One way:

   DROP VIEW sales_by_client
  

• Second way:

  CREATE OR REPLACE VIEW sales_by_client AS
  SELECT c.client_id, 
  	   c.name,
         SUM(invoice_total) AS total_sale
  FROM clients c
  JOIN invoices i USING (client_id)
  GROUP BY client_id, name
  

Updateable View

• An updateable view is one through which we can update view data. That is, we can use that view in INSERT, UPDATE, DELETE statements.
• If we don't have the following clauses in a view query then we will call it an UPDATABLE view.:
  • DISTINCT
  • Aggregate Functions (MIN, MAX, SUM, etc)
  • GROUP BY/ HAVING
  • UNION

WITH CHECK OPTION clause

• When we will apply this clause in the create view query, then this will prevent UPDATE, DELETE statements from excluding rows from view.

  CREATE OR REPLACE VIEW sales_by_client AS
  SELECT c.client_id, 
  	   c.name,
         SUM(invoice_total) AS total_sale
  FROM clients c
  JOIN invoices i USING (client_id)
  GROUP BY client_id, name
  WITH CHECK OPTION