H2

H2

## H2 Server
$ java -cp h2-1.4.193.jar org.h2.tools.Server -baseDir /e/Working/SQL/H2/Java/H2-Base -properties /e/Working/SQL/H2/Java/H2-Base
# OR
$ java -cp h2-1.4.193.jar org.h2.tools.Console -baseDir /e/Working/SQL/H2/Java/H2-Base -properties /e/Working/SQL/H2/Java/H2-Base
http://192.168.56.1:8082/login.jsp?jsessionid=127e3c922687e2a50c5750ac4fbb894c
Saved Settings:	
Generic H2 (Embedded)
Setting Name:	
Generic H2 (Embedded)
Driver Class:	
org.h2.Driver
JDBC URL:	
jdbc:h2:sampledb
[
# Default Database Name: test 
# Default Database Directory: ~test (C:\Users\<user>)
]
# Database Directory:
# E:\Working\SQL\H2\Java\H2-Base\sampledb\sampledb.mv.db
# /e/Working/SQL/H2/Java/H2-Base/sampledb/sampledb.mv.db
User Name:	
sa
Password:

## H2 Shell
$ java -cp h2-1.4.193.jar org.h2.tools.Shell -url "jdbc:h2:tcp://192.168.56.1:9092/sampledb" -user "sa"
# OR
$ java -cp h2-1.4.193.jar org.h2.tools.Shell
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:tcp://192.168.56.1:9092/sampledb
[Enter]   org.h2.Driver
Driver    org.h2.Driver
[Enter]
User      sa
[Enter]   Hide
Password


sql> DROP TABLE IF EXISTS users;
sql> CREATE TABLE users
(
    -- id INT IDENTITY NOT NULL,
    id INT  GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
    username VARCHAR(50) NOT NULL, 
    login_date DATE DEFAULT CURRENT_DATE,
    login_time TIME DEFAULT CURRENT_TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    
    CONSTRAINT pk_id PRIMARY KEY(id),
    CONSTRAINT idx_username UNIQUE(username)    
);
sql> COMMIT;

sql> SELECT * FROM users;

sql> INSERT INTO users(username, login_date, login_time, created_at, updated_at)
VALUES('Foo', '2016-11-06', '10:49:35', '2016-11-06 10:49:35.0', '2016-11-06 10:49:35.0');
COMMIT;

sql> SELECT * FROM users;

-- DELETE
sql> DELETE FROM users;

sql> exit
-- OR
sql> quit


-- Version
sql> SELECT DATABASE();
sql> SELECT DATABASE() FROM DUAL;
sql> SELECT H2VERSION() FROM DUAL

-- Database
sql> SELECT DATABASE();

-- Tables in Database
sql> SHOW TABLES;

-- Columns
sql> SHOW COLUMNS FROM users;

-- SELECT
sql> SELECT 'Hello, World!';


-- Run script from the default directory of h2-1.4.193.jar
sql> RUNSCRIPT FROM 'file.sql';


## H2 Server # Git Bash

Aspire@Acer MINGW64 ~
$ java -version
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

Aspire@Acer MINGW64 ~
$ cd "E:\Working\SQL\H2\Java\H2-Maven-Jar"

Aspire@Acer MINGW64 /e/Working/SQL/H2/Java/H2-Maven-Jar
$ java -cp h2-1.4.193.jar org.h2.tools.Server -baseDir /e/Working/SQL/H2/Java/H2-Base -properties /e/Working/SQL/H2/Java/H2-Base
TCP server running at tcp://192.168.56.1:9092 (only local connections)
PG server running at pg://192.168.56.1:5435 (only local connections)
Web Console server running at http://192.168.56.1:8082 (only local connections)


## H2 Shell # Git Bash

Aspire@Acer MINGW64 ~
$ java -version
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

Aspire@Acer MINGW64 ~
$ cd "E:\Working\SQL\H2\Java\H2-Maven-Jar"

Aspire@Acer MINGW64 /e/Working/SQL/H2/Java/H2-Maven-Jar
$ java -cp h2-1.4.193.jar org.h2.tools.Shell -url "jdbc:h2:tcp://192.168.56.1:9092/sampledb" -user "sa"

Welcome to H2 Shell 1.4.193 (2016-10-31)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> DROP TABLE IF EXISTS users;
CREATE TABLE users
(
    -- id INT IDENTITY NOT NULL,
    id INT  GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
    username VARCHAR(50) NOT NULL,
    login_date DATE DEFAULT CURRENT_DATE,
    login_time TIME DEFAULT CURRENT_TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_id PRIMARY KEY(id),
    CONSTRAINT idx_username UNIQUE(username)
);
COMMIT;

SELECT * FROM users;

INSERT INTO users(username, login_date, login_time, created_at, updated_at)
VALUES('Foo', '2016-11-06', '10:49:35', '2016-11-06 10:49:35.0', '2016-11-06 10:49:35.0');
COMMIT;

SELECT * FROM users;(Update count: 0, 0 ms)
sql> ...> ...> ...> ...> ...> ...> ...> ...> ...> ...> ...> (Update count: 0, 31 ms)
sql> (Update count: 0, 0 ms)
sql> sql> ID | USERNAME | LOGIN_DATE | LOGIN_TIME | CREATED_AT | UPDATED_AT
(0 rows, 47 ms)
sql> sql> ...> (Update count: 1, 16 ms)
sql> (Update count: 0, 0 ms)
sql> sql>
ID  | USERNAME | LOGIN_DATE | LOGIN_TIME | CREATED_AT            | UPDATED_AT
101 | Foo      | 2016-11-06 | 10:49:35   | 2016-11-06 10:49:35.0 | 2016-11-06 10:49:35.0
(1 row, 31 ms)
sql> exit
Connection closed

