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

#SQL #Edu

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 …**;

1. Find all the Toy Story movies
	1. SELECT * FROM movies WHERE Title LIKE 'Toy Story%';
2. Find all the movies directed by John Lasseter
	1. SELECT * FROM movies WHERE Director LIKE 'John Lasseter%';
3. Find all the movies (and director) not directed by John Lasseter
	1. SELECT Title, Director FROM movies WHERE Director NOT LIKE 'John Lasseter%';
4. Find all the WALL-* movies
	1. SELECT Title FROM movies WHERE Title LIKE 'WALL-_';

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 ✓

This page is Lesson 3 from SQLBolt, titled "Queries with constraints (Pt. 2)". It teaches how to use various SQL operators in the WHERE clause, especially for working with text data. The main points include:

Operators for text columns:
- =: Exact, case-sensitive string comparison
- != or <>: Exact, case-sensitive inequality
- LIKE / NOT LIKE: Case-insensitive string comparison/inequality
- %: Wildcard for matching zero or more characters (used with LIKE)
- _: Wildcard for matching a single character (used with LIKE)
- IN (...): Checks if value is in a list
- NOT IN (...): Checks if value is not in a list
Other tips:
- All strings need to be quoted.
- For large-scale or full-text search, specialized libraries (like Apache Lucene or Sphinx) are recommended over SQL.
Exercise Table:
- A sample Movies table is provided, with columns: Id, Title, Director, Year, Length_minutes.
Practice Tasks:
- Find all Toy Story movies.
- Find all movies directed by John Lasseter.
- Find all movies not directed by John Lasseter.
- Find all "WALL-*" movies (movies with names starting with "WALL-").

The lesson includes interactive exercises to practice writing SQL queries using these operators and constraints in the WHERE clause.[1]