SQL: GETTING STARTED

1. INTRODUCTION

To use SQL you must be registered as a INGRES user and have been allocated a database name. For the purpose of this document we will assume that the database we wish to use is called frans. To invoke INGRES using the SQL query language enter:

sql frans

All being well you should see a message of the form:

INGRES TERMINAL MONITOR -- Copyright (c) 1981,
1993 Ingres Corporation
INGRES HP-UX Version 6.4/05 (hp8.us5/00) login
Tue Mar 17 13:28:36 1998

continue
*

The "*" is the SQL terminal monitor prompt. To exit simply type:

* \q

and return. You will see a "logout" message of then form:

INGRES Version 6.4/05 (hp8.us5/00) logout
Tue Mar 17 13:29:38 1998

Tables in INGRES represent a relation. We will assume a management application and construct three tables, an employees table containing typical employee data, a breakfast table containing information as to whether employees have breakfast in the staff canteen or not, and a parking permit table containing information about the allocation and withdrawal of parking permits to employees. The schema for each of these tables is as follows:

We can create these table using SQL as follows:

* CREATE TABLE Employees (Emp_number INTEGER, Name C20);
* \g

* CREATE TABLE Breakfasts (Date DATE, ID_number INTEGER);
* \g

* CREATE TABLE Parking_permits (Permit_Num INTEGER,
* Name C20, Date_Issued DATE);
* \g

Note the "\g", this is the SQL execute statement. If SQL terminal monitor will not accept a statement due to (say) a syntax error we can edit it using "\e". Note also the type declarations used above INTEGER, c20, c5 and date.

We can display the table using the statement:

* SELECT * FROM Employees;
* \g

* SELECT * FROM Breakfasts;
* \g

* SELECT * FROM Parking_permits;
* \g

You will see output of the form:

* SELECT * FROM Employees;
* \g
Executing . . .

+-------------+--------------------+
|emp_number   |name                |
+-------------+--------------------+
+-------------+--------------------+
(0 rows)
continue
* SELECT * FROM Breakfasts;
* \g
Executing . . .

+-------------------------+-------------+
|date                     |id_number    |
+-------------------------+-------------+
+-------------------------+-------------+
(0 rows)
continue
* SELECT * FROM Parking_permit;
* \g
Executing . . .

+-------------+-------------+-------------------------|
|permit_num   |emp_name     |date_issued              |
+-------------+-------------+-------------------------+
+-------------+-------------+-------------------------+
(0 rows)
continue
*

We can enter data into a table using:

* INSERT INTO Employee (number, name)
* VALUES (10001,'Frans Coenen');
* \g

However it is much simpler to load data into the tables from a file using the SQL copy statement. Thus if we have a file employees.data:

10001,'Alonso'
10002,'Sebastian'
10003,'Prospero'
10004,'Antonio'
10005,'Ferdinand'
10006,'Gonzalo'
10007,'Adrian'
10008,'Francisco'
10009,'Caliban'
10010,'Trinculo'
10011,'Stephano'
10012,'Miranda'
10013,'Ariel'
10014,'Iris'
10015,'Ceres'
10016,'Juno'

stored in our current directory this can be loaded as follows:

* COPY TABLE employees (emp_number=C0, name=C0)
* FROM 'employees.data';
* \g

The C0 in the statement specifies the "," dividing elements in the data file. The result can be checked using a select query:

* SELECT * FROM Employees
* \g
Executing . . .


+-------------+--------------------+
|emp_number   |name                |
+-------------+--------------------+
|        10001|'Alonso'            |
|        10002|'Sebastian'         |
|        10003|'Prospero'          |
|        10004|'Antonio'           |
|        10005|'Ferdinand'         |
|        10006|'Gonzalo'           |
|        10007|'Adrian'            |
|        10008|'Francisco'         |
|        10009|'Caliban'           |
|        10010|'Trinculo'          |
|        10011|'Stephano'          |
|        10012|'Miranda'           |
|        10013|'Ariel'             |
|        10014|'Iris'              |
|        10015|'Ceres'             |
|        10016|'Juno'              |
+-------------+--------------------+
(16 rows)
continue
*

