Datenbank HSQLDB

HSQLDB (Hyper Structured Query Language Database) ist eine freie, vollständig in Java programmierte relationale SQL-Datenbank (RDBMS) und steht unter einer freien Lizenz nach dem Muster der BSD-Lizenz.

Create Table


DROP TABLE lotto IF EXISTS CASCADE;
CREATE TABLE lotto ( 
  ID    INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL, 
  Tag   INTEGER NOT NULL, 
  Monat INTEGER NOT NULL, 
  Jahr  INTEGER NOT NULL, 
  Zahl1 INTEGER NOT NULL, 
  Zahl2 INTEGER NOT NULL, 
  Zahl3 INTEGER NOT NULL, 
  Zahl4 INTEGER NOT NULL, 
  Zahl5 INTEGER NOT NULL, 
  Zahl6 INTEGER NOT NULL, 
  Superzahl  INTEGER, 
  ErstelltAm TIMESTAMP NOT NULL,
CONSTRAINT lotto_PK PRIMARY KEY (ID));
	

Create Trigger


DROP TRIGGER lotto_tr_count IF EXISTS;
CREATE TRIGGER lotto_tr_count AFTER INSERT ON lotto
     REFERENCING NEW AS NEWROW FOR EACH ROW 
     BEGIN ATOMIC 
		-- do whatever you want
     END;
	

TO_DATE, TO_CHAR, Dummy Table


SELECT TO_DATE( '2-1-2008', 'DD-MM-YYYY' ) as WochenTag 
FROM INFORMATION_SCHEMA.SYSTEM_USERS;

WOCHENTAG
---------------------
2008-01-02 00:00:00.0


SELECT 
  TO_CHAR(TO_DATE(L.TAG || '-' || L.MONAT || '-' ||  L.JAHR, 'DD-MM-YYYY' ), 'DD.MM.YYYY') as Datum
FROM "PUBLIC"."LOTTO_AB_02_12_2000" L 
order by TO_DATE(TAG || '-' || MONAT || '-' ||  JAHR, 'DD-MM-YYYY' ) desc LIMIT 1 OFFSET 1; 

DATUM
----------
12.06.2010
	

EXTRACT, DAY_OF_WEEK, CAST, LIMIT, OFFSET


SELECT 
  case when EXTRACT(DAY_OF_WEEK from CAST(L.JAHR || '-' || L.MONAT || '-' || L.TAG AS DATE)) = 4
  	then 'MI' else 'SA' 
  end as Wochentag
FROM "PUBLIC"."LOTTO_AB_02_12_2000" L 
order by TO_DATE(TAG || '-' || MONAT || '-' ||  JAHR, 'DD-MM-YYYY' ) desc LIMIT 1 OFFSET 0;

WOCHENTAG
---------
SA
	

GROUP_CONCAT, UNION ALL


SELECT 
  'Max' as Vorname
, 'Mustermann' as Nachname
FROM INFORMATION_SCHEMA.SYSTEM_USERS
UNION ALL
SELECT 
  'Maxi' as Vorname
, 'Mustermann' as Nachname
FROM INFORMATION_SCHEMA.SYSTEM_USERS;	
	
VORNAME NACHNAME
------- ----------
Max     Mustermann
Maxi    Mustermann
 
 	
	
SELECT 
  GROUP_CONCAT(zahl) as result 
FROM (
  select '1' as zahl from INFORMATION_SCHEMA.SYSTEM_USERS 
  UNION ALL
  select '2' as zahl from INFORMATION_SCHEMA.SYSTEM_USERS 
);
	
RESULT
------
1,2
	

ARRAY


SELECT 
ARRAY[1, 2, 3, 4, 5, 6] as My_Array
FROM INFORMATION_SCHEMA.SYSTEM_USERS;

MYARRAY
------------------
ARRAY[1,2,3,4,5,6]



SELECT 
CONCAT(arr[1], ' ', arr[2], ' ',arr[3], ' ', arr[4], ' ', arr[5], ' ', arr[6]) as blah 
FROM (
  select 
    SORT_ARRAY(ARRAY[14,20,37,41,44,47]) as arr 
  from INFORMATION_SCHEMA.SYSTEM_USERS
);

BLAH
-----------------
14 20 37 41 44 47
	

CONCAT, ARRAY, SORT_ARRAY, LIMIT, OFFSET


SELECT 
  (select CONCAT(
	  case when zahlen[1] < 9 then ' ' || zahlen[1] else '' || zahlen[1] end, ' ', 
	  case when zahlen[2] < 9 then ' ' || zahlen[2] else '' || zahlen[2] end, ' ', 
	  case when zahlen[3] < 9 then ' ' || zahlen[3] else '' || zahlen[3] end, ' ', 
	  case when zahlen[4] < 9 then ' ' || zahlen[4] else '' || zahlen[4] end, ' ', 
	  case when zahlen[5] < 9 then ' ' || zahlen[5] else '' || zahlen[5] end, ' ', 
	  case when zahlen[6] < 9 then ' ' || zahlen[6] else '' || zahlen[6] end
	  ) 
    from
		(select SORT_ARRAY(ARRAY[Zahl1, Zahl2, Zahl3, Zahl4, Zahl5, Zahl6]) as zahlen 
		 from "PUBLIC"."LOTTO_AB_02_12_2000" 
		 where ID = L.ID
		 order by TO_DATE(TAG || '-' || MONAT || '-' ||  JAHR, 'DD-MM-YYYY' ) ) 
	)
  as Zahlen  
FROM "PUBLIC"."LOTTO_AB_02_12_2000" L 
order by TO_DATE(TAG || '-' || MONAT || '-' ||  JAHR, 'DD-MM-YYYY' ) 
desc LIMIT 1 OFFSET 0;

ZAHLEN
-----------------
 6 16 21 32 35 38
	

Top


Example
Sitemap Kontakt Impressum