= only matches text exactly. To match a pattern, use LIKE with two wildcards:
%stands for any run of characters (including none)_stands for exactly one character
SELECT name FROM student WHERE name LIKE 'S%';
'S%' means "starts with S". '%a%' means "contains an a". '_o%' means "an o as the second letter".
Two more handy tests:
IN (…)matches any value in a list:WHERE form IN ('11A', '11C')BETWEEN low AND highmatches a range, including both ends:
SELECT name, score FROM student WHERE score BETWEEN 70 AND 90;
Both are shorter than writing several ORs.
Question. Show the name of every student whose name has a as its second letter.
Answer. _ stands for exactly one character, so _a means "any first letter, then a". The % lets the rest of the name be anything:
SELECT name FROM student WHERE name LIKE '_a%';
name
Jamal
Sara
'%a%' would be the wrong pattern here: it means "an a anywhere", and would also match a name such as Ana.
BETWEENwants the smaller value first.BETWEEN 90 AND 70matches nothing; writeBETWEEN 70 AND 90.%and_only work withLIKE.WHERE name = 'S%'looks for a name that is literallyS%.- Case. In SQLite,
LIKEignores the difference between capital and small letters A to Z, so's%'also finds Sara. Some other databases do not.
Pattern & range filters
LIKE / IN / BETWEEN are all WHERE — they keep matching rows.
난이도 2/5 · 140코인 + 오늘 첫 완료 시 작은 보너스 추가
Show the name of every student whose name contains the letter a. Use LIKE with %.
출력을 보려면 '실행'을 클릭하세요.
난이도 2/5 · 140코인 + 오늘 첫 완료 시 작은 보너스 추가
Show the name 및 score of students whose score is between 70 and 90 (inclusive). Use BETWEEN.
출력을 보려면 '실행'을 클릭하세요.
난이도 2/5 · 140코인 + 오늘 첫 완료 시 작은 보너스 추가
Show the name 및 form of every student in form 11B 또는 11C. Use IN with a list of the two forms.
출력을 보려면 '실행'을 클릭하세요.
난이도 2/5 · 140코인 + 오늘 첫 완료 시 작은 보너스 추가
Show the name of every student whose name is exactly three letters long. Each _ stands for one character.
출력을 보려면 '실행'을 클릭하세요.