CP363 : SQL Views

A view is a virtual table. With a view you can take result sets or a joined set of tables and treat them as a single table. You can perform selects and joins on these virtual tables in the same way you can on a 'regular' table. Views are an excellent way of providing short cuts for commonly used joins, or for limiting access to data.

You cannot create a view containing an ORDER BY clause.

Syntax

CREATE VIEW view-name
( { column-definition }, .. ) AS select statement

CREATE VIEW v_keyword_supp_key AS
  SELECT `k`.`keyword_id` AS `keyword_id`,`k`.`k_desc` AS `k_desc`,`sk`.`supp_key_id` AS `supp_key_id`,
  `sk`.`sk_desc` AS `sk_desc`
  FROM (`keyword` `k` JOIN `supp_key` `sk` ON((`k`.`keyword_id` = `sk`.`keyword_id`)))
  ORDER BY `k`.`k_desc`,`sk`.`sk_desc`

Creates a view named v_keyword_supp_key that joins two DCRIS tables and selects certain attributes of that join. This view can then be used like any other table:

SELECT * FROM v_keyword_supp_key
  WHERE k_desc = 'Military History'

CREATE VIEW V_BECount AS
  SELECT m.member_id, COUNT( keyword_id ) AS BECount
  FROM member AS m LEFT OUTER JOIN member_keyword AS mk
  ON m.member_id = mk.member_id
  GROUP BY m.member_id

This view creates a virtual table named V_BECount that contains the broad expertise count of all members. This virtual attribute can then be selected and queried like any other:

SELECT member_id, BECount FROM V_BECount
  WHERE BECount > 5
  ORDER BY member_id