Learn SQLite in Neovim: WHERE Clause — Filter Your Data | Episode 5

Learn SQLite in Neovim: WHERE Clause — Filter Your Data

The WHERE clause in SQL allows you to filter query results to retrieve only the rows that meet specific conditions. In this example, we create a music library table with eight songs and demonstrate how to use WHERE with various comparison operators to filter by year, duration, and artist.

Code

-- Create a table named songs
CREATE TABLE songs (
  id INTEGER PRIMARY KEY,
  title TEXT,
  artist TEXT,
  year INTEGER,
  duration INTEGER  -- duration in seconds
);

-- Insert eight classic and modern songs
INSERT INTO songs (title, artist, year, duration) VALUES
  ('Bohemian Rhapsody', 'Queen', 1975, 354),
  ('Shape of You', 'Ed Sheeran', 2017, 233),
  ('Blinding Lights', 'The Weeknd', 2019, 200),
  ('Imagine', 'John Lennon', 1971, 183),
  ('Hey Jude', 'The Beatles', 1968, 431),
  ('Bad Guy', 'Billie Eilish', 2019, 194),
  ('Smells Like Teen Spirit', 'Nirvana', 1991, 301),
  ('Rolling in the Deep', 'Adele', 2010, 228);

-- Select all songs
SELECT * FROM songs;

-- Filter songs released in 2019
SELECT * FROM songs WHERE year = 2019;

-- Filter songs released after 2000
SELECT * FROM songs WHERE year > 2000;

-- Filter songs longer than 300 seconds
SELECT * FROM songs WHERE duration > 300;

-- Filter songs by artist 'Queen' (text values use single quotes)
SELECT * FROM songs WHERE artist = 'Queen';

-- Filter songs shorter than 210 seconds
SELECT * FROM songs WHERE duration < 210;

-- Filter songs not released in 2019
SELECT * FROM songs WHERE year != 2019;

Key Points

• The WHERE clause filters rows by specifying conditions after the FROM clause in a SELECT statement.
• Use comparison operators like =, >, <, and != to define filtering criteria.
• Text values in WHERE conditions must be enclosed in single quotes.
• WHERE works with both numeric and text columns to precisely control query results.
• Combining WHERE with different operators helps extract meaningful subsets of data from your tables.