Home > Articles > Data > SQL

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Examples of Tables

Up to now, we have discussed the theory of tables, but you have not seen any real ones. In the following sections you will see some actual tables. We look at a table to see how it looks in both Oracle and Access. We discuss some of the design decisions that are used in constructing many tables. We also examine the tables of the Lunches database, which is used in many of the examples throughout this book.

1-12 An example of a table in Oracle and Access

This section shows the same table in both Oracle and Access. This is our first opportunity to examine how Oracle and Access compare.

You will have to decide for yourself how similar they are and how different they are. To me, this example shows that they are about 90 percent similar and about 10 percent different. Of course, this is just one example. You might ask yourself which percentages you would use to describe this.

Oracle tables can be shown in two formats that are very similar, but have a few slight differences. To keep things simple here, I am only showing you one of those formats. The following Oracle table was obtained using the “SQL Command Line” environment. The other Oracle format occurs in the “Database Home Page” environment. I will discuss it briefly in the notes at the end of this section.

l_employees table: Oracle format

EMPLOYEE                      DEPT               CREDIT PHONE  MANAGER
-------- ---------- --------- ---- ------------ ------- ------ -------
     201 SUSAN      BROWN     EXE  01-JUN-1998   $30.00 3484    (null)
     202 JIM        KERN      SAL  16-AUG-1999   $25.00 8722       201
     203 MARTHA     WOODS     SHP  02-FEB-2009   $25.00 7591       201
     204 ELLEN      OWENS     SAL  01-JUL-2008   $15.00 6830       202
     205 HENRY      PERKINS   SAL  01-MAR-2006   $25.00 5286       202
     206 CAROL      ROSE      ACT  (null)        (null) (null)  (null)
     207 DAN        SMITH     SHP  01-DEC-2008   $25.00 2259       203
     208 FRED       CAMPBELL  SHP  01-APR-2008   $25.00 1752       203
     209 PAULA      JACOBS    MKT  17-MAR-1999   $15.00 3357       201
     210 NANCY      HOFFMAN   SAL  16-FEB-2007   $25.00 2974       203

Similarities between Oracle and Access

  • Column names are printed at the top of the column. The column names are part of the structure of the table, not part of the data in the table.
  • Sometimes the column names shown in the column headings are truncated. This is a slight problem. You are given tools to deal with it.
  • Columns containing text data are justified to the left.
  • Columns containing numbers are justified to the right.
  • Columns containing dates often display only the date. The format for displaying the date is not part of the data. The value of the date is stored in the table, but the format of the date is specified separately. The date actually contains both a date and a time, but the time is often not displayed.
  • Columns displaying currency amounts are actually stored as numbers, and use a format to put in the dollar signs and decimal points.

Differences between Oracle and Access

  • Display framework: Oracle displays lines of character data. Access uses graphical techniques to display the data in a grid and color the borders of the grid.

  • Case: The Oracle table is shown all in uppercase. The Access table uses uppercase only for the first letter. It is a common convention to set the databases up this way. Mixed-case data can be put into an Oracle table, but this makes the data more difficult to handle, so Oracle data is usually either all uppercase or all lowercase. Access data is handled as if it were all uppercase, although it is displayed in mixed case. This makes it look nicer, but sometimes it can also be deceiving. In Access, the data appears to be mixed case, but the data behaves as if it were in uppercase. For instance, John and jOhn appear different in Access, but they are handled as if they are the same.

  • Column headings: Oracle can use several lines for a column heading. Access displays the heading on a single line.

  • Date formats: The dates above show Oracle and Access using the same date format. I made that happen here because I wanted Oracle and Access to look similar. However, on your computer the dates will probably use different formats.

    Oracle and Access can both display dates in a variety of formats. Each has a default format to use for dates when no other format is specified. However, Oracle uses one method to specify this default format for dates and Access uses a different method.

  • Date alignment: Oracle aligns dates to the left, whereas Access aligns them to the right.

  • Nulls: In this book, I have set up Oracle to always display nulls as (null) in all the columns of every table. This cannot easily be done in Access.

  • Position pointer: The Access table contains a record selector and a pointer to a particular field within that record, which allows you to modify the data. The Oracle table does not contain these.

  • Ability to add data: In Access, a blank row at the bottom of a table indicates that new rows of data can be entered into the table. Also an extra column is displayed called “Add New Field”. This is not done in Oracle.

