© Dmitriy Shironosov/ShutterStock, Inc. Databases | 171 LABORATORY 16
Databases OBJECTIVE
■■ Write simple SQL queries using the Simple SQL app. REFERENCES Software needed: 1) Simple SQL app from the Lab Manual website (Simple SQL.jar) © Jones & Bartlett Learning, LLC
NOT FOR SALE OR DISTRIBUTION 172 | Laboratory 16 BACKGROUND
Database programs are less standard and generally much more expensive than
spreadsheets. Microsoft Access is one of the most popular database programs. Depending
upon which bundle of programs you have, you might have access to Access on your
computer. This lab uses an app called “Simple SQL” to introduce relational databases
and SQL (Structured Query Language) queries. Obviously, it is not as big and robust as
Microsoft Access, but it has the virtue of being free and portable! If you have access to
MySQL, Oracle, or other relational database products, you can experiment with more
complicated SQL queries. Review these topics from your textbook, lecture notes, or
online resources:
■■ Database management systems, the relational database model
■■ Structured Query Language (SQL), database design ACTIVITY
Now that we’ve worked on spreadsheets, we’ll experiment with another application that
organizes vast amounts of information: databases. We will focus on SQL queries in a
very simple relational database app. Start the Simple SQL app and click on the Example
tables button. This generates two tables, People and CityInfo, and inserts them into the
database. Next, choose Example 1 from the Process queries pull-down list: The tables in this database are listed in the upper text box labeled Tables. The SQL
query appears in the lower box. You can type in your own queries, using select
statements similar to those found in many textbooks and tutorials, or you can load
the examples.
There are some major differences between real SQL and the SQL implemented by
this app. For one thing, this app supports only a tiny subset of the full SQL language.
Also, the app is case-sensitive, unlike real SQL, in which you can freely mix uppercase
and lowercase. The Simple SQL app generally uses lowercase letters, but the names
of the fields and the names of the tables, as well as the data inside apostrophes, often
© Jones & Bartlett Learning, LLC
NOT FOR SALE OR DISTRIBUTION Databases | 173 contain uppercase letters. For example, the keyword select is all lowercase, but Name and
Age are the names of two fields from a table named People.
Click on Process Query, and the app attempts to execute the SQL command in the
lower text area. If it is successful, it makes a new table with a name like result0, result1,
etc. These names appear in the Tables list in the upper box. You will see a new table,
result0, added to the Tables list.
To view a table, click once on its name and then press the Edit button. Do that now
with the result0 table. A new window appears: Each field gets its own column, with a name and a type. Only two types are allowed:
String and Number. The rows of the table appear across the tops of the columns, and to
the left is a list of row numbers. At the bottom is an informational box.
This edit window allows only five fields to be displayed, even though tables in Simple
SQL can contain up to eight fields (which is necessary when joining, which we’ll talk
about in a moment). There are modification commands in SQL you can read about, such
as insert, update, and delete. Such commands are essential if a program is changing
the table, such as a banking program written in COBOL or a medical statistics program
written in C++ would do.
However, Simple SQL supports only two commands: select and join. To change
anything in a table, such as the table name, field names, field types, or data in the fields,
you must do it in the edit window, then click the OK button to save your changes. To add
a new field, simply pick a blank column and type in information. Make sure to name your
new column of data by typing a name in the blank text field at the top of the column. If
Simple SQL does not find a name there, your new field will not be recognized.
To copy a table, select it from the Tables list and click the Edit button. Rename the
table in the edit window and save it by clicking OK. To delete a table, click once on its
name in the main window and then click the Delete button.
When editing, you must be careful if you decide to delete a row. If you delete one row
from a particular column, you must delete the same row from the other columns. Otherwise,
Simple SQL will line up the rows as they appear in the columns, and your data may get
“twisted.” Also, note that in some circumstances, Simple SQL pads out missing rows in some
columns with 0. In addition, don’t edit the row numbers in the leftmost column. Simple SQL
ignores whatever you do there and refreshes the numbers when you redisplay the table.
© Jones & Bartlett Learning, LLC
NOT FOR SALE OR DISTRIBUTION 174 | Laboratory 16 Next, let’s join for a while. No, this is not carpentry or some collective form of
meditation. Join is one of those mathematical operations of SQL, and its basis is the
Cartesian product. Select Example 4 from the pull-down box and click Process Query.
Look at the resulting table: The statement People join CityInfo is a valid SQL statement that takes the Cartesian product. This means that all the fields
in both the People and the CityInfo tables are in the new result, and the rows follow this
pattern. For each and every row in People, append each row of CityInfo. Since there are
five rows in People and three rows in CityInfo, there will be 15 rows (5 × 3) in the result.
Look at the first three rows of the result: Mark, 45, Valentine. Notice that this is the same
as the first row of People. But the first three rows of CityInfo are appended to the end,
forming three new, unique rows. This is the Cartesian product and the basis of join.
Why do tables join? To make room for a large family dinner? No, sorry—tables join
so that the information in them can be cross-correlated. In a moment, we will correlate
