Accessing Multiple Tables › Qualifying a Table Name › Exercise 6-2

Exercise 6-2

Now You Try It

You have to give the Human Resources department a list of employees and the skills each has. However, it is easier to read this report if the employees' names are listed as well.

Enter a SELECT statement to list employee ID, last name, first name, and skill ID using the EMPLOYEE and EXPERTISE tables. Qualify the column name that is the same in both tables. The EXPERTISE table is in DEMOPROJ and the EMPLOYEE table is in DEMOEMPL.

The result looks like this:

EMP_ID  EMP_LNAME             EMP_FNAME             SKILL_ID
------  ---------             ---------             --------
  1003  Baldwin               James                     1000
  1034  Gallway               James                     6470
  1234  Mills                 Thomas                    1000
  1765  Alexander             David                     6770
  2004  Johnson               Eleanor                   6770
  2010  Parker                Cora                      7000
  2096  Carlson               Thomas                    3065
  2096  Carlson               Thomas                    3333
  2106  Widman                Susan                     6770
  2174  Zander                Jonathan                  4430
  2180  Albertini             Joan                      7000
  2209  Smith                 Michael                   5309
  2246  Hamel                 Marylou                   1000
  2246  Hamel                 Marylou                   6670
                        .
                        .
                        .
69 rows processed

If your results do not match what you see above, check Review Answers for Chapter 6 for the correct SQL syntax. Remember that result tables may be shortened in this guide.

Using an Alias

You use an alias as a shorthand method for qualifying a column name.

You specify the alias in the FROM clause:

from expertise x, employee e

You then use the alias as a prefix on the column name.

How It's Done

To use an alias instead of the table name to qualify the column names from the previous example, enter:

select x.emp_id, emp_lname,
       emp_fname, skill_id
       from expertise x, employee e
       where x.emp_id = e.emp_id;

The result is the same as the previous statement.