Working with Databases in Java - Beginning Java Programming: The Object-Oriented Approach - page 310

The SQL DDL needed to define Tables 9-1 and 9-2 is illustrated in the following code:

CREATE TABLE 'EmployeeSchema'.'Employee' (
'EmployeeID' INT NOT NULL,
'Name' VARCHAR(45) NULL,
'Gender' VARCHAR(45) NULL,
'DNR' INT NULL,
PRIMARY KEY ('EmployeeID'),
INDEX 'DNRForeign_idx' ('DNR' ASC),
CONSTRAINT 'DNRForeign'
FOREIGN KEY ('DNR')
REFERENCES 'EmployeeSchema'.'Department' ('DNR')
ON DELETE NO ACTION
ON UPDATE NO ACTION)

CREATE TABLE 'EmployeeSchema'.'Department' (
'DNR' INT NOT NULL
'DName' VARCHAR(45) NULL,
'DAddress' VARCHAR(45) NULL,
PRIMARY KEY ('DNR'))

The SQL DML instructions allow you to query, insert, update, and delete the data. Some examples
are presented in Table 9-3.

taBle 9-3: Examples of SQL Queries

sQl Query meaning

select * from employeeschema.employee; Select all information from the Employee table.

select name from employeeschema.employee
Where dnr=1;

Select the name of all employees working in
department number 1.

select count(*) from employeeschema.
department;

Select the number of departments from the
Department table.

select e.name, d.dname from
employeeschema.employee e, employeeschema.
department d Where e.dnr=d.dnr;

Select the names of all employees together
with the names of the departments they work
in.

insert into employeeschema.employee values (6,
“david peeters”, “male”,2);

Add a new employee to the Employee table.

update employeeschema.employee set dnr=3
Where name=”david peeters”;

Change the department of an employee.

delete from employeeschema.employee Where
name=”david peeters”;

Delete an employee from the Employee table.

In the remainder of this chapter, MySQL is used to demonstrate relational database access
from within Java. MySQL is a popular open-source RDBMS currently maintained by Oracle
( www.mysql.com ). It has been very popular in web applications and is also part of the LAMP
(Linux, Apache, MySQL, and Perl/PHP/Python) open-source software stack.