SQL Fundamentals (Pre-requisite)

SQL (Structured Query Language) is the standard language for interacting with relational databases. It allows users to perform various operations such as querying, updating, and managing data within a database.

Here’s a breakdown of key SQL fundamentals with examples:

1. SQL Commands

SQL is categorized into several types of commands:

1. Data Query Language (DQL):

   • Used for querying data from a database.
   • Command: SELECT

2. Data Definition Language (DDL):

   • Used to define or modify database schema.
   • Commands: CREATE, ALTER, DROP, TRUNCATE

3. Data Manipulation Language (DML):

   • Used for manipulating data.
   • Commands: INSERT, UPDATE, DELETE

4. Data Control Language (DCL):

   • Used to control access to data.
   • Commands: GRANT, REVOKE

5. Transaction Control Language (TCL):

   • Used to manage database transactions.
   • Commands: COMMIT, ROLLBACK, SAVEPOINT

2. SELECT Statement (DQL)

Example 1: Retrieve All Data from a Table

sql
Copy code
SELECT * FROM employees;

Explanation:

• * selects all columns from the employees table.

Example 2: Retrieve Specific Columns

sql
Copy code
SELECT first_name, last_name FROM employees;

Explanation:

• Retrieves only the first_name and last_name columns from the employees table.

Example 3: Using WHERE Clause for Filtering

sql
Copy code
SELECT first_name, salary
FROM employees
WHERE salary > 50000;

Explanation:

• Retrieves employees with a salary greater than 50,000.

Example 4: Sorting Results with ORDER BY

sql
Copy code
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

Explanation:

• Sorts employees by salary in descending order.

Example 5: Aggregating Data with GROUP BY

sql
Copy code
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Explanation:

• Calculates the average salary for each department.

3. INSERT Statement (DML)

Example: Insert a Single Row

sql
Copy code
INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES ('John', 'Doe', 10, 55000);

Explanation:

• Inserts a new employee record into the employees table.

4. UPDATE Statement (DML)

Example: Update Data in a Table

sql
Copy code
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;

Explanation:

• Increases the salary of employees in department 10 by 10%.

5. DELETE Statement (DML)

Example: Delete Specific Records

sql
Copy code
DELETE FROM employees
WHERE department_id = 5;

Explanation:

• Deletes all employees in department 5.

6. CREATE TABLE Statement (DDL)

Example: Create a New Table

sql
Copy code
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

Explanation:

• Creates a table named employees with specified columns and a primary key.

7. ALTER TABLE Statement (DDL)

Example: Add a New Column

sql
Copy code
ALTER TABLE employees
ADD hire_date DATE;

Explanation:

• Adds a hire_date column to the employees table.

8. DROP TABLE Statement (DDL)

Example: Delete a Table

sql
Copy code
DROP TABLE employees;

Explanation:

• Deletes the employees table and all its data.

9. JOINs

Example: INNER JOIN

sql
Copy code
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

Explanation:

• Combines employees and departments tables based on department_id.

Example: LEFT JOIN

sql
Copy code
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

Explanation:

• Retrieves all employees, including those without a department.

10. Transactions (TCL)

Example: COMMIT and ROLLBACK

sql
Copy code
BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;

ROLLBACK; -- Undo the update

COMMIT; -- Make the update permanent

Explanation:

• Demonstrates transaction control with ROLLBACK and COMMIT.

11. Subqueries

Example: Subquery in WHERE Clause

sql
Copy code
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation:

• Retrieves employees whose salary is above the average salary.

12. Functions

Example: Using SQL Functions

sql
Copy code
SELECT UPPER(first_name), LENGTH(first_name)
FROM employees;

Explanation:

• UPPER: Converts text to uppercase.
• LENGTH: Returns the length of a string.

Conclusion

Understanding SQL fundamentals is essential for working with PL/SQL and databases effectively. Start with basic commands, practice frequently, and progressively move towards more complex queries and concepts. SQL serves as the backbone for data manipulation and is integral to database programming.