SQL Queries in Derby
Original: http://zetcode.com/db/apachederbytutorial/sql/
In this chapter, we will use SQL understood by the Derby database engine. It is a quick list of the most important SQL statements present in Derby.
SQL (Structured Query Language) is a database computer language designed to manage data in relational database management systems. Derby supports only a limited set of SQL statements. Some important statements known to other database systems are missing. Derby implements a core subset of SQL-92 as well as some SQL-99 functions.
DROP TABLE
The DROP TABLE statement deletes a table from the database.
ij> DROP TABLE AUTHORS; 0 rows inserted/updated/deleted ij> DROP TABLE BOOKS; 0 rows inserted/updated/deleted
Assuming we have previously created the AUTHORS and BOOKS tables, we will drop them and create them again. The DROP TABLE SQL statement deletes the table from the database. Note that the DROP TABLE IF EXISTS statement does not exist in Derby.
CREATE TABLE
The CREATE TABLE statement creates a new table.
ij> CREATE TABLE AUTHORS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY > (START WITH 1, INCREMENT BY 1), NAME VARCHAR(25)); 0 rows inserted/updated/deleted
We create AUTHORS with two columns: ID and NAME. In the ID column, we will place a large integer of up to 25 characters in the NAME column. PRIMARY KEY uniquely identifies each record in the table. Every author is a unique personality. Even if there are authors with the same name, each of them is in a separate row in the AUTHORS table. Only one column in a table can have this constraint.
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) creates and identifies columns. The identity column is the one that stores a number that increases by one on every insert. Identity columns are sometimes called autoincrement columns.
ij> CREATE TABLE BOOKS(ID BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY > (START WITH 1, INCREMENT BY 1), AUTHOR_ID BIGINT, TITLE VARCHAR(150), > FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(ID)); 0 rows inserted/updated/deleted
We create a BOOKS table with three columns. FOREIGN KEY specifies that the value in the AUTHOR_ID column must match the value in the ID column of the AUTHORS table. Foreign keys provide a way to enforce the referential integrity of a database. Each book is written by one or more authors. So, in the BOOKS table for the AUTHOR_ID column, we can only have values that exist in the AUTHORS table.
Insert row
The INSERT statement is used to create one or more rows in a database table.
ij> INSERT INTO AUTHORS(NAME) VALUES('Jack London');
ij> INSERT INTO AUTHORS(NAME) VALUES('Honore de Balzac');
ij> INSERT INTO AUTHORS(NAME) VALUES('Lion Feuchtwanger');
ij> INSERT INTO AUTHORS(NAME) VALUES('Emile Zola');
ij> INSERT INTO AUTHORS(NAME) VALUES('Truman Capote');
We use the INSERT INTO SQL statement to add five rows to the AUTHORS table.
ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Call of the Wild'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(1, 'Martin Eden'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Old Goriot'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(2, 'Cousin Bette'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(3, 'Jew Suess'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'Nana'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(4, 'The Belly of Paris'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'In Cold blood'); ij> INSERT INTO BOOKS(AUTHOR_ID, TITLE) VALUES(5, 'Breakfast at Tiffany');
We insert eight rows into the BOOKS table.
ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS > WHERE AUTHORS.ID = BOOKS.AUTHOR_ID; NAME|TITLE -------------------------------------------------- Jack London|Call of the Wild Jack London|Martin Eden Honore de Balzac|Old Goriot Honore de Balzac | Cousin Bette Lion Feuchtwanger | Jew Suess Emile Zola|Nana Emile Zola|The Belly of Paris Truman Capote|In Cold blood Truman Capote|Breakfast at Tiffany
9 rows selected
The SQL query above joins the two tables together. It assigns each book title to an author.
Query
Queries are used to find data from database tables. The SELECT statement is the main statement that executes the query.
Restrict data output
Limiting data output is critical because many databases have tens of thousands of rows. Derby does not support the LIMIT clause known from other databases. Derby 10.7 introduced the FETCH and OFFSET clauses which perform the same function.
ij> SELECT * FROM BOOKS FETCH FIRST 4 ROWS ONLY; ID | AUTHOR_ID | TITLE -------------------------------------------------- 1 |1 |Call of the Wild 2|1|Martin Eden 3|2|Old Goriot 4|2|Cousin Bette
In the first example, we extracted only the first 4 rows from the BOOKS table.
ij> SELECT * FROM BOOKS OFFSET 4 ROWS; ID | AUTHOR_ID | TITLE ----------------------------------------------- 5 |3 |Jew Suess 6|4|Nana 7 |4 |The Belly of Paris 8 |5 |In Cold blood 9 |5 |Breakfast at Tiffany
Using the OFFSET packet, we skip the first four lines and display the rest.
ij> SELECT * FROM BOOKS OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY; ID | AUTHOR_ID | TITLE -------------------------------------------------- --------------- 5 |3 |Jew Suess 6|4|Nana 7 |4 |The Belly of Paris
3 rows selected
We can select a subset of rows using a combination of OFFSET and FETCH clauses.
Use the WHERE clause to select specific rows
A WHERE clause can be used to filter the results. It provides selection criteria to select only specific rows from the data.
ij> SELECT * FROM CARS WHERE PRICE > 40000; ID |NAME |PRICE -------------------------------------------------- ---- 1 |Audi |52642 2|Mercedes|57127 5 |Bentley |350000 7 |Hummer |41400
4 rows selected
Using the WHERE clause, we only select cars whose price is above 40000.
ij> SELECT NAME FROM CARS WHERE NAME LIKE '%en'; name ------------------------------ Citroen Volkswagen
2 rows selected
With the LIKE clause, we select specific car names that fit the search pattern. In our case, the car ends with the "en" characters.
ij> SELECT * FROM CARS WHERE ID IN (2, 5, 7); ID |NAME |PRICE -------------------------------------------------- ---- 2|Mercedes|57127 5 |Bentley |350000 7 |Hummer |41400
3 rows selected
The IN clause can be used to select rows from a specific range of values. The above SQL statement returns rows with ID equal to 2, 5 and 7.
ij> SELECT * FROM CARS WHERE PRICE BETWEEN 20000 AND 50000; ID |NAME |PRICE -------------------------------------------------- ---- 4 |Volvo |29000 6 |Citroen |21000 7 |Hummer |41400 8 |Volkswagen |21600
4 rows selected
We select cars with a price between 20000 and 50000. To do this, we use the BETWEEN AND keyword after the WHERE clause.
Sort data
Ordering data can be done using the ORDER BY clause.
ij> SELECT * FROM CARS ORDER BY PRICE; ID |NAME |PRICE -------------------------------------------------- ---- 3 |Skoda |9000 6 |Citroen |21000 8 |Volkswagen |21600 4 |Volvo |29000 7 |Hummer |41400 1 |Audi |52642 2|Mercedes|57127 5 |Bentley |350000
8 rows selected
We order cars by price. The default order type is ascending.
ij> SELECT * FROM CARS ORDER BY PRICE DESC; ID |NAME |PRICE -------------------------------------------------- ---- 5 |Bentley |350000 2|Mercedes|57127 1 |Audi |52642 7 |Hummer |41400 4 |Volvo |29000 8 |Volkswagen |21600 6 |Citroen |21000 3 |Skoda |9000
To sort the data in descending order, we added the DESC keyword.
Derby functions
Derby supports some useful functions. These built-in functions are expressions that use SQL keywords or special operators to perform certain operations.
Aggregate function
Aggregate functions evaluate expressions over a set of rows. While other built-in functions operate on a single expression, aggregates operate on a set of values and reduce them to a single scalar value. Built-in aggregations can calculate the minimum, maximum, sum, count, and average of expressions in a set of values as well as count rows.
ij> SELECT COUNT(ID) FROM AUTHORS; 1 ----------- 5
COUNT() is an aggregate function used to count the number of rows accessed in an expression. There are five authors in the AUTHORS table.
ij> SELECT MIN(PRICE) AS "PRICE", MAX(PRICE) AS "MAX", > AVG(PRICE) AS "AVG", SUM(PRICE) AS "SUM" FROM CARS; PRICE |MAX |AVG |SUM ----------------------------------------------- 9000 |350000 |72721 |581769
1 row selected
In the above query, we use four other functions: MAX(), MIN(), AVG() and SUM (). The AS clause provides labels for the columns.
Derby Tools
Original: http://zetcode.com/db/apachederbytutorial/tools/
In this chapter, we mentioned the Derby tool. Derby-tools and tools are a set of scripts that come with Derby. They are commonly used to create, check and update Derby databases.
In this page we will refer to sysinfo, dblook, ij, startNetworkServer and stopNetworkServertools.
Start the Derby tool
The Derby tools can be run in two ways. We use the names of the scripts located in the bin directory of the Derby installation directory, or they can be launched using the derbyrun.jar file.
$ $DERBY_HOME/bin/ij $ java -jar $DERBY_HOME/lib/derbyrun.jar ij
We can start the ij tool by specifying the script name in the terminal. The second line runs ij using the derbyrun.jar file.
System information
The sysinfo tool provides information about the operating system, Java and Derby. It will print Java version, Java home directory, OS version, Java runtime version, Derby version, current and supported locales. This tool is useful for tracking down certain installation or configuration problems with Derby.
$ $DERBY_HOME/bin/sysinfo ------------------ Java Information ------------------ Java Version: 1.8.0_111 Java Vendor: Oracle Corporation Java home: /home/janbodnar/bin/jdk1.8.0_111/jre Java classpath: /home/janbodnar/bin/jdk1.8.0_111/db/lib/derby.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbynet.jar:/home/janbodnar/bin/ jdk1.8.0_111/db/lib/derbytools.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbyoptionaltools.jar:/home/janbodnar/bin/jdk1.8.0_111/db/lib/derbyclient .jar OS name: Linux OS architecture: amd64 OS version: 4.4.0-66-generic ...
This is an excerpt of the information provided on the specific system.
ij
ij is an interactive scripting tool. It is used to run scripted or interactive queries against the Derby database.
$ cat cars.sql
SET SCHEMA USER12;
CREATE TABLE CARS (ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS(Name, Price) VALUES('Audi', 52642);
INSERT INTO CARS(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO CARS(Name, Price) VALUES('Skoda', 9000);
INSERT INTO CARS(Name, Price) VALUES('Volvo', 29000);
INSERT INTO CARS(Name, Price) VALUES('Bentley', 350000);
INSERT INTO CARS(Name, Price) VALUES('Citroen', 21000);
INSERT INTO CARS(Name, Price) VALUES('Hummer', 41400);
INSERT INTO CARS(Name, Price) VALUES('Volkswagen', 21600);
We have a cars.sql file which creates a database schema and a CARS table.
$ $DERBY_HOME/bin/ij ij version 10.11 ij> CONNECT 'jdbc:derby:testdb;user=user12;create=true';
We start the ij tool. We create a testdb database and establish a connection.
ij> SHOW CONNECTIONS; CONNECTION0* - jdbc:derby:testdb * = current connection
The SHOW CONNECTIONS statement displays open connections to the Derby database.
ij> RUN 'cars.sql'; ij> CREATE SCHEMA USER12; 0 rows inserted/updated/deleted ij> CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT); 0 rows inserted/updated/deleted ij> INSERT INTO CARS VALUES(1, 'Audi', 52642); 1 row inserted/updated/deleted ij> INSERT INTO CARS VALUES(2, 'Mercedes', 57127); 1 row inserted/updated/deleted ij> INSERT INTO CARS VALUES(3, 'Skoda', 9000); ...
We load and execute the cars.sql website. We were told what was going on.
ij> SELECT * FROM CARS; ID |NAME |PRICE -------------------------------------------------- ---- 1 |Audi |52642 2|Mercedes|57127 3 |Skoda |9000 4 |Volvo |29000 5 |Bentley |350000 6 |Citroen |21000 7 |Hummer |41400 8 |Volkswagen |21600
8 rows selected
We select all rows from the CARS table.
ij> CONNECT 'jdbc:derby:testdb;shutdown=true'; ERROR 08006: Database 'testdb' shutdown.
Closing a database in Derby causes an exception. Expected error 08006.
ij> SHOW CONNECTIONS; No current connection
Connection closed.
ij> EXIT;
We use the EXIT command to exit the ij tool. Note that there are semicolons after each command.
dblook
The dblook tool is used to save the data definition language of database objects, including tables, views, indexes, and triggers.
$DERBY_HOME/bin/dblook -d jdbc:derby:testdb -- Timestamp: 2017-03-13 20:05:43.281 -- Source database is: testdb -- Connection URL is: jdbc:derby:testdb -- appendLogs: false
– DDL Statements for schemas
CREATE SCHEMA “USER12”;
– DDL Statements for tables
CREATE TABLE “USER12”. “CARS” (“ID” INTEGER NOT NULL, “NAME” VARCHAR(30), “PRICE” INTEGER);
CREATE TABLE “APP”. “CARS” (“ID” BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), “NAME” VARCHAR(30), “PRICE” INTEGER);
– DDL Statements for keys
– PRIMARY/UNIQUE
ALTER TABLE “APP”. “CARS” ADD CONSTRAINT “SQL170313140819740” PRIMARY KEY (“ID”);
ALTER TABLE “USER12″.”CARS” ADD CONSTRAINT “SQL170313200304680” PRIMARY KEY (“ID”);
In the above example, we have dumped objects from the testdb database. With the -d option, we provide the connection URL of the database. In our case, the dblook tool saves a database schema and a table. Output can be redirected to a file using the -o option.
startNetworkServer and stopNetworkServer
These scripts start and stop the Derby Network server. In the case of a networked server, multiple connections to the Derby database can be created.
$ $DERBY_HOME/bin/startNetworkServer & [1] 12421 $ Mon Mar 13 20:12:39 CET 2017 : Security manager installed using the Basic server security policy. Mon Mar 13 20:12:40 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) started and ready to accept connections
Here, we use the startNetworkServer script to start the Derby Network Server.
ij> CONNECT 'jdbc:derby://localhost:1527/testdb';
Here, we connect to the testdb database through Derby Network Server. The connection URL for network connections is different.
ij> SELECT * FROM USER12. CARS; ID |NAME |PRICE -------------------------------------------------- ---- 1 |Audi |52642 2|Mercedes|57127 3 |Skoda |9000 4 |Volvo |29000 5 |Bentley |350000 6 |Citroen |21000 7 |Hummer |41400 8 |Volkswagen |21600
8 rows selected
We select all cars from the CARS table. Since we didn’t provide the database schema in the connection URL, we must specify it now. The database schema is the username; in our case USER12.
$ $DERBY_HOME/bin/stopNetworkServer Mon Mar 13 20:15:42 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown $ Mon Mar 13 20:15:42 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
We have stopped the server using the stopNetworkServer script.
NetworkServerControl
NetworkServerControl is a system tool that can start and stop the Derby network server and configure or retrieve diagnostic information. With the exception of ping, all commands can only be executed from the computer running the server.
$ $DERBY_HOME/bin/NetworkServerControl start &
Using the start command, we start the Derby server.
$ $DERBY_HOME/bin/NetworkServerControl ping Tue Mar 21 15:53:29 CET 2017 : Connection obtained for host: localhost, port number 1527.
The ping command tests whether the Derby server is started.
$ $DERBY_HOME/bin/NetworkServerControl sysinfo --------- Derby Network Server Information -------- Version: CSS10110/10.11.1.2 - (1629631) Build: 1629631 DRDA Product Id: CSS10110 -- listing properties -- derby.drda.traceDirectory=/home/janbodnar/.derby/ derby.drda.maxThreads=0 derby.drda.sslMode=off derby.drda.keepAlive=true ...
The sysinfo command provides system information.
$ $DERBY_HOME/bin/NetworkServerControl runtimeinfo --- Derby Network Server Runtime Information --- ---------- Session Information --------------- Session # : 3 -------------------------------------------------- ----------- # Connection Threads : 1 # Active Sessions : 1 # Waiting Sessions : 0
Total Memory : 78643200 Free Memory : 75359512
The runtimeinfo command provides extensive debugging information about sessions, threads, prepared statements, and memory usage of a running web server.
$ $DERBY_HOME/bin/NetworkServerControl shutdown Tue Mar 21 15:56:43 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown Tue Mar 21 15:56:44 CET 2017 : Apache Derby Network Server - 10.11.1.2 - (1629631) shutdown
The shutdown command stops the Derby server.
In this chapter, we wrote about Derby tools.
Date and time functions
Date and time functions work with dates and times
ij> VALUES CURRENT_DATE; 1 ---------- 2017-03-15
ij> VALUES CURRENT SCHEMA;
1
USER12
VALUES CURRENT_DATE returns the current date.
ij> VALUES CURRENT_TIME; 1 -------- 17:22:49
VALUES CURRENT_TIME returns the current time.
ij> VALUES CURRENT_TIMESTAMP; 1 ----------------------------- 2017-03-15 17:29:49.987
VALUES CURRENT_TIMESTAMP returns the current timestamp, ie the current date and time, as a value.
String function
Derby includes functions available for strings.
ij> VALUES LENGTH('Wonderful day');
1
-----------
13
1 row selected
The LENGTH() function returns the number of characters in a string.
ij> VALUES UPPER('derby');
1
-----
DERBY
1 row selected
ij> VALUES LOWER(‘Derby’);
1
derby
1 row selected
The UPPER() function converts a character to uppercase, and LOWER() converts a character to lowercase.
ij> VALUES SUBSTR('blueberries', 5);
1
-----------
berries
SUBSTR() returns part of a string. The first parameter is a string and the second parameter is the starting position. The index of the first position is 1.
ij> VALUES SUBSTR('blueberries', 1, 4);
1
----
blue
The third parameter is optional; it provides the length of the substring to be returned.
Mathematical functions
Derby includes some mathematical functions.
ij> VALUES ABS(-4); 1 ----------- 4
ABS() returns the absolute value of a numeric expression.
ij> VALUES CEIL(3.4), CEIL(3.8); 1 ------------------------ 4.0 4.0
The CEIL() function rounds the specified number.
ij> VALUES FLOOR(3.4), FLOOR(3.8); 1 ------------------------ 3.0 3.0
The FLOOR() function rounds the specified number.
ij> VALUES COS(0.6), SIN(0.6); 1 ------------------------ 0.8253356149096783 0.5646424733950354
COS() and SIN() are trigonometric cosine and sine functions.
ij> VALUES RADIANS(180), DEGREES(3.141592653589793); 1 ------------------------ 3.141592653589793 180.0
The RADIANS() function converts degrees to radians, and the DEGREES() function converts degrees to radians.
ij> VALUES SQRT(16.0); 1 ------------------------ 4.0
The SQRT() function returns the square root of a floating point number.
Update and delete data
Now, we’ll focus on updating and deleting data in the CARS table.
ij> UPDATE CARS SET PRICE=58000 WHERE ID=2; 1 row inserted/updated/deleted
The UPDATE statement is used to modify the data in the database table. Mercedes cars have PRICE set to 58000.
ij> SELECT * FROM CARS WHERE ID=2; ID |NAME |PRICE -------------------------------------------------- ---- 2 |Mercedes |58000
1 row selected
Subsequent SELECT statements confirm the modification of the data.
ij> CREATE TABLE CARS2(ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY > (START WITH 1, INCREMENT BY 1), NAME VARCHAR(30), PRICE INT);
For the next case, we create a new CARS2 table.
ij> INSERT INTO CARS2(NAME, PRICE) SELECT NAME, PRICE FROM CARS; 8 rows inserted/updated/deleted
We insert all the rows from the CARS table into the CARS2 table, thus copying all the data.
ij> SELECT * FROM CARS2; ID |NAME |PRICE -------------------------------------------------- ---- 1 |Audi |52642 2 |Mercedes |58000 3 |Skoda |9000 4 |Volvo |29000 5 |Bentley |350000 6 |Citroen |21000 7 |Hummer |41400 8 |Volkswagen |21600
8 rows selected
We check the CARS2 table and see that all data is replicating OK.
ij> DELETE FROM CARS2 WHERE ID=8; 1 row inserted/updated/deleted
We use the DELETE FROM statement to delete rows in a table.
ij> DELETE FROM CARS2; 7 rows inserted/updated/deleted
A DELETE FROM statement without a WHERE clause deletes all rows in the table.
ij> DROP TABLE CARS2; 0 rows inserted/updated/deleted
The DROP TABLE statement completely drops the table from the database.
RENAME statement
The RENAME statement belongs to the SQL data definition language.
ij> RENAME TABLE CARS TO MYCARS;
The RENAME TABLE statement allows us to rename an existing table. We renamed the FRIENDS table to MYFRIENDS.
ij> RENAME COLUMN MYCARS.ID TO CID;
The RENAME COLUMN statement renames a specific table column.
In this chapter, we have used the basics of the SQL language in Derby.