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
-
On a scale from 1 (needs improvement) to 3 (excellent),
how would you rate this chapter?
- 1
- 2
- 3