Let's start with a simple sql statement:-
SELECT * FROM BOOKS
This statement is based upon the assumption that you have a table called BOOKS. When you execute the statement it will return every column and every row in the table.
You might, for example, have a table with three columns, with the columns named Author, Title, Year. Like so...
| Author |
Title |
Year |
|
Jane Austen
|
Pride and Prejudice
|
1813
|
|
Joseph Conrad
|
Heart of Darkness
|
1899
|
|
J. D. Salinger
|
The Catcher in the Rye
|
1951
|
If you only wanted to see the titles of the books you would use
SELECT Title FROM BOOKS
| Title |
|
Pride and Prejudice
|
|
Heart of Darkness
|
|
The Catcher in the Rye
|
What if you didn't want to see any books published since 1900. In that case you need a WHERE clause in your sql statement.
SELECT * FROM BOOKS WHERE Year < '1900'
| Author |
Title |
Year |
|
Jane Austen
|
Pride and Prejudice
|
1813
|
|
Joseph Conrad
|
Heart of Darkness
|
1899
|
Note that the year is enclosed in single quotation marks. You will need to enclose any qualifiers in quotation marks unless they are numbers (and your table column is defined as one of the numeric data types).
You don't need to specify the WHOLE contents of a column when you are using the WHERE clause. There is a placeholder that can be used as a wildcard. This is the percent (%) symbol. This is used in conjunction with the LIKE clause.
So if, for example, you wish to find any books whose title begins with the word "Pride", you would use the following statement.
SELECT * FROM BOOKS WHERE Title LIKE 'Pride%'
| Author |
Title |
Year |
|
Jane Austen
|
Pride and Prejudice
|
1813
|
Alternatively, you might want to find all the books whose title ends with the word "Darkness". In this case you just place the % sign BEFORE the text you are looking for. Like So:-
SELECT * FROM BOOKS WHERE Title LIKE '%Darkness'
| Author |
Title |
Year |
|
Joseph Conrad
|
Heart of Darkness
|
1899
|
You can also combine the two and find rows with a specific piece of text anywhere within a single column:-
SELECT * FROM BOOKS WHERE Title LIKE '%Catcher%'
| Author |
Title |
Year |
|
J. D. Salinger
|
The Catcher in the Rye
|
1951
|
So far we have seen how to select which particular rows you want to retrieve from a table.
Next we will see how we can present the rows that are returned in a specific order.
To do this we add an "ORDER BY" clause to the end of or statement.
SELECT * FROM BOOKS ORDER BY Year
| Author |
Title |
Year |
|
Jane Austen
|
Pride and Prejudice
|
1813
|
|
Joseph Conrad
|
Heart of Darkness
|
1899
|
|
J. D. Salinger
|
The Catcher in the Rye
|
1951
|
The default is to sort in ASCENDING order of the chosen column. To sort in DESCENDING order, you need to add the DESC clause to the specific column. It is, of course, possible to make the ascending order explicit by adding the ASC clause to the column.
SELECT * FROM BOOKS ORDER BY Year DESC
| Author |
Title |
Year |
|
J. D. Salinger
|
The Catcher in the Rye
|
1951
|
|
Joseph Conrad
|
Heart of Darkness
|
1899
|
|
Jane Austen
|
Pride and Prejudice
|
1813
|
It is common practice to have the column that is the first column in the sort order at the begining, or far left, of the list of rows returned.
To do this you will need to eschew the use of the asterisk (*) and specify which columns you require and therefore the order in which the columns appear.
The following statement will return the year of publication at the begining of the result set.
SELECT Year, Author, Title FROM BOOKS ORDER BY Year ASC
| Year |
Author |
Title |
|
1813
|
Jane Austen
|
Pride and Prejudice
|
|
1899
|
Joseph Conrad
|
Heart of Darkness
|
|
1951
|
J. D. Salinger
|
The Catcher in the Rye
|
This is the end of this taster of sql. I hope you have found it useful.
Document:
URL:
Last Update: