Searching and sorting data
One of the most significant advantages of sorting information in databases is that SQL provides us with an easy to use syntax to search and sort data. Therefore, we do not need to need to think about how to implement searching and sorting algorithms - SQL is a declarative language, implementation details are handled for us and we are left to focus on declaring what we want to find and how we want it to be organised.
To search for particular data the
select statement is used. An example of a
select statement might be:
SELECT * FROM Product
This statement would return all of the products that are stored. Notice that an asterisk is used as a wildcard to denote that all fields of each product should be returned.
Constraining the search
Normally we want to constraint the search in someway, this is quite straightforward:
1 2 3
SELECT * FROM Product WHERE Price > 3.25
This statement would return all products that have a price higher than 3.25.
We can constraint further by chaining expressions together:
1 2 3
SELECT * FROM Product WHERE Price > 3.25 AND ProductID > 10
Likewise, we can specify which fields we want to include in our results:
1 2 3
SELECT Name, Price FROM Product WHERE Price > 3.25 AND ProductID > 10
This statement would return only the name and price of each product that had a price higher than 3.25 and an id greater than 10.
Searching with Python
When working in Python we would construct our select statement using qmarks:
1 2 3
SELECT Name, Price FROM Product WHERE Price > ? AND ProductID > ?
This would enable us to reuse the same select statement with different values. The video below demonstrates how to do this in Python:
Use the above video to create a select statement and then query the database.
Another important ability of databases is that they can sort data easily. We can improve select statements by adding an additional clause to sort data in a particular way:
1 2 3 4
SELECT Name, Price FROM Product WHERE Price > ? AND ProductID > ? ORDER BY Name ASC
This means that the results are presented in alphabetical order from A to Z (ascending order). We can also order on multiple fields and in descending order (Z to A) as well:
1 2 3 4
SELECT Name, Price FROM Product WHERE Price > ? AND ProductID > ? ORDER BY Name ASC, Price DESC
Currently there is little reason to search or sort the coffee shop database as it contains very little data. Normally a database would have a lot of data and it would be difficult to find the information by just looking at the various tables.
To understand how useful searching and sorting is we will look at an existing database of that contains information about movies.
We are going to use an existing database contain information about movies to investigate simple
SELECT statements. Download the database file and the associated worksheet:
Once you have both of the files you should follow the instructions below:
- Open the Movie database using the SQLite Inspector
- For each of the screenshots on the worksheet work out what SQL statement is required to produce the given output
- 1. Introduction to databases
- 2. Creating the data model
- 3. Inserting data
- 4. Updating and deleting data
- 5. Searching and sorting data
- 6. Product Management Menu
- 7. Introduction to relational databases
- 8. Creating a data model with relationships
- 9. Referential integrity
- 10. Completing the coffee shop