DJ's site

Oracle SQL Snippets

Here is a summary of SQL usage that I just can't remember. (Taken from http://www.techonthenet.com/)

DDL

Data Description Language statements modify table structure.

Let's tackle that by example. The famous ACME corporation wants to set up a new HR database.

CREATE TABLE employees (
	id INTEGER PRIMARY KEY USING INDEX TABLESPACE hr_indx,
	lastname VARCHAR(20) NOT NULL,
	firstname VARCHAR(20) NOT NULL,
	salary INTEGER NOT NULL
) TABLESPACE hr_data;

So far so good. Now on 2009-03-01, "John Doe" joined ACME as an intern for 3 months. The HR system must not forget that John was here, but it should also not mistake John for a current employee. Also while John was here, it must be clear, that he's "just" an intern.

ALTER TABLE employees ADD (
	first_day DATE NOT NULL,
	last_day DATE,
	employee_type CHAR(1) NOT NULL
);

-- Mark John (employee #7) as an intern
UPDATE employees SET
	first_day = to_date('01.03.2009', 'dd.mm.yyyy'),
	last_day = to_date('31.05.2009', 'dd.mm.yyyy'),
	employee_type = 'I'
WHERE employee_id='7';

-- All others were here since the beginning
UPDATE employees SET
	first_day = to_date('2009-01-01', 'yyyy-mm-dd');
	employee_type = 'R'
WHERE employee_id <> '7';

Some day they hired a woman who is married for the 4th time. Her last name (Smithers-Johannson-Lisbon-Taylor) did not fit into the 20 character column. So ACME decided to change that (and the first name field as well, just in case).

ALTER TABLE employees MODIFY (
	lastname VARCHAR(100) NOT NULL,
	firstname VARCHAR(50) NOT NULL
);

After College John starts his career with a junior manager position at ACME. Of course they do not want to loose the information that John already worked here as an intern.

CREATE TABLE employments (
  employee_id INTEGER NOT NULL,
  first_day DATE NOT NULL,
  last_day DATE,
  employment_type CHAR(1) NOT NULL,
CONSTRAINT employment_pk
  PRIMARY KEY (employee_id, first_day),
CONSTRAINT fk_employee
  FOREIGN KEY (employee_id)
  REFERENCES employees(id)
) TABLESPACE hr_data;

-- copy data from employees table
INSERT INTO employments (
  employee_id,
  first_day,
  last_day,
  employment_type
)
SELECT
  id,
  first_day,
  last_day,
  employee_type
FROM employees;

-- remove obsolete columns from employees table
ALTER TABLE employees DROP COLUMN first_day;
ALTER TABLE employees DROP COLUMN last_day;
ALTER TABLE employees DROP COLUMN employee_type;