Module 4

Perfect 👍 we’ve covered:
✅ Module 1: .NET Core & ASP.NET
✅ Module 2: ASP.NET MVC
✅ Module 3: Web API

Now let’s move to:

---

✅ Module 4: SQL

4.1 What is SQL?

• SQL (Structured Query Language) is used to manage and interact with Relational Databases (RDBMS).

• Supported by DBMS like SQL Server, MySQL, Oracle, PostgreSQL.

---

4.2 SQL Categories

1. DDL (Data Definition Language) → defines structure

   • CREATE, ALTER, DROP, TRUNCATE

   • Example:

     CREATE TABLE Employees(
        Id INT PRIMARY KEY,
        Name VARCHAR(50),
        Salary DECIMAL(10,2)
     );
     

2. DML (Data Manipulation Language) → modifies data

   • INSERT, UPDATE, DELETE

   • Example:

     INSERT INTO Employees VALUES (1,'John',50000);
     UPDATE Employees SET Salary=60000 WHERE Id=1;
     DELETE FROM Employees WHERE Id=1;
     

3. DQL (Data Query Language) → retrieves data

   • SELECT

   • Example:

     SELECT Name, Salary FROM Employees WHERE Salary > 40000;
     

4. DCL (Data Control Language) → access permissions

   • GRANT, REVOKE

5. TCL (Transaction Control Language) → transaction handling

   • COMMIT, ROLLBACK, SAVEPOINT

---

4.3 Keys in SQL

• Primary Key → Uniquely identifies a row.

• Foreign Key → References primary key from another table.

• Unique Key → Unique but allows 1 NULL.

• Composite Key → Combination of multiple columns.

---

4.4 Joins

Used to combine data from multiple tables.

Type	Description	Example
INNER JOIN	Returns matching rows only	Employees with Dept info
LEFT JOIN	All left + matching right	All employees + dept (if exists)
RIGHT JOIN	All right + matching left	All departments + employees
FULL JOIN	All rows from both	All employees + all departments

📌 Example:

SELECT e.Name, d.DeptName
FROM Employees e
INNER JOIN Departments d ON e.DeptId = d.Id;

---

4.5 Aggregate Functions

• COUNT() → Number of rows

• SUM() → Total

• AVG() → Average

• MAX() → Maximum value

• MIN() → Minimum value

Example:

SELECT DeptId, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptId;

---

4.6 Subqueries

• Query inside another query.

SELECT Name FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

---

4.7 Stored Procedure vs Function

• Stored Procedure: Can perform DML (Insert, Update, Delete), return multiple values.

• Function: Returns a single value or table, can be used in SELECT.

Example Stored Procedure:

CREATE PROCEDURE GetEmployeesByDept @DeptId INT
AS
BEGIN
   SELECT * FROM Employees WHERE DeptId=@DeptId;
END;

---

4.8 Normalization

• Organizing data to reduce redundancy.

• Forms:

  • 1NF → Atomic values (no repeating groups).

  • 2NF → Remove partial dependency.

  • 3NF → Remove transitive dependency.

---

📌 Summary for Module 4:

• Revise DDL, DML, Joins, Aggregate functions.

• Be able to write CRUD queries.

• Know Stored Procedures, Functions, Normalization.