SQL Basics

• Views are virtual tables
• Their rows are the result of a query rather than a separately stored table
• A view stores a query definition that is evaluated when the view is used
• An ordinary view stores a query, not its result data

CREATE VIEW <NewTableName> AS
SELECT      <column1>,
            <column2>
FROM        <table1> INNER JOIN <table2> ON <table1.value1> = <table2.value2>

Creating a view

• Views are used to:
  • Reuse queries — convenience
  • Simplify complex SQL statements — simplicity
  • Expose selected parts of a table rather than the whole table
  • Let users with fewer privileges work with a limited part of the data — security
  • Format data, for example by combining columns — presentation

• Points to consider when using views:
  • Views need unique names, just like tables
  • There is no fixed SQL-wide limit on the number of views
  • Creating a view requires the appropriate privileges
  • Views can be nested: one view can use another view's result
  • Do not rely on ORDER BY in a view; rules involving LIMIT or TOP vary by DBMS
  • Support for indexes, triggers and defaults on views varies by DBMS
  • Some DBMSs require every column in a view to have its own name or alias
  • In some DBMSs, including SQLite, ordinary views are read-only
  • A DBMS may reject an insert or update that would cause the affected row to fall outside the view's result

• Instead of repeatedly running the query below,

SELECT cust_name,
       cust_contact,
       prod_id
FROM   Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
                 INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num;	

• it may be useful to create a view

CREATE VIEW ProductCustomers AS
SELECT      cust_name,
            cust_contact,
            prod_id
FROM        Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
                      INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num;

• Once the view exists, its result can be retrieved with a simpler query

SELECT *
FROM   ProductCustomers;

cust_name	cust_contact	prod_id
Village Toys	John Smith	BR01
Village Toys	John Smith	BR03
Village Toys	John Smith	BNBG01
Village Toys	John Smith	BNBG02
Village Toys	John Smith	BNBG03
Fun4All	Jim Jones	BR01
Fun4All	Jim Jones	BR02
Fun4All	Jim Jones	BR03
Fun4All	Denise L. Stephens	BR03
Fun4All	Denise L. Stephens	BNBG01
Fun4All	Denise L. Stephens	BNBG02
Fun4All	Denise L. Stephens	BNBG03
Fun4All	Denise L. Stephens	RGAN01
The Toy Store	Kim Howard	RGAN01
The Toy Store	Kim Howard	BR03
The Toy Store	Kim Howard	BNBG01
The Toy Store	Kim Howard	BNBG02
The Toy Store	Kim Howard	BNBG03

Keep a view general enough to be reusable

• If combined columns are needed frequently:

-- Oracle / PostgreSQL
SELECT   RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title
FROM     Vendors
ORDER BY vend_name; 

-- MySQL
SELECT   CONCAT(vend_name, ' (', vend_country, ')') AS vend_title
FROM     Vendors
ORDER BY vend_name; 	

-- SQL Server
SELECT   RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM     Vendors
ORDER BY vend_name; 

vend_title

Bear Emporium (usa)

Bears R Us (usa)

Doll House Inc. (usa)

Fun and Games (England)

Furball Inc. (usa)

Jouets et ours (France)

-- Oracle / PostgreSQL
CREATE VIEW VendorLocations AS
SELECT      RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title
FROM        Vendors; 

-- MySQL
CREATE VIEW VendorLocations AS
SELECT      CONCAT(vend_name, ' (', vend_country, ')') AS vend_title
FROM        Vendors;	

-- SQL Server
CREATE VIEW VendorLocations AS
SELECT      RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM        Vendors; 

• Remember that ORDER BY is not included in the view

SELECT   *
FROM     VendorLocations
ORDER BY vend_title;

vend_title

Bear Emporium (usa)

Bears R Us (usa)

Doll House Inc. (usa)

Fun and Games (England)

Furball Inc. (usa)

Jouets et ours (France)

• A WHERE clause in a view can exclude particular data

CREATE VIEW CustomerEmailList AS
SELECT      cust_name AS Klantnaam,
            cust_email AS Email_adres
FROM        Customers
WHERE       cust_email IS NOT NULL;

SELECT *
FROM   CustomerEmailList;

Klantnaam	Email_adres
Village Toys	sales@villagetoys.com
Fun4All	jjones@fun4all.com
Fun4All	dstephens@fun4all.com

DROP VIEW <View-Name>;

DROP VIEW VendorLocations;

71. Create and test a view showing all information about customers who have placed an order

72. When Did SQL Begin?