Nuts and Bolts - part 3

INSERT

The INSERT statement is composed by two elements, the attribute list and the values list. The lists are positional. The formatting should mark clearly the various elements of the query in order to make it simpler to spot the correspondence between the attribute and the value.

  • After the words INSERT INTO there is one space followed by the table’s name
  • After the table’s name there is one carriage return
  • The attributes if present are enclosed between round brackets indented one tab from the word INSERT
  • The attribute’s list indents one tab from the round brackets
  • The attributes indent one tab fro the round brackets and are separated by a carriage return
  • The word VALUES indents at the same level of INSERT and is followed by a carriage return
  • The value’s list is surrounded by round brackets indented one tab from the word VALUE
  • The values indent one tab fro the round brackets and are separated by a carriage return

UPDATE

The WHERE condition on the update is the same of the SELECT. Take a look to 1.1.1 for more informations. Just after the table’s name there is the keyword SET and the list of the updates. The formatting should reflect this particular structure.

  • After the table’s name there is one carriage return
  • The word SET indents one tab from the word UPDATE
  • The update’s list indents one tab from the word set
  • Each update is separated by a carriage return 

DELETE

The delete is the simplest of the DML as lists just the target relation and the eventual WHERE condition. The formatting is quite simple as the where condition is the only part which requires to be structured as seen in the select.

Data Definition Language

The data definition language groups all the commands defining, altering or dropping the database’s relations. Each command type follows a different syntax structure.

CREATE

The create statement is composed by the verb CREATE followed by the object type (e.g. TABLE) and the new relation’s name followed by a carriage return. The rest of the statement indents one tab from the verb CREATE.
The rules for the CREATE TABLE are the following.

  • The fields are indented one tab stop from the surrounding brackets
  • Between the field’s name and the type there is one or more tab stops
  • The number of tabs is determined by the field with the longest name
  • Between the field’s type and the optional DEFAULT there is one space
  • Between the the optional DEFAULT and the optional field’s constraint there is one space
  • If the DEFAULT is missing, between the field’s type and the field’s constraint there is one space
  • Table constraints will require the constraint name followed by one carriage return
  • The constraint type indents one tab from the word CONSTRAINT
  • Any clause indents one tab from the constraint type

 

The rules for the CREATE VIEW are the following.

  • After the view name there is one carriage return
  • The word AS indents one tab from the verb CREATE
  • The round brackets surrounding the view definition indents at the same level of the word AS
  • The view’s SELECT follows the same rules defined in the select.

ALTER

The alter statement’s only rule is very simple, all the alter actions indent by one tab fromt he ALTER verb and are separated by a carriage return.

DROP

The drop statement’s formatting is very simple. Everything is on a single line.