Selecting and sorting data
Last updated on 2023-09-19 | Edit this page
- What is a query?
- How do you query databases using SQL?
- How do you retrieve unique values in SQL?
- How do you sort results in SQL?
- Understand how SQL can be used to query databases
- Understand how to build queries, using SQL keywords such as
A query is a question or request for data. For example, “How many journals does our library subscribe to?” When we query a database, we can ask the same question using a common language called Structured Query Language or SQL in what is called a statement. Some of the most useful queries - the ones we are introducing in this first section - are used to return results from a table that match specific criteria.
Let’s start by opening DB Browser for SQLite and the
doaj-article-sample database (see Setup). Choose
Browse Data and the
articles table. The
articles table contains columns or fields such as
Let’s write a SQL query that selects only the
column from the
SELECT title FROM articles;
In the first query above, we have capitalized the words
FROM because they are SQL keywords.
Even though capitalization makes no difference to the SQL interpreter,
capitalization of these SQL terms helps for readability and is therefore
considered good style. As you write and expand your own queries, it
might be helpful to pick an option, such as CamelCase, and use
that style when naming tables and columns. Some tables and columns
require capitalization and some do not. An occasional change of
capitalization for these table and column names may be needed.
SELECT Title, Authors, ISSNs, Year FROM Articles;
SELECT Title, authors, ISSNs, Year FROM articles;
If we want more information, we can add a new column to the list of
fields right after
SELECT Title, Authors, ISSNs, Year, DOI FROM articles;
Or we can select all of the columns in a table using the wildcard
SELECT * FROM articles;
There may be a situation when you need to retrieve unique records and
not multiple duplicate records. The SQL
DISTINCT keyword is
SELECT to eliminate duplicate records and fetch
only unique records. Let’s return all of the unique
in a SQL query.
SELECT DISTINCT ISSNs FROM articles;
Note, some database systems require a semicolon
each SQL statement. If we select more than one column, then the distinct
pairs of values are returned.
SELECT DISTINCT ISSNs, Day, Month, Year FROM articles;
We can also sort the results of our queries by using the keyword
ORDER BY. Let’s create a query that sorts the articles
table in ascending order by ISSNs using the
ASC keyword in
SELECT * FROM articles ORDER BY ISSNs ASC;
ASC tells us to order it in ascending order.
Instead, we can use
DESC to get the descending order
SELECT * FROM articles ORDER BY First_Author DESC;
ASC is the default, so by omitting
DESC, SQLite will sort ascending (ASC).
We can also sort on several fields at once, in different directions.
For example, we can order by
ISSNs descending and then
First_Author ascending in the same query.
SELECT * FROM articles ORDER BY ISSNs DESC, First_Author ASC;
SELECT Title, First_Author, ISSNs, Citation_Count FROM articles ORDER BY Citation_Count DESC, Title ASC;