SQL queries in Derby

In this chapter we work with the SQL understood by the Derby database engine. It is a quick list of the most important SQL statements present inDerby.

SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems. Derby supports only a limited set of SQL statements. Some important statements known from other database systems are missing. Derby implements an SQL-92 core subset, as well as some SQL-99 features.

Dropping tables

The DROP TABLE statement removes a table from the database.

ij> DROP TABLE AUTHORS;
0 rows inserted/updated/deleted
ij> DROP TABLE BOOKS;
0 rows inserted/updated/deleted

Supposing that we have previously created the AUTHORS and BOOKS tables, we are going to drop them and create again. The DROP TABLE SQL statement drops the table from the database. Note that the DROP TABLE IF EXISTS statement does not exist in Derby.

Creating tables

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 an AUTHORS with two columns: ID and NAME. In the ID column we will place big integers, in the NAME column strings with up to 25 characters. A PRIMARY KEY uniquely identifies each record in the table. Each 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.

The GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) creates and identity column. Identity column is a column that stores numbers that increment by one with each insertion. 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. The FOREIGN KEY specifies that the values in the AUTHOR_ID column must match the values in the ID column of the AUTHORS table. Foreign keys provide a way to enforce the referential integrity of a database. Each book was written by one or more authors. So in the BOOKS table for the AUTHOR_ID column we can have only values that are present in the AUTHORS table.

Inserting rows

The INSERT statement is used to create one or more rows in the 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 add five rows to the AUTHORS table using the INSERT INTO SQL statement.

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 above SQL query joins the two tables. It assigns each book title to an author.

Queries

Queries are used to look up data from the database tables. The SELECT statement is the main statement to perform queries.

Limiting data output

Limiting data output is essential since many databases have thousands even millions of rows. Derby does not support the LIMIT clause known from other databases. Derby 10.7 introduced FETCH and OFFSET clauses that do the same thing.

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 have fetched 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

With the OFFSET cluase, we skip the first four rows 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 portion of the rows using the combination of OFFSET and FETCH clauses.

Selecting specific rows with the WHERE clause

The WHERE clause can be used to filter the results. It provides a 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

With the WHERE clause we only select the cars which have a price higher than 40000.

ij> SELECT NAME FROM CARS WHERE NAME LIKE '%en';
NAME                          
------------------------------
Citroen                       
Volkswagen                    

2 rows selected

With a LIKE clause we select specific car names that fit the search pattern. In our case it is cars that end in '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 that have IDs 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 that cost in the range 20000 and 50000. For this we use the BETWEEN AND keywords following the WHERE clause.

Ordering data

Ordering data can be done with 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 the cars by price. The default order type is ascending order.

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 order data in descending order, we add the DESC keyword.

Derby functions

Derby supports a few useful functions. These built-in functions are expressions in which an SQL keyword or special operator executes some operation.

Aggregate functions

Aggregate functions evaluate an expression over a set of rows. Whereas the 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 aggregates can calculate the minimum, maximum, sum, count, and average of an expression over a set of values as well as count rows.

ij> SELECT COUNT(ID) FROM AUTHORS;
1          
-----------
5   

The COUNT() is an aggregate function that counts 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 other four functions: MAX(), MIN(), AVG(), and SUM(). The AS clause gives a label for a column.

Date and time functions

Date and time functions work with date and time

ij> VALUES CURRENT_DATE;
1
----------
2017-03-15

ij> VALUES CURRENT SCHEMA;
1
--------------------------
USER12  

The VALUES CURRENT_DATE returns the current date.

ij> VALUES CURRENT_TIME;
1       
--------
17:22:49

The VALUES CURRENT_TIME returns the current time.

ij> VALUES CURRENT_TIMESTAMP;
1                            
-----------------------------
2017-03-15 17:29:49.987 

The VALUES CURRENT_TIMESTAMP returns the current timestamp, i.e., the current date and time as one value.

String functions

Derby contains functions that work with 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 characters to upper case and LOWER() to lower case.

ij> VALUES SUBSTR('blueberries', 5);        
1          
-----------
berries 

The SUBSTR() returns the part of a string. The first parameter is the string and the second is the starting position. The first position's index 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.

Math functions

Derby contains a few math 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() functions rounds the specified number up.

ij> VALUES FLOOR(3.4), FLOOR(3.8);
1                       
------------------------
3.0                     
3.0   

The FLOOR() functions rounds the specified number down.

ij> VALUES COS(0.6), SIN(0.6);
1                       
------------------------
0.8253356149096783      
0.5646424733950354

The COS() and SIN() are trigonometric cos and sin 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 radians to degrees.

ij> VALUES SQRT(16.0);
1                       
------------------------
4.0 

The SQRT() function returns the square root of a floating point number.

Updating and deleting data

Now we will concern ourselves with 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 data in a database table. The PRICE of a Mercedes car is set to 58000.

ij> SELECT * FROM CARS WHERE ID=2;
ID         |NAME                          |PRICE      
------------------------------------------------------
2          |Mercedes                      |58000      

1 row selected

The subsequent SELECT statement confirms 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 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 was copying OK.

ij> DELETE FROM CARS2 WHERE ID=8;
1 row inserted/updated/deleted

We used the DELETE FROM statement to delete a rown in the table.

ij> DELETE FROM CARS2;
7 rows inserted/updated/deleted

The DELETE FROM statement without the WHERE clause deletes all rows in the table.

ij> DROP TABLE CARS2;
0 rows inserted/updated/deleted

The DROP TABLE statement deletes the table completly from the database.

RENAME statements

The RENAME statement belongs to the data definition language of the SQL.

ij> RENAME TABLE CARS TO MYCARS;

The RENAME TABLE statement allows us to rename an existing table. We rename a FRIENDS table to MYFRIENDS.

ij> RENAME COLUMN MYCARS.ID TO CID;

The RENAME COLUMN statement renames a particular table column.

In the chapter, we have worked with the basics of the SQL language in Derby.