Getting Started with SQL

When communicating with a database, the most common task is to retrieve data, which can be accomplished in SQL using a ‘select’ statement. In its most basic form a ‘select’ statement contains a ‘select’ clause and a ‘from’ clause.

SELECT column_name FROM table_name;

Where multiple columns need to be selected they can be separated by commas.

SELECT column_name1, column_name2, column_name3 FROM table_name;

If all the columns in a table are required, then the comma separated list can be replaced by a single ‘*’.

SELECT * FROM table_name;

The following table of data, called ‘person’, can be used to illustrate how this works.

id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
2 Simon Smith Mr 1960-04-01
3 Freida Bloggs Mrs 1970-10-12
4 Fiona Jones Miss 1985-05-19

All of the data in this table can be retrieved as follows.

SELECT id, firstname, lastname, title, dob FROM person;

As mentioned above, if all of the columns in a table are required, the comma separated list can be replaced by a ‘*’.

SELECT * FROM person;

Regardless of which of the above methods is used, the data will be retrieved in the order that it exists in the table. If it is necessary to order the data in some way, then an additional ‘order by’ clause needs to be included in the ‘select’ statement. As with the ‘select’ clause, more than one column can be used in the ‘order by’ clause, by separating them with a comma, as shown here, where the data is ordered by ‘lastname’ and then ‘firstname’.

SELECT id, firstname, lastname, title, dob FROM person ORDER BY lastname, firstname;
id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
3 Freida Bloggs Mrs 1970-10-12
4 Fiona Jones Miss 1985-05-19
2 Simon Smith Mr 1960-04-01

In order for statements in SQL to me more readable, it is common practice to start a new line for each clause in a statement. Using upper case letters for the names of the clauses, ‘select’, ‘from’ and ‘order by’ is another technique used to make a statement more readable.

SELECT id, firstname, lastname, title, dob
FROM person
ORDER BY lastname, firstname;