What Is The Purpose Of Filters In A Database

Filtering

Overview

Pedagogy: 10 min
Exercises: 10 min

Questions

• How can I select subsets of data?

Objectives

• Write queries that select records that satisfy user-specified atmospheric condition.

• Explicate the lodge in which the clauses in a query are executed.

1 of the well-nigh powerful features of a database is the ability to filter data, i.e., to select simply those records that friction match certain criteria. For instance, suppose we want to see when a item site was visited. We can select these records from the Visited table past using a WHERE clause in our query:

            SELECT * FROM Visited WHERE site = 'DR-1';                      

id	site	dated
619	DR-1	1927-02-08
622	DR-i	1927-02-10
844	DR-1	1932-03-22

The database manager executes this query in two stages. First, it checks at each row in the Visited table to see which ones satisfy the WHERE. It so uses the cavalcade names following the SELECT keyword to decide which columns to display.

This processing guild ways that we can filter records using WHERE based on values in columns that aren't so displayed:

            SELECT id FROM Visited WHERE site = 'DR-ane';                      

id
619
622
844

We can use many other Boolean operators to filter our data. For example, we tin ask for all information from the DR-1 site nerveless before 1930:

            SELECT * FROM Visited WHERE site = 'DR-ane' AND dated < '1930-01-01';                      

id	site	dated
619	DR-1	1927-02-08
622	DR-1	1927-02-10

Date Types

Most database managers have a special data type for dates. In fact, many have two: 1 for dates, such as "May 31, 1971", and ane for durations, such as "31 days". SQLite doesn't: instead, it stores dates as either text (in the ISO-8601 standard format "YYYY-MM-DD HH:MM:SS.SSSS"), real numbers (Julian days, the number of days since November 24, 4714 BCE), or integers (Unix fourth dimension, the number of seconds since midnight, January 1, 1970). If this sounds complicated, it is, but non nearly every bit complicated as figuring out historical dates in Sweden.

If nosotros desire to observe out what measurements were taken by either Lake or Roerich, we can combine the tests on their names using OR:

            SELECT * FROM Survey WHERE person = 'lake' OR person = 'roe';                      

taken	person	quant	reading
734	lake	sal	0.05
751	lake	sal	0.1
752	lake	rad	2.xix
752	lake	sal	0.09
752	lake	temp	-xvi.0
752	roe	sal	41.6
837	lake	rad	i.46
837	lake	sal	0.21
837	roe	sal	22.5
844	roe	rad	11.25

Alternatively, we can use IN to see if a value is in a specific ready:

            SELECT * FROM Survey WHERE person IN ('lake', 'roe');                      

taken	person	quant	reading
734	lake	sal	0.05
751	lake	sal	0.1
752	lake	rad	2.19
752	lake	sal	0.09
752	lake	temp	-sixteen.0
752	roe	sal	41.half dozen
837	lake	rad	1.46
837	lake	sal	0.21
837	roe	sal	22.5
844	roe	rad	eleven.25

We can combine AND with OR, just we need to exist careful about which operator is executed first. If nosotros don't use parentheses, we become this:

            SELECT * FROM Survey WHERE quant = 'sal' AND person = 'lake' OR person = 'roe';                      

taken	person	quant	reading
734	lake	sal	0.05
751	lake	sal	0.i
752	lake	sal	0.09
752	roe	sal	41.half-dozen
837	lake	sal	0.21
837	roe	sal	22.5
844	roe	rad	11.25

which is salinity measurements by Lake, and any measurement by Roerich. Nosotros probably want this instead:

            SELECT * FROM Survey WHERE quant = 'sal' AND (person = 'lake' OR person = 'roe');                      

taken	person	quant	reading
734	lake	sal	0.05
751	lake	sal	0.1
752	lake	sal	0.09
752	roe	sal	41.half-dozen
837	lake	sal	0.21
837	roe	sal	22.5

We can also filter past partial matches. For instance, if we want to know something only nigh the site names kickoff with "DR" nosotros can use the LIKE keyword. The percentage symbol acts every bit a wildcard, matching any characters in that place. It can be used at the beginning, middle, or end of the cord:

            SELECT * FROM Visited WHERE site Like 'DR%';                      

id	site	dated
619	DR-1	1927-02-08
622	DR-1	1927-02-10
734	DR-iii	1930-01-07
735	DR-3	1930-01-12
751	DR-3	1930-02-26
752	DR-3
844	DR-1	1932-03-22

Finally, we can employ DISTINCT with WHERE to requite a second level of filtering:

            SELECT DISTINCT person, quant FROM Survey WHERE person = 'lake' OR person = 'roe';                      

person	quant
lake	sal
lake	rad
lake	temp
roe	sal
roe	rad

Only remember: Singled-out is applied to the values displayed in the chosen columns, not to the entire rows as they are being processed.

Growing Queries

What we have simply done is how about people "grow" their SQL queries. Nosotros started with something uncomplicated that did role of what nosotros wanted, so added more clauses i by one, testing their effects as we went. This is a proficient strategy — in fact, for circuitous queries it's oftentimes the only strategy — but information technology depends on quick turnaround, and on united states recognizing the right answer when nosotros get it.

The best way to achieve a quick turnaround is often to put a subset of data in a temporary database and run our queries against that, or to fill up a small database with synthesized records. For example, instead of trying our queries confronting an bodily database of 20 one thousand thousand Australians, nosotros could run it confronting a sample of ten thousand, or write a modest program to generate 10 thousand random (just plausible) records and utilize that.

Fix This Query

Suppose we want to select all sites that lie within 48 degrees of the equator. Our first query is:

              SELECT * FROM Site WHERE (lat > -48) OR (lat < 48);                          

Explain why this is wrong, and rewrite the query so that it is correct.

Solution

Considering we used OR, a site on the South Pole for example will still run into the second criteria and thus be included. Instead, we want to restrict this to sites that encounter both criteria:

                SELECT * FROM Site WHERE (lat > -48) AND (lat < 48);                              

Finding Outliers

Normalized salinity readings are supposed to be betwixt 0.0 and 1.0. Write a query that selects all records from Survey with salinity values outside this range.

Solution

                SELECT * FROM Survey WHERE quant = 'sal' AND ((reading > 1.0) OR (reading < 0.0));                              

taken	person	quant	reading
752	roe	sal	41.6
837	roe	sal	22.5

Matching Patterns

Which of these expressions are true?

1. 'a' LIKE 'a'
2. 'a' LIKE '%a'
3. 'beta' LIKE '%a'
4. 'blastoff' LIKE 'a%%'
5. 'alpha' Like 'a%p%'

Solution

1. True because these are the same character.
2. Truthful considering the wildcard can match aught or more characters.
3. True because the % matches bet and the a matches the a.
4. True because the offset wildcard matches lpha and the second wildcard matches zero characters (or vice versa).
5. Truthful because the first wildcard matches l and the 2nd wildcard matches ha.

Cardinal Points

• Utilize WHERE to specify conditions that records must meet in order to be included in a query's results.

• Utilise AND, OR, and NOT to combine tests.

• Filtering is done on whole records, so conditions can utilise fields that are not actually displayed.

• Write queries incrementally.

What Is The Purpose Of Filters In A Database,

Source: https://swcarpentry.github.io/sql-novice-survey/03-filter/

Posted by: tedescobutibill79.blogspot.com

Share this post