JDBC API of package java. sql

Exercise 24.4 (Employee Database) in Java How to Program 10th Edition. It reads:

Write an application that allows the user to:

a) Add employees to the employee table

b) Add payroll information to the appropriate table for each new employee. For example, for a salaried employee add the payroll information to the salariedEmployees table.

c) The attached SQL file will be used in this program

SQL File:

DROP DATABASE IF EXISTS employees;

CREATE DATABASE employees;

USE employees;

CREATE TABLE employees ( socialSecurityNumber varchar (30) NOT NULL, firstName varchar (30) NOT NULL, lastName varchar (30) NOT NULL, birthday date NOT NULL, employeeType varchar (30) NOT NULL, departmentName varchar (30) NOT NULL, PRIMARY KEY (socialSecurityNumber) );

CREATE TABLE salariedEmployees ( socialsecurityNumber varchar (30) NOT NULL, weeklySalary real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) );

CREATE TABLE commissionEmployees ( socialSecurityNumber varchar (30) NOT NULL, grossSales int NOT NULL, commissionRate real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) );

CREATE TABLE basePlusCommissionEmployees ( socialSecurityNumber varchar (30) NOT NULL, grossSales int NOT NULL, commissionRate real NOT NULL, baseSalary real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) );

CREATE TABLE hourlyEmployees ( socialSecurityNumber varchar (30) NOT NULL, hours int NOT NULL, wage real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) );

INSERT INTO employees VALUES ('111-11-1111', 'John', 'Smith', '1945-1-2', 'salariedEmployee', 'R&D');

INSERT INTO employees VALUES ('222-22-2222', 'Sue', 'Jones', '1961-2-3', 'commissionEmployee', 'SALES');

INSERT INTO employees VALUES ('333-33-3333', 'Bob', 'Lowis', '1958-10-5', 'basePlusCommissionEmployee', 'SALES');

INSERT INTO employees VALUES ('444-44-4444', 'Karen', 'Price', '1972-5-25', 'hourlyEmployee', 'HR');

INSERT INTO salariedEmployees VALUES ('111-11-1111', 2013.67, 0);

INSERT INTO commissionEmployees VALUES ('222-22-2222', 10100, 0.05, 0);

INSERT INTO basePlusCommissionEmployees VALUES ('333-33-3333', 5000, 0.04, 300, 0);

INSERT INTO hourlyEmployees VALUES ('444-44-4444', 30, 35.5, 0);

JDBC API of package java. sql