Aspire@Acer MINGW64 /e/Working/SQL/H2/Java/H2-Maven-Jar
$ java -cp h2-1.4.193.jar org.h2.tools.Shell

Welcome to H2 Shell 1.4.193 (2016-10-31)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:tcp://192.168.56.1:9092/sampledb
[Enter]   org.h2.Driver
Driver    org.h2.Driver
[Enter]
User      sa
[Enter]   Hide
Password
Password  Password  ><                                                                                                                                                                      Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> SELECT * FROM users;
ID  | USERNAME | LOGIN_DATE | LOGIN_TIME | CREATED_AT            | UPDATED_AT
101 | Foo      | 2016-11-06 | 10:49:35   | 2016-11-06 10:49:35.0 | 2016-11-06 10:49:35.0
(1 row, 61 ms)
sql> quit
Connection closed

---------------------------------------------------------------------------------------------------------------------------------

-- H2

DROP TABLE IF EXISTS Table1;

CREATE TABLE Table1
(ID INT, Value VARCHAR(10));
SELECT * FROM Table1;

INSERT INTO Table1 (ID, Value)
VALUES(1, 'First');

INSERT INTO Table1 (ID, Value)
VALUES(2, 'Second');

INSERT INTO Table1 (ID, Value)
VALUES(3, 'Third');

INSERT INTO Table1 (ID, Value)
VALUES(4, 'Fourth');

INSERT INTO Table1 (ID, Value)
VALUES(5, 'Fifth');

SELECT * FROM Table1;


DROP TABLE IF EXISTS Table2;

CREATE TABLE Table2
(ID INT, Value VARCHAR(10));
SELECT * FROM Table2;

INSERT INTO Table2 (ID, Value)
VALUES(1, 'I');

INSERT INTO Table2(ID, Value)
VALUES(2, 'II');

INSERT INTO Table2 (ID, Value)
VALUES(3, 'III');

INSERT INTO Table2 (ID, Value)
VALUES(6, 'VI');

INSERT INTO Table2 (ID, Value)
VALUES(7, 'VII');

INSERT INTO Table2 (ID, Value)
VALUES(8, 'VIII');

SELECT * FROM Table2;


/* (INNER) JOIN */
SELECT t1.*, t2.* FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID;


/* LEFT (OUTER) JOIN */
SELECT t1.*, t2.* FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID;


/* RIGHT (OUTER) JOIN */
SELECT t1.*, t2.* FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID;


/* 
-- NO FULL (OUTER) JOIN in H2 Database
SELECT t1.*, t2.* FROM Table1 t1
FULL JOIN Table2 t2 ON t1.ID = t2.ID;
*/

/* Emulate FULL (OUTER) JOIN -- NOTE: NULLS LAST */
SELECT * FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.id
UNION
SELECT * FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.id = t2.id;


/* CROSS JOIN -- NOTE: Pivot t2.ID (Differs From SQL Server, PostgreSQL) */
SELECT t1.*, t2.* FROM Table1 t1
CROSS JOIN Table2 t2;

---------------------------------------------------------------------------------------------------------------------------------
-- H2 nth Highest

DROP TABLE IF EXISTS Employee;

CREATE TABLE Employee
(ID INT, Name NVARCHAR(50), Salary numeric(15, 2));
COMMIT;

INSERT INTO Employee
VALUES
(1, 'A', 10000), -- 4th
(2, 'B', 8000),  -- 5th
(3, 'C', 8000),  
(4, 'D', 6000),  -- 6th
(5, 'E', 6000),  
(6, 'F', 6000),
(7, 'G', 5000),  -- 7th  
(8, 'H', 5000),
(9, 'I', 5000),
(10, 'J', 5000),
(11, 'K', 4000),  -- 8th
(12, 'L', 4000),
(13, 'M', 3000),  -- 9th
(14, 'N', 3000),
(15, 'O', 1000),  -- 10th
(16, 'P', 14000), -- 2nd
(17, 'Q', 14000),
(18, 'R', 12000), -- 3rd
(19, 'S', 12000),
(20, 'T', 16000), -- 1st
(21, 'U', 16000), 
(22, 'V', 16000), 
(23, 'W', 14000),
(24, 'X', 12000),
(25, 'Y', 12000),
(26, 'Z', 10000);
COMMIT;

SELECT * FROM Employee;

-- 16000 -- 1st
-- 14000 -- 2nd
-- 12000 -- 3rd
-- 10000 -- 4th
--  8000 -- 5th
--  6000 -- 6th
--  5000 -- 7th  
--  4000 -- 8th
--  3000 -- 9th
--  1000 -- 10th

-- nth Highest -- 5th Highest -- 8000
SELECT Salary FROM
(
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC LIMIT 5
)
A ORDER BY Salary LIMIT 1;
-- Alternatively
SELECT *
FROM Employee Emp1
WHERE (5 - 1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary) LIMIT 1;

-- 2nd Highest -- 14000
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);

-- Alternatively
SELECT MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee);

---------------------------------------------------------------------------------------------------------------------------------