Thursday, May 6, 2010

Self training in SQL set operations

create table if not exists T0(col1 text,col2 text);
create table if not exists T1(col1 text,col2 text);
create table if not exists T2(col1 text,col2 text);
create table if not exists T3(col1 text,col2 text);
delete from T0;
delete from T1;
delete from T2;
delete from T3;
insert into T0 values ('C11','C21');
insert into T0 values ('C11','C22');
insert into T0 values ('C12','C23');
insert into T0 values ('C12','C24');
insert into T0 values ('C11','C21');
insert into T0 values ('C11','C22');
insert into T0 values ('C12','C23');
insert into T0 values ('C12','C24');
insert into T0 values ('C12','C24');
insert into T0 values ('C12','C24');

select '--0-----';select * from T0;

insert into T1 select distinct col1,col2 from T0;
select '--1-----';select * from T1;


insert into T2 select * from (
select T0.col1, A1.col2 from T0,T0 as A1
where T0.col1 != A1.col1 and T0.col2 != A1.col2
);

select '--2-----';select count() from T2;select * from T2;

insert into T3 select * from (
select T1.col1,T1.col2 from T1,T2 as T2
where T1.col2 != T2.col2 and T1.col1 != T2.col2
) group by col1;
select '--3-----';select * from T3;

MyDatabase of Economic Info:

In my database I have a start sql routine called CreateAll which makes a database of all the .csv format spreadsheet you give it. It only matters that some csv files are data, and others are lists of text strings taken somewhere from the sources data descriptors. And it matters that data files have each series of data tagged with some key text code. At this point, the data base can take search words and produce plots, descriptors, or data pointer is, when used in combination with the stat package.

References:
R Project - Statistical analysis and plotting
SQLite the little engine that could
SQLIteSudio - Buggy, but still my favorite tool
DOS Box - May Gates never kill it.

No comments: