SQL

This is currently one of the most popular languages to have under your belt.
SQL (pronounced sequel) was thought to stand for Structured Query Language but it apparently doesn't stand for anything. I think it's an odd combination of letters to not be an acronym.

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: