Suppose I have following table - CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER); I want to aggregate the data by calculating average of every 10 points in the table. i.e. If table has 20 data points the result is two aggregate points. 1st aggregate point the average of 1-10 data points, and 2nd of 11-20. Is this possible using a SQL query?

I have a table which I use to determine actions based on conditions provided. This is what it looks like right now. IPAddress, MACAddress, Hostname, ScriptName, Argument1, Argument2, Argument3 10.2.1.* , .* , .* , diagnostics.sh, -runAll, , In this table, the first three columns are the conditions, and the last four columns are the actions. Values of the condition cells are regex values that I do REGEX_LIKE on, so when I am looking up the correct script to run on a device, I look at it's IP (or MAC I'm given one or the other, not both) and hostname and if the regex matches, then I get the script name that needs to be run and arguments for that script. Some scripts have one argument, some have two and others have three. How do I modify this so I can an arbitrary list of conditions and actions. e.g. I am given a Key, Value map (attribute name, attribute value) for conditions, and once a rule has been satisfied, I retrieve a list of action Key, Value elements that are relevant for that device. To keep things simple, there won't be any updates done to this database once it is initialized. It's initialized on start up by reading rules from a flat file, and the used at runtime to determine rules that have been satisfied for provided input. This is the schema I think is better suited for this, but I don't know if this is correct and I don't know how to get the matching rules either. Table Name: Column List Attributes: attributeId, name, value Conditions: conditionId, ruleId, attributeId Actions : actionId, ruleId, attributeId Rules : ruleId

I have some flat files with the following columns; 3 integers, 3 reals, and 1 varchar(20). For querying I need an index that contains both 1 of the integer columns and the varchar column. Each file is around 1.8GB in size with around 38 million rows. Currently I am using a HSQL(Standalone) database to load a file for processing; one database per file. It is very slow to load (120+ min) the file and results in a 4.7GB database file when the database is created with the following options. "Properties" -> { "check_props" -> "true", "shutdown" -> "true", "hsqldb.default_table_type" -> "cached", "sql.syntax_mss" -> "true", "hsqldb.log_data" -> "false", "hsqldb.inc_backup" -> "false" } The file read in batches of 100k records. The read is very fast (almost instant) so I do not think it is the read that is slowing things down. It also takes a very long time to close the connection to the database. I have the option to use Derby, H2, or SQLite. Will any of these result in faster load time and/or smaller database file size in this scenario? If so what are the connection string options that should be used to achieve this? Alternatively, are there different connection string options I can use with HSQL(Standalone) that will reduce the load time and/or database file size? Driver information added. JDBCDriver[ "Name" -> "HSQL(Standalone)", "Driver" -> "org.hsqldb.jdbcDriver", "Protocol" -> "jdbc:hsqldb:file:", "Version" -> 3.1, "Description" -> "HSQL Database Engine (In-Process Mode) - Version 2.3.3 - This ...", "Location" -> "C:\... "] Driver information for the other options available to me. Derby JDBCDriver[ "Name" -> "Derby(Embedded)", "Driver" -> "org.apache.derby.jdbc.EmbeddedDriver", "Protocol" -> "jdbc:derby:", "Version" -> 3.1, "Description" -> "Derby Database Engine (Embedded Mode) - Version 10.12.1.1 - This...", "Location" -> "C:\... "] H2 JDBCDriver[ "Name" -> "H2(Embedded)", "Driver" -> "org.h2.Driver", "Protocol" -> "jdbc:h2:", "Version" -> 3.1, "Description" -> "H2 Database Engine (Embedded Mode) - Version 1.3.176 - This...", "Location" -> "C:\... "] SQLite JDBCDriver[ "Name" -> "SQLite", "Driver" -> "org.sqlite.JDBC", "Protocol" -> "jdbc:sqlite:", "Version" -> 3.1, "Description" -> "SQLite using Zentus-derived JDBC Driver - Version 3.8.11.2", "Location" -> "C:\..."] Additional variants include the below. However, I need it all to run on the client's computer. I believe this excludes server and webserver modes. {"Derby(Embedded)", "Derby(Server)", "H2(Embedded)", "H2(Memory)", "H2(Server)", "HSQL(Memory)", "HSQL(Server)", "HSQL(Standalone)", "SQLite", "SQLite(Memory)"}

Does the H2 Database support pessimistic locking? If so, how does its use interact with H2’s MVCC implementation? I'm not looking for advice on whether or not to use pessimistic locking. I have a specific need to emulate the behavior of another database that defaults to row-level pessimistic locking. I need to be able to test if a row is locked, and if not locked, lock it out from writes by any other database connection.

I decided to stop using H2 for obvious reasons (I have Oracle on production, compatibility mode is a fake). So, I wrote simple test framework which for each test in my application does the following: 1. Generate random username (in example below it's test_user). 2. Create new user and tablespace: create tablespace test_user_ts datafile 'test_user_tabspace.dat' size 10M reuse autoextend on next 500K; create temporary tablespace test_user_ts_tmp tempfile 'test_user.tabspace_temp.dat' size 10M reuse autoextend on next 500K; create user test_user identified by test_password default tablespace test_user_ts temporary tablespace test_user_ts_tmp; grant create session to test_user; grant all privileges to test_user; 4. Populate database with test data. 5. Run test. 6. Clean up: drop user test_user cascade; drop tablespace test_user_ts_tmp; drop tablespace test_user_ts; **The problem is that stages 1-3 are slow. How can I make them as fast as possible?** Is there maybe any way of _copying_ existing db schema to another one? Db version: Oracle 11g I have full control over Oracle instance. It runs on a vagrant image on my dev machine.

I'm got an [adjacency list](https://stackoverflow.com/q/4048151/14731) consisting of two tables: CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY); CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id SMALLINT NOT NULL, UNIQUE KEY (parent_id, child_id), FOREIGN KEY (parent_id) REFERENCES permission(id) ON DELETE CASCADE, FOREIGN KEY (child_id) REFERENCES permission(id) ON DELETE CASCADE); When I run the following [CTE](http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression) I get an empty set: WITH RECURSIVE cte (parent_id, child_id) AS ( ( SELECT anchor.parent_id, anchor.child_id FROM permission_graph anchor WHERE anchor.child_id = -32763 ) UNION ALL ( SELECT recursive.parent_id, recursive.child_id FROM cte, permission_graph recursive WHERE recursive.child_id = cte.child_id ) ) SELECT cte.parent_id, cte.child_id FROM cte But if I run the anchor condition: SELECT anchor.parent_id, anchor.child_id FROM permission_graph anchor WHERE anchor.child_id = -32763 I get: [parent_id = -32767, child_id = -32763] [parent_id = -32768, child_id = -32763] **Why is the CTE returning an empty set when the anchor result is non-empty?** Shouldn't the CTE result contain the anchor result?

## Problem ## I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS. ---------- ## SQL Query ## SELECT * FROM tSysNls WHERE nlsGuid IN ( SELECT nlsGuid = CASE WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid END FROM tSysNLS en LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute AND en.nlsClazz=de.nlsClazz AND de.nlsLocale= 'de' LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute AND de.nlsClazz=de_AT.nlsClazz AND de_AT.nlsLocale= 'de_AT' WHERE en.nlsLocale= 'en' AND en.nlsClazz= 'Contact' AND en.nlsAttribute= 'firstName') ---------- ## Table ## CREATE TABLE tsysNLS ( nlsGuid nvarchar(207) NOT NULL, nlsLocale nvarchar(5) NOT NULL, nlsClazz nvarchar(100) NOT NULL, nlsAttribute nvarchar(100) NOT NULL, nlsDisplayName nvarchar(255) NOT NULL, nlsOldname nvarchar(50), nlsDescription nvarchar(255), nlsShapefilename nvarchar(10) ); ---------- ## Example data ## INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename) VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL); INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename) VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor'); INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename) VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');

I am using an H2 database with JDBC to create a connection with setAutoCommit(false). I am currently programatically checking whether or not there is an uncommitted transaction, but I was wondering if there was a way to directly query H2 for information about this state. I have found some information for other databases on this topic, but nothing so far on H2.

What is the difference between these joins? 1. a left join b left join c 2. (a left join b) left join c 3. a left join (b left join c) Does the grouping only affect the order of the joins? Will the query engine decompose #1 into one of #2 or #3, i.e. pick an order for doing the joins? I'm asking because h2 uses indexes on c for #1, but for #2 does a table scan of c. Here b is a simple many-to-many join table with columns (a_id, c_id).

I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition. However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the NOT NULL columns and I don't seem to find anything similar to check the constraint on the table columns. Notice I don't want to check the NOT NULL column values with something like SELECT COLUMN FROM TABLE WHERE COLUMN IS NOT NULL! I want to check the table definition for that specific column. thank you