|
HSQLDB example source code file (TestSelfNameResolution.txt)
The HSQLDB TestSelfNameResolution.txt source codedrop 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); Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfNameResolution.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.