SQL Lesson 3: Queries with constraints (Pt. 2)

When writing WHERE clauses with columns containing text data, SQL supports a number of useful operators to do things like case-insensitive string comparison and wildcard pattern matching. We show a few common text-data specific operators below:

Operator Condition Example
= Case sensitive exact string comparison (notice the single equals) col_name = "abc"
!= or <> Case sensitive exact string inequality comparison col_name != "abcd"
LIKE Case insensitive exact string comparison col_name LIKE "ABC"
NOT LIKE Case insensitive exact string inequality comparison col_name NOT LIKE "ABCD"
% Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) col_name LIKE "%AT%"
(matches "AT", "ATTIC", "CAT" or even "BATS")
_ Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) col_name LIKE "AN_"
(matches "AND", but not "AN")
IN (…) String exists in a list col_name IN ("A", "B", "C")
NOT IN (…) String does not exist in a list col_name NOT IN ("D", "E", "F")
Did you know?

All strings must be quoted so that the query parser can distinguish words in the string from SQL keywords.

We should note that while most database implementations are quite efficient when using these operators, full-text search is best left to dedicated libraries like Apache Lucene or Sphinx. These libraries are designed specifically to do full text search, and as a result are more efficient and can support a wider variety of search features including internationalization and advanced queries.

Exercise

Here's the definition of a query with a WHERE clause again, go ahead and try and write some queries with the operators above to limit the results to the information we need in the tasks below.

Select query with constraints
SELECT column, another_column, … FROM mytable WHERE condition AND/OR another_condition AND/OR …;
Sorry but the SQLBolt exercises require a more recent browser to run.
Please upgrade to the latest version of Internet Explorer, Chrome, or Firefox!

Otherwise, continue to the next lesson: SQL Lesson 4: Filtering and sorting Query results
Table: movies
Loading SQL...
Exercise 3 — Tasks
  1. Find all the Toy Story movies
  2. Find all the movies directed by John Lasseter
  3. Find all the movies (and director) not directed by John Lasseter
  4. Find all the WALL-* movies
Stuck? Read this task's Solution.
Solve all tasks to continue to the next lesson.
Finish above Tasks