SQL Guidelines

Right Align the Reserved Words

Select

SELECT last_name, first_name

FROM employee

WHERE department_id = 15

AND hire_date < SYSDATE;

Insert

INSERT INTO employee

(employee_id, … )

VALUES

(105, …);

Update

UPDATE employee

SET hire_date = SYSDATE

WHERE hire_date IS NULL

AND termination_date IS NULL

Delete

DELETE FROM employee

WHERE department_id = 15;

Don't Skimp on Line Seperators

Within clauses

• Use separate line for each expression in a SELECT list
• Place each table in a FROM clause on its own line
• Place each expression in WHERE clause on its own line

SELECT last_name,

C.name

MAX (SH.salary) best_salary_ever

FROM employee E

Company C

Salary_history SH

WHERE E.company_id = C.company_id

AND E.employee_id = SH.employee_id

AND E.hire_date > ADD_MONTHS (SYSDATE, -60);

UPDATE employee

SET hire_date = SYSDATE

Termination_date = NULL

WHERE department_id = 105;

Use Sensible Abbreviations for Table and Column Aliases

Instead of a code segment such as,

SELECT … select list …

FROM employee A

Company B

History C

Bonus D

Profile E

Sales F

WHERE A.company_id = B.company_id

AND A.employee_id = C.employee_id

AND B.company_id = F.company_id

AND A.employee_id = D.employee_id

AND B.company_id = E.company_id;

Use a code segment such as,

SELECT … select list …

FROM employee EMP

Company CO

History HIST

Bonus

Profile PROF

Sales

WHERE EMP.company_id = CO.company_id

AND EMP.employee_id = HIST.employee_id

AND CO.company_id = SALES.company_id

AND EMP.employee_id = BONUS.employee_id

AND CO.company_id = PROF.company_id;