How To Filter Rows With Characters In Sql
Filtering
Overview
Teaching: x min
Exercises: 10 minQuestions
How tin I select subsets of data?
Objectives
Write queries that select records that satisfy user-specified conditions.
Explain the order in which the clauses in a query are executed.
One of the nigh powerful features of a database is the ability to filter data, i.eastward., to select but those records that friction match certain criteria. For case, suppose nosotros want to run into when a item site was visited. Nosotros 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-one | 1927-02-08 |
622 | DR-1 | 1927-02-10 |
844 | DR-1 | 1932-03-22 |
The database manager executes this query in two stages. Beginning, it checks at each row in the Visited
table to see which ones satisfy the WHERE
. It then uses the column names following the SELECT
keyword to determine which columns to display.
This processing guild means that we can filter records using WHERE
based on values in columns that aren't then displayed:
SELECT id FROM Visited WHERE site = 'DR-1';
id |
---|
619 |
622 |
844 |
Nosotros tin utilize many other Boolean operators to filter our data. For case, nosotros 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-1 | 1927-02-08 |
622 | DR-one | 1927-02-10 |
Date Types
Near database managers take a special data type for dates. In fact, many have two: ane for dates, such every bit "May 31, 1971", and 1 for durations, such as "31 days". SQLite doesn't: instead, information technology stores dates equally 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 time, the number of seconds since midnight, January 1, 1970). If this sounds complicated, information technology is, merely not about as complicated as figuring out historical dates in Sweden.
If nosotros want to find out what measurements were taken past 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.19 |
752 | lake | sal | 0.09 |
752 | lake | temp | -sixteen.0 |
752 | roe | sal | 41.6 |
837 | lake | rad | 1.46 |
837 | lake | sal | 0.21 |
837 | roe | sal | 22.5 |
844 | roe | rad | xi.25 |
Alternatively, we tin utilise IN
to see 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.6 |
837 | lake | rad | 1.46 |
837 | lake | sal | 0.21 |
837 | roe | sal | 22.v |
844 | roe | rad | 11.25 |
We can combine AND
with OR
, only we need to be careful virtually which operator is executed first. If nosotros don't apply parentheses, we go 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.ane |
752 | lake | sal | 0.09 |
752 | roe | sal | 41.6 |
837 | lake | sal | 0.21 |
837 | roe | sal | 22.v |
844 | roe | rad | 11.25 |
which is salinity measurements past Lake, and any measurement by Roerich. We 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.vi |
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 just about the site names beginning with "DR" we tin use the LIKE
keyword. The pct symbol acts as a wildcard, matching whatever characters in that place. It tin be used at the first, centre, or terminate 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-three | 1930-01-12 |
751 | DR-three | 1930-02-26 |
752 | DR-3 | |
844 | DR-ane | 1932-03-22 |
Finally, we can employ Singled-out
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 |
Merely remember: DISTINCT
is applied to the values displayed in the called columns, not to the entire rows as they are being processed.
Growing Queries
What we accept only done is how near people "grow" their SQL queries. We started with something elementary that did role of what we wanted, then added more clauses 1 by one, testing their effects as nosotros went. This is a good strategy — in fact, for complex queries it'southward often the only strategy — only information technology depends on quick turnaround, and on us recognizing the right answer when we get it.
The best style 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 pocket-sized database with synthesized records. For example, instead of trying our queries against an actual database of 20 million Australians, we could run information technology against a sample of ten thousand, or write a small program to generate 10 thousand random (but plausible) records and use that.
Gear up This Query
Suppose we want to select all sites that prevarication within 48 degrees of the equator. Our showtime 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 nosotros used
OR
, a site on the South Pole for example will even so meet the 2nd criteria and thus be included. Instead, we want to restrict this to sites that come across both criteria:SELECT * FROM Site WHERE (lat > -48) AND (lat < 48);
Finding Outliers
Normalized salinity readings are supposed to be between 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?
'a' Like 'a'
'a' Similar '%a'
'beta' LIKE '%a'
'blastoff' LIKE 'a%%'
'alpha' Like 'a%p%'
Solution
- Truthful because these are the same grapheme.
- Truthful because the wildcard can match zero or more characters.
- True because the
%
matchesbet
and thea
matches thea
.- True because the first wildcard matches
lpha
and the 2nd wildcard matches zero characters (or vice versa).- Truthful because the showtime wildcard matches
l
and the second wildcard matchesha
.
Key Points
Utilise WHERE to specify conditions that records must encounter in order to be included in a query'southward results.
Use AND, OR, and NOT to combine tests.
Filtering is washed on whole records, and so conditions can use fields that are not actually displayed.
Write queries incrementally.
How To Filter Rows With Characters In Sql,
Source: https://swcarpentry.github.io/sql-novice-survey/03-filter/
Posted by: harrispresucest.blogspot.com
0 Response to "How To Filter Rows With Characters In Sql"
Post a Comment