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

'''D'''ata '''D'''escription '''L'''anguage 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;
@]