SQL LIKE Operator and Wildcards

SQL LIKE Operator

• Implemented in a WHERE clause to find a specific pattern in a column
• Syntax includes SELECT column1, column2,... FROM table_name WHERE columnN LIKE pattern;

Wildcards Used with LIKE

• % matches zero or more characters, such as 'M%' matching "Mark" or "Maria"
• _ matches exactly one character, such as 'M_' matching "Ma" or "Mo"
• [ ] matches any single character inside brackets; '[AB]%' matches names beginning with A or B
• [^ ] matches any character NOT in brackets;'[^M]%' matches names not beginning with M

LIKE Pattern Examples

• 'a%' finds entries starting with “a”
• '%a' finds entries ending with "a"
• '%or%' finds entries containing "or" anywhere
• '_r%' finds entries where the second character is "r"
• 'a_%' finds entries starting with "a" and has at least 2 characters
• 'a%o' finds entries that start with "a" and end with "o"

LIKE Query Descriptions

• SELECT * FROM Customers WHERE City LIKE 'ber%'; finds cities beginning with "ber" such as "Berlin"
• SELECT * FROM Customers WHERE City LIKE '%es%'; finds cities containing "es" such as "Brest" or "Estonia"
• SELECT * FROM Customers WHERE City LIKE '_ondon'; finds 6-letter cities with the second letter "o" and ending in "ondon" such as "London"
• SELECT * FROM Customers WHERE City LIKE 'L_n_on'; finds cities such as "London" or "Lennon" using the pattern L + any character + n + any character + on

Practice Modifications (LIKE)

• SELECT * FROM employees WHERE last_name LIKE '%son'; retrieves names ending in "son"
• SELECT * FROM customers WHERE email LIKE '%gmail.com'; searches for emails with "gmail"

SQL IN Operator

• Filters records matching any value in a list
• Acts as shorthand for multiple OR conditions
• Syntax includes SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2,...);
• SELECT * FROM employees WHERE department IN ('HR', 'IT', 'Sales'); to filter employees in specific departments
• The above is equivalent to SELECT * FROM employees WHERE department = 'HR' OR department = 'IT' OR department = 'Sales';
• SELECT * FROM students WHERE grade IN ('A', 'B', 'C'); locates students with grades A, B, or C
• SELECT * FROM orders WHERE customer_id IN (101, 102, 105); locates orders by customers 101, 102, and 105
• SELECT * FROM products WHERE price IN (100, 200, 500); locates products costing 100, 200, or 500

IN with Subqueries

• SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

SQL BETWEEN Operator

• Filters values within a defined range
• Works with numbers, dates, or text (alphabetically)
• SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'; finds orders placed in 2024
• SELECT * FROM products WHERE price BETWEEN 100 AND 500; finds products with prices between 100 and 500

Hands-On SQL Activity: Feedback Table

• Create a customer feedback table with the following statement CREATE TABLE customer_feedback ( id INT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50), feedback TEXT, rating INT, date_given DATE );
• Insert at least 5 entries of sample data
• Perform queries to retrieve certain data

Example Queries

• SELECT * FROM customer_feedback WHERE customer_name LIKE '%a%'; finds feedback where the name contains "a"
• SELECT * FROM customer_feedback WHERE feedback LIKE 'Good%'; finds feedback where the comment starts with "Good"
• SELECT * FROM customer_feedback WHERE feedback LIKE '%service'; finds feedback where the comment ends with "service"
• SELECT * FROM customer_feedback WHERE rating IN (3, 4, 5); finds feedback with a rating of 3, 4, or 5
• SELECT * FROM customer_feedback WHERE customer_name IN ('John', 'Emma','Michael'); finds feedback from "John", "Emma", or "Michael"
• SELECT * FROM customer_feedback WHERE date_given IN ('2024-03-01', '2024-03-05', '2024-03-10'); finds feedback given on specific dates