1-13 Some design decisions in the l_employees table

The l_employees table contains some design decisions that I want to point out to you because they reflect some common practices within relational databases. Like all design decisions, they could have been made in other ways. This is not the only way to design the table. It might not even be the best way. But you may often encounter these design decisions and you need to be aware of them.

l_employees table

EMPLOYEE                      DEPT               CREDIT PHONE   MANAGER
-------- ---------- --------- ---- ------------ ------- ------  -------
     201 SUSAN      BROWN     EXE  01-JUN-1998   $30.00 3484     (null)
     202 JIM        KERN      SAL  16-AUG-1999   $25.00 8722        201
     203 MARTHA     WOODS     SHP  02-FEB-2009   $25.00 7591        201
     204 ELLEN      OWENS     SAL  01-JUL-2008   $15.00 6830        202
     205 HENRY      PERKINS   SAL  01-MAR-2006   $25.00 5286        202
     206 CAROL      ROSE      ACT  (null)        (null) (null)   (null)
     207 DAN        SMITH     SHP  01-DEC-2008   $25.00 2259        203
     208 FRED       CAMPBELL  SHP  01-APR-2008   $25.00 1752        203
     209 PAULA      JACOBS    MKT  17-MAR-1999   $15.00 3357        201
     210 NANCY      HOFFMAN   SAL  16-FEB-2007   $25.00 2974        203

Design decisions to be aware of

  • The phone_number column contains text data, not numbers. Although the data look like numbers, and the column name says number, it actually has a text datatype. You can tell this by its alignment, which is to the left. The reason the table is set up this way is that the phone number data will never be used for arithmetic. You never add two phone numbers together or multiply them. You only use them the way they are, as a text field. So this table stores them as text.
  • The employee_id column contains numbers. You can tell this by its alignment, which is to the right. Now, we do not do arithmetic with employee IDs, we never add them together, so why isn’t this a text field, too? The answer is that numbers are often used for primary key columns even when no arithmetic will be performed on them. This can allow the computer to handle the table more quickly.
  • The manager_id column contains numbers, but it is not a primary key column. So why doesn’t it contain text? This column is intended to match with the employee_id column, so it has been given the same datatype as that column. This improves the speed of matching the two columns.
  • The name of the table, l_employees, might seem strange. The l indicates that this table is part of a group of tables. The names of all the tables in the group start with the same letter(s). In this case it shows that the table is part of the Lunches database. (Here I use the term database to mean a collection of related tables.)
  • The people who design databases put a considerable amount of work into the consistent naming of objects, using standard prefixes, suffixes, abbreviations, and column names. This makes the whole model easier to understand and more usable for the code that is developed for each database.

1-14 The Lunches database

Most of the examples of SQL code in this book are based on the Lunches database. You can get a complete listing of this database from the Web site. To read this book, you will need to understand the story and the data, so here is the basic story.

There is a small company with ten employees. This company will serve lunch to its employees on three occasions. Each employee can attend as many of these lunches as his or her schedule permits. When employees register to attend a lunch, they get to pick what they want to eat. They may choose from among the ten foods available to them. They can decide to have a single portion or a double portion of any of these foods. The Lunches database keeps track of all this information.

That is the story. Now let’s look at the data. When I call this a database, I mean that it is a collection of related tables. The set of tables, taken together, tell the story. There are seven tables in this database:

  • Employees (l_employees)
  • Departments (l_departments)
  • Constants (l_constants)
  • Lunches (l_lunches)
  • Foods (l_foods)
  • Suppliers (l_suppliers)
  • Lunch Items (l_lunch_items)

To show that these tables are all related to each other and to distinguish them from other tables we may use, the names of these tables are all prefixed with the letter l. When there are multiple words, such as lunch_items, the spaces are replaced with underscore characters. This helps the computer understand that the two words together are a single name.

l_employees table

