-- An Interactive Instance of H2 Database Online
-- Warning first ever load will take up to 50 seconds.
-- Within this editor:
-- Press Control+Enter to run a line
-- Press Control+E to run the highlighted text
CREATE TABLE person (
-- define columns (name / type / default value / nullable)
id DECIMAL NOT NULL,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
date_of_birth DATE,
place_of_birth VARCHAR(50),
ssn CHAR(11),
weight DECIMAL DEFAULT 0 NOT NULL,
-- select one of the defined columns as the Primary Key and
-- guess a meaningfull name for the Primary Key constraint: 'person_pk' may be a good choice
CONSTRAINT person_pk PRIMARY KEY (id)
);
CREATE TABLE contact (
-- define columns (name / type / default value / nullable)
id DECIMAL NOT NULL,
person_id DECIMAL NOT NULL,
-- use a default value, if contact_type is omitted
contact_type VARCHAR(25) DEFAULT 'email' NOT NULL,
contact_value VARCHAR(50) NOT NULL,
-- select one of the defined columns as the Primary Key
CONSTRAINT contact_pk PRIMARY KEY (id),
-- define Foreign Key relation between column person_id and column id of table person
CONSTRAINT contact_fk FOREIGN KEY (person_id) REFERENCES person(id),
-- more constraint(s)
CONSTRAINT contact_check CHECK (contact_type IN ('fixed line', 'mobile', 'email', 'icq', 'skype'))
);
INSERT INTO person VALUES (1, 'Larry', 'Goldstein', DATE'1970-11-20', 'Dallas', '078-05-1120', 95);
INSERT INTO person VALUES (2, 'Tom', 'Burton', DATE'1977-01-22', 'Birmingham', '078-05-1121', 75);
INSERT INTO person VALUES (3, 'Lisa', 'Hamilton', DATE'1975-12-23', 'Richland', '078-05-1122', 56);
INSERT INTO person VALUES (4, 'Kim', 'Goldstein', DATE'2011-06-01', 'Shanghai', '078-05-1123', 11);
INSERT INTO person VALUES (5, 'James', 'de Winter', DATE'1975-12-23', 'San Francisco', '078-05-1124', 75);
INSERT INTO person VALUES (6, 'Elias', 'Baker', DATE'1939-10-03', 'San Francisco', '078-05-1125', 55);
INSERT INTO person VALUES (7, 'Yorgos', 'Stefanos', DATE'1975-12-23', 'Athens', '078-05-1126', 64);
INSERT INTO person VALUES (8, 'John', 'de Winter', DATE'1977-01-22', 'San Francisco', '078-05-1127', 77);
INSERT INTO person VALUES (9, 'Richie', 'Rich', DATE'1975-12-23', 'Richland', '078-05-1128', 90);
INSERT INTO person VALUES (10, 'Victor', 'de Winter', DATE'1979-02-28', 'San Francisco', '078-05-1129', 78);
INSERT INTO contact VALUES (1, 1, 'fixed line', '555-0100');
INSERT INTO contact VALUES (2, 1, 'email', 'larry.goldstein@acme.xx');
INSERT INTO contact VALUES (3, 1, 'email', 'lg@my_company.xx');
INSERT INTO contact VALUES (4, 1, 'icq', '12111');
INSERT INTO contact VALUES (5, 4, 'fixed line', '5550101');
INSERT INTO contact VALUES (6, 4, 'mobile', '10123444444');
INSERT INTO contact VALUES (7, 5, 'email', 'james.dewinter@acme.xx');
INSERT INTO contact VALUES (8, 7, 'fixed line', '+30000000000000');
INSERT INTO contact VALUES (9, 7, 'mobile', '+30695100000000');
SELECT lastname FROM person;
-- retrieves only seven rows. Duplicate values are thrown away.
SELECT DISTINCT lastname FROM person;
-- Hint:
-- The keyword 'DISTINCT' refers to the entirety of the resulting rows, which you can imagine as
-- the concatenation of all columns. It follows directly behind the SELECT keyword.
-- The following query leads to ten rows, although three persons have the same lastname.
SELECT DISTINCT lastname, firstname FROM person;
-- again only seven rows
SELECT DISTINCT lastname, lastname FROM person;
SELECT * FROM person;
-- Examples from https://en.wikibooks.org/wiki/Structured_Query_Language/SELECT:_Join_Operation
-- Run the first Example code first, to create the database.
SELECT * FROM person p
JOIN contact c ON p.id = c.person_id;
-- show only important columns
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id;
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile')
ORDER BY p.lastname, p.firstname, c.contact_type DESC;
-- show only desired rows
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
-- use functions: min() / max() / count()
SELECT count(*)
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
-- JOIN a table with itself. Example: Search different persons with the same lastname
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname
WHERE p1.id != p2.id;
-- JOIN more than two tables. Example: contact information of different persons with same lastname
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname, c.contact_type, c.contact_value
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname
JOIN contact c ON p2.id = c.person_id
WHERE p1.id != p2.id;
DROP TABLE IF EXISTS weather;
DROP TABLE IF EXISTS cities;
DROP TABLE IF EXISTS sales;
create table sales(item text, sale_time timestamp, quantity int);
insert into sales values('a', '2001-01-01 00:18:00', 10);
insert into sales values('b', '2001-01-01 00:18:30', 20);
insert into sales values('c', '2001-01-01 00:19:00', 30);
CREATE TABLE weather (
city VARCHAR,
temp_lo INTEGER, -- minimum temperature on a day
temp_hi INTEGER, -- maximum temperature on a day
prcp REAL,
date DATE
);
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather VALUES ('San Francisco', 60, 70, 0.22, '2022-06-10');
INSERT INTO weather VALUES ('San Francisco', 44, 55, 0.33, '2023-01-01');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
CREATE TABLE cities(Country VARCHAR, Name VARCHAR, `Year` INTEGER, Population INTEGER);
INSERT INTO cities VALUES ('NL', 'Amsterdam', 2000, 1005);
INSERT INTO cities VALUES ('NL', 'Amsterdam', 2010, 1065);
INSERT INTO cities VALUES ('NL', 'Amsterdam', 2020, 1158);
INSERT INTO cities VALUES ('US', 'Seattle', 2000, 564);
INSERT INTO cities VALUES ('US', 'Seattle', 2010, 608);
INSERT INTO cities VALUES ('US', 'Seattle', 2020, 738);
INSERT INTO cities VALUES ('US', 'New York City', 2000, 8015);
INSERT INTO cities VALUES ('US', 'New York City', 2010, 8175);
INSERT INTO cities VALUES ('US', 'New York City', 2020, 8772);
/** SELECT * FROm information_schema.tables; **/
SELECT * FROM sales;
SELECT * FROM weather;
SELECT * FROM cities;
H2 Database Documentation