Category: 04. Oracle Clauses

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 `

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.

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.

Purpose: The CASE statement allows for conditional logic within SQL queries, enabling you to return different values based on certain conditions. Syntax: Example Explanation: This categorizes employees’ salaries as ‘Low’, ‘Medium’, or ‘High’ based on defined salary ranges.

Purpose: The BETWEEN clause filters records within a specified range, inclusive of the boundary values.Syntax: Example: Explanation: This retrieves employees whose salaries are between 50,000 and 70,000, including those exact values.

Purpose: The IN clause allows you to specify multiple values in a WHERE clause, checking if a column’s value matches any value in a list or subquery.Syntax: Example: Explanation: This retrieves employees from departments 10 or 20, making it easier than using multiple OR conditions.

Purpose: The LIKE clause is used in the WHERE clause to search for a specified pattern in a column.Syntax: Example: Explanation: This retrieves employees whose first names start with “J”. The % wildcard represents zero or more characters.

Purpose: The DISTINCT clause removes duplicate rows from the result set, ensuring each row is unique.Syntax: Example: Explanation: This retrieves unique department IDs from the employees table, eliminating duplicates.

Purpose: Limits the number of rows returned by a query. In Oracle, this can be done using ROWNUM.Syntax (using ROWNUM): Example: Explanation: This retrieves the first 10 rows from the employees table. ROWNUM assigns a number to each row in the result set.

Purpose: The JOIN clause combines rows from two or more tables based on a related column, facilitating complex queries across multiple tables.Types of Joins: Example Explanation: This retrieves employee names along with their department names by joining the employees and departments tables on department_id.