Fundamentals of databases: Structured Query Language (SQL)

From Wikibooks, open books for an open world
Jump to navigation Jump to search

PAPER 2 - ⇑ Fundamentals of databases ⇑

← Database design and normalisation techniques Structured Query Language (SQL) SELECT →


Specification[edit]

Specification coverage
  • 3.10.4 Structured query language (SQL)
  • 3.10.5 Client-server databases

Introduction[edit]

Structured query language (SQL) - a specialised programming language for manipulating databases.


Structured Query Language (SQL) is a specialised programming language that is used for managing relational databases. Its functions allow users to define tables, insert, update and delete data and to carry out queries on data to produce and output subsets of the main data. In common with other programming languages, SQL works by typing lines of code.

Reserved words

When you are writing SQL you might get some unexpected errors, where a query fails to run when it doesn't appear to have any problem with it. This may be due to using a resevered word in your query. SQL has a lot of reserved words, words that have special meanings, and if you use one of these in a query it won't treat it as a field name. For example:

SELECT Username, Password FROM tblUsers

This might bring up an error as Password is a reserved word in SQL, meaning you can't use it as an attribute name. To get by this problem you might want to change your fieldnames to something a little more sensible or put the fieldname in square brackets:

SELECT Username, [Password] FROM tblUsers

There are many other reserved words out there, so be careful:

PERCENT, PLAN, PRECISION, EXISTS, PRIMARY, PRINT, PUBLIC,
BACKUP, FOREIGN, READ,FREETEXT, FROM, REFERENCES, BULK, 
FULL, RESTORE, GROUP, IDENTITY, RULE, SAVE, INDEX, SELECT, 
STATISTICS, KEY, TABLE, NATIONAL, DATABASE, UNION, DELETE, 
DISK, ON, USER, PASSWORD

Different databases have different sets of reserved words, you can find a good list here

Note: If you are not using a SQL server (for example, using MySQL with PHP) you may need to use `backticks` instead of square bracket notation.