*************************************************************************
                Some more SQL examples
--------------------------------------------------------------------------
1) Granting permission to query, update or modify your tables.
   Suppose you wish to grant me (username a343meee) permission
   to issue select and update commands on the data in your table 
   people which resides in your default schema. 

            grant select, update on table people to a343meee

Then, when I run my SQL queries on your table, I have to distinguish it
by your user id. If your user name is a343foo, then I can issue the
following select statement:

            select * from a343foo.people

which will display all the tuples in your people table.
However, if the table resides in a schema called csc343h then both you
and I have to use this schema name in our commands.

you:
            grant select, update on table csc343h.people to a343meee
me:
            select * from csc343h.people

To grant all privileges (alter, delete, insert, select, update, etc) on
table people to all members of a Unix group named c343h01, we type

	GRANT ALL ON TABLE people to c343h01

Members of your 343 group will have all privileges on your group
database.  Hence, you will not need to use these commands unless you
alter your groups.  If a member leaves your group, use the revoke
command to revoke access to your tables.

Note: if a table is to be used in a view definition, the above command may 
      fail to grant permissions to each member of the group. In such a case 
      grant permissions to each user explicitly. 

2) Let us see a more realistic  example of defining a table with more data
types in addition to constraints and primary keys.

   create table table1 \
     (name         char(20) not null, \
      profession   varchar(30), \
      salary       decimal(10,2), \
      birthdate    date,
      primary key(name))

Here the name field is defined to be the primary key. Notice that it must
be defined as not null. We can now insert the following tuple.

   insert into table1 values ('Joe Doe', 'used car salesman', 1200.56, \
                              '1943-12-25')

Note the format of the date is yyyy-mm-dd. In addition, DB2 allows to
parse the date attribute using its month(), year() and day() functions.
Suppose we wish to display everyone in table1 whose birth day falls
on January, 1. This can be done as follows:

   select * from table1 where month(birthdate) = 1 and day(birthdate) = 1


3)Inserting into a table from another table:
Suppose we want to create a table named overachievers which contains all
the tuples from table1 whose profession is 'used car salesman'. Assuming
that the table overachievers is already defined, then, we use the fullselect
syntax as follows:

    insert into overachievers \
       select * from table1 \
       where profession = 'used car salesman'


Finally, here is a sample db2 session  that creates two tables dept and
emp, and sets a referential constraint between them so that when
the referencing dept tuple is deleted, the referenced emp has its deptno
field set null.

db2 => create table dept( dept_no integer not null, name char(20), \
db2 (cont.) => primary key(dept_no))
DB20000I  The SQL command completed successfully.

db2 => create table emp( empno integer not null, name char(20), \
db2 (cont.) => deptno integer, \
db2 (cont.) => foreign key(deptno) references dept on delete set null)
DB20000I  The SQL command completed successfully.

db2 => -- other choices for "on delete ..."
db2 => -- 	on delete NO ACTION
db2 => -- 	on delete RESTRICT
db2 => -- 	on delete CASCADE

db2 => insert into dept values(1, 'research')
DB20000I  The SQL command completed successfully.

db2 => insert into dept values(2, 'development')
DB20000I  The SQL command completed successfully.

db2 => insert into dept values(3,'admin')
DB20000I  The SQL command completed successfully.

db2 => insert into emp values(1,'adel', 1)
DB20000I  The SQL command completed successfully.

db2 =>  insert into emp values(2, 'jim', 2)
DB20000I  The SQL command completed successfully.

db2 =>  insert into emp values(3,'bill', 3)
DB20000I  The SQL command completed successfully.

db2 => select * from emp

EMPNO       NAME                 DEPTNO
----------- -------------------- -----------
   1        adel                  1
   2        jim                   2
   3        bill                  3

  3 record(s) selected.

db2 => select * from dept

DEPT        NAME
----------- --------------------
  1         research
  2         development
  3         admin

  3 record(s) selected.

db2 => delete from dept where name = 'research'
DB20000I  The SQL command completed successfully.

db2 => select * from dept

DEPT        NAME
----------- --------------------
  2         development
  3         admin

  2 record(s) selected.

db2 => select * from emp

EMPNO       NAME                 DEPTNO
----------- -------------------- -----------
  1         adel                  -
  2         jim                   2
  3         bill                  3

  3 record(s) selected.

db2 =>

Notice the value of DEPTNO for employee 1 is set to null (-) as dictated
by the constraint.