SQL: Operators with example

SQL: Operators with example

SQL operators are symbols or keywords used to perform various operations, comparisons, and logical evaluations on data within SQL queries. They allow you to manipulate, filter, and combine values based on specific conditions. Here are some commonly used SQL operators with examples:

1. Arithmetic Operators:

• Addition (+): Performs addition of two or more values.
• Subtraction (-): Performs subtraction of one value from another.
• Multiplication (*): Performs multiplication of two or more values.
• Division (/): Performs division of one value by another.
• Modulus (%): Returns the remainder of a division operation. Example:

   SELECT 10 + 5, 20 - 8, 6 * 4, 20 / 5, 17 % 5 FROM table_name;

2. Comparison Operators:

• Equal to (=): Checks if two values are equal.
• Not equal to (<> or !=): Checks if two values are not equal.
• Greater than (>): Checks if one value is greater than another.
• Less than (<): Checks if one value is less than another.
• Greater than or equal to (>=): Checks if one value is greater than or equal to another.
• Less than or equal to (<=): Checks if one value is less than or equal to another. Example:

   SELECT * FROM table_name WHERE age > 30;

3. Logical Operators:

• AND: Combines multiple conditions and returns true if all conditions are true.
• OR: Combines multiple conditions and returns true if any condition is true.
• NOT: Negates a condition. Example:

   SELECT * FROM table_name WHERE age > 25 AND department = 'Sales';

4. String Concatenation Operator:

• Concatenation (||): Joins two or more strings together. Example:

   SELECT first_name || ' ' || last_name AS full_name FROM table_name;

5. Pattern Matching Operators:

• LIKE: Matches a pattern in a string using wildcard characters (% and _).
• IN: Checks if a value matches any value in a list. Example:

   SELECT * FROM table_name WHERE last_name LIKE 'S%';

6. NULL Comparison Operators:

• IS NULL: Checks if a value is NULL.
• IS NOT NULL: Checks if a value is not NULL. Example:

   SELECT * FROM table_name WHERE column_name IS NULL;

These are just a few examples of SQL operators. SQL offers a variety of operators to perform different types of operations and comparisons, enabling you to manipulate and analyze data effectively within your queries.

SQL: BETWEEN Operator With Example

The SQL BETWEEN operator is used to retrieve values within a specified range. It checks if a value falls within the range, including both the start and end values. The syntax for the BETWEEN operator is as follows:

value BETWEEN start_value AND end_value

Here’s an example to illustrate the usage of the BETWEEN operator:

Consider a table named “employees” with a column named “age” that stores the age of employees. Here’s a sample data:

+-----+
| age |
+-----+
| 25  |
| 28  |
| 30  |
| 32  |
| 35  |
+-----+

To retrieve the employees with ages between 28 and 32 (inclusive), you can use the BETWEEN operator in the SELECT statement:

SELECT * FROM employees WHERE age BETWEEN 28 AND 32;

Output:

+-----+
| age |
+-----+
| 28  |
| 30  |
| 32  |
+-----+

In this example, the SELECT statement retrieves the rows from the “employees” table where the “age” column falls between 28 and 32, including both the start and end values.

The BETWEEN operator simplifies the process of specifying a range condition in SQL queries, providing a concise way to filter data based on inclusive ranges. It is commonly used in WHERE clauses to retrieve values within specific boundaries.

SQL: Text Values with Example

In SQL, text values are typically represented as character strings enclosed in single quotes (‘ ‘). Text values are used to store and manipulate textual information, such as names, descriptions, addresses, and more. Here’s an explanation with an example:

Consider a table named “employees” with columns: id, first_name, last_name, and department. Here’s a sample data:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 3  | Alex       | Johnson   | IT         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

In this example, the “first_name”, “last_name”, and “department” columns store text values.

When working with text values in SQL, you need to enclose them in single quotes (‘ ‘) to indicate that they are character strings. Here are some examples of SQL queries using text values:

1. Inserting a row with text values:

   INSERT INTO employees (first_name, last_name, department) VALUES ('John', 'Doe', 'Finance');

2. Updating a row with text values:

   UPDATE employees SET department = 'Marketing' WHERE id = 2;

3. Querying rows based on text values:

   SELECT * FROM employees WHERE department = 'Sales';

Output:

   +----+------------+-----------+------------+
   | id | first_name | last_name | department |
   +----+------------+-----------+------------+
   | 1  | John       | Smith     | Sales      |
   | 4  | Lisa       | Brown     | Sales      |
   +----+------------+-----------+------------+

In these examples, the text values are represented by the names, such as ‘John’, ‘Doe’, ‘Finance’, etc., enclosed in single quotes.

It’s important to note that SQL is generally case-insensitive for text values, unless the database’s collation settings specify otherwise. However, it’s a good practice to maintain consistency and use consistent capitalization or formatting for text values throughout your SQL queries and data.

By using text values, you can store, manipulate, and retrieve textual information effectively in SQL databases, enabling you to work with a wide range of textual data in your applications or systems.

SQL: Logical Operations with example

In SQL, logical operations are used to evaluate conditions and perform logical comparisons on Boolean expressions. Logical operators allow you to combine conditions, negate conditions, or check for the existence of certain values. Here are some commonly used logical operators in SQL, along with examples:

1. AND Operator:
   The AND operator is used to combine multiple conditions, and it returns true only if all the conditions are true. Example:

   SELECT * FROM employees WHERE age > 25 AND department = 'Sales';

Output:

   +----+------------+-----------+-----+
   | id | first_name | last_name | age |
   +----+------------+-----------+-----+
   | 1  | John       | Smith     | 28  |
   +----+------------+-----------+-----+

In this example, the SELECT statement retrieves rows from the “employees” table where the age is greater than 25 and the department is ‘Sales’.

2. OR Operator:
   The OR operator is used to combine multiple conditions, and it returns true if any of the conditions are true. Example:

   SELECT * FROM employees WHERE age > 30 OR department = 'HR';

Output:

   +----+------------+-----------+-----+
   | id | first_name | last_name | age |
   +----+------------+-----------+-----+
   | 2  | Jane       | Doe       | 32  |
   | 4  | Lisa       | Brown     | 30  |
   +----+------------+-----------+-----+

In this example, the SELECT statement retrieves rows where the age is greater than 30 or the department is ‘HR’.

3. NOT Operator:
   The NOT operator is used to negate a condition and returns the opposite Boolean value. Example:

   SELECT * FROM employees WHERE NOT department = 'Sales';

Output:

   +----+------------+-----------+-----+
   | id | first_name | last_name | age |
   +----+------------+-----------+-----+
   | 2  | Jane       | Doe       | 32  |
   | 3  | Alex       | Johnson   | 24  |
   +----+------------+-----------+-----+

In this example, the SELECT statement retrieves rows where the department is not ‘Sales’.

Logical operators allow you to construct complex conditions by combining or negating individual conditions. They are crucial in SQL for filtering data and specifying conditions for data retrieval. By utilizing logical operators effectively, you can formulate powerful queries that meet your specific criteria.