For clarity we can consider SQL to have three syntax terms.
- Identifiers – names that uniquely identify something in the database, a table or field for example.
- Literal – an actual value
- Keyword – a word that is reserved by and has some meaning to the database
For example here is an SQL statement.
Though our example is on one line we can write an SQL statement over several lines if we wish and is common practice when grouping a complex query into its related parts. Because of this SQL uses a semi-colon (;) to denote the end of a statement. For better clarity the statement above might be laid out.
|WHERE staff_id = ‘012453’;|
Syntax Rule – SQL statements must end in a semi-colon (;). Layout Convention – group individual parts or clauses of a statement on their own line.
Though SQL is not case sensitive it is common practice to write all keywords in uppercase and identifiers lowercase. Identifiers are not allowed to have spaces.
Syntax Rule – Identifiers must not have spaces.
- can include letters and numbers but must begin with a letter.
- cannot include spaces.
- cannot be a keywords used in SQL.
When entering a literal you should surround the literal with single quotes (‘). This practice ensures then if a space is within the literal it is not seen as the next element of the query.
Syntax Rule – Literals should be surrounded by single quotes (‘).
For the practical application of SQL we are going to use MySQL as our Database. MySQL is an open source Relation Database Management System (RDBMS) which means it is free. Though there are a number of free databases including the popular Oracle, MySQL is easily downloaded and installed with little setup requirements, is robust, fast and very scalable and has lots of books and on line material for reference.
Though these are winning factors, The main reason for using MySQL over Oracle is its closeness to the ANSI standard. Unlike Oracle, which tends to do things in an Oracle like way, MySQL for the purposes of the SQL we will be attempting is ANSi standard and as such can be applied to any other Relational Database that follows the same standard. In other words, ‘Learn Once – Apply Anywhere’.