SQL Basics

• The LIKE keyword works with wildcards
• LIKE is used with text fields (strings)
• Use the LIKE operator to specify search patterns in conditions

SELECT <column1>,
       <column2>
FROM   <table>
WHERE  <column1> LIKE 'text%';	

• The % wildcard matches any sequence of characters
• Every value found in column1 that starts with text will match
• A value also matches if nothing follows text in the string
• Case sensitivity depends on the DBMS and its configuration
• A pattern may contain more than one wildcard

SELECT <column1>,
       <column2>
FROM   <table>
WHERE  <column1> LIKE '%text in the middle%';	

• The % wildcard matches any sequence of characters, but NULL is unknown and is not a string

Select the product ID and name from Products where 'bean bag' occurs anywhere in the product name

-- Oracle / MySQL / SQL Server
SELECT prod_id,
       prod_name
FROM   Products
WHERE  prod_name LIKE '%bean bag%';

• The underscore (_) wildcard matches exactly one character
• Where % matches zero or more characters, a single underscore always matches one character

SELECT <column1>,
       <column2>
FROM   <table>
WHERE  <column1> LIKE 't_kst';	

Select the product ID and name from Products for teddy bears measuring at least 10 inches

-- Oracle / MySQL / SQL Server
SELECT prod_id,
       prod_name
FROM   Products
WHERE  prod_name LIKE '__ inch teddy bear%';

21. Select all customer email addresses ending in .com (3)

22. Select the products whose names contain the word 'doll' (2)

23. Select all columns from Customers where cust_state ends in an uppercase I (1)

24. Select all columns from Products where the product description does NOT contain 'bear'. Sort by price in descending order (6)

25. Select all products whose descriptions contain 'toy' followed by 'carrots' (1)