Content area
SQL is a foundation technology for the study of information systems. This teaching note provides a free Internet-based Active X control that functions as an interactive SQL resource. The Active X control can be used to process/run SELECT SQL statements against the included database. The database contains four tables: orders, order details, inventory, and accounts receivable (i.e., customers). These tables provide a rich set of data for active learning of SQL concepts and terminology. [PUBLICATION ABSTRACT]
Full text
ABSTRACT
SQL is a foundation technology for the study of information systems. This teaching note provides a free Internet-based Active X control that functions as an interactive SQL resource. The Active X control can be used to process/run SELECT SQL statements against the included database. The database contains four tables: orders, order details, inventory, and accounts receivable (i.e., customers). These tables provide a rich set of data for active learning of SQL concepts and terminology.
Keywords: SQL, Active X Control, SELECT Query, Active Learning
1. INTRODUCTION
SQL is the universal acronym for Structured Query Language. Structured Query Language (SQL) is the technology used to retrieve information from databases. The American National Standards Institute (ANSI) has published guidelines that have established SQL as the standard language for accessing and manipulating relational database management systems. SQL statements are used to perform data processing tasks such as selecting data for a report, selecting data as a an answer to a query, or updating data in a database. Commercial relational database management systems, including products of Oracle(TM) and Microsoft(TM), among others, use SQL. While proprietary database systems usually contain proprietary extensions of SQL that are only used on their particular systems, ANSI-standard SQL statements comprise the majority of available features.
2. INTERNET RESOURCES
There are a variety of Internet resources available to one who wishes to learn about SQL. Many of these resources are available at no cost to the user. These free resources fall into two general categories. The first category contains sites that offer only text. The second category contains sites that provide an interactive SQL processor that enables one to actually 'run' SQL statements and see the resulting output.
2.1 Text-Only Sites
Text-only sites tend to provide lengthy, more detailed discussion of SQL statements than sites that provide an interactive SQL processor. Text-only sites provide examples that illustrate queries and their output. I provide two links to this type of site.
A first example is: http://www.1keydata.com/sql/sql.html This site provides a basic SQL tutorial. SQL syntax is presented, explained, and illustrated with examples. A second example is: http://philip.greenspun.com/sql/ This site is heavily-oriented towards Oracle(TM)developers. It is much more technical and advanced than the previous site mentioned above.
2.2 Interactive Processor Sites
The following are several sites that provide an interactive processor. A first resource is: http://sqlcourse.com/intro.html The web page is offered as a promotion with the hope that users will subsequently enroll in one or more of the for-fee training courses advertised at the site. A second, similar resource is: http://www.w3schools.com/sql/default.asp A third resource is http://sqlzoo.net/ This is a very detailed site with a large amount of information pertaining to databases.
3. ACTIVE X INTERACTIVE SQL PROCESSOR
This section describes an Internet resource for learning SQL that has been prepared by the author. It falls into the second category described above as it provides an interactive SQL processor that enables one to actually 'run' SQL statements. It is, however, technically different. The resource includes the processor and a database.
Each of the three interactive sites cited above function with a client-server model. The user, the client, enters SQL into a standard text box, then clicks a button to 'send' the SQL to the server for processing. The server is remote. It processes the SQL against the database and returns output to the client. At times, a remote server may not be available, an inherent problem with a client-server model.
SQL Processor must initially be downloaded and 'installed', then it functions essentially as a desktop or pure 'client' application. Both the processor and database are stored on the user's computer. Technically, SQL Processor is an ActiveX document that runs in Microsoft(TM) Internet Explorer. Installation is controlled entirely by Microsoft(TM) Internet Explorer and most of the required content is downloaded from the Microsoft.com web site. Installation requires that the browser security setting be set to LOW to enable downloading of Active Content.
SQL Processor is available at http://www.gbodnar.com/websql.htm. Complete instructions for installation are provided, but the process is quite simple and direct and involves nothing more than a few mouse clicks. The first visit to this page initiates the installation process. Subsequent visits will not require that browser security setting be reset to Low.
SQL Processor processes a database with four tables: orders, order details, inventory, and acctsreceivable (i.e., accounts receivable / customers). An order is recorded in two separate tables. The table titled Orders' contains a single record of header information for each order: order number, customer number, date, order type, subtotal, shipping, amount due, and posted fields. The items ordered and related amounts for each entry (a 'many to one relationship1) are recorded in the table titled 'Order Details'. Each entry in the order details table contains the order id number, the vendor product number of the item, quantity of the item, price, and item total (quantity times price). The accounts receivable ledger is stored in the table titled 'acctsreceivable'. The accounts receivable ledger contains seven data fields for each account. The account field uniquely identifies each customer. The next five fields (company, street, city, state, and zip code) store address information. The balance field stores the account balance as a positive or negative number. The inventory table stores the description and sales price of items in inventory. It has three fields: vendor product, name, and price.
SQL Processor includes a data dictionary function that displays the details of each data field in a table (i.e., length, type, etc.) This is quite useful when preparing SQL statements.
Only SELECT queries can be used in SQL Processor. Select Queries do not modify a database. INSERT, DELETE, and UPDATE queries modify a database and thus require that an active learning resource process them against a copy of the database or provide a capability to restore the database. This feature is neither practical nor possible with an Active X document. The SELECT statement is the core of SQL; much of the syntax used in SELECT queries is applicable to the other three types of queries.
4. SUMMARY
SQL is a foundation technology in information systems. This teaching note documents a free Internet-based interactive SQL learning resource for hand-on learning of this important technology.
AUTHOR BIOGRAPHY
George H. Bodnar is an Associate Professor in the School of Business Administration of Duquesne University, Pittsburgh, PA. He earned his Ph.D. in 1975 from the Wharton School, University of Pennsylvania with an Accounting major and a minor in Operations Research. He authored a textbook Accounting Information Systems in 1980. It has been in continuous publication for 25 years, now in a 9th Edition with Prentice Hall. He has integrated computer technology in his teaching efforts for more than 30 years, and has been an active programmer in Microsoft Visual Basic for more than 10 years.
Copyright EDSIG Winter 2005