Category: 03. Oracle Query

DELETE Statement: Used to remove records from a table. Syntax: Example: Explanation: This deletes the employee record with employee_id 1 from the employees table. The WHERE clause is crucial to avoid deleting all records.

INSERT Statement: Used to add new records to a table.Syntax: Example: Explanation: This adds a new employee named Jane Doe with a salary of 70,000 in department 10. Each value corresponds to the specified column.

UPDATE Statement: Used to modify existing records in a table.Syntax: Example: Explanation: This increases the salary of all employees in department 10 by 10%. The WHERE clause ensures that only the relevant records are updated.

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.