Author: admin
-
WHERE Clause
Purpose: The WHERE clause filters records based on specific conditions, limiting the results to those that meet the criteria.Syntax: Example: Explanation: This retrieves all records from the employees table where the salary exceeds 60,000.
-
FROM Clause
Purpose: The FROM clause identifies the table(s) from which to fetch data.Syntax: Example: Explanation: This selects all columns from the departments table. You can also join multiple tables here.
-
SELECT Clause
Purpose: The SELECT clause specifies which columns or expressions to retrieve from a database.Syntax: Example: Explanation: This retrieves the first_name and last_name columns from the employees table. You can use * to select all columns.
-
Deleting Data
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.
-
Inserting Data
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.
-
Updating Data
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.
-
Using Functions
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.
-
Subqueries
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.
-
Joining Tables
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.
-
Filtering Grouped Results
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.