We can populate the other tables in a similar manner using a file called breakfasts.data:

3-16-1998,10004
3-16-1998,10007
3-16-1998,10008
3-16-1998,10010
3-16-1998,10011
3-16-1998,10015
3-17-1998,10001
3-17-1998,10004
3-17-1998,10007
3-17-1998,10008
3-17-1998,10010
3-17-1998,10011
3-17-1998,10015
3-18-1998,10004
3-18-1998,10007
3-18-1998,10008
3-18-1998,10010
3-18-1998,10011
3-18-1998,10013
3-18-1998,10015
3-19-1998,10004
3-19-1998,10007
3-19-1998,10010
3-19-1998,10011
3-19-1998,10015
3-20-1998,10004
3-20-1998,10006
3-20-1998,10007
3-20-1998,10010
3-20-1998,10011
3-20-1998,10015

(note American date format), and another called parking_permits.data:

1,10002 16/3/98
2,10003 1/6/98
3,10005 21/10/98
4,10009 21/10/98
5,10012 20/11/98
6,10014 28/2/98
7,10016 28/2/98
8,10008 19/3/98

(UK data format) loaded as follows:

and:

* COPY TABLE Parking_permits (Permit_num=C0, Name=C0, Date_issued=C0)
* FROM 'parking_permits.data';
* \g

We can also use the copy statement to create a file by extracting data from an INGRES database but with the from statement replaced with an into statement. In this case you will however have to specify field lengths. Note that dates are displayed as follows:

* SELECT * FROM breakfasts;
* \g
Executing . . .

+-------------------------+-------------+
|date                     |id_number    |
+-------------------------+-------------+
|16-mar-1998              |        10004|
|16-mar-1998              |        10007|
|16-mar-1998              |        10008|
|16-mar-1998              |        10010|
|16-mar-1998              |        10011|
|16-mar-1998              |        10015|
|17-mar-1998              |        10001|
|17-mar-1998              |        10004|
|17-mar-1998              |        10007|
|17-mar-1998              |        10008|
|17-mar-1998              |        10010|
|17-mar-1998              |        10011|
|17-mar-1998              |        10015|
|18-mar-1998              |        10004|
|18-mar-1998              |        10007|
|18-mar-1998              |        10008|
|18-mar-1998              |        10010|
|18-mar-1998              |        10011|
|18-mar-1998              |        10013|
|18-mar-1998              |        10015|
|19-mar-1998              |        10004|
|19-mar-1998              |        10007|
|19-mar-1998              |        10010|
|19-mar-1998              |        10011|
|19-mar-1998              |        10015|
|20-mar-1998              |        10004|
|20-mar-1998              |        10006|
|20-mar-1998              |        10007|
|20-mar-1998              |        10010|
|20-mar-1998              |        10011|
|20-mar-1998              |        10015|
+-------------------------+-------------+
(31 rows)

3. EXTRACTING DATA

We can perform various extraction functions using the select statement. Some examples follow:

1. * SELECT * FROM employees;
   

   Prints the entire table.

2. * SELECT name FROM employees;
   

   Prints an entire column. To eliminate duplications (if any) we can use the keyword distinct. Thus:

   * SELECT distinct name FROM employees;
   

3. To print more then one column:

   * SELECT permit_num, name FROM Parking_permits;
   

   or: * SELECT name, permit_num FROM Parking_permits; to print the result in a different order.
4. We can print the two columns in alphabetical order according to name:

   * SELECT permit_num, name FROM Parking_permits
   * ORDER BY Name ASC;
   

   To produce the list in reverse alphabetical order we replace the (default)ASC (ascending) statement with a DESC (descending) statement. Note that the column to be ordered can also be indicated by a number.
