Alignments

• Main keywords in select/insert/delete/update statement

  • Left align:

  DELETE FROM job_history jh  
  WHERE  employee_id = (SELECT employee_id  
                        FROM   employee e  
                        WHERE  jh.employee_id = e.employee_id  
                               AND start_date = (SELECT Min(start_date)  
                                                 FROM   job_history jh  
                                                 WHERE  jh.employee_id = e.employee_id)  
                               AND 5 > (SELECT Count( * )  
                                        FROM   job_history jh  
                                        WHERE  jh.employee_id = e.employee_id  
                                        GROUP  BY employee_id  
                                        HAVING Count( * ) >= 4)); 

  INSERT INTO employees 
              (employee_id, 
               first_name, 
               department_id)  
  VALUES     (113, 
              'Louis', 
              100); 
              

  UPDATE employees  
  SET    department_id = 70  
  WHERE  employee_id = 113;  

  • Right align:

  DELETE FROM job_history jh  
   WHERE employee_id = (SELECT employee_id  
                          FROM employee e  
                         WHERE jh.employee_id = e.employee_id  
                               AND start_date = (SELECT Min(start_date)  
                                                   FROM job_history jh  
                                                  WHERE jh.employee_id = e.employee_id)  
                               AND 5 > (SELECT Count( * )  
                                          FROM job_history jh  
                                         WHERE jh.employee_id = e.employee_id  
                                         GROUP BY employee_id  
                                        HAVING Count( * ) >= 4));  
  

  INSERT INTO employees 
              (employee_id, 
               first_name, 
               department_id)  
       VALUES(113, 
              'Louis', 
              100);  

  UPDATE employees  
     SET department_id = 70  
   WHERE employee_id = 113; 

• Select list

  • Column alias

    SELECT DISTINCT 
           p.name      AS product, 
           p.listprice AS 'List Price', 
           p.discount  AS 'discount' 
    FROM   production.product p 

• Join clause: ON keyword

  • Left align with JOIN keyword

    SELECT DISTINCT 
           p.name      AS product, 
           p.listprice AS 'List Price', 
           p.discount  AS 'discount' 
    FROM   production.product p 
    JOIN   production.productsubcategory s 
    ON     p.productsubcategoryid = s.productsubcategoryid 
    AND    p.id = s.id 

  • Right align with JOIN keyword

    SELECT DISTINCT 
           p.name      AS product, 
           p.listprice AS 'List Price', 
           p.discount  AS 'discount' 
    FROM   production.product p 
    JOIN   production.productsubcategory s 
      ON   p.productsubcategoryid = s.productsubcategoryid 
     AND   p.id = s.id 

• Logical condition operator: AND, OR, NOT

  • Left align with WHERE keyword

    SELECT DISTINCT 
           p.name      AS product, 
           p.listprice AS 'List Price', 
           p.discount  AS 'discount' 
    FROM   production.product p 
    JOIN   production.productsubcategory s 
    ON     p.productsubcategoryid = s.productsubcategoryid 
    AND    p.id = s.id 
    WHERE  s.name LIKE @product 
    AND    p.listprice < @maxprice 
    AND    EXISTS(SELECT * 
                  FROM   apps.per_assignments_f asg 
                  WHERE  asg.assignment_type NOT IN ( 'B' ) 
                  AND    b > 1 
                  OR     c > 2) 
    OR     p.price     = 100; 

  • Right align with WHERE keyword

    SELECT DISTINCT 
           p.name      AS product, 
           p.listprice AS 'List Price', 
           p.discount  AS 'discount' 
    FROM   production.product p 
    JOIN   production.productsubcategory s 
      ON p.productsubcategoryid = s.productsubcategoryid 
     AND p.id = s.id 
    WHERE  s.name LIKE @product 
       AND p.listprice < @maxprice 
       AND EXISTS(SELECT * 
                  FROM   apps.per_assignments_f asg 
                  WHERE  asg.assignment_type NOT IN ( 'B' ) 
                     AND b > 1 
                      OR c > 2) 
        OR p.price     = 100;