.
 

Enterprise Systems

banner-enterprise

     

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;