Creating a standard set of tables and data

Objectives:

1. To create table definitions from an export file.
2. To populate tables from an export file.

The exercises in this trainer use a specific set of data. It is important that the table definitions you are working with match exactly those used. This means we need to create a set of standard tables and populate them with stadard data.

Creating and populating table definitions from an export file.

1. If you do not have an INGRES/MENU session running, start one now.
2. Using the cursor keys, scroll down to the Queries option and use Select(f8) to select that option.
3. Select(f8) (using the cursor keys) the SQL option on the Queries menu to start the ISQL environment.
   This will have started the isql application in which you can type sql commands and run them interactively. We have already come across this environment in the previous exercise so you may well recognise it.
4. You may remember that the first thing you need to do when you enter the isql environment is to type the following commands:

   commit;
   set autocommit on;
   

   Remember that the semi-colon is used to show the end of an SQL command. Pressing the return key does not cause the commands to be executed.

   Even if you have just run these commands you should re-enter them and run them again. It's a very good habit to get into.

5. When you have typed these commands you need to run them. To do this press the Go(f8) key.

   Ingres will show you the result of running the commands. You then need to press End(f3) to return to the ISQL screen.

   Remember, you should enter these two commands every time you begin an 'isql' session. If you do not, the database will lock up.

6. Now go to this page then use the File menu Save As... option to save the contents of the page in a file in your home directory called makelib.sql
7. Back in Ingres, use F1 to go to the Ingres command line and invoke the File, ReadFile option. Enter the filename:
   makelib.sql
8. Use F1 to go to the Ingres command line and invoke the OnError option. Use the cursor keys to highlight the Continue option then accept this option with the Go f(8) key.
   This tells Ingres to ignore errors in one statement and continue processing the next. In the case of this example the only errors will be that the DROP TABLE statements will not work if the tables don't exist. Don't worry too much about this.
9. Use (F1 followed by) the Complete option to run all the commands in the buffer.
   If you use Go, Ingres will only partially run the file. Complete is the best command in this case.
10. When the commands have executed invoke the File option to save the output in a file called 'output.sql'.
11. You can then look at the output to see what has been done either by editing output.sql outside of Ingres, or by scrolling down the output as shown in the Ingres environment. On screen you are looking at the end of the output so you need to begin by scrolling up.
    Remember: Ingres is running inside a window but it is NOT a windowed application. You CANNOT use the mouse or the scroll bars. Use MUST use the keyboard keys to scroll down the Ingres screen.
12. Use:
    • End(f3) to back out of the output,
    • Quit(f4) to back out of the SQL environment, and
    • Cancel(f6) to back out of the Queries menu.
    This leaves you back at the top level Ingres menu.
    Q: Do you think the Ingres interface is consistent?

Ingres has now created and populated a standard set of tables. Will we now use these tables to work with SQL Data Manipulation Language (DML) commands.

Continue with trainer.