HSQLDB example source code file (TestSelfNameResolution.txt)

This example HSQLDB source code file (TestSelfNameResolution.txt) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

cd, id, id, insert, integer, integer, into, key, key, not, not, null, values, varchar

The HSQLDB TestSelfNameResolution.txt source code

drop table t0 if exists;
create table t0(c varchar(20), i integer);
insert into t0 values ('first', 1);
insert into t0 values ('second', 2);
insert into t0 values ('third', 3);
select * from t0 order by c;
select t0.c, t0.i from t0 order by c, t0.i;
select c as cc from t0 order by cc;
select c as cc from t0 order by i;
select upper(c) as uc from t0 t order by uc;
select i, c from t0 order by upper(c);
/*c3*/select i, c as cc from t0 order by upper(cc);
/*e*/select distinct upper(c) as uc from t0 t order by i;
/*c3*/select c as ccol, sum(i) as sumi from t0 group by ccol;
/*c1*/select c as ccol, sum(i) as sumi from t0
 group by c
 having substring(upper(c) from 1 for 1) = 'S'
/*c2*/select c as ccol, sum(i) as sumi from t0
 group by c
 having avg(i) < 3
/*c1*/select c as ccol, sum(i) as sumi from t0
 group by c
 having substring(upper(c) from 1 for 1) = 'S'
 and avg(i) < 10 order by c desc
-- bug item #1167704
drop table test if exists;
create table test(id integer, cost float);
insert into test values(1,10);
insert into test values(2,20);
insert into test values (3,1);
insert into test values (4,2);
/*r
 3,kr 1.0
 4,kr 2.0
 1,kr 10.0
 2,kr 20.0
*/SELECT ID, 'kr ' + Cost FROM Test ORDER BY Cost;
/*r
 3,kr 1.0
 4,kr 2.0
 1,kr 10.0
 2,kr 20.0
*/SELECT ID, 'kr ' || Cost FROM Test ORDER BY Cost;

DROP TABLE "CD" IF EXISTS CASCADE;
CREATE CACHED TABLE "CD"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Title" VARCHAR(50));
CREATE CACHED TABLE "Artist"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Name" VARCHAR(50));
CREATE CACHED TABLE "Album"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Artist" INTEGER NOT NULL,"Name" VARCHAR(200),"Year" DATE,"CD" INTEGER NOT NULL,CONSTRAINT SYS_FK_42 FOREIGN KEY("CD") REFERENCES "CD"("ID"),CONSTRAINT SYS_FK_52 FOREIGN KEY("Artist") REFERENCES "Artist"("ID") ON DELETE CASCADE);
CREATE CACHED TABLE "Song"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Album" INTEGER NOT NULL,"Name" VARCHAR(300),"Track" INTEGER,"Duration" TIME,"Genre" VARCHAR(50),CONSTRAINT SYS_FK_55 FOREIGN KEY("Album") REFERENCES "Album"("ID") ON DELETE CASCADE);
INSERT INTO "CD" VALUES(21,'24');
INSERT INTO "Artist" VALUES(123,'Annie Lennox');
INSERT INTO "Album" VALUES(172,123,'Medusa',NULL,21);
INSERT INTO "Song" VALUES(2669,172,'waiting in vain',9,'00:05:39',NULL);

-- issue with aliases - bug 1344316
create table test1 (idtest int,test varchar(100),primary key(idtest));
create table test2 (idtest2 int,idtest int,test varchar(100),primary key(idtest2),foreign key (idtest) REFERENCES test1(idtest));
INSERT INTO test1 (idtest, test) VALUES (1,'hello');
INSERT INTO test2 (idtest2,idtest, test) VALUES(2,1,'world');
select t1.test, t2.idtest2 as idtest  from test1 t1 join test2 t2 on (t1.idtest=t2.idtest)
select t1.test, t1.test as idtest  from test1 t1 JOIN test2 t2 on (t1.idtest=t2.idtest)

-----
drop table tablea if exists;
drop table tableb if exists;
create table tablea (cola int, colb int, colc int);
create table tableb (cola int, colb int, colc int);
SELECT T1.colA, T1.colB FROM tableA T1 WHERE T1.colC = (
 SELECT MAX(T2.colC) FROM tableA T2 WHERE T1.colB = T2.colB);
SELECT T1.colA, T1.colB FROM tableA T1 WHERE T1.colC IN (
 SELECT MAX(T2.colC) FROM tableA T2 WHERE T1.colB = T2.colB);