AND & OR operator:

AND:

This is used to filter the records based on more than one condition.

Syntax:

SELECT column1, column2, …

FROM table_name

WHERE condition1 AND condition2 AND condition3 …;

Scenario 1:

If you want return all the records from Vijayawada city and above 20000 salaries.

Example:

SELECT * FROM Employee

WHERE City =’Vijayawada’ AND Salary >= 20000;

NOTE: AND operator display records if all the conditions are TRUE

OR operator:

It requires at least one of the specified conditions to be true for a row to be included.

Example:

SELECT * FROM Employees

WHERE City = ‘Hyderbad’ OR City = ‘Bangalore’;

NOTE:  OR operator display records at least one condition is TRUE

LIKE Operator:

This operator is used in WHERE Clause to search for specific pattern in a column.

It is used along with the WHERE clause of the UPDATE, DELETE and SELECT statements, to filter the rows based on the given pattern. These patterns are specified using Wildcards.

There are two wildcards are used in conjunction with LIKE operator

• The percentage sign % represents zero, one, or multiple characters.
• The underscore sign _ represents one, single character.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern;

Example:

Select all Employees whose name starts with letter “s”

Select * from Employee

Where EmpName LIKE ‘%s’;

Example 2:

Find addresses containing ‘Street’:

SELECT address FROM addresses WHERE address LIKE ‘%Street%’;

Ends With

To return records that end with a specific letter or phrase, add the % at the beginning of the letter or phrase.

Example

Return all Employees that end with ‘o’:

SELECT * FROM Employee

WHERE FirstName LIKE ‘%o’;

Example:

Return all employees name that starts with “s” and ends with “r”

SELECT * FROM Employee

WHERE EmpName LIKE ‘s%r’;

IN Operator:

• The IN operator allows you to specify multiple values in a WHERE clause.
• It checks if a value matches any of the values within a specified list.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1, value2, …);

Example 1:

Find customers from California, Vijayawada and Hyderabad

SELECT * FROM Employee WHERE City IN (‘Vijayawada’, ‘Hyderabad’);

NOT IN:

This keyword returns all the records that are NOT any values in the list.

Example:

SELECT * FROM Employee WHERE City NOT IN (‘Vijayawada’, ‘Hyderabad’);

You can also use IN operator in subqueries:

Example:

Find products with prices higher than the average:

SELECT * FROM products WHERE price IN (SELECT AVG(price) FROM products);

Alias operator:

This operator is used to give a column or Table as temporary name.

Alias are created with as keyword.

Why use aliases:

Readability: Make query results more understandable, especially with long or complex table/column names.

Convenience: Shorten names for easier typing and referencing.

Syntax:

Table aliases:

SELECT column_name(s)

FROM table_name AS alias_name;

Column aliases:

SELECT column_name AS alias_name

FROM table_name;

Example 1:

Count the total employees:

SELECT COUNT(Emp_id) AS total_employees

FROM Employee;

Example 2:

Combining Columns with alias:

SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name

FROM employees;

Example 3:

SELECT EmpName, CONCAT(Address,’, ‘,PostalCode,’, ‘,City,’, ‘,’State’) AS Address

FROM Employee;

BETWEEN Operator:

This operator selects the values within the given range.

Commonly used with WHERE Clause to filter the results.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

Example 1:

To display salary within the range between 20000 to 30000

SELECT * FROM Employee WHERE salary BETWEEN 20000 AND 30000;

Example 2:

Find customers whose names start with letters between ‘S’ and ‘M’:

SELECT * FROM Employee WHERE FirstName BETWEEN ‘S%’ AND ‘M%’;

BETWEEN Operator with UPDATE Statement:

You can also use BETWEEN operator with UPDATE statement.

Example:

Let us update the Employees salary whose age lies between 23 to 30.

UPDATE Employee SET Salary = 20000

WHERE age BETWEEN 23 AND 30;

           NOT BETWEEN:

To display the Employee salary outside range of above example

SELECT * FROM Employee WHERE salary NOT BETWEEN 20000 AND 30000;

NOT BETWEEN Operator with IN:

We can use NOT Between operator in combination with the IN operator to select values that are outside range of values.

Example:

SELECT * FROM Employee

Where salary NOT BETWEEN 15000 AND 25000

AND City NOT IN (‘Hyderabad’);

UNION Operator:

This operator is used to combine the result set of two or more SELECT statements.

The column must be same data type and order also.

Syntax:

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

Example 1:

SELECT product_name, price FROM products_2023

UNION

SELECT product_name, price FROM products_2024;

Common Uses:

Merging results from different tables:

• Combining customer data from multiple sources.
• Unifying product information from different systems.

Creating a distinct list of values:

• Getting a list of unique email addresses from multiple tables.
• Finding distinct product categories across different product lines.

Constructing complex queries:

• Building queries with multiple conditions and result sets.

                 UNION ALL Operator:

It is similar to UNION but with one key difference:

 it does not remove duplicate rows from the combined result set. In other words, UNION ALL includes every single row from every participating SELECT statement, even if they appear multiple times.

Syntax:

SELECT column_name(s) FROM table1

UNION [ALL]

SELECT column_name(s) FROM table2;

Let’s consider two tables, Employees and potential_Employees, both with a Emp_id column. We want to create a list of all Employee IDs, including duplicates.

Example:

SELECT Emp_id FROM Employee

UNION ALL

SELECT Emp_id FROM potential_Employee;

This query will return every Employee ID from both tables, even if a customer exists in both.

                     Choosing between UNION and UNION ALL:

Choose UNION ALL if you:

• Need to combine all rows, regardless of duplicates, for further processing or analysis.
• Want to preserve the complete picture of data, including potential inconsistencies or overlaps.
• Performance is a concern and processing a smaller result set with duplicates is faster.

Choose UNION if you:

• Want to create a distinct list of values from multiple sources.
• Need to avoid redundancy and only have unique data in the final result set.
• Efficiency is important and removing duplicates upfront offers better performance.

CASE Operator:

• The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement).

• Allows you to return different values or perform different actions based on specified conditions.

Syntax:

  CASE

 WHEN condition1 THEN result1

 WHEN condition2 THEN result2

 ELSE result3

END

 Example 1:

  SELECT customer_id,

  CASE

 WHEN points > 1000 THEN ‘Gold’

  WHEN points > 500 THEN ‘Silver’

   ELSE ‘Bronze’

   END AS membership_level

FROM customers;

Example 2:

SELECT FirstName, City, Country

FROM Employee

ORDER BY

(CASE

 WHEN City IS NULL THEN Country

 ELSE City

END);

IS NULL operator:

• This operator is used to checks if a value in a column is NULL, representing the absence of data.
• Essential for handling missing or unknown values in SQL databases.

       Syntax:

       SELECT column_name(s)

       FROM table_name

       WHERE column_name IS NULL;

Common Uses:

• Identifying missing data:
• Finding customers with no email address.
• Discovering products with missing descriptions.

Filtering results:

• Excluding rows with missing information.
• Focusing on data that is complete and reliable.

Handling NULL values in calculations:

• Using aggregate functions like COUNT, SUM, AVG that ignore NULLs.
• Applying conditional logic with CASE expressions to handle NULLs appropriately.

Example 1:

Find Employees with no phone number:

SELECT * FROM Employee WHERE phone IS NULL;

Example 2:

Count orders with missing shipping dates:

SELECT COUNT(*) FROM orders WHERE shipping_date IS NULL;