<plaintext>

Review
Questions

Mygoals
	Union
	RI
	"manual" pivot
	many-to-many problem/solutions
	Views
	Indexes
	Performance
	more meta (tab_comments col_comments)
	Security
	Glossary
		Schema
		RI (PK/FK)
		"Normal" forms

	Filter aggregates / Having
	Compound expressions - operator precedence - OR/AND
	Self-join
	stable inserts
	
Activities
	Create views (queries)
	Create queries utilizing queries.
	Mine ORACLE metadata (useful web sites?)
	Work backwards from 
		SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;
		to query.
	Largest order for each customer

	
URLs
	http://www.helptalk.net/forums/access/index.shtml
	http://www.mvps.org/access/
	http://faqchest.dynhost.com/prgm/php3-l/php-00/php-0008/php-000873/php00081816_23406.html

Notes:
	autonumber - http://www.mvps.org/access/general/gen0025.htm

	http://www.mvps.org/access/

SQL> desc emp
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 SCOTT            3000
        20 FORD             3000
        30 BLAKE            2850

SQL> l

SUBQUERY in WHERE

select deptno, ename, sal from emp a
where a.sal =
(select max(b.sal) from emp b
where b.deptno = a.deptno)
order by 1;

select count(*) from emp;

select a.empno, a.ename as employee, b.ename as supervisor
from emp a, emp b
where b.empno = a.mgr;

select count(*) from emp where mgr is null;

select a.empno, a.ename as employee, b.ename as supervisor
from emp a, emp b
where b.empno (+) = a.mgr;

select a.empno, a.ename as employee, nvl(b.ename,'No supr') as supervisor
from emp a, emp b
where b.empno (+) = a.mgr;
                                                                 
select a.empno, a.ename as employee, b.ename as supervisor
from emp a, emp b
where b.empno = a.mgr
UNION
select a.empno, a.ename as employee, 'No Supr' as supervisor
from emp a 
where a.mgr is null;

select a.empno, a.ename as employee, b.ename as supervisor
from emp a, emp b
where b.empno = a.mgr
UNION
select a.empno, a.ename as employee, 'No Supr' as supervisor
from emp a 
where a.mgr is null
order by 2;

comment on table emp is 'Basic employee data.';

comment on column emp.ename is 'Employee last name in uppercase.';

select table_name, comments from all_tab_comments
where table_name = 'EMP';

select table_name, comments from all_col_comments
where table_name = 'EMP'
  and column_name = 'ENAME';

select sum(sal) from emp
group by deptno;

select sum(sal) from emp
group by deptno
having sum(sal) > 3000;

update emp set mgr = 7566 where empno = 7788;     

	select nvl(deptno,999) as dept, count(*)
	from emp
	group by nvl(deptno,999);

	select decode(deptno,10,1*sal,0),
       		decode(deptno,20,1*sal,0),
       		decode(deptno,30,1*sal,0)
	from emp;

	select decode(deptno,10,1*sal,0) as dept10,
       		decode(deptno,20,1*sal,0) as dept20,
       		decode(deptno,30,1*sal,0) as dept30
	from emp;

	select sum(decode(deptno,10,1*sal,0)) as dept10,
       		sum(decode(deptno,20,1*sal,0)) as dept20,
       		sum(decode(deptno,30,1*sal,0)) as dept30
	from emp;