7. SQLs and Pearls

You can find databases in the form of contacts list and shopping websites. In library and information management systems, they can be used for storing and retrieving records of, for example, books, journal articles, images and artefacts and where they're located. There are off-the-shelf packages but they tend to restrict how you can manipulate the information. A relational database table can look like this:

Table: Books

Auth_id	Author	Year	Title
001	Russell, B	1912	The problems of philosophy
002	Wollstonecraft, M	1792	A vindication of the rights of women
003	Luther King, M	1964	Why we can't wait
004	Gingrich, N	2008	Real change: from the world that fails to the world that works

Table: Publishers

Pub_id	Company_name	Address	Auth_id
01	Oxford University Press	Oxford	001
02	Prometheus Books	Buffalo, NY	002
03	Harpers and Row	New York	003
04	Regnery Publishers	Washington DC	004

Structured Query Language (SQL) is used in relational database management systems (RDBMS), which was introduced by E F Codd in the 1970s (see his obituary). A query is based on the following commands:

• SELECT - what information you wish to retrieve
• FROM - from which table(s) you wish to retrieve the information
• WHERE - the conditions of the search

A basic search of the table above entitled "Books" could look like this:

SELECT Auth_id, Author, Year, Title FROM Books WHERE Author LIKE 'Russ%' \g

Or you could use * to select all columns:

 SELECT * FROM Books WHERE Author LIKE 'Russ%' \g

This shows that you want to retrieve details from the Books table where the author has "Russ" in his/her name. The % sign is a wildcard and is useful if, for example, you are unsure of the spelling or you don't know if the database uses American or UK English spelling.

The search becomes slightly complicated if you want to obtain one set of data from two or more tables. Taking the two tables above, you can see that there is one column that is common in both: Auth_id. This is called a primary key and can be used to join the two tables to establish a relationship.

If you want to search for titles (from Books table) that are published by Oxford University Press (from Publishers table), you can use the following command:

SELECT Books.Title FROM Books, Publishers WHERE Publishers.Company_name = 'Oxford University Press' AND Publishers.Auth.id = Books.Auth.id \g

This indicates that you are searching for the titles contained in the Books table and cross-referencing it with the Publishers table using the Auth.id column (which is common to both tables) to find titles published by Oxford University Press. \g or ; executes the query.

A quick reference is available on the W3Schools website.