Author: admin

Purpose: Oracle provides numerous built-in functions for various data manipulations.Categories: Example: Explanation: This retrieves the first names in uppercase and the salaries rounded to the nearest whole number.

Definition: A subquery is a query nested within another SQL query. They can be used in SELECT, INSERT, UPDATE, or DELETE statements.Example: Explanation: This retrieves the names of employees who work in departments located in location_id 1000. The inner query fetches the relevant department_ids first.

Purpose: Joins are used to combine rows from two or more tables based on a related column between them.Types of Joins: Example (INNER JOIN): Explanation: This query retrieves the names of employees along with their corresponding department names, joining the employees and departments tables on the department_id.

HAVING Clause: After using GROUP BY, you can filter groups with the HAVING clause, which applies conditions to grouped results.Example: Explanation: This retrieves departments where the average salary exceeds 60,000. Unlike WHERE, HAVING is applied after the grouping.

GROUP BY Clause: This clause groups rows that have the same values in specified columns into summary rows.Example: Explanation: This query counts the number of employees in each department. The COUNT(*) function counts all rows for each department_id.

Purpose: Aggregate functions allow you to perform calculations on a set of values, returning a single summary value.Common Functions: Example: Explanation: This calculates the average salary for each department, grouping results by department_id. The AVG() function computes the average, and the results will have one row for each department.

Limiting Rows: Example: Explanation: This query retrieves the first five records from the employees table. ROWNUM is a pseudo-column that indicates the order of rows returned by a query.

Example: Explanation: This command retrieves the first_name, last_name, and salary of employees, ordering the results by salary in descending order. If you wanted ascending order, you could use ASC or simply omit it, as it’s the default.

Combining Conditions: Example:

Example Explanation: This query selects only the first_name and last_name of employees whose salary is greater than 50,000. The WHERE clause restricts the results to those meeting the condition.