Explain the 'CASE' expression in Oracle SQL statement

In Oracle SQL, the CASE expression is used to add conditional logic within a SQL statement. It allows you to perform different actions based on different conditions. The basic syntax of the CASE expression is:

Here's an example to demonstrate how to use the CASE expression:

Suppose we have a table named employees with columns employee_id, first_name, last_name, and salary. We want to create a new column called salary_category that categorizes employees based on their salary into three categories: "Low", "Medium", and "High".

ROWNUM vs LIMIT in SQL

ROWNUM and LIMIT are both mechanisms used in different database management systems to restrict the number of rows returned by a query, but they are used in different contexts and have some differences:

1. ROWNUM:

   • ROWNUM is a pseudo-column in Oracle databases that returns a unique sequential number for each row retrieved by a query.
   • It is typically used in conjunction with a WHERE clause to limit the number of rows returned.
   • For example:

     SELECT * FROM table_name WHERE ROWNUM <= 10;
     

   • This query will return the first 10 rows from the table table_name.