Table of Contents for

Learning SQL, 2nd Edition

If you create a table and allow users to query it, they will be able to access every column and every row in the table. As I pointed out earlier, however, your table may include some columns that contain sensitive data, such as identification numbers or credit card numbers; not only is it a bad idea to expose such data to all users, but also it might violate your company’s privacy policies, or even state or federal laws, to do so.

The best approach for these situations is to keep the table private (i.e., don’t grant select permission to any users) and then to create one or more views that either omit or obscure (such as the 'ends in ####' approach taken with the customer_vw.fed_id column) the sensitive columns. You may also constrain which rows a set of users may access by adding a where clause to your view definition. For example, the next view definition allows only business customers to be queried:

CREATE VIEW business_customer_vw
 (cust_id,
  fed_id,
  cust_type_cd,
  address,
  city,
  state,
  zipcode
 )
AS
SELECT cust_id,
  concat('ends in ', substr(fed_id, 8, 4)) fed_id,
  cust_type_cd,
  address,
  city,
  state,
  postal_code
FROM customer
WHERE cust_type_cd = 'B'

If you provide this view to your corporate banking department, they will be able to access only business accounts because the condition in the view’s where clause will always be included in their queries.

Reporting applications generally require aggregated data, and views are a great way to make it appear as though data is being pre-aggregated and stored in the database. As an example, let’s say that an application generates a report each month showing the number of accounts and total deposits for every customer. Rather than allowing the application developers to write queries against the base tables, you could provide them with the following view:

CREATE VIEW customer_totals_vw
 (cust_id,
  cust_type_cd,
  cust_name,
  num_accounts,
  tot_deposits
 )
AS
SELECT cst.cust_id, cst.cust_type_cd,
  CASE
    WHEN cst.cust_type_cd = 'B' THEN
     (SELECT bus.name FROM business bus WHERE bus.cust_id = cst.cust_id)
    ELSE
     (SELECT concat(ind.fname, ' ', ind.lname)
      FROM individual ind
      WHERE ind.cust_id = cst.cust_id)
  END cust_name,
  sum(CASE WHEN act.status = 'ACTIVE' THEN 1 ELSE 0 END) tot_active_accounts,
  sum(CASE WHEN act.status = 'ACTIVE' THEN act.avail_balance ELSE 0 END) tot_balance
FROM customer cst INNER JOIN account act
  ON act.cust_id = cst.cust_id
GROUP BY cst.cust_id, cst.cust_type_cd;

Using this approach gives you a great deal of flexibility as a database designer. If you decide at some point in the future that query performance would improve dramatically if the data were preaggregated in a table rather than summed using a view, you can create a customer_totals table and modify the customer_totals_vw view definition to retrieve data from this table. Before modifying the view definition, you can use it to populate the new table. Here are the necessary SQL statements for this scenario:

mysql> CREATE TABLE customer_totals
    -> AS
    -> SELECT * FROM customer_totals_vw;
Query OK, 13 rows affected (3.33 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> CREATE OR REPLACE VIEW customer_totals_vw
    ->  (cust_id,
    ->   cust_type_cd,
    ->   cust_name,
    ->   num_accounts,
    ->   tot_deposits
    ->  )
    -> AS
    -> SELECT cust_id,  cust_type_cd,  cust_name,  num_accounts,  tot_deposits
    -> FROM customer_totals;
Query OK, 0 rows affected (0.02 sec)

From now on, all queries that use the customer_totals_vw view will pull data from the new customer_totals table, meaning that users will see a performance improvement without needing to modify their queries.

One of the most common reasons for deploying views is to shield end users from complexity. For example, let’s say that a report is created each month showing the number of employees, the total number of active accounts, and the total number of transactions for each branch. Rather than expecting the report designer to navigate four different tables to gather the necessary data, you could provide a view that looks as follows:

CREATE VIEW branch_activity_vw
 (branch_name,
  city,
  state,
  num_employees,
  num_active_accounts,
  tot_transactions
 )
AS
SELECT br.name, br.city, br.state,
 (SELECT count(*)
  FROM employee emp
  WHERE emp.assigned_branch_id = br.branch_id) num_emps,
 (SELECT count(*)
  FROM account acnt
  WHERE acnt.status = 'ACTIVE' AND acnt.open_branch_id = br.branch_id) num_accounts,
 (SELECT count(*)
  FROM transaction txn
  WHERE txn.execution_branch_id = br.branch_id) num_txns
FROM branch br;

This view definition is interesting because three of the six column values are generated using scalar subqueries. If someone uses this view but does not reference the num_employees, num_active_accounts, or tot_transactions column, then none of the subqueries will be executed.

Some database designs break large tables into multiple pieces in order to improve performance. For example, if the transaction table became large, the designers may decide to break it into two tables: transaction_current, which holds the latest six months’ of data, and transaction_historic, which holds all data up to six months ago. If a customer wants to see all the transactions for a particular account, you would need to query both tables. By creating a view that queries both tables and combines the results together, however, you can make it look like all transaction data is stored in a single table. Here’s the view definition:

CREATE VIEW transaction_vw
 (txn_date,
  account_id,
  txn_type_cd,
  amount,
  teller_emp_id,
  execution_branch_id,
  funds_avail_date
 )
AS
SELECT txn_date,  account_id,  txn_type_cd,  amount,  teller_emp_id,
  execution_branch_id,  funds_avail_date
FROM transaction_historic
UNION ALL
SELECT txn_date,  account_id,  txn_type_cd,  amount,  teller_emp_id,
  execution_branch_id,  funds_avail_date
FROM transaction_current;

Using a view in this case is a good idea because it allows the designers to change the structure of the underlying data without the need to force all database users to modify their queries.

The view at the beginning of the chapter is about as simple as it gets, so let’s start there:

CREATE VIEW customer_vw
 (cust_id,
  fed_id,
  cust_type_cd,
  address,
  city,
  state,
  zipcode
 )
AS
SELECT cust_id,
  concat('ends in ', substr(fed_id, 8, 4)) fed_id,
  cust_type_cd,
  address,
  city,
  state,
  postal_code
FROM customer;

The customer_vw view queries a single table, and only one of the seven columns is derived via an expression. This view definition doesn’t violate any of the restrictions listed earlier, so you can use it to modify data in the customer table, as in:

mysql> UPDATE customer_vw
    -> SET city = 'Woooburn'
    -> WHERE city = 'Woburn';
Query OK, 1 row affected (0.34 sec)
Rows matched: 1  Changed: 1  Warnings: 0

As you can see, the statement claims to have modified one row, but let’s check the underlying customer table just to be sure:

mysql> SELECT DISTINCT city FROM customer;
+------------+
| city       |
+------------+
| Lynnfield  |
| Woooburn   |
| Quincy     |
| Waltham    |
| Salem      |
| Wilmington |
| Newton     |
+------------+
7 rows in set (0.12 sec)

While you can modify most of the columns in the view in this fashion, you will not be able to modify the fed_id column, since it is derived from an expression:

mysql> UPDATE customer_vw
    -> SET city = 'Woburn', fed_id = '999999999'
    -> WHERE city = 'Woooburn';
ERROR 1348 (HY000): Column 'fed_id' is not updatable

In this case, it may not be a bad thing, since the whole point of the view is to obscure the federal identifiers.

If you want to insert data using the customer_vw view, you are out of luck; views that contain derived columns cannot be used for inserting data, even if the derived columns are not included in the statement. For example, the next statement attempts to populate only the cust_id, cust_type_cd, and city columns using the customer_vw view:

mysql> INSERT INTO customer_vw(cust_id, cust_type_cd, city)
    -> VALUES (9999, 'I', 'Worcester');
ERROR 1471 (HY000): The target table customer_vw of the INSERT is not insertable
-into

Now that you have seen the limitations of simple views, the next section will demonstrate the use of a view that joins multiple tables.

While single-table views are certainly common, many of the views that you come across will include multiple tables in the from clause of the underlying query. The next view, for example, joins the business and customer tables so that all the data for business customers can be easily queried:

CREATE VIEW business_customer_vw
 (cust_id,
  fed_id,
  address,
  city,
  state,
  postal_code,
  business_name,
  state_id,
  incorp_date
 )
AS
SELECT cst.cust_id,
  cst.fed_id,
  cst.address,
  cst.city,
  cst.state,
  cst.postal_code,
  bsn.name,
  bsn.state_id,
  bsn.incorp_date
FROM customer cst INNER JOIN business bsn
  ON cst.cust_id = bsn.cust_id
WHERE cust_type_cd = 'B';

You may use this view to update data in either the customer or the business table, as the following statements demonstrate:

mysql> UPDATE business_customer_vw
    -> SET postal_code = '99999'
    -> WHERE cust_id = 10;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE business_customer_vw
    -> SET incorp_date = '2008-11-17'
    -> WHERE cust_id = 10;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The first statement modifies the customer.postal_code column, whereas the second statement modifies the business.incorp_date column. You might be wondering what happens if you try to update columns from both tables in a single statement, so let’s find out:

mysql> UPDATE business_customer_vw
    -> SET postal_code = '88888', incorp_date = '2008-10-31'
    -> WHERE cust_id = 10;
ERROR 1393 (HY000): Can not modify more than one base table through a join view
'bank.business_customer_vw'

As you can see, you are allowed to modify both of the underlying tables, as long as you don’t try to do it with a single statement. Now let’s try to insert data into both tables for a new customer (cust_id = 99):

mysql> INSERT INTO business_customer_vw
    ->  (cust_id, fed_id, address, city, state, postal_code)
    -> VALUES (99, '04-9999999', '99 Main St.', 'Peabody', 'MA', '01975');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO business_customer_vw
    ->  (cust_id, business_name, state_id, incorp_date)
    -> VALUES (99, 'Ninety-Nine Restaurant', '99-999-999', '1999-01-01');
ERROR 1393 (HY000): Can not modify more than one base table through a join view
'bank.business_customer_vw'

The first statement, which attempts to insert data into the customer table, works fine, but the second statement, which attempts to insert a row into the business table, raises an exception. The second statement fails because both tables include a cust_id column, but the cust_id column in the view definition is mapped to the customer.cust_id column. Therefore, it is not possible to insert data into the business table using the preceding view definition.

Create a view that queries the employee table and generates the following output when queried with no where clause:

+-----------------+------------------+
| supervisor_name | employee_name    |
+-----------------+------------------+
| NULL            | Michael Smith    |
| Michael Smith   | Susan Barker     |
| Michael Smith   | Robert Tyler     |
| Robert Tyler    | Susan Hawthorne  |
| Susan Hawthorne | John Gooding     |
| Susan Hawthorne | Helen Fleming    |
| Helen Fleming   | Chris Tucker     |
| Helen Fleming   | Sarah Parker     |
| Helen Fleming   | Jane Grossman    |
| Susan Hawthorne | Paula Roberts    |
| Paula Roberts   | Thomas Ziegler   |
| Paula Roberts   | Samantha Jameson |
| Susan Hawthorne | John Blake       |
| John Blake      | Cindy Mason      |
| John Blake      | Frank Portman    |
| Susan Hawthorne | Theresa Markham  |
| Theresa Markham | Beth Fowler      |
| Theresa Markham | Rick Tulman      |
+-----------------+------------------+
18 rows in set (1.47 sec)

The bank president would like to have a report showing the name and city of each branch, along with the total balances of all accounts opened at the branch. Create a view to generate the data.