19.2 VIEW

  • An SQL view is a virtual/logical table that is constructed from other tables or views.

  • It has no data of its own, but obtains data from base/actual tables or other views.

  • It hides complicated SQL syntax.

  • It provides level of isolation between table data and users’ view of data.

19.2.1 DROP VIEW

DROP VIEW IF EXISTS CustomerNameView;

19.2.2 CREATE VIEW

  • Views do not support the SQL ORDER BY clause
CREATE VIEW CustomerNameView AS
    SELECT  LastName AS CustomerLastName,FirstName AS CustomerFirstName
    FROM    CUSTOMER
;

19.2.3 Generate a report from VIEW

SELECT  *
FROM  CustomerNameView
ORDER BY  CustomerLastName, CustomerFirstName
;
TABLE19.1: Displaying records 1 - 10
CustomerLastName CustomerFirstName
Frederickson Mary Beth
Gray Donald
Janes Jeffrey
Johnson Lynda
Smathers Fred
Smith David
tree apple
Twilight Tiffany
Warning Selma
Wilkens Chris

19.2.4 Export the Report to CSV

SELECT * FROM CustomerNameView
INTO OUTFILE 'I:/customer_names.csv' 
  FIELDS ENCLOSED BY '' 
  TERMINATED BY ';' 
  ESCAPED BY '"' 
  LINES TERMINATED BY '\r\n';

Note: \r\n is the new line character in Windows.

19.2.5 ALTER VIEW

Once a view is created, you can modify it using the ALTER VIEW statement. The syntax of the ALTER VIEW statement is similar to the CREATE VIEW statement except that the CREATE keyword is replaced by the ALTER keyword.

19.2.6 Combine Columns

Create a view CustomerPhoneView that combines two columns, AreaCode and PhoneNumber, into a single value.

DROP VIEW IF EXISTS CustomerPhoneView;
-- DROP VIEW IF EXISTS CustomerPhoneView;
CREATE VIEW CustomerPhoneView AS
    SELECT  LastName AS CustomerLastName,FirstName AS CustomerFirstName,
            CONCAT('(', AreaCode,')',PhoneNumber) AS CustomerPhone
    FROM    CUSTOMER
;

19.2.7 Use JOIN in View

  • (Text page 335) Create a VIEW CustomerInterestsView that shows which customers are interested in which artists. Display full names for both customers and artists.

  • Generate a report from the view.

DROP VIEW IF EXISTS CustomerInterestsView;
-- DROP VIEW IF EXISTS CustomerInterestsView;
CREATE VIEW CustomerInterestsView AS
    SELECT CONCAT(c.lastname,' ',c.firstname) AS customername, 
            CONCAT(a.lastname,' ',a.firstname) AS artistname
    FROM customer c JOIN customer_artist_int ca ON c.customerid=ca.customerid 
        JOIN artist a ON ca.artistid=a.artistid

19.2.8 Exercise

Do the following four views. For each view, export the report to a CSV file.

  1. Create a VIEW ArtistInfo that reports all the artists for their full name as well as nationality and age.

  2. Create a VIEW BasicCustomerDataView that reports lastname,firstname , areacode and phonenumber for all the customers from the state WA

  3. (Refer to Text page 335) Create a VIEW ArtistWorkNetView

  4. Create a VIEW ArtistPopularityRank that ranks artists by number of customers having interest in descending order. The report should include firstname, lastname and nationality.