Database:Retrieving rows of data - SQL SELECT
- 1 Meta
- 2 Full frontal - Code examples up-front!
- 3 Introduction
- 4 Links
Expand using link to the right to the meta section with instructor's notes.
This chapter has three slides, one of which is the main one, 01_Retrieving_data.pdf, about the SQL select statement. The other two slides are optional extra lectures with some more detailed and in-depth contexts: 02_SQL_-_WHERE_clause.pdf and 03_Extra-lecture-Create-table.pdf .
This chapter deals with the SQL SELECT statement for retrieving rows of data from a table. Since the students haven’t gotten familiar with the SQLite3 DBMS yet, the questions/exercises don’t require having installed the database and engine yet. After the next chapter, where we introduce the SQLite DBMS, the exercises in this chapter can be revisited and done using the software and database.
Expect this lecture to take at least 30 minutes for the main lecture, and if you opt for the two extra lectures, an additional 30 minutes should suffice. We recommend you to review the exercises/questions (including the suggested solutions) and to watch the video. If you are unsure about the timing, write some additional exercises/questions, just in case you have time over after the lecture is finished.
The main terms and concepts covered in this lecture are:
- How is data organised in a database?
- What is a table?
- What are types?
- SELECT statement (SQL)
- WHERE clause - specifying criteria
- ORDER BY
- (wildcard for “all columns”) - stress that the * really means “all columns” and nothing else
- Boolean expressions with AND, OR, comparisons
- The LIKE operator (might be good to mention that it is case insensitive in SQLite except for Swedish and other foreign characters like åäö vs ÅÄÖ - which you could argue almost is a bug)
- The simple example table is not an example of a well-designed table, but it is an example of a simple table suitable for an introduction
- Mention that databases usually have many more than one table, but we focus on one table at the time in the beginning, to keep things simple
- The type system of SQLite is a little special - read up on it (and refer interested students to) the type system on the SQLite documentation site
- PRIMARY KEY isn’t actually a constraint, but a modifier - however, it “comes with” an implicit UNIQUE constraint
- NULL is mentioned briefly as “A field can have either a value of a type or the special value NULL” - refer to a coming lecture with more about NULL (The authors have seen that NULL is something that the students don’t get intuitively)
- We use a BNF-like meta syntax like:
SELECT column[,column]* FROM table [WHERE criteria];
- Explain that this is a formal syntax about the SQL syntax and that [ ] means “optionally followed by” and * means “zero or more times” etc. If the students don’t like or understand this meta syntax, show or refer them to the graphical syntax on the SQLite online documentation - which we usually describe as a “car race track” where you can follow a “track” with arrows to see what parts a syntax has and in what order.
- Main point here is that the
*in the meta syntax is not a verbatim * like the one in SQL meaning “every column”.
- A think some students struggle to understand is using a complex WHERE condition on the same column, like e.g. [...] WHERE name = "William" OR name = "Bill"
- The surprising thing for some students is that you can’t say (like in natural languages) [...] WHERE name = "William" OR "Bill" since in formal languages such as SQL, the operands of the OR operator need to be both of type boolean (true/false).
Close this chapter/lecture by telling the students to install SQLite3 (and preferably also bash if they don’t have it yet - e.g. Cygwin for Windows users, unless they use Bash for windows or similar) - From now on, the course material asumes that they have installed the environment for doing the exercises etc
Full frontal - Code examples up-front!
Here's a small code example related to this topic. You are not expected to understand it right-away. It is here just to give you some code examples which will be explained during the lectures and exercises, so that you know what it looks like.
SELECT name, price, alcohol, volume FROM product WHERE name LIKE '%loch lomond%' AND price > 500.0; name price alcohol volume ------------------------------- ------- ------- ------- Loch Lomond Inchmurrin 12 Years 559.0 46.0 700 Loch Lomond Càrn Mòr 6 Years 628.0 46.0 700
This chapter deals with the SQL SELECT statement for retrieving rows of data from a table.
SELECT is a statement in the SQL language for retrieving data from one or more tables in a database. You can fetch all data or or some data using a criteria specified with the so called WHERE clause.
The SELECT statement allows for application to retrieve data they need according to the criteria specified in the WHERE clause. An example could be a search function in a web store. The user enters some search string and ticks some boxes. Let's say the user searches for "loch lomond" and ticks a box saying "high price". Now, if high price means "more than 500 SEK" (we're talking fine whiskey here!), then the web application could translate the query to the following SQL:
SELECT name, price, alcohol, volume FROM product WHERE name LIKE '%loch lomond%' AND price > 500.0;
The database would then retrieve all products matching both the name (loch lomond) and the price criteria (price above 500 SEK). The WHERE clause consists of the keyword
WHERE followed by a boolean expression (something which is either true or false) like
name LIKE '%loch lomond%' AND price > 500.0. The example shows a compound boolean expression using the operator
AND which means that both operands must evaluate to true, for the whole expression to evaluate to true (both the name must match and the price condition must match).
See the videos and presentation, read the pages linked to below (under External links) and move on to the exercises in the next chapter.
Some notes on the WHERE clause
We've seen over the years that students have a difficulty understanding compound WHERE clauses with AND, OR etc. The problems seems to stem from the fact that our natural language has simplified logical statements using such connectives. For instance, we'd say:
All cars whose colors are red or blue...
This is not the way we express a predicate (a statement about the world) in a formal language. In an SQL WHERE clause, we'd need to repeat what is red or blue. This is because a predicate in a WHERE clause, for instance, needs to have a truth value (true or false). If the predicate is compound, like when using OR, both operands connected by the OR needs to have a truth value:
... WHERE color = 'Red' OR color = 'Blue';
Note that the OR connects two claims; that the color is 'Red' or that the color is 'Blue'. We cannot say:
color = 'Red' OR 'Blue'
The first part works as a predicate:
color = 'Red'. But the part after the OR doesn't work, since
'Blue' isn't a predicate in itself. We need to say
color = 'Blue' to fit after the OR.
Please also make a note of the fact that AND, OR, and, NOT all have boolean (true/false) operands. You cannot use AND as a part of any other part of an SQL statement than where you need a boolean value. You cannot, for instance, write:
SELECT name AND email FROM user;
Nor can you write:
UPDATE user SET name='Bengt' AND firstname.lastname@example.org';
Both of the above are examples of when you are using AND in the wrong way in place of the correct syntax with a comma between columns.
Swedish video lectures
English video lectures
Extra lectures (English)
This video lectures were added due to students' problems with the written exam 2018. We recommend that you see them.
- Databases - WHERE clause (Full playlist) | Databases - where 1/2 | 2/2 | SQL WHERE clause (PDF)
- Databases - create statement (eng. video) | CREATE TABLE syntax (PDF)
- No files for this chapter
We expect you to read the following pages:
- SQLite.org Datatypes In SQLite Version 3
- W3 schools SELECT
- Zetcode SELECT
- Wikibooks - Structured_Query_Language/Snippets (The basic syntax is enough for this course) Note that you have to refer to the SQLite3 manual to see what constructs are supported!
- SQLite3 manual SELECT( in particular, read SELECT_CORE )