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;