3. More SQL Concepts¶
3.1. Chinook Database schema¶
The Chinook data model represents a digital media store, including tables for artists, albums, media tracks, invoices, and customers.
3.2. Selecting different tables from the databases¶
3.3. Sorting rows¶
The ORDER BY clause is used to sort a set of results from a query. SQLite stores data in tables in
an unspecified order. It means that table rows may or may not be in the order they were inserted into the table. If you use the
SELECT statement to query data from a table, the order of the rows in the result set is unspecified. To sort the result set, add
the ORDER BY clause to the SELECT statement like this:
ORDER BY column_name [ASC | DESC]
3.4. Data filtering¶
We have already seen some ways of archiving data, e.g., the LIMIT clause.
In the following section, we will see some more ways to filter data, specifically the WHERE, IN, BETWEEN, and LIKE clauses.
This block teaches us how to use the WHERE clause. The WHERE clause is an optional clause of the SELECT statement. It appears after the FROM clause as the following statement:
WHERE column_name [= | != | < | <= | > | >=] value
In this block, we will learn how to use the BETWEEN clause. The BETWEEN operator is a logical operator that checks
whether a value is within a range of values. If the value is in the specified range, the BETWEEN operator returns true. The
BETWEEN operator can be used in the WHERE clause of SELECT, DELETE, UPDATE, and REPLACE statements. The
following statement shows an example of the BETWEEN clause:
BETWEEN value1 AND value2
In this block, we will learn how to use the SQLite IN operator to determine whether a value matches any value in a list of values or the result of a subquery. The SQLite IN operator determines whether a value matches any value in a list or a subquery result. The syntax of the IN operator is as follows:
IN (value1, value2, ..., valueN)
In this section, we will learn about the LIKE clause. The LIKE clause is a search operator that allows you to search for a character string within another character string. The syntax of the LIKE clause is as follows:
LIKE '%value%'
3.5. Grouping of data¶
In this section, we will learn how to use the GROUP BY clause of SQLite to create a summary row set from a row set.
The GROUP BY clause is an optional clause of the SELECT statement. The GROUP BY clause groups a selected set of rows into summary rows
by values of one or more columns. The GROUP BY clause returns one row for each group. For each group, you can apply an aggregate function such as
MIN, MAX, SUM, COUNT, or AVG to provide more information about each group. The following statement illustrates the syntax of the GROUP BY clause of SQLite:
GROUP BY column1, column2, ...
Here we will learn how to use the SQLite HAVING clause to specify a filter condition for a group or aggregate.
The SQLite HAVING clause is an optional clause of the SELECT statement. The HAVING clause specifies a search condition for a group.
We often use the HAVING clause with the GROUP BY clause. The GROUP BY clause groups a set of rows into a set of summary rows or groups.
Then, the HAVING clause filters groups based on a specific condition. If we use the HAVING clause, we must include the GROUP BY clause; otherwise, an error will occur.
The following illustrates the syntax of the HAVING clause:
HAVING condition