6.1 SQL AND RELATIONAL DATABASES

---

Structured Query Language (SQL) was developed to provide a common interface language for accessing relational databases. The key concept behind relational databases is that all information is stored in tables. The example in Figure X.2 illustrates a customer order tracking system that uses three tables. Consider the first table called 'Orders', it contains four rows, each with an order number. The first three rows are for the same order, and order number. In this case all three entries are also for the same customer, but it involves three different parts. The entries in the 'customer_id' and 'part_id' columns can be used to lookup more information from the two other tables.

Figure X.2 - An Order Tracking Database

The tables in Figure X.2 can be created using the SQL commands in Figure X.3. One command is needed for each table. Each command starts with 'CREATE TABLE' followed by the name of the table. After this the columns of the table are defined. In the case of the 'Orders' table there are four columns. Each column is given a unique name, and a data type is defined. Once these commands have been used the tables will exist, but be empty with no data in them.

Figure X.3 - The SQL Commands to Create the Tables in Figure X.2

Figure X.4 shows SQL commands to enter data into the tables. In all cases these statements begin with 'INSERT INTO', followed by the table name. After that the data fields to fill are named, followed by the actual values to insert. The column names are provided so that the values can be supplied in a different order, or omitted altogether. In this example all of the values are provided for all 'INSERT INTO' statement, but this is not necessary.

Figure X.4 - Entering Data Into The Tables

Once data has been entered into the database it can be recalled using a simple 'SELECT' statement. In the first example the '*' indicates to select all data values 'FROM' the 'Customers' table. The second example shows only listing the 'name' values from the 'Customers' table. Finally the third example shows the listing of 'order_numbers' from the 'Orders' table where the 'quantity' of parts is greater than 10.

Figure X.5 - Simple Database Query Examples

It is possible to make database queries where the results are merged from many different tables. The example in Figure X.6 shows a query that is to list values for 'order_number', 'name', 'description' and 'location'. These are to be merged from three tables 'Orders', or 'O', Customers, or 'C', and 'Parts', or 'P'. Finally, the conditions for a match follow the 'WHERE' statement. The conditions are the 'customer_id' field in the 'Customer' and 'Order' tables must match., and the 'part_id' field must match in the 'Order' and 'Part' tables.

Figure X.6 - A More Advanced Query

The SQL queries are easily used when interacting with a command interface. Although, it is more common for these commands to be used from within computer programs that call the database to make automatic queries.