Ingres SQL Training Notes

From PTAGISWiki

Trainer: Mary Schulte

• other students from http://www.pagibigfund.gov.ph/
• search Ingres docs with this url:
• http://www.google.com/search?q=site%3Adocs.ingres.com
• http://mycroft.mozdev.org/search-engines.html?name=ingres (broken)

Contents 1 Pre-class Questions 2 Product overview 3 SQL select 4 Data formats 4.1 Dates 4.2 Money 4.3 BLOBS 4.4 Character 4.5 other functions 5 SQL where 6 SQL join 6.1 join syntax (not in where clause) 7 set functions 8 subqueries 9 Advanced table joins 10 Views 11 Procedures 12 Rules

Pre-class Questions

• How do I build a query that returns the number of interrogations per month for a given site? Do I have to calculate the date boundaries by hand?
• Similarly, how do I summarize the number of fish seen at a site with totals by year?

select distict tag_id, first_obs_date from obs_site where obs_site = 'JDJ' order by first_obs_date

• How do I read a query execution plan?
• why doesn't this work?

select tag_id, first_obs_date, date_part('year',first_obs_date) as year  
from obs_site 
where obs_site = 'JDJ'
group by year

Answer (this works):

select date_part('year',first_obs_date) as year, count(*) as events
from obs_site
where obs_site = 'JDJ'
group by date_part('year',first_obs_date);

select date_part('month',first_obs_date) as month, count(*) as events
from obs_site
where obs_site = 'JDJ' and first_obs_date between '01-jan-2006' and '30-dec-2006'
group by date_part('month',first_obs_date);

• what are the considerations for making a composite key index vs several indexes?
• how and when should I make a star schema to optimize reporting?

Product overview

• text tools
• graphical tools
• open road
  • open road can do web services
• contributed tool on the wiki (an open road app) that is a gui admin tool
• squirrel is a JDBC browser that works with Ingres

SQL select

• does a field need to be in the select list if it is in the order list? No.

select distinct name from department; commit;

• can use distinct with pairs of columns

select distinct name, region from dept;

• just the first x:

select first 8 name, region from department;

Data formats

Dates

• date format in select is dependent on II_DATE_FORMAT environment variable
• two date formats: ingresdate and ansidate
• ingresdate is the historical ingres format
• ansidate is the ansi standard
• date defaults to ingresdate
• there is a configuration parameter to determine whether ansi or ingres date will be default
• II_DATE_FORMAT only applies to ingresdate
• ingresdate includes fractions of a second and ansidate does not
• default for II_DATE_FORMAT is MULTINATIONAL4 which allows these formats:

dd-mmm-yyyy
mm-dd-yyyy
yyyy.mm.dd
yyyy_mm_dd
mmddyy
mm-dd
mm/yy
dd/mm/yy

• display II_DATE_FORMAT with ingprenv

• date functions and date math, results in days

select lastname, '1-jan-2003' - hiredate from employee;

• interval function, results in months (because of parameter)

select lastname, interval('months', '1-jan-2003' - hiredate);

• date('today')
• date('now') includes time

Money

BLOBS

Character

• lowercase()
• concat()
• trim()

other functions

• information about your session

select dbmsinfo('username');
select dbmsinfo('database');
select dbmsinfo('_version');

• case statement

SQL where

• columns in where do not have to be in select
• date functions

select * from blah where hiredate > '01-jan-2002';

• between is inclusive

select * from blah where salary between 29000 and 30000;

•  % means 0 or more of any character
• _ means 1 of any character

select * from blah where street like '\[0-9\]%' escape '\';

SQL join

• inner join (simplest?)
• join a table to itself
• can be specified in the where clause

select * from building b, dept d where b.building_id = d.building;

join syntax (not in where clause)

• uses keywords join and on

select * from building b join department d on b.building_id = d.building

• still an inner join

set functions

• count
• sum
• avg
• max
• min
• group by, having
• having = where clause for groups

subqueries

• exists
• correlated subqueries

Advanced table joins

• an inner join only includes rows when there is a match on both sides
• an outer join includes non-matching rows from both sides
• union to merge data sets

select * from orders union select * from orderhist;

• 'union all' is less expensive than a 'union' statement because 'union' has the step of removing dupes
• outer join can be done with 'union' or 'join' syntax, join is easier
• left and right outer joins are ways to describe which non-matching lines will be in result set
• full description of left and right outer joins are in 10-11 through 10-14 of class book
• full join is left and right outer join
• constraints in the 'on' clause are evaluated before the join
• constraints in the 'where' clause are evaluated after the join

Views

• in-line views = select in the from clause
• different than correlated subquery which is select in the where clause

Procedures

• how to view all procedures:

copydb -c with_proc ptagis3 (as user pittag)
vi copy.out

• running copydb as user ingres doesn't show the procedures owned by pittag
• unloaddb will dump all procedures owned by anyone

Rules

• for auditing
• enforcing business rules

create rule new_emp after insert into employee execute procedure new_emp_proc; commit;