5. Using a WHERE we can limit a retrieval to a specified row in a table. For example:

   * SELECT ID_number, date FROM Breakfasts WHERE
   * date = '18-mar-1998';
   

   or:

   * SELECT ID_number, date FROM Breakfasts WHERE
   * date = '18-mar-1998';
   

   prints the rows where date = '18-mar-1998'. Alternatively we can state:

   * SELECT ID_number, date FROM Breakfasts WHERE
   * date = '18-mar-1998' OR date = '20-mar-1998';
   

6. You can combine multiple predicates by means of the logical operators AND, OR and NOT. Thus:

   * SELECT ID_number, date FROM Breakfasts WHERE
   * NOT id_number = 10007 and date = '18-mar-1998';
   

   and so on.
7. Statements (predicates) can also be defined using the comparison operators "=", "!=", ">", ">=", "<", "<=". Thus:

   * SELECT Emp_number, Name FROM Employees
   * WHERE Emp_number < 10005 OR Emp_number > 10010;
   

8. We can also retrieve information through partial pattern matching. For example:

   * SELECT Emp_number, Name FROM Employees WHERE name
   * LIKE '%on%';
   

   Alternatively we can use the wild card character "_". Thus:

   * SELECT Emp_number, Name FROM Employees WHERE Name
   * LIKE 'A_onso';
   

   It should be noted that the manuals all use the standard "*" and "?" to represent wild card characters. This is not correct, you should use "%" to do what you would expect of "*" and "_" to do what you would expect of "?" Also note that wherever "=" is used in the manual in connection with wild card characters this is also an error and should be replaced with "like" as in the above examples. In some versions the wild card character ("?" or "_") does not work at all.

These just give a flavour of the SQL predicates that can be created. There are more. For example INGRES supports a full complement of arithmetic operators. Computation can be used with any of the data manipulation statement types.

4. SET FUNCTIONS

Set functions in SQL allow you to perform operations on sets of data. these operations can provide valuable statistics on all or some values in a column. For example, you can count the number of rows in a table, sum the values in a column, and calculate the average of the values in a column, to name a few. Thus to count the number of titles in our example database:

* SELECT NUMBER = COUNT(name) FROM Employees;

or:

* SELECT NUMBER = COUNT(ID_number) FROM Breakfasts
* WHERE date = '18-mar-1998' ;

5. MULTIPLE TABLE RETRIEVAL

SQL also enables the user to retrieve data from several tables with a single query. This is achieved using joins and nested (or sub) queries.

5.1 Joins

An example:

* SELECT b.Date, e.Name
* FROM Breakfasts b, Employees e
* WHERE b.ID_number = e.emp_number;

This is a join between the tables Breakfasts and Employees. So that INGRES can identify the table to which a column belongs the column name is proceeded by a full-stop ".". Note that the join is actually established in the where clause. To cut down on the amount of typing required correlation names are used such as "b" and "e" in the above example.

Another example:

* SELECT b.Date, e.Name, p.date_issued
* FROM Breakfasts b, Employees e, parking_permits p
* WHERE b.ID_number = e.Emp_number AND e.Name = p.Name;

5.2 Nested queries

SQL queries can be nested to any level. For example:

* SELECT Name, Emp_number
* FROM employees
* WHERE Emp_number in
*   (SELECT ID_number
*   FROM breakfasts
*   WHERE date = '18-mar-1998' );

instead of the "in" predicate we can also use "exists" or "any-or-all".

5.3 Unions

The union operator provides a means to derive the union of several queries provide that the number of select fields are the same.

6. PURGING AND DESTROYING TABLES

You can "purge" all the data within a table using the delete statement:

* delete from Employee;
* \g

The statement removes all the rows from the named table. To remove a table altogether use the statement:

* drop Employees;
* \g

7. FINDING OUT WHAT TABLES ARE IN A DATABASE

There is a tendency to forget the names of the tables that you may have created in a database. The best way to discover what exists in a database is to use the ingmenu interface. In an "xterm" type:

ingmenu frans

return (press enter on the keypad). Select tables and you should see a display of the form: