The SELECT Statement

Retrieval of data is effected in SQL by the SELECT statement. For example, the NAME, SALARY, and SUPERVISOR data for employees in the computer science department is extracted as follows:

               SELECT      NAME, SALARY, SUPERVISOR
               FROM        EMPLOYEE
               WHERE       DEPT = ‘Computer Science’

This query applied to instance of EMPLOYEE previously given returns the following data:

                NAME        SALARY         SUPERVISOR
                Kaplan      35,000           Brown
                Brown       55,000           Black

The WHERE clause in a SELECT statement is optional. SQL also allows the retrieved records to be grouped together for statistical computations by means of built-in statistical functions. For example, the following query gives the average salary for employees in each department:

                SELECT       DEPT, AVG(SALARY)
                FROM         EMPLOYEE
                GROUP BY     DEPT

Data from two or more relations can be retrieved and linked together in a SELECT statement. For example, the location of employees can be retrieved by linking the data in EMPLOYEE with that in DEPARTMENT, as follows:

          SELECT     NAME, LOCATION
          FROM       EMPLOYEE, DEPARTMENT
          WHERE      EMPLOYEE.DEPT = DEPARTMENT.DEPT

This query attempts to match every tuple in EMPLOYEE with every tuple in DEPARTMENT but selects only those pairs for which the DEPT attribute in the EMPLOYEE tuple matches the DEPT attribute in the DEPARTMENT tuple. Because DEPT is a common attribute to both relations, every use of it is explicitly identified as occurring with respect to one of the two relations. Queries involving two relations in this manner are known as joins.

The UPDATE Statement

Finally, the UPDATE statement allows one or more attributes of existing tuples in a relation to be modified. For example, the following statement gives all employees in the Computer Science department a raise of $1000:

                UPDATE     EMPLOYEE
                SET        SALARY = SALARY + 1000
                WHERE      DEPT = ‘Computer Science’

This statement selects those tuples in EMPLOYEE that have the value of Computer Science for the DEPT attribute. It then increases the value of the SALARY attribute for all these tuples by $1000 each.

BASE RELATIONS AND VIEWS

The concept of a view has an important security application in relational systems. A view is a virtual relation derived by an SQL definition from base relations and other views. The data base stores the view definitions and materializes the view as needed. In contrast, a base relation is actually stored in the data base.

For example, the EMPLOYEE relation previously discussed is a base relation. The following SQL statement defines a view called COMPUTER_SCI_DEPT:

          CREATE     VIEW COMPUTER_SCI_DEPT
          AS         SELECT    NAME, SALARY, SUPERVISOR
                     FROM      EMPLOYEE
                     WHERE     DEPT = ‘Computer Science’

This defines the virtual relation as follows:

A user who has permission to access COMPUTER_SCI_DEPT is thereby restricted to retrieving information about employees in the computer science department. The dynamic aspect of views can be illustrated by an example in which a new employee, Turing, is inserted in base relation EMPLOYEE, modifying it as follows:

The view COMPUTER_SCI_DEPT is automatically modified to include Turing, as follows:

In general, views can be defined in terms of other base relations and views.

Views can also provide statistical information. For example, the following view gives the average salary for each department:

           CREATE      VIEW AVSAL(DEPT,AVG)
           AS          SELECT       DEPT,AVG(SALARY)
                       FROM         EMPLOYEE
                       GROUP BY     DEPT

For retrieval purposes, there is no distinction between views and base relations. Views, therefore, provide a very powerful mechanism for controlling what information can be retrieved. When updates are considered, views and base relations must be treated quite differently. In general, users cannot directly update views, particularly when they are constructed from the joining of two or more relations. Instead, the base relations must be updated, with views thus being updated indirectly. This fact limits the usefulness of views for authorizing update operations.

DISCRETIONARY ACCESS CONTROLS

This section describes the discretionary access control (DAC) facilities included in the SQL standard, though the standard is incomplete and does not address several important issues. Some of these deficiencies are being addressed in the evolving standard. Different vendors have also provided more comprehensive facilities than the standard calls for.

SQL Privileges

The creator of a relation in an SQL data base is its owner and can grant other users access to that relation. The access privileges or modes recognized in SQL correspond directly to the CREATE, INSERT, SELECT, DELETE, and UPDATE SQL statements discussed previously. In addition, a REFERENCES privilege controls the establishment of foreign keys to a relation.