EMPLOYEE                      DEPT               CREDIT PHONE  MANAGER
-------- ---------- --------- ---- ------------ ------- ------ -------
     201 SUSAN      BROWN     EXE  01-JUN-1998   $30.00 3484    (null)
     202 JIM        KERN      SAL  16-AUG-1999   $25.00 8722       201
     203 MARTHA     WOODS     SHP  02-FEB-2009   $25.00 7591       201
     204 ELLEN      OWENS     SAL  01-JUL-2008   $15.00 6830       202
     205 HENRY      PERKINS   SAL  01-MAR-2006   $25.00 5286       202
     206 CAROL      ROSE      ACT  (null)        (null) (null)  (null)
     207 DAN        SMITH     SHP  01-DEC-2008   $25.00 2259       203
     208 FRED       CAMPBELL  SHP  01-APR-2008   $25.00 1752       203
     209 PAULA      JACOBS    MKT  17-MAR-1999   $15.00 3357       201
     210 NANCY      HOFFMAN   SAL  16-FEB-2007   $25.00 2974       203

The l_employees table lists all the employees. Each employee can be identified by an employee ID, which is a number assigned to him or her. This allows the company to hire two people with the same name. The primary key is the employee_id column.

Each employee has a manager, who is also an employee of the company. The manager is identified by his or her employee ID. For instance, the manager_id column shows that Jim Kern is managed by employee 201. Employee 201 is Susan Brown.

Susan Brown and Carol Rose are the only employees without a manager. You can tell this because there is a null in the manager_id columns. However, these nulls mean different things.

Susan Brown is the head of the company. The null in this case does not mean that we do not know who her manager is. Rather, it means that she does not have a manager.

Carol Rose is a new hire. The null in her manager_id column could mean that she has not yet been assigned to a manager or it could mean that the information has not yet been entered into the database.

l_departments table

---- ------------------------------

Each employee works for one department. The department code is shown in the l_employees table. The full name of each department is shown in the l_departments table. The primary key of this table is dept_code.

These tables can be linked together by matching the dept_code columns. For example, the l_employees table shows us that employee 202, Jim Kern, has a department code of SAL. The l_departments table says that the sales department uses the department code SAL. This tells us that Jim Kern works in the sales department.

l_constants table

------------------------------ ----------- ------------ --------------
CITYWIDE UNIFORMS              01-JUN-1998      $200.00 SUSAN BROWN

The l_constants table contains some constant values and has only one row. We use these values with the other tables of the database. These values are expected to change infrequently, if at all. Storing them in a separate table keeps the SQL code flexible by providing an alternative to hard-coding these values into SQL. Because the table of constants has only one row, it does not need a primary key.

l_lunches table

--------- ----------- ----------- -----------
        1 16-NOV-2011         201 13-OCT-2011
        2 16-NOV-2011         207 13-OCT-2011
        3 16-NOV-2011         203 13-OCT-2011
        4 16-NOV-2011         204 13-OCT-2011
        6 16-NOV-2011         202 13-OCT-2011
        7 16-NOV-2011         210 13-OCT-2011
        8 25-NOV-2011         201 14-OCT-2011
        9 25-NOV-2011         208 14-OCT-2011
       12 25-NOV-2011         204 14-OCT-2011
       13 25-NOV-2011         207 18-OCT-2011
       15 25-NOV-2011         205 21-OCT-2011
       16 05-DEC-2011         201 21-OCT-2011
       17 05-DEC-2011         210 21-OCT-2011
       20 05-DEC-2011         205 24-OCT-2011
       21 05-DEC-2011         203 24-OCT-2011
       22 05-DEC-2011         208 24-OCT-2011

The l_lunches table registers an employee to attend a lunch. It assigns a lunch ID to each lunch that will be served. For example, employee 207, Dan Smith, will attend a lunch on November 16, 2011. His lunch is identified as lunch_id = 2.

The lunch_id column is the primary key of this table. This is an example of a surrogate key, which is also called a meaningless primary key. Each row is assigned a unique number, but there is no intrinsic meaning to that number. It is just a convenient name to use for the row, or the object that the row represents — in this case, a lunch.

The l_lunches table shows the most common way to use a surrogate key. Usually a single column is the primary key. That column has a different value in every row.

Some database designers like to use surrogate keys because they can improve the efficiency of queries within the database. Surrogate keys are used especially to replace a primary key that would have many columns, and when a table is often joined to many other tables.

Other designers do not like surrogate keys because they prefer to have each column contain meaningful data. This is an area of debate among database designers, with many pros and cons on each side. People who use databases need only be aware that these columns are meaningless numbers used to join one table to another.

