db2 More SQL hints

luzl

More SQL: temp.tables, where, order by, aliases, subqueries, column & scalar functions, group by, having, union, except, intersect

create table tab1 ( ... )  in userspace1;
alter table tab1 add primary key (...)
drop table tab1


declare global temporary table temp1
like tab1
on commit preserve rows
not logged
in mytempspace

where clause:
x = y, x <> y, x < y, x > y, x <= y, x >= y,
IS NULL, IS NOT NULL
IN, NOT IN
BETWEEN, NOT BETWEEN
LIKE, NOT LIKE
EXISTS, NOT EXISTS

select name, salary from stuff where salary > 20000

... where years in (1,2,3)
... where years not between 3 and 8
... where name like 'S_____'
... where name like 'S%'
... order by salary desc

Aliasses can be used in the "order by":
Bad news is that the name aliases for fields (simple or calculated) can not be used in the "where" clause, because the "where" clause is processed before the alias name is applied to the result.  So you may need to repeat the whole expression in the where clause.
But good news is that the name aliases can be used in the "order by" clause.

select  value(x.param1, x.param2) myparam
from test1 x
where (x.param1, x.param2) >= 40
order by myparam;

Subquery:
select lname
from employee
where lname in (select sperson from sales where sales_date < '01/01/1996')

Column Functions:
SUM, AVG, MIN, MAX, COUNT
note: don't try to specify column functions in a where clause - they will NOT work, because where clause is evaluated before the select clause.

select name, salary
from employee
where salary > (select avg(salary) from employee)
order by salary desc

Scalar Functions:
ABS, HEX, LENGTH, YEAR, MONTH, DAY, LCASE or LOWER, UCASE or UPPER

select min(length(deptname) as min, max(length(deptname)) as max from dept

group by  &  having:
select sales_date, max(sales) as max_sales
from sales
group by sales_date where max(sales) > 25

union, except, intersect:
select sales_person from sales where region = 'reg1'
union
select sales_person from sales where sales > 3

note: union processes both queries eliminates duplicates between them, and returns the final combined result set

select sales_person from sales where region = 'reg1'
except
select sales_person from sales where sales > 3

select sales_person from sales where region = 'reg1'
intersect
select sales_person from sales where sales > 3

insert, update, delete:
insert into tab1 values (123,'something',....)
insert into tab1 select ... from ... where ..

update tab1 set (a,b) = (123,'something') where ...

delete from tab1 where ...