Which Clause Should You Add To The Select Command To Filter Results From An Sql Database

Filtering

Overview

Didactics: x min
Exercises: ten min

Questions

• How can I select subsets of data?

Objectives

• Write queries that select records that satisfy user-specified conditions.

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

I of the most powerful features of a database is the power to filter information, i.e., to select but those records that match sure criteria. For example, suppose nosotros want to see when a particular site was visited. We can select these records from the Visited table by 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-1	1927-02-10
844	DR-1	1932-03-22

The database manager executes this query in ii stages. Starting time, it checks at each row in the Visited tabular array to see which ones satisfy the WHERE. It so uses the column names post-obit the SELECT keyword to determine which columns to display.

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

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

id
619
622
844

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

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

id	site	dated
619	DR-ane	1927-02-08
622	DR-1	1927-02-ten

Date Types

Nearly database managers have a special information type for dates. In fact, many have two: one for dates, such as "May 31, 1971", and one for durations, such as "31 days". SQLite doesn't: instead, information technology 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, Jan i, 1970). If this sounds complicated, information technology is, but not nearly as complicated every bit figuring out historical dates in Sweden.

If nosotros desire to find out what measurements were taken past either Lake or Roerich, we tin 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.ane
752	lake	rad	2.nineteen
752	lake	sal	0.09
752	lake	temp	-xvi.0
752	roe	sal	41.six
837	lake	rad	1.46
837	lake	sal	0.21
837	roe	sal	22.5
844	roe	rad	11.25

Alternatively, nosotros can apply IN to encounter if a value is in a specific set:

            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	-16.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	11.25

We tin combine AND with OR, but we need to be careful well-nigh which operator is executed first. If nosotros don't use parentheses, nosotros get 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.six
837	lake	sal	0.21
837	roe	sal	22.5
844	roe	rad	xi.25

which is salinity measurements by Lake, and whatever measurement by Roerich. We probably desire 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.6
837	lake	sal	0.21
837	roe	sal	22.5

We can also filter past partial matches. For case, if nosotros desire to know something just about the site names beginning with "DR" we can use the LIKE keyword. The percentage symbol acts as a wildcard, matching any characters in that place. It can be used at the outset, eye, or end of the string:

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

id	site	dated
619	DR-one	1927-02-08
622	DR-1	1927-02-10
734	DR-3	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, nosotros can use DISTINCT with WHERE to give a second level of filtering:

            SELECT Singled-out person, quant FROM Survey WHERE person = 'lake' OR person = 'roe';                      

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

But remember: Distinct is applied to the values displayed in the chosen columns, not to the unabridged rows every bit they are being candy.

Growing Queries

What nosotros accept just done is how most people "grow" their SQL queries. We started with something uncomplicated that did office of what we wanted, and then added more clauses 1 by i, testing their furnishings as we went. This is a good strategy — in fact, for complex queries it'due south often the only strategy — but it depends on quick turnaround, and on united states recognizing the correct respond when we get it.

The best mode to accomplish a quick turnaround is often to put a subset of data in a temporary database and run our queries against that, or to fill a pocket-sized database with synthesized records. For example, instead of trying our queries against an actual database of 20 million Australians, we could run it against a sample of 10 one thousand, or write a small programme to generate x thousand random (but plausible) records and employ that.

Set This Query

Suppose we want to select all sites that prevarication inside 48 degrees of the equator. Our offset query is:

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

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

Solution

Considering we used OR, a site on the Due south Pole for example will still meet the 2nd criteria and thus be included. Instead, nosotros want to restrict this to sites that meet 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 exterior 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.six
837	roe	sal	22.five

Matching Patterns

Which of these expressions are truthful?

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

Solution

1. True because these are the same character.
2. True considering the wildcard tin can friction match zippo or more characters.
3. True considering the % matches bet and the a matches the a.
4. True because the starting time wildcard matches lpha and the second wildcard matches zero characters (or vice versa).
5. True considering the starting time wildcard matches l and the 2d wildcard matches ha.

Central Points

• Utilize WHERE to specify weather condition that records must meet in club to exist included in a query'due south results.

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

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

• Write queries incrementally.

Which Clause Should You Add To The Select Command To Filter Results From An Sql Database,

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

Posted by: galindowhistamed1951.blogspot.com

Share this post