Databases · Lesson 4.1
When both must be true
So WHERE XP greater than or equal to 40 kept five learners.
Ben's 40 and Eli's 40 came back too, because 40 is equal to 40.
Now say the screen wants the learners from 40 to 100 XP.
They've started, but they aren't far in yet.
But that condition also keeps Ada's 120 and Dee's 300.
So how do you keep only the values from 40 to 100?
Say you set Amazon's price slider for headphones from $20 to $50.
Then a pair must pass two conditions.
The first is price greater than or equal to 20.
The second is price less than or equal to 50.
Amazon's database might join the two with AND.
AND means a row is kept only if it passes both conditions.
The Basic pair, at $15, fails the first condition, so it's skipped.
The Pro pair, at $80, fails the second, so it's skipped too.
Sport at $20, Studio at $40 and Max at $50 pass both, so they're kept.
Google Flights lets you pick flights that leave from 9 am to 12 pm.
Say its database asks for hour greater than or equal to 9.
And it joins hour less than or equal to 12 with AND.
United, at hour 7, fails the first condition, so it's skipped.
JetBlue, at hour 15, fails the second, so it's skipped too.
Delta, at 10, and American, at 12, pass both, so they're kept.
Back on Simplified, the screen keeps that first condition.
And it joins XP less than or equal to 100 with AND.
Cy's 0 fails the first condition, so Cy is skipped.
Ada's 120 and Dee's 300 fail the second, so they're skipped too.
Ben's 40, Eli's 40 and Fay's 75 pass both, so they're kept.
So with AND, one failed condition is enough to skip a row.
Simplified's users table holds six learners.
Ada has 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.
Its server sends Postgres SELECT name FROM users WHERE xp >= 40 AND xp < 120;.
How many rows pass both conditions?
Explanation
First, AND keeps a row only if it passes xp >= 40 and xp < 120.
Then, Ben's 40, Eli's 40 and Fay's 75 pass both.
Cy's 0 fails the first, and Ada's 120 and Dee's 300 fail the second.
Therefore, 3 rows pass: Ben, Eli and Fay.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp > 50 AND xp < 200;.
How many rows does the result have?
Databases · Lesson 4.2
Asking for a range
So the analytics screen joined two conditions with AND, and kept Ben, Eli and Fay.
But it names XP twice, just to say one range.
So is there a shorter way to ask for a range?
You can write XP BETWEEN 40 AND 100 instead.
BETWEEN keeps the values from one number to another, both ends included.
Its 40 stands for XP greater than or equal to 40.
And its 100 stands for XP less than or equal to 100.
So Amazon's database might send price BETWEEN 20 AND 50.
The Sport pair, at exactly $20, sits on the bottom end, so it's kept.
The Max pair, at exactly $50, sits on the top end, so it's kept too.
The Studio, at $40, is inside, so it's kept.
The Basic and the Pro are outside, so they're skipped.
So three pairs come back.
YouTube's search filters can pick videos that run 4 to 20 minutes.
Say its database asks for minutes BETWEEN 4 AND 20.
The 4-minute recipe and the 20-minute tutorial sit on the ends, so they're kept.
The 12-minute vlog is inside, so it's kept too.
The 3-minute trailer and the 45-minute podcast are outside, so they're skipped.
So three videos come back.
Back on Simplified, the screen can send XP BETWEEN 40 AND 100.
Ben's 40 and Eli's 40 sit on the bottom end, so they're kept.
Fay's 75 is inside, so it's kept too.
Cy's 0, Ada's 120 and Dee's 300 are outside, so they're skipped.
So the same three learners come back.
So BETWEEN keeps a value that sits right on either end.
Simplified's users table holds six learners.
Ada has 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.
Its server sends Postgres SELECT name FROM users WHERE xp BETWEEN 40 AND 120;.
How many rows pass the condition?
Explanation
First, BETWEEN 40 AND 120 keeps xp from 40 to 120, both ends included.
Then, Ada's 120, Ben's 40, Eli's 40 and Fay's 75 are in that range.
Cy's 0 and Dee's 300 aren't.
Therefore, 4 rows pass: Ada, Ben, Eli and Fay.
Say Amazon's database gets SELECT name FROM headphones WHERE price BETWEEN 40 AND 120;. One pair of headphones costs exactly $40.
Is it in the result?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp BETWEEN 0 AND 40;.
How many rows does the result have?
Say Uber's database keeps each trip's length in a miles column, and gets WHERE miles BETWEEN 2 AND 10.
Which condition keeps exactly the same rows?
Say Instagram's database keeps each account's follower count in a followers column, and gets WHERE followers BETWEEN 1000 AND 5000.
Which of these accounts passes?
Say Google Flights' database keeps each flight's departure hour, from 0 to 23. Five flights leave at hours 8, 9, 11, 12 and 13. Its database gets WHERE hour BETWEEN 9 AND 12.
How many of these flights pass?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp BETWEEN 121 AND 299;.
How many rows does the result have?
Databases · Lesson 4.3
When either will do
So XP BETWEEN 40 AND 100 kept the learners in the middle: Ben, Eli and Fay.
Now the screen wants the two ends: those who've barely started, and those furthest along.
That's under 40 XP, or over 100.
But XP less than 40 AND XP greater than 100 keeps no one.
Because no XP can be under 40 and over 100 at once.
So how do you keep a row that passes either condition?
You join the conditions with OR instead.
OR means a row is kept if it passes either condition, or both.
Cy's 0 passes XP less than 40, so Cy is kept.
Ada's 120 and Dee's 300 pass XP greater than 100, so they're kept too.
Ben's 40, Eli's 40 and Fay's 75 pass neither, so they're skipped.
So three learners come back: Ada, Cy and Dee.
Say you open Netflix, happy to watch a comedy or a drama.
Its query might say genre equals Comedy OR genre equals Drama.
The Crown, a drama, passes the second, so it's kept.
Emily in Paris, a comedy, passes the first, so it's kept.
Stranger Things and Squid Game pass neither, so they're skipped.
So two shows come back.
Say Amazon shows a row of picks: any Sony pair, or any pair under $50.
Its database might send brand equals Sony OR price less than 50.
The XM5, a Sony, passes the first, so it's kept.
The Tune 510, at $25, passes the second, so it's kept.
The CH-520, a Sony at $40, passes both, so it's kept.
The Solo 4, a Beats at $200, passes neither, so it's skipped.
So three pairs come back, and the CH-520 comes back just once.
So with OR, passing any one condition is enough to keep a row.
Simplified's users table holds six learners.
Ada has 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.
Its server sends Postgres SELECT name FROM users WHERE xp = 40 OR xp = 75;.
How many rows pass the condition?
Explanation
First, OR keeps a row if it passes xp = 40 or xp = 75.
Then, Ben's 40 and Eli's 40 pass the first, and Fay's 75 the second.
Ada, Cy and Dee pass neither.
Therefore, 3 rows pass: Ben, Eli and Fay.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE name = 'Ben' OR xp = 40;.
How many rows does the result have?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp <= 40 OR xp >= 300;.
How many rows does the result have?
Say Spotify's database keeps one artist for each song, in an artist column. You want one list with every Taylor Swift song and every Drake song.
Which condition keeps them?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp = 0 OR xp >= 120;.
How many rows does the result have?
Say Spotify's songs table holds three songs: Love Story by Taylor Swift, Shake It Off by Taylor Swift, and Crazy in Love by Beyoncé. Its database gets SELECT title FROM songs WHERE artist = 'Taylor Swift' OR title = 'Love Story';.
How many rows does the result have?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp < 40 OR xp > 300;.
How many rows does the result have?
Databases · Lesson 4.4
Matching a list of values
So Amazon's picks kept any pair that passed either condition.
Now tick Sony, Bose and JBL on Amazon's headphones page.
With OR, you'd write one condition for each brand: Sony, Bose and JBL.
But it writes brand three times, and grows with every brand you tick.
So is there a shorter way to match a list of values?
IN gives a list of values, and a row passes if it equals any one of them.
So Amazon's database might send brand IN Sony, Bose and JBL.
Each value sits in its own quotes, with commas between them.
The XM5, the QC Ultra and the Tune 510 have brands in the list, so they're kept.
The Solo 4 is a Beats, which isn't in the list, so it's skipped.
So three pairs come back.
And Netflix's database could send genre IN Comedy and Drama.
The Crown's Drama and Emily in Paris's Comedy are in the list, so both are kept.
Stranger Things and Squid Game have other genres, so they're skipped.
So two shows come back.
On Simplified, say a mentor wants to check on two learners, Ada and Dee.
The analytics screen could send name IN Ada and Dee.
Ada's and Dee's names are in the list, so their rows are kept.
Ben, Cy, Eli and Fay aren't, so their rows are skipped.
So two learners come back: Ada and Dee.
So the list works just like OR.
Ada in the list stands for name equals Ada.
And Dee stands for name equals Dee.
Simplified's users table holds six learners.
Ada has 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.
Its server sends Postgres SELECT name FROM users WHERE xp IN (0, 300);.
How many rows pass the condition?
Explanation
First, xp IN (0, 300) passes a row whose xp equals 0 or equals 300.
Then, Cy has 0 and Dee has 300, and no one else has either.
Therefore, 2 rows pass: Cy and Dee.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp IN (40, 100);.
How many rows does the result have?
Simplified's users table holds six learners: Ada, Ben, Cy, Dee, Eli and Fay. No learner is called Zed. Its server sends Postgres SELECT name FROM users WHERE name IN ('Ada', 'Zed');.
How many rows does the result have?
Say Netflix's database keeps one genre for each show, and gets WHERE genre IN ('Comedy', 'Drama', 'Horror').
Which condition keeps exactly the same rows?
Say Uber's database keeps the city of each trip in a city column. You want one list with every trip in Boston and every trip in Chicago.
Which condition keeps them?
Say Amazon's headphones table holds three pairs: the XM5 by Sony, the Solo 4 by Beats, and the QC Ultra by Bose. Its database gets WHERE brand IN ('Sony', 'Bose').
Which pairs come back?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name FROM users WHERE xp IN (0, 40) AND name <> 'Eli';.
How many rows does the result have?