My Blog

Author: admin

  • CASE Statement

    —

    by

    admin
    in

    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.

  • BETWEEN Clause

    —

    by

    admin
    in

    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.

  • IN Clause

    —

    by

    admin
    in

    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.

  • LIKE Clause

    —

    by

    admin
    in

    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.

  • DISTINCT Clause

    —

    by

    admin
    in

    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.

  • LIMIT / ROWNUM Clause

    —

    by

    admin
    in

    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.

  • JOIN Clause

    —

    by

    admin
    in

    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.

  • HAVING Clause

    —

    by

    admin
    in

    Purpose: The HAVING clause filters results after aggregation, allowing you to specify conditions on grouped records.Syntax Example: Explanation: This retrieves departments where the average salary exceeds 50,000. HAVING is used because the condition is applied after grouping.

  • GROUP BY Clause

    —

    by

    admin
    in

    Purpose: The GROUP BY clause groups rows that have the same values in specified columns into summary rows, often used with aggregate functions.Syntax: Example: Explanation: This counts the number of employees in each department, returning one row per department.

  • ORDER BY Clause

    —

    by

    admin
    in

    Purpose: The ORDER BY clause sorts the result set based on one or more columns, in ascending or descending order.Syntax: Example: Explanation: This sorts the results alphabetically by last_name. The default is ascending order; use DESC for descending.