l_foods table

SUPPLIER PRODUCT    MENU                                  PRICE
-------- ------- ------- -------------------- -------- --------
ASP      FS            1 FRESH SALAD             $2.00    $0.25
ASP      SP            2 SOUP OF THE DAY         $1.50 (null)
ASP      SW            3 SANDWICH                $3.50    $0.40
CBC      GS            4 GRILLED STEAK           $6.00    $0.70
CBC      SW            5 HAMBURGER               $2.50    $0.30
FRV      BR            6 BROCCOLI                $1.00    $0.05
FRV      FF            7 FRENCH FRIES            $1.50 (null)
JBR      AS            8 SODA                    $1.25    $0.25
JBR      VR            9 COFFEE                  $0.85    $0.15
VSB      AS           10 DESSERT                 $3.00    $0.50

The l_foods table lists the foods an employee can choose for his or her lunch. Each food is identified by a supplier ID and a product code. Together, these two columns form the primary key. The product codes belong to the suppliers. It is possible for two suppliers to use the same product code for different foods. In fact, the product code AS has two different meanings. Supplier JBR uses this product code for soda, but supplier VSB uses it for dessert.

The price increases are proposed, but are not yet in effect. The nulls in the price_increase column mean that there will not be a price increase for those food items.

l_suppliers table

-------- ------------------------------

The l_suppliers table shows the full names for the suppliers of the foods. For example, the l_foods table shows that french fries will be obtained from supplier ID FRV. The l_suppliers table shows that Frank Reed’s Vegetables is the full name of this supplier. The primary key of these tables is the supplier ID.

l_lunch_items table

                      SUPPLIER PRODUCT
--------- ----------- -------- ------- ---------
        1           1 ASP      FS              1
        1           2 ASP      SW              2
        1           3 JBR      VR              2
        2           1 ASP      SW              2
        2           2 FRV      FF              1
        2           3 JBR      VR              2
        2           4 VSB      AS              1
        3           1 ASP      FS              1
        3           2 CBC      GS              1
        3           3 FRV      FF              1
        3           4 JBR      VR              1
        3           5 JBR      AS              1
(and many more rows)

When you look at the l_lunch_items table you need to be aware that the data in the item_number column is aligned to the right because it is a column of numbers. The data in the supplier_id column is aligned to the left because it is a column of text. So when you look at the first row, 1 ASP is not a single piece of data. Instead, the item_number value is 1 and the supplier_id value is ASP.

The l_lunch_items table shows which foods each employee has chosen for his or her lunch. It also shows whether they want a single or a double portion. For example, look at lunch_id 2, which we already know to be Dan Smith’s lunch on November 16. It consists of four items. The first item is identified as ASP-SW. Here I am putting the supplier_id and the product_code column data together separated by a hyphen. Looking in the l_foods table, we find this is a sandwich. The l_lunch_items table says he wants two of them, which is shown in the quantity column. See if you can figure out all the foods he wants for his lunch.

The correct answer is:

  • 2 sandwiches
  • 1 order of french fries
  • 2 cups of coffee
  • 1 dessert

The primary key of this table consists of the first two columns of the table, lunch_id and item_number. The item_number column is a tie-breaker column, which is another type of meaningless primary key. In this design, I wanted to use the lunch ID to identify each food within a lunch. However, most lunches have several foods. So I cannot use the lunch ID by itself as a primary key, because that would create several rows in the table with the same value in the primary key, which is not allowed. I needed a way for each row to have a different value in the primary key. That is what a tie-breaker column does. The item_number column numbers the items within each lunch. Therefore, the combination of lunch ID and item number provides a unique identity for each row of the table and can serve as the primary key. A primary key of this sort, containing more than one column, is sometimes called a composite key.

Challenging features of the Lunches database

Most SQL books have you work with a database that is tame and contains no challenges. This book is different. I have intentionally put some features in the Lunches database that could cause you to get the wrong result if you do not handle them properly. I show you how to become aware of these situations and how to deal with them. Many real business databases contain similar challenges. Here are a few of them:

  • Two employees are not attending any of the lunches — employee 209, Paula Jacobs, and employee 206, Carol Rose.
  • One food has not been ordered in any of the lunches — broccoli.
  • One of the departments is not yet staffed with any employees — the personnel department.
  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020