Writing SQL Commands

In this section we briefly describe the structure of an SQL statement and the notation we
use to define the format of the various SQL constructs. An SQL statement consists of
reserved words and user-defined words. Reserved words are a fixed part of the SQL language
and have a fixed meaning. They must be spelt exactly as required and cannot be split
across lines. User-defined words are made up by the user (according to certain syntax rules)
and represent the names of various database objects such as tables, columns, views, indexes,
and so on. The words in a statement are also built according to a set of syntax rules.
Although the standard does not require it, many dialects of SQL require the use of a statement
terminator to mark the end of each SQL statement (usually the semicolon ‘;’ is used).
Most components of an SQL statement are case insensitive, which means that letters
can be typed in either upper or lower case. The one important exception to this rule is that
literal character data must be typed exactly as it appears in the database. For example, if
we store a person’s surname as ‘SMITH’ and then search for it using the string ‘Smith’,
the row will not be found.
Although SQL is free-format, an SQL statement or set of statements is more readable if
indentation and lineation are used. For example:
n each clause in a statement should begin on a new line;
n the beginning of each clause should line up with the beginning of other clauses;
n if a clause has several parts, they should each appear on a separate line and be indented
under the start of the clause to show the relationship.
Throughout this and the next chapter, we use the following extended form of the Backus
Naur Form (BNF) notation to define SQL statements:
n upper-case letters are used to represent reserved words and must be spelt exactly as shown;
n lower-case letters are used to represent user-defined words;
n a vertical bar ( | ) indicates a choice among alternatives; for example, a | b | c;
n curly braces indicate a required element; for example, {a};
n square brackets indicate an optional element; for example, [a];
n an ellipsis ( . . . ) is used to indicate optional repetition of an item zero or more times.
For example:
{a | b} (, c . . . )
means either a or b followed by zero or more repetitions of c separated by commas.
In practice, the DDL statements are used to create the database structure (that is, the tables)
and the access mechanisms (that is, what each user can legally access), and then the DML
statements are used to populate and query the tables. However, in this chapter we present
the DML before the DDL statements to reflect the importance of DML statements to the
general user. We discuss the main DDL statements in the next chapter.