Question

In the following pages, we will work with the SQL understood by the Derby database engine. We will not go into much detail about the SQL language. This chapter is a quick recapitulate of the most important SQL statements present in the Derby database.

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.

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

We have previously created the AUTHORS and BOOKS tables. We are going to recreate them. The DROP TABLE SQL statement drops the table from the database. Note that the DROP TABLE IF EXISTS statement does not exist in Derby.

ij> CREATE TABLE AUTHORS(ID INT PRIMARY KEY, NAME VARCHAR(25));
0 rows inserted/updated/deleted

The CREATE TABLE statement creates a new table. It has two columns: ID and NAME . In the ID column we will place 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.

ij> CREATE TABLE BOOKS(ID INT PRIMARY KEY, AUTHOR_ID INT, 
> TITLE VARCHAR(100), FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(ID));
0 rows inserted/updated/deleted

We create a BOOKS table, which has 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.

ij> INSERT INTO AUTHORS(ID, NAME) VALUES(1, 'Jack London');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(2, 'Honore de Balzac');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(3, 'Lion Feuchtwanger');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(4, 'Emile Zola');
ij> INSERT INTO AUTHORS(ID, NAME) VALUES(5, 'Truman Capote');

We add five rows to the AUTHORS table using the INSERT INTO SQL statement.

ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(1, 1, 'Call of the Wild');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(2, 1, 'Martin Eden');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(3, 2, 'Old Goriot');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(4, 2, 'Cousin Bette');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(5, 3, 'Jew Suess');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(6, 4, 'Nana');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(7, 4, 'The Belly of Paris');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(8, 5, 'In Cold blood');
ij> INSERT INTO BOOKS(ID, AUTHOR_ID, TITLE) VALUES(9, 5, 'Breakfast at Tiffany');

We insert 8 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.

Autoincrement

Derby allows to create an autoincrement column. The value of an autoincrement column increments automatically with every INSERT statement.

CREATE TABLE FRIENDS(ID INT PRIMARY KEY
  GENERATED ALWAYS AS IDENTITY
  (START WITH 1, INCREMENT BY 1), 
  NAME VARCHAR(20));

INSERT INTO FRIENDS(NAME) VALUES('Jane');
INSERT INTO FRIENDS(NAME) VALUES('Thomas');
INSERT INTO FRIENDS(NAME) VALUES('Beka');

In the above SQL code, the ID column is an autoincrement column. If we omit the ID in the SQL INSERT statements the Derby automatically generates a unique number. It starts with number 1 and is incremented by one with each subsequent INSERT .

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.

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.

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 5 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.

ij> VALUES CURRENT_DATE;
1     
----------
2012-02-15

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

The VALUES CURRENT_DATE returns the current date and VALUES CURRENT SCHEMA returns the current schema of the connection.

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.

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 INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);

For the next case, we create a new CARS2 table.

ij> INSERT INTO CARS2 SELECT * 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 FRIENDS TO MYFRIENDS;

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

ij> RENAME COLUMN MYFRIENDS.ID TO FID;

The RENAME COLUMN statement renames a particular table column.

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