Before You Start › What You Should Know About SQL › Table and View Examples

Table and View Examples

Following is a conceptual diagram of a table named PERSONNEL:

Following is a conceptual diagram of a table named PAY:

The two previously shown tables contain information about the same four people (match the EMPNO columns), but the order of the rows in each table is not significant.

However, the columns appear in the same order in each row. For example, in the PERSONNEL table, EMPNO is always first, LNAME is always second, FNAME is always third, and so on.

The values which appear in a column fall within the same type, that is to say, LNAME, FNAME and MI each contain character data, while SALARY contains numeric data.

The values which appear in LNAME all fall within the range of valid values, or domain, of "last name," the values in FNAME are within the domain of "first name," and the values in SALARY are within the domain of "salary" which is $999,999.99 to 0.00 for this example.

Some columns, such as ST (for "state"), may contain duplicate values (in this case, TX), but that does not mean that TX is the only value in the domain for the column ST.

Other columns contain only unique values, such as EMPNO, since no two employees of this company have the same employee number. In the previous example, the employee number is used to uniquely identify information about each employee no matter which table contains the information.

Using the tables in the previous example, you could define a view that allows you to see the name of each employee (columns LNAME, FNAME and MI from the PERSONNEL table) and the salary of that employee (column SALARY from the PAY table). In your "view," the information you requested would look like a table and actually joins specified data from two different tables.

Following is a conceptual diagram of a view which you have named WAGES:

The view WAGES, derived from the tables PERSONNEL and PAY, thus shows a "view" of only the columns that you want to see.