updated 1/16

Ingres SQL:	How to get started		

*******************************************************************

There's an Ingres manual in the uAPE lab, and you should also
be able to check one out from the Accounts office (1st floor AP&M,
where you get the OCE accounts from).

For the INGRES SQL assignment, you'll need to turn 
in a script of steps (c)-(f) of your session 
This can be done using the UNIX 'script' command 
(run 'man script' for details).  

If you use SQL script files (see below), you'll need to turn in
your script (you could use 'cat' while running 'script' to display
your script file) as well.  When you execute your SQL script using
\i, it will only display the output and _not_ the commands, so please
label which output corresponds with which command.


Good luck!  


*******************************************************************

Before using INGRES 
type the following command at the UNIX promt:

prep cs232f

Next, from within UNIX shell, you can use the following SQL commands:

createdb <dbname>	- create a database
	Recommended:  Use your account name (eg, cs232wxx) as the database
	name, since everyone's db's will be stored in the same directory
	using the same namespace.

destroydb <dbname>	- destroy a database

sql <dbname>		- access a database
	This will start up the Ingres Terminal Monitor.  From within this
	interactive program, you can perform SQL commands.


*******************************************************************

INGRES Terminal Monitor commands:

Some terminal monitor commands are preceeded with the backslash ('\')
character.  You can type just the first character instead of the whole
command name (eg, \q instead of \quit).

\quit		- Exit the terminal monitor

\go		- Execute the previously entered text as an SQL command.
		  Ingres ignores extra whitespace, including carriage 
		  returns. You must separate commands in the text by ";".
                  The \g must be added to the end of an SQL command
                  (or a sequence of commands separated by ";") to execute
                  the commands. If this is not done, nothing happens. 
			
		  Example:
			select *
			from movie \g


                  Example:
                        insert into young
                        select distinct * from employee
                        where age < 35;
                        select * from young \g

                        (The first command copies into table young
                        all rows from employee whose age is less than 35;
                        the second command displays young after the insert.)
                       

\e <filename>	- Edit an SQL script file using vi.  

\i <filename>	- Execute an SQL script file.  Add all your SQL commands
		  to a file using your favorite text editor, and then use
		  this command to run the script.  This beats retyping
		  those SQL commands over and over again.  Don't forget
		  to add \g's to the end of each command!


*******************************************************************

SQL:

To get the list of tables in the database:

help \g

To get the attributes and types for a particular table:

help <table-name> \g

To create a new table (example):

create table employee (name  char(16), department  char(16), age  int) \g 

To drop a table:

drop <table-name> \g

To insert a single row into a table (example):

insert into employee values ('Joe', 'parts', 25) \g

To delete rows:

delete from employee
where age < 35 \g

Tables are persistent.  When you quit SQL, all table creations/drops
and changes to the tables will be committed/saved.


To view the contents of a table:
	select * from <tablename> \g

To eliminate duplicate rows, use 'distinct':
	Example:

	select distinct actor
	from MOVIE \g

Character strings should be single-quoted (').

All the SQL you'll need for the assignment should be in the class
transparencies.  However, the actual syntax for some of the commands
is a little different in this version of INGRES:

Creating a table:
	Declare integer attributes with just their names (instead of
	their names and size), as shown above.

	Char string sizes need to be parenthesized.

	Example:

		create table R ( A int,
				 B int,
				 C char(16) ) \g

Removing rows from a table:
	delete from <tablename>
	[where search_condition]  <-- deletes all rows w/o this

	Example:

	Remove all movies directed by Bertolucci.

	delete from MOVIE
	where director = 'Berto' \g




Using aliases:
For multiple aliases for the same table in the 'from' clause of an SQL 
query, you need to put the table name for each alias.

Example:

This example from the transparency won't work in Ingres:

select t.actor
from movie t s			<- Error
where t.actor = s.director

Ingres will accept this:

select t.actor
from movie t, movie s		<- ok
where t.actor = s.director