Author: admin
-
LIKE Operator
Purpose: Searches for a specified pattern in a column.Syntax: Example: Explanation: This retrieves employees whose first names start with “A”. The % wildcard represents zero or more characters.
-
IN Operator
Purpose: Tests whether a value matches any value in a list or subquery. Syntax: Example Explanation: This retrieves employees who work in departments 10, 20, or 30.
-
BETWEEN Operator
Purpose: Checks if a value lies within a specified range, inclusive.Syntax: Example: Explanation: This retrieves employees with salaries between 40,000 and 60,000.
-
Logical Operators
Purpose: Used to combine multiple conditions in a WHERE clause.Operators: Explanation: This retrieves employees in department 10 with a salary greater than 50,000.
-
Comparison Operators
Purpose: Used to compare two values, returning a boolean result (TRUE, FALSE, or NULL).Operators: Example: Explanation: This retrieves employees whose salary is 50,000 or more.
-
Arithmetic Operators
Purpose: Used to perform mathematical calculations on numeric values.Operators: Example: Explanation: This calculates a 10% bonus on the salary for each employee.
-
WITH Clause (Common Table Expressions)
Purpose: The WITH clause allows you to define a temporary result set (Common Table Expression, or CTE) that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, improving readability and structure. Syntax: Example: Explanation: This creates a CTE named `
-
Subqueries
Definition: A subquery is a query nested inside another SQL query, which can be used in SELECT, INSERT, UPDATE, or DELETE statements. Syntax Example: Explanation: This retrieves names of employees who work in departments located in a specific location_id, using a subquery to find relevant department_ids.
-
UNION and UNION ALL
Purpose: Combines the results of two or more SELECT statements. UNION removes duplicate rows, while UNION ALL retains all rows, including duplicates. Syntax: Example: Explanation: This retrieves unique first names from both the employees and managers tables. To include duplicates, use UNION ALL.