Filtering Data with WHERE Clause
In SQL, the WHERE clause is used to filter rows based on a specified condition or set of conditions. The WHERE clause is used with the SELECT, UPDATE, and DELETE statements.
The basic syntax for using the WHERE clause is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
In the above syntax, column1, column2, ... are the columns that you want to select data from, table_name is the name of the table that contains the data, and condition is the condition that you want to use to filter the data.
For example, let’s say you have a table named customers with columns customer_id, first_name, last_name, and email, and you want to select all the customers whose first name is ‘John’. You would use the following SQL statement:
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE first_name = 'John';
This would return all the rows from the customers table where the first_name column is ‘John’.
You can also use operators such as >, <, >=, <=, and <> (not equal to) in the WHERE clause to filter data based on specific conditions. For example, the following SQL statement would return all the rows from the customers table where the customer_id is greater than 100:
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE customer_id > 100;
You can also combine multiple conditions in the WHERE clause using the AND and OR operators. For example, the following SQL statement would return all the rows from the customers table where the first_name is ‘John’ and the last_name is ‘Doe’:
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE first_name = 'John' AND last_name = 'Doe';
In summary, the WHERE clause is a powerful feature of SQL that allows you to filter data based on specific conditions. By combining operators and conditions, you can select and filter the data that you need from your database.
Comparison Operators (=, <>, >, <, >=, <=, BETWEEN, LIKE, IN)
Comparison operators are used to compare values in SQL queries to filter data. Here are some of the commonly used comparison operators in SQL:
=: Checks for equality between two values. For example,SELECT * FROM users WHERE name = 'John';will return all records where the name is ‘John’.<>or!=: Checks for inequality between two values. For example,SELECT * FROM users WHERE age <> 30;will return all records where the age is not 30.>: Checks if the left-hand value is greater than the right-hand value. For example,SELECT * FROM users WHERE age > 30;will return all records where the age is greater than 30.<: Checks if the left-hand value is less than the right-hand value. For example,SELECT * FROM users WHERE age < 30;will return all records where the age is less than 30.>=: Checks if the left-hand value is greater than or equal to the right-hand value. For example,SELECT * FROM users WHERE age >= 30;will return all records where the age is greater than or equal to 30.<=: Checks if the left-hand value is less than or equal to the right-hand value. For example,SELECT * FROM users WHERE age <= 30;will return all records where the age is less than or equal to 30.BETWEEN: Checks if a value is within a range of values. For example,SELECT * FROM users WHERE age BETWEEN 25 AND 35;will return all records where the age is between 25 and 35 (inclusive).LIKE: Used for pattern matching. For example,SELECT * FROM users WHERE name LIKE 'J%';will return all records where the name starts with ‘J’.IN: Checks if a value matches any value in a list. For example,SELECT * FROM users WHERE age IN (25, 30, 35);will return all records where the age is either 25, 30, or 35.
By using these comparison operators in combination with other SQL statements, you can create powerful queries to filter data based on specific criteria.
Logical Operators (AND, OR, NOT)
In SQL, logical operators are used to combine multiple conditions to filter data. The logical operators include AND, OR, and NOT.
The AND operator is used to retrieve data that meets multiple conditions. For example, to retrieve all employees who work in the sales department and have a salary greater than $50,000, you can use the following SQL statement:
SELECT * FROM employees
WHERE department = 'sales' AND salary > 50000;
The OR operator is used to retrieve data that meets at least one of the conditions. For example, to retrieve all employees who work in the sales department or have a salary greater than $50,000, you can use the following SQL statement:
SELECT * FROM employees
WHERE department = 'sales' OR salary > 50000;
The NOT operator is used to retrieve data that does not meet a specific condition. For example, to retrieve all employees who do not work in the sales department, you can use the following SQL statement:
SELECT * FROM employees
WHERE NOT department = 'sales'; Exercise:
Write SELECT statements with WHERE and logical operators to filter data
Use the LIKE operator to perform pattern matching on data
dowhatkennydoes.com 