Before you keep reading...

Runestone Academy can only continue if we get support from individuals like you. As a student you are well aware of the high cost of textbooks. Our mission is to provide great books to you for free, but we ask that you consider a $10 donation, more if you can or less if $10 is a burden.

Support Runestone Academy Today

9.5. Filtering

We’ve seen how to look only at certain columns of the table but it is often useful to only look at certain rows in a table. For example, we could want to look only at the bike trips which are at least a certain number of minutes. Let’s say you’re only interested in bike trips of 60 minutes or more:

SELECT member_type, start_date, duration FROM trip_data WHERE duration >= 3600 LIMIT 10

Before you keep reading...

Making great stuff takes time and $$. If you appreciate the book you are reading now and want to keep quality materials free for other students please consider a donation to Runestone Academy. We ask that you consider a $10 donation, but if you can give more thats great, if $10 is too much for your budget we would be happy with whatever you can afford as a show of support.

Support Runestone Academy Today

It’s also possible to filter by multiple criteria. For example to look at only bike trips which are 60 minutes or more and only the subscriber type of Member:

SELECT member_type, start_date, duration FROM trip_data WHERE duration >= 3600 AND member_type = "Member" LIMIT 10

9.5.1. Practice Exercises

select * from trip_data where bike_number = 'W01274' and duration < 900

Question 2

select end_station, duration
from trip_data
where start_station = 31111 and duration > 8 * 60 * 60

Question 3

select count(*)
from trip_data
where start_station = 31111 and duration > 8 * 60 * 60 and  end_station = 31111

Figure out how to get all the trips on the bike with id of W01274 and only include rides which are shorter than 15 minutes.

==== assert 0,1 == 828 assert 1,1 == 594 assert 12,1 == 669

Get the ending station and the duration of all of the bike trips originating at station 31111 that lasted 8 hours or more.

==== assert 20,0 == 31100 assert 20,1 == 40733 assert 0,0 == 31202 assert 0,1 == 45722

How many trips longer than 8 hours started and ended and station 31111 by casual riders?

==== assert 0,0 == 4

Lesson Feedback

During this lesson I was primarily in my...
• 1. Comfort Zone
• 2. Learning Zone
• 3. Panic Zone

Completing this lesson took...
• 1. Very little time
• 2. A reasonable amount of time
• 3. More time than is reasonable

Based on my own interests and needs, the things taught in this lesson...
• 1. Don't seem worth learning
• 2. May be worth learning
• 3. Are definitely worth learning

For me to master the things taught in this lesson feels...
• 1. Definitely within reach
• 2. Within reach if I try my hardest
• 3. Out of reach no matter how hard I try

You have attempted 1 of 12